修改复制用户密码

修改复制用户密码

• 在备库查询当前配置的主库密码

select * from  mysql.slave_master_info\G

root@3309.sock:(none)>select * from  mysql.slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000002
        Master_log_pos: 283532411
                  Host: 192.192.192.192
             User_name: repl
         User_password: mysql
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 5
                  Bind:
    Ignored_server_ids: 0
                  Uuid: 9a23ade1-b476-11ea-8fe6-000c29f430c1
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 1
          Channel_name:
           Tls_version:
1 row in set (0.04 sec)

root@3309.sock:(none)>
root@3309.sock:(none)>

• 主库修改复制用户密码

alter user repl@'192.168.%' identified by 'xxxx';
或者
alter user repl@'192.169.100.50' identified by 'xxxx';
alter user repl@'192.169.100.51' identified by 'xxx';

• 备库更新配置的复制用户密码

stop slave;
change master to
master_host='ip',
MASTER_PORT=3306,
master_user='repl',
master_password='xxx';

如果报错，可以在error日志中查找到对应的二进制日志文件和位点信息：
最后在错误日志中找到如下位点信息，这是我操作之后报的第一个位点信息。

change master to
master_host='ip',
MASTER_PORT=3309,
master_user='repl',
master_password='xxxx',
MASTER_LOG_FILE = 'mysql-bin.003762',
MASTER_LOG_POS = 282213447;


如果是gtid模式：
记录gtid信息
Retrieved_Gtid_Set: dc2006d2-82aa-11ea-81f4-000c29034b31:10-12
Executed_Gtid_Set: 2851878c-82b5-11ea-9276-000c2963c787:1-11,
dc2006d2-82aa-11ea-81f4-000c29034b31:1-12

设置位点信息：
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED='dc2006d2-82aa-11ea-81f4-000c29034b31:10-12';
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

备库同步密码：
change master to
master_host='ip',
MASTER_PORT=3307,
master_user='repl',
master_password='xxxx',
master_auto_position=1;

• 备库验证配置

select Host,User_name,User_password from  mysql.slave_master_info;

start slave ;

show slave status\G

双主

主库执行
change master to
master_host='ip',
MASTER_PORT=3307,
master_user='repl',
master_password='xxxx';

备库执行
change master to
master_host='ip',
MASTER_PORT=3307,
master_user='repl',
master_password='xxxx';