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

在生产环境中有时候需要修改复制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)

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：php中文网