MySQL修改复制用户及密码

文章由LinuxBoy分享于2019-03-31 03:03:10热评（110）

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数据库
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 3296006
Relay_Log_Space: 978

--主库上复制账户的信息
master@localhost[test]> show grants for 'repl'@'192.168.1.177';
+----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected] |
+----------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY PASSWORD '*A424E797037BF191C5C2038C039' |
+----------------------------------------------------------------------------------------------------------------+

--修改复制账户密码
master@localhost[test]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.177' IDENTIFIED BY 'replpwd';

--如下查询密码已更改
master@localhost[test]> select user,host,password from mysql.user where user='repl';
+------+---------------+-------------------------------------------+
| user | host | password |
+------+---------------+-------------------------------------------+
| repl | 192.168.1.177 | *4A04E4FD524292A79E3DCFEBBD46094478F178EF |
+------+---------------+-------------------------------------------+

--更新记录
master@localhost[test]> insert into tb1 values(2,'fred');

--重库上可以查询到刚刚被更新的记录
slave@localhost[test]> select * from tb1;
+------+-------+
| id | name |
+------+-------+
| 1 | robin |
| 2 | fred |
+------+-------+

slave@localhost[test]> stop slave;
Query OK, 0 rows affected (0.02 sec)

slave@localhost[test]> start slave;
Query OK, 0 rows affected (0.01 sec)

--再次查看状态出现了错误提示
slave@localhost[test]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.177
Master_User: repl
Master_Port: 3406
Connect_Retry: 60
Master_Log_File: inst3406bin.000001
Read_Master_Log_Pos: 3296438
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1243
Relay_Master_Log_File: inst3406bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB: test,sakila
....................
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:3406' - retry-time: 60 retries: 1

--更改重库连接密码，该信息记录在从库master.info文件中
slave@localhost[test]> stop slave;

slave@localhost[test]> change master to
-> master_user='repl',
-> master_password='replpwd';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

--修改密码后，从库状态正常，以下检查结果不再列出
slave@localhost[test]> start slave;

--查看master.info，密码已更改且为名文
slave@localhost[(none)]> system grep repl /data/inst3506/data3506/master.info
repl
replpwd

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 :

推荐文章：

MySQL修改复制用户及密码