pay db:
jrhdpt11:/root/sbin# mysql -uroot -p'R00t,uHagt.0511' -e "show processlist" | grep backup
Warning: Using a password on the command line interface can be insecure.
832734 backup 121.43.153.147:37873 NULL Binlog Dump 129384 Master has sent all binlog to slave; waiting for binlog to be updated NULL
832752 backup dr-mysql01.zjcap.com:38021 NULL Binlog Dump 129321 Master has sent all binlog to slave; waiting for binlog to be updated NULL
mysql> show grants for backup@'%';
+-------------------------------------------------------------------------------------------------------------------+
| Grants for backup@% |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY PASSWORD '*93CCED77A19FA43FCA335E99889F351FABDA1320' |
+-------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
ZJZC数据库从120.26.57.101:
jrhdpt02:/root# set -o vi
jrhdpt02:/root# mysql -ubackup -pxxxxxxxx -h121.43.153.146
Warning: Using a password on the command line interface can be insecure.
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'121.43.153.147' identified by 'xxxxxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'115.236.160.82' identified by 'xxxxxxxx';
mysql> delete from mysql.user where user='backup' and host='%';
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql> select host,user from mysql.user where user='backup';
+----------------+--------+
| host | user |
+----------------+--------+
| 115.236.160.82 | backup |
| 121.43.153.147 | backup |
+----------------+--------+
2 rows in set (0.00 sec)
ZJZC数据库从120.26.57.101,已经连接不上
jrhdpt02:/root# mysql -ubackup -pxxxxxxxx -h121.43.153.146
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'backup'@'120.26.57.101' (using password: YES)
mysql> show grants for backup@'115.236.160.82';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@115.236.160.82 |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'115.236.160.82' IDENTIFIED BY PASSWORD '*93CCED77A19FA43FCA335E99889F351FABDA1320' |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for backup@'121.43.153.147';
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for backup@121.43.153.147 |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'backup'@'121.43.153.147' IDENTIFIED BY PASSWORD '*93CCED77A19FA43FCA335E99889F351FABDA1320' |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql 主从复制用户权限限制
最新推荐文章于 2023-04-10 16:30:34 发布