mysql修改复制账号密码_MySQL修改复制用户及密码

在生产环境中有时候需要修改复制用户账户的密码，比如密码遗失，或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导致主从不一致而需要进行修复。本文描述了修改复制账户密码以及变更复制账户。

1、更改复制账户密码

--演示环境，同一主机上的2个实例，主3406，从3506 --当前版本，注：master账户表明是对主库进行相关操作，slave则是对从库进行相关操作 master@localhost[(none)]> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.12-log | +---------------+------------+ --主库上的记录 master@localhost[test]> select * from tb1; +------+-------+ | id | name | +------+-------+ | 1 | robin | +------+-------+ --从库上的记录 slave@localhost[test]> select * from tb1; +------+-------+ | id | name | +------+-------+ | 1 | robin | +------+-------+ --当前从库上的状态信息 slave@localhost[test]> show slave status/G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 Read_Master_Log_Pos: 3296006 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 811 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test,sakila --仅复制了test以及sakila

2、更换复制账户及密码

master@localhost[test]> GRANT REPLICATION SLAVE ON *.* TO 'repl2'@'192.168.1.177' IDENTIFIED BY 'Repl2'; Query OK, 0 rows affected (0.00 sec) slave@localhost[test]> stop slave; Query OK, 0 rows affected (0.28 sec) master@localhost[test]> insert into tb1 values(3,'jack'); Query OK, 1 row affected (0.00 sec) slave@localhost[test]> change master to -> MASTER_USER='repl2', -> MASTER_PASSWORD='Repl2'; Query OK, 0 rows affected, 2 warnings (0.01 sec) slave@localhost[test]> system more /data/inst3506/data3506/master.info 23 inst3406bin.000001 3294834 192.168.1.177 repl2 Repl2 3406 .......... slave@localhost[test]> start slave; Query OK, 0 rows affected (0.01 sec) slave@localhost[test]> select * from tb1 where id=3; +------+------+ | id | name | +------+------+ | 3 | jack | +------+------+ 1 row in set (0.00 sec) slave@localhost[(none)]> show slave status /G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.177 Master_User: repl2 Master_Port: 3406 Connect_Retry: 60 Master_Log_File: inst3406bin.000001 --Author :Leshami Read_Master_Log_Pos: 3296871 --Blog : http://blog.csdn.net/leshami Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 501 Relay_Master_Log_File: inst3406bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test,sakila

3、关于change master

Options not specified retain their value, except as indicated in the following discussion. Thus, in most

STOP SLAVE; -- if replication was running

MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the

Note: Replication cannot use Unix socket files. You must be able to connect to the

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master

Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as