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 CHANGE MASTER TO changes the parameters that the slave server uses for connecting to the master server, for reading the master binary log, and reading the slave relay log. It also updates the contents of the master info and relay log info repositories (see Section 16.2.2, “Replication Relay and Status Logs”). To use CHANGE MASTER TO, the slave replication threads must be stopped (use STOP SLAVE if necessary). In MySQL 5.6.11 and later, gtid_next [2060] must also be set to AUTOMATIC (Bug #16062608).

Options not specified retain their value, except as indicated in the following discussion. Thus, in most cases, there is no need to specify options that do not change. For example, if the password to connect to your MySQL master has changed, you just need to issue these statements to tell the slave about the new password:

STOP SLAVE; -- if replication was running CHANGE MASTER TO MASTER_PASSWORD='new3cret'; START SLAVE; -- if you want to restart replication

MASTER_HOST, MASTER_USER, MASTER_PASSWORD, and MASTER_PORT provide information to the slave about how to connect to its master:

Note: Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

Setting MASTER_HOST='' (that is, setting its value explicitly to an empty string) is not the same as not setting MASTER_HOST at all. Beginning with MySQL 5.5, trying to set MASTER_HOST to an empty string fails with an error. Previously, setting MASTER_HOST to an empty string caused START SLAVE subsequently to fail. (Bug #28796)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏运维小白

17.4 配置从

主从配置 - 从上操作 安装mysql 查看my.cnf,配置server-id=132,要求和主不一样 修改完配置文件后,启动或者重启mysqld服务 把主上...

1986
来自专栏1分钟快速搭建MySQL主从数据库

1分钟快速搭建wwwhj8828com18088049999MySQL主从数据库

mysql-utilities工具集是一个集中了多种工具的合集,可以理解为是DBA的工具箱,本文介绍利用其中的mysqlreplicate工具来快速搭建MySQ...

3175
来自专栏IMWeb前端团队

使用Xposed强制androidwebView开启debug模式

本文作者:IMWeb 袁飞翔 原文出处:IMWeb社区 未经同意,禁止转载 从 https://developer.chrome.com/devtoo...

3408
来自专栏数据和云

例证MySQL GTID与MariaDB GTID的不同之处

GTID是全称是Global Transaction Identifier,可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识...

1072
来自专栏一个会写诗的程序员的博客

使用Xposed强制android WebView开启debug模式使用Xposed强制android WebView开启debug模式Xposed前期工作

从 https://developer.chrome.com/devtools/docs/remote-debugging 我们可以知道在android 4.4...

2692
来自专栏耕耘实录

MySQL数据库的主从同步配置

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

1381
来自专栏一个会写诗的程序员的博客

Xposed: 勾住(Hook) Android应用程序对象的方法,实现AOPXposed参考文档:

Xposed能够勾住(Hook) Android应用程序对象的方法,实现AOP,一个简单的例子:

955
来自专栏工科狗和生物喵

【Hadoop学起来】分布式Hadoop的搭建(Ubuntu 17.04)

正文之前 作为一个以后肯定要做大数据的人,至今还没玩过Java 和 Hadoop 会不会被老师打死?所以就想着,在我的国外的云主机上搭建个Hadoop ,以后在...

4034
来自专栏耕耘实录

MySQL数据库的主主同步配置

版权声明:本文为耕耘实录原创文章,各大自媒体平台同步更新。欢迎转载,转载请注明出处,谢谢

1543
来自专栏乐沙弥的世界

基于mysqldump搭建gtid主从

有关知识点参考: 配置MySQL GTID 主从复制 基于mysqldump快速搭建从库 使用mysqldump导出数据库

750

扫码关注云+社区