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 repl@192.168.1.177 | +----------------------------------------------------------------------------------------------------------------+ | 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 'repl@192.168.1.177: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 : 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

www.idc126.com

原文地址:https://www.77169.com/classical/HTML/196422.shtm

0

发表评论