mysql的权限管理

 

 

 

show create user  tmp@'10.10.10.10';

show grants for tmp@'10.10.10.10';

 

 

# 添加超级用户
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';   

 

 

回收权限：

root@xxx((none)) > 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 `metadata`.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `business`.* TO 'glc_x'@'10.10.10.10'  |
+-------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Fri Nov 27 15:51:43 2020
root@xxx((none)) > revoke INSERT, UPDATE, DELETE ON `metadata`.* from 'glc_x'@'10.10.10.10';                    
Query OK, 0 rows affected (0.00 sec)

Fri Nov 27 15:53:03 2020

Fri Nov 27 15:53:52 2020
root@xxx((none)) > revoke INSERT, UPDATE, DELETE ON `business`.* from 'glc_x'@'10.10.10.10'; 
Query OK, 0 rows affected (0.00 sec)

Fri Nov 27 15:54:02 2020
root@xxx((none)) > 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 ON `metadata`.* TO 'glc_x'@'10.10.10.10' |
| GRANT SELECT ON `business`.* TO 'glc_x'@'10.10.10.10'  |
+-------------------------------------------------------------------+
3 rows in set (0.00 sec)

Fri Nov 27 15:54:09 2020
root@xxx((none)) > 

 

 

 

 

 

 

使用超级用户在线备份数据库

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