一、用户管理

1、创建用户

#任何ip的客户端都可以访问createuser'tester'@'%'identifiedby'123456';#只有本地的客户端才可以访问createuser'tester'@'localhost'identifiedby'123456';#只有指定的192.168.1.90这个ip才可以访问createuser'tester'@'192.168.1.90'identifiedby'123456';

2、修改用户

(1)修改密码

updatemysql.usersetauthentication_string=password('新密码')whereuser='tester'andhost='localhost';#5.7版本使用updatemysql.usersetpassword=password('新密码')whereuser='tester'andhost='localhost';#5.6版本使用

(2)修改host

updatemysql.usersethost='192.168.1.100'whereuser='tester';

(3)修改用户后需要刷新硬盘或重启数据库才生效；其中刷新硬盘需要有reload权限

GRANTreloadON*.*to'root'@'%';

(4)删除用户

DELETEFROMmysql.userWHEREuser='tester'andhost='localhost';

(5)忘记密码的重置流程

1)在/etc/my.cnf 加入skip-grant-tables跳过授权表

2)重启MySQL，后无密码登录

3)修改密码

updatemysql.usersetauthentication_string=password('root')whereuser='root';

4)在/etc/my.cnf删除skip-grant-tables

5)重启MySQL,输入密码进入

二、权限管理

1、权限授予

(1)查询用户权限

SHOWGRANTSFORtester;SELECT*FROMmysql.userWHEREuser='tester'\G

(2)授予权限

GRANTALLPRIVILEGESON*.*TO'tester'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;

(3)撤销权限

REVOKEALLPRIVILEGESON*.*FROM'tester'@'%';

2、账号权限体系

(1)服务级用户权限

GRANTALLPRIVILEGESON*.*TO'tester'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;

(2)数据库级用户权限

GRANTALLPRIVILEGESONstaff.*TO'tester'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;

(3)表级用户权限

GRANTALLPRIVILEGESONstaff.employeeTO'tester'@'%'IDENTIFIEDBY'123456'WITHGRANTOPTION;

(4)字段级用户权限

三、表空间管理(InnoDB)

(1)共享表空间(默认)

数据和文件都存放在data目录下的ibdata1文件里，多数据共用一个。

查看共享表空间大小和存放路径：

showvariableslike'innodb_data%';

(2)独占表空间

每个数据库都有自己的文件夹和文件

.frm保存元数据，表结构，表结构的定义

.ibd存放数据和索引文件

查看value为ON，则开启了独占表空间：

showvariableslike'innodb_file_per_table';

四、备份与还原

1、备份数据

热备份：进行备份时，数据库的读写操作不受影响

温备份：进行备份时，数据库的读操作可以进行，但不能进行写操作

冷备份：进行备份时，数据库不可以进行读写操作

2、备份命令

#全库备份mysqldump--single-transaction-uroot-p123456-A>all_201810911.sql#备份数据库staffmysqldump--single-transaction-uroot--password=123456staff>E:\mysql_bak\staff_20180729.sql#备份192.168.1.90服务器上的数据库staffmysqldump--single-transaction--opt-uroot--password=123456-h292.168.1.90staff>E:\mysql_bak\staff_20180729.sql#备份数据库staff的员工表employeemysqldump--single-transaction-uroot--password=123456staffemployee>E:\mysql_bak\staff_20180729.sql

3、还原数据库

mysql-uroot-p123456<all_201810911.sqlmysql-uroot-p123456staff<staff_201810911.sql

或还原数据表

sourceE:\mysql_bak\201807\staff_20180729.sql;

五、主从同步

主数据库配置(可读可写)

server-id=1#主库和从库需要不一致log-bin=mysql-bin#开启log-bin二进制日志文件binlog-do-db=db_test#需要同步的数据库binlog-ignore-db=staff#不需要同步的数据库

从数据库配置

server-id=2#主库和从库需要不一致log-bin=mysql-bin#开启log-bin二进制日志文件replicate-do-db=db_test#需要同步的数据库read_only#为保证数据库的数据一致性，从数据库只允许读取操作，不允许写操作

六、查询所有数据库占用磁盘空间大小

selectTABLE_SCHEMA,concat(truncate(sum(data_length)/1024/1024,2),'MB')asdata_size,concat(truncate(sum(index_length)/1024/1024,2),'MB')asindex_sizefrominformation_schema.tablesgroupbyTABLE_SCHEMAORDERBYdata_sizedesc;#orderbydata_lengthdesc;