MySQL修改复制密码后。。。

来源:转载

问题背景:


最近在测试ProxySQL+MHA实现MySQL读写分离+高可用,发现用于复制的密码忘记了,于是乎。。。

操作步鄹:


1、首先在主库修改用于复制的账号密码


[email protected]:mysql.sock11:15:15 [(none)]>set password for 'replica_user'@'%'=password('654321');
Query OK, 0 rows affected, 1 warning (0.00 sec)

2、在从库执行


[email protected]:mysql.sock04:50:09 [tom]>stop slave;
Query OK, 0 rows affected (0.01 sec)
[email protected]:mysql.sock04:50:21 [tom]>change master to master_host='10.0.0.6', master_user='replica_user', master_password='654321';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
[email protected]:mysql.sock04:50:33 [tom]>start slave;
Query OK, 0 rows affected (0.01 sec)

如果是线上环境就比较悲剧了,为什么会这样呢?


Last_SQL_Errno: 1007
Last_SQL_Error: Error 'Can't create database 'tom'; database exists' on query. Default database: 'tom'. Query: 'create database tom'

看看官方的解释:https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html


问题解决:


1、常见操作


[email protected]:mysql.sock11:44:34 [tom]>stop slave;
Query OK, 0 rows affected (0.01 sec)
[email protected]:mysql.sock11:44:38 [tom]>change master to master_user='replica_user', master_password='654321';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
[email protected]:mysql.sock11:44:46 [tom]>start slave;
Query OK, 0 rows affected (0.02 sec)
[email protected]:mysql.sock11:44:49 [tom]>show slave status/G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

2、你也可以偷个懒


[email protected]:mysql.sock05:19:12 [tom]>stop slave;
Query OK, 0 rows affected (0.01 sec)
[email protected]:mysql.sock05:19:17 [tom]>change master to master_password='123456';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
[email protected]:mysql.sock05:19:38 [tom]>start slave;
Query OK, 0 rows affected (0.01 sec)
[email protected]:mysql.sock05:19:41 [tom]>show slave status/G
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
......

3、或者你也可以


[email protected]:mysql.sock11:52:34 [tom]>stop slave;
Query OK, 0 rows affected (0.00 sec)
[email protected]:mysql.sock11:52:39 [tom]>change master to master_host='xxxx',
-> master_port=xxxx,
-> master_user='xxxx',
-> master_password='xxxx',
-> master_log_file='xxxx',
-> master_log_pos=xxxx;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
[email protected]:mysql.sock11:53:09 [tom]>start slave;
Query OK, 0 rows affected (0.01 sec)

为了方便大家交流,本人开通了微信公众号,和QQ群291519319。喜欢技术的一起来交流吧



分享给朋友:
您可能感兴趣的文章:
随机阅读: