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)

时间： 2024-10-12 19:35:48

MySQL修改复制用户及密码的相关文章

Linux学习之CentOS(二十二)--进入单用户模式下修改Root用户的密码_Linux

在上一篇随笔里面详细讲解了Linux系统的启动过程.,我们知道Linux系统的启动级别一共有6种级别,通过 /etc/inittab 这个文件我们就能看到: [root@xiaoluo ~]# cat /etc/inittab # inittab is only used by upstart for the default runlevel. # # ADDING OTHER CONFIGURATION HERE WILL HAVE NO EFFECT ON YOUR SYSTEM. # #

三种方法修改MySQL中一个用户的密码

在MySQL中修改一个用户(比如叫"hunte")的密码,可以用如下3个办法: #在控制台上输入 bash$ mysql -u root mysql #用mysql客户程序 mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='hunte'; mysql> FLUSH PRIVILEGES; mysql> QUIT #在控制台上输入 bash$ mysql -u r

如何重设MYSQL数据库ROOT用户的密码

一.在已知MYSQL数据库的ROOT用户密码的情况下,修改密码的方法: 1.在SHELL环境下,使用mysqladmin命令设置:mysqladmin –u root –p password "新密码"   回车后要求输入旧密码 2.在mysql>环境中,使用update命令,直接更新mysql库user表的数据: Update mysql.user  set  password=password('新密码')  where  user='root'; flush   privi

如何修改mysql中root用户的密码

此环境测试为Linux系统,对于Windows系统,可直接进入到myql的命令行模式进行操作 通过命令行登录管理MySQL服务器(提示输入密码时直接回车): 第一步,登陆mysql [root@localhost bin]# /usr/local/webserver/mysql/bin/http://www.aliyun.com/zixun/aggregation/33.html">mysql -u root -p -S /tmp/mysql.sockEnter password: Wel

CentOS6.3安装MySQL5.6.10并修改MySQL的root用户密码

环境:CentOS 6.3 最小化缺省安装,配置好网卡. 安装MySQL前,确认Internet连接正常,以便下载安装文件. 先使用 yum -y update 指令升级系统到最新版本. 本安装将MySQL的数据文件与执行文件分离,如果你打算设置到不同的路径,注意修改对应的执行命令和数据库初始化脚本. # 修改防火墙设置,打开3306端口 vi /etc/sysconfig/iptables -A INPUT -m state --state NEW -m tcp -p tcp --dport

CentOS 6.5 安装 MySQL 5.6.17 并修改MySQL的root用户密码

单机搭建CentOS-6.5安装MySQL 5.6.17并修改MySQL的root用户密码 Mysql 5.5以后使用了CMake进行安装,参考与以前的区别请参考: http://www.blogjava.net/kelly859/archive/2012/09/04/387005.html   接上文. 1:下载:当前mysql版本到了5.6.17 wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.17.tar.gz 2:必

如何修改遗失的MySQL的ROOT用户密码

如果忘记了 MySQL 的 root 密码,可以用以下方法重新设置: 1. KILL掉系统里的MySQL进程: 2. 用以下命令启动MySQL,以不检查权限的方式启动: mysqld_safe -skip-grant-tables & 3. 然后用空密码方式使用root用户登录 MySQL: mysql -u root 4. 修改root用户的密码: mysql> update mysql.user set password=PASSWORD('新密码') where User='root'

MySQL修改用户的密码(SET PASSWORD)的例子

MySQL修改用户密码时使用SET PASSWORD命令,基本的使用方法如下. SET PASSWORD = PASSWORD('some password') SET PASSWORD FOR user = PASSWORD('some password') 第一个命令是修改当前用户的密码,第二个命令是修改指定用户的密码.在这里重要的是使用PASSWORD函数加密指定的密码之后保存到数据库里. 需要修改密码,最简单的方法是: 1.用root登录mysql:mysql -u root -p ro

Linux中mysql修改用户名密码

忘记了MySQL的root密码,可以用以下方法重新设置: 1. KILL掉系统里的MySQL进程:  代码如下 复制代码 killall -TERM mysqld 2. 用以下命令启动MySQL,以不检查权限的方式启动:  代码如下 复制代码 safe_mysqld –skip-grant-tables & 3. 然后用空密码方式使用root用户登录 MySQL:  代码如下 复制代码 mysql -u root 4. 修改root用户的密码:  代码如下 复制代码 mysql> updat