# 添加超级用户 grant all privileges on *.* to 'dump_tmp'@'10.10.10.10' identified by 'dump_tmp'; grant all privileges on *.* to 'tmp'@'10.%' identified by 'tmp' with grant option; grant all privileges on *.* to 'tmp'@'127.0.0.1' identified by password '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' with grant option; # 删除超级用户 drop user dump_tmp@'10.10.10.10'; > # 其中将“123456”加密后的密码就是“*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9”，根据自己的需要来添加用户的密码。 > select password('123456'); +-------------------------------------------+ | password('123456') | +-------------------------------------------+ | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-------------------------------------------+ 1 row in set (0.00 sec) Mon Nov 25 09:44:19 2019 > ##########################################
原来的权限：
> show grants for 'glc_x'@'10.10.10.10';
+-----------------------------------------------------------------------------------------+
| Grants for glc_x@10.10.10.10 |
+-----------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `ms`.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `metadata`.* TO 'glc_x'@'10.10.10.10' |
##############
新增权限：
> grant select,insert,update,delete on `rd`.* to 'glc_x'@'10.136.26.35';
##############
现在的权限： > show grants for 'misc_cms_x'@'10.10.10.10'; +-----------------------------------------------------------------------------------------+ | Grants for misc_cms_x@10.10.10.10 | +-----------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `rd`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `ms`.* TO 'glc_x'@'10.10.10.10' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `metadata`.* TO 'glc_x'@'10.10.10.10' | +-----------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) Wed Dec 18 11:50:34 2019 >
##########################################
重新加载一下配置，才能有效（仅仅针对公司）
./load reload
##########################################
查看用户权限：
show grants for 'user'@'host';
使用超级用户在线备份数据库
mysql -udump_tmp -pdump_tmp -hXXX -P3306 -e 'show databases;' | grep -Ev 'Database|information_schema|mysql|performance_schema|sys' | xargs mysqldump --set-gtid-purged=off -udump_tmp -pdump_tmp -hXXX -P3306 --single-transaction --master-data=2 --databases > data_backup.sql pt-show-grants -udump_tmp -pdump_tmp -hXXX -P3306 >grant_backup.sql