MySQL主从同步的权限_mysql 主从复制用户权限限制

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.

832734backup121.43.153.147:37873NULLBinlog Dump129384Master has sent all binlog to slave; waiting for binlog to be updatedNULL

832752backupdr-mysql01.zjcap.com:38021NULLBinlog Dump129321Master has sent all binlog to slave; waiting for binlog to be updatedNULL

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)