MySQL管理常用的命令

一、用戶管理

1、創(chuàng)建用戶

# 任何ip的客戶端都可以訪問
create user 'tester'@'%' identified by '123456';
# 只有本地的客戶端才可以訪問
create user 'tester'@'localhost' identified by '123456';
# 只有指定的192.168.1.90這個ip才可以訪問
create user 'tester'@'192.168.1.90' identified by '123456';

2、修改用戶

(1)修改密碼

update mysql.user set authentication_string=password('新密碼') where user='tester' and host='localhost';  # 5.7版本使用
update mysql.user set password=password('新密碼') where user='tester' and host='localhost';  # 5.6版本使用

(2)修改host

update mysql.user set host='192.168.1.100' where user='tester';

(3)修改用戶后需要刷新硬盤或重啟數(shù)據(jù)庫才生效；其中刷新硬盤需要有reload權(quán)限

GRANT reload ON *.* to 'root'@'%';

(4)刪除用戶

DELETE FROM mysql.user WHERE user='tester' and host='localhost';

(5)忘記密碼的重置流程

1)在/etc/my.cnf 加入skip-grant-tables跳過授權(quán)表

2)重啟MySQL，后無密碼登錄

3)修改密碼

update mysql.user set authentication_string=password('root') where user='root';

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

5)重啟MySQL,輸入密碼進(jìn)入

二、權(quán)限管理

1、權(quán)限授予

(1)查詢用戶權(quán)限

SHOW GRANTS FOR tester;
SELECT * FROM mysql.user WHERE user='tester' \G

(2)授予權(quán)限

GRANT ALL PRIVILEGES ON *.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

(3)撤銷權(quán)限

REVOKE ALL PRIVILEGES ON *.* FROM 'tester'@'%';

2、賬號權(quán)限體系

(1)服務(wù)級用戶權(quán)限

GRANT ALL PRIVILEGES ON *.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

(2)數(shù)據(jù)庫級用戶權(quán)限

GRANT ALL PRIVILEGES ON staff.* TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

(3)表級用戶權(quán)限

GRANT ALL PRIVILEGES ON staff.employee TO 'tester'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

(4)字段級用戶權(quán)限

三、表空間管理(InnoDB)

(1)共享表空間(默認(rèn))

數(shù)據(jù)和文件都存放在data目錄下的ibdata1文件里，多數(shù)據(jù)共用一個。

查看共享表空間大小和存放路徑：

show variables like 'innodb_data%';

(2)獨占表空間

每個數(shù)據(jù)庫都有自己的文件夾和文件

.frm保存元數(shù)據(jù)，表結(jié)構(gòu)，表結(jié)構(gòu)的定義

.ibd存放數(shù)據(jù)和索引文件

查看value為ON，則開啟了獨占表空間：

show variables like 'innodb_file_per_table';

四、備份與還原

1、備份數(shù)據(jù)

熱備份：進(jìn)行備份時，數(shù)據(jù)庫的讀寫操作不受影響

溫備份：進(jìn)行備份時，數(shù)據(jù)庫的讀操作可以進(jìn)行，但不能進(jìn)行寫操作

冷備份：進(jìn)行備份時，數(shù)據(jù)庫不可以進(jìn)行讀寫操作

2、備份命令

#全庫備份
mysqldump --single-transaction -uroot -p123456 -A > all_201810911.sql
# 備份數(shù)據(jù)庫staff
mysqldump --single-transaction -uroot --password=123456 staff > E:\mysql_bak\staff_20180729.sql
# 備份192.168.1.90服務(wù)器上的數(shù)據(jù)庫staff
mysqldump --single-transaction --opt -uroot --password=123456 -h292.168.1.90 staff > E:\mysql_bak\staff_20180729.sql
# 備份數(shù)據(jù)庫staff的員工表employee
mysqldump --single-transaction -uroot --password=123456 staff employee > E:\mysql_bak\staff_20180729.sql

3、還原數(shù)據(jù)庫

mysql -uroot -p123456 < all_201810911.sql
mysql -uroot -p123456 staff < staff_201810911.sql

或還原數(shù)據(jù)表

source 
E:\mysql_bak\201807\staff_20180729.sql;

五、主從同步

主數(shù)據(jù)庫配置(可讀可寫)

server-id=1 # 主庫和從庫需要不一致
log-bin=mysql-bin # 開啟log-bin二進(jìn)制日志文件
binlog-do-db=db_test # 需要同步的數(shù)據(jù)庫
binlog-ignore-db=staff # 不需要同步的數(shù)據(jù)庫

從數(shù)據(jù)庫配置

server-id=2 # 主庫和從庫需要不一致
log-bin=mysql-bin # 開啟log-bin二進(jìn)制日志文件
replicate-do-db=db_test # 需要同步的數(shù)據(jù)庫
read_only # 為保證數(shù)據(jù)庫的數(shù)據(jù)一致性，從數(shù)據(jù)庫只允許讀取操作，不允許寫操作

六、查詢所有數(shù)據(jù)庫占用磁盤空間大小

select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
ORDER BY data_size desc;
#order by data_length desc;