MariaDB 主从环境维护及主从库角色切换说明 -- cnDBA.cn

在上一节我们看了主从复制的搭建，这里看下主从复制的状态查看及切换：
MariaDB 主从复制搭建手册
http://www.cndba.cn/dave/article/2659

1 查看从服务器状态

为了防止复制过程中出现故障从而导致复制进程停止，需要经常检查从服务器的复制状态。一般使用show slave status 命令来检查，如下例所示：

MariaDB [cndba]> show slave status/G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.2
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 969
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1139
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 969
              Relay_Log_Space: 1442
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

在显示的这些信息中，我们主要关心“Slave_IO_Running”和“Slave_SQL_Running”这两个进程状态是否是“yes”，这两个进程的含义分别如下。http://www.cndba.cn/dave/article/2660

1）Slave_IO_Running：此进程负责从服务器（Slave）从主服务器（Master）上读取BINLOG日志，并写入从服务器上的中继日志中。
2）Slave_SQL_Running：此进程负责读取并且执行中继日志中的BINLOG 日志。

只要其中有一个进程的状态是no，则表示复制进程停止，错误原因可以从“Last_Errno”字段的值中看到。除了查看上面的信息，用户还可以通过这个命令了解从服务器的配置情况以及当前和主服务器的同步情况，包括指向那个主服务器，主服务器的端口，复制使用的用户，当前日志恢复到的位置等，这些信息都是记录在从服务器这一端的，主服务器上并没有相应的信息。http://www.cndba.cn/dave/article/2660

MariaDB [cndba]> show processlist;
+----+-------------+-----------+-------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db    | Command   | Time  | State                                                                       | Info             | Progress |
+----+-------------+-----------+-------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
|  1 | system user |           | NULL  | Daemon    |  NULL | InnoDB purge coordinator                                                    | NULL             |    0.000 |
|  2 | system user |           | NULL  | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  3 | system user |           | NULL  | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  4 | system user |           | NULL  | Daemon    |  NULL | InnoDB purge worker                                                         | NULL             |    0.000 |
|  5 | system user |           | NULL  | Daemon    |  NULL | InnoDB shutdown handler                                                     | NULL             |    0.000 |
| 11 | root        | localhost | mysql | Sleep     | 12138 |                                                                             | NULL             |    0.000 |
| 19 | system user |           | NULL  | Slave_IO  | 11097 | Waiting for master to send event                                            | NULL             |    0.000 |
| 20 | system user |           | NULL  | Slave_SQL |     4 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
| 21 | root        | localhost | NULL  | Sleep     | 11083 |                                                                             | NULL             |    0.000 |
| 22 | root        | localhost | cndba | Query     |     0 | init                                                                        | show processlist |    0.000 |
+----+-------------+-----------+-------+-----------+-------+-----------------------------------------------------------------------------+------------------+----------+
10 rows in set (0.00 sec)

2 主从服务器同步维护

在某些繁忙的OLTP(在线事务处理)系统上，由于主服务器更新频繁，而从服务器由于各种原因（比如硬件性能较差）导致更新速度较慢，从而使得主从服务器之间的数据差距越来越大，最终对某些应用产生影响。
在这种情况下，可以定期地进行主从服务器的数据同步，使得主从数据差距能够减到最小。常用的方法是：在负载较低的时候暂时阻塞主数据库的更新，强制主从数据库更新同步。具体操作步骤如下。

（1）在主服务器上，执行以下语句（注意，会阻塞主数据库的所有更新操作）：

MariaDB [cndba]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

MariaDB [cndba]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1156 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记录SHOW 语句的输出的日志名和偏移量，这些是从服务器复制的目的坐标。

（2）在从服务器上，执行下面语句，其中MASTER_POS_WAIT()函数的参数是前面步骤中得到的复制坐标值：

MariaDB [cndba]> select master_pos_wait('mysql-bin.000004','1156');
+--------------------------------------------+
| master_pos_wait('mysql-bin.000004','1156') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

这个SELECT 语句会阻塞直到从服务器达到指定的日志文件和偏移量后，返回0，如果返回-1，则表示超时退出。查询返回0 时，则从服务器与主服务器同步。http://www.cndba.cn/dave/article/2660

（3）在主服务器上，执行下面的语句允许主服务器重新开始处理更新：

MariaDB [(www.cndba.cn/dave)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

3 切换主从库

首先要保证主从同步是正常的。然后在进行切换操作。

3.1 在主库创建同步用户

MariaDB [mysql]> grant replication slave on *.* to 'slave'@'%' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)

3.2 查询从库状态

MariaDB [cndba]> show slave status/G

3.3 查询主库状态

MariaDB [mysql]> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)

3.4 停止 IO_THREAD 线程(从库上操作)

MariaDB [(www.cndba.cn/dave)]> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(www.cndba.cn/dave)]> show slave status/G

3.5 激活从库(从库上操作)

MariaDB [(www.cndba.cn/dave)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(www.cndba.cn/dave)]> reset master;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(www.cndba.cn/dave)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(www.cndba.cn/dave)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+
1 row in set (0.00 sec)

MariaDB [(www.cndba.cn/dave)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

备注：
reset slave all 命令会删除从库的 replication 参数，之后 show slave status/G 的信息返回为空。

3.6 将原来主库变为从库

MariaDB [mysql]> change master to master_host='192.168.56.3',master_user='slave',master_password='slave',master_log_file='mysql-bin.000001',master_log_pos=328;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> show slave status/G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.3
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 328
               Relay_Log_File: Dave-relay-bin.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 328
              Relay_Log_Space: 863
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
1 row in set (0.00 sec)

备注：
因为我们原来的主备是同步状态的，所以不需要进行数据的初始化，直接配置新的同步即可。 http://www.cndba.cn/dave/article/2660

3.7 验证同步

新主库：

MariaDB [cndba]> select * from dave;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
|    2 | www.zhixintech.cc |
|    2 | www.zhixintech.cc |
+------+-------------------+
3 rows in set (0.00 sec)

MariaDB [cndba]> delete from dave where id =2;
Query OK, 2 rows affected (0.01 sec)

MariaDB [cndba]> select * from dave;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

新从库：

MariaDB [mysql]> use cndba
Database changed
MariaDB [cndba]> select * from dave;
+------+-------------------+
| id   | name              |
+------+-------------------+
|    1 | www.cndba.cn/dave |
+------+-------------------+
1 row in set (0.00 sec)

同步成功，切换主从完成。 http://www.cndba.cn/dave/article/2660 http://www.cndba.cn/dave/article/2660

4 直接激活从库

如果主从环境主机已经宕机了，那么直接激活从库即可：http://www.cndba.cn/dave/article/2660

MariaDB [(www.cndba.cn/dave)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(www.cndba.cn/dave)]> reset master;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(www.cndba.cn/dave)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)

然后将从库IP地址改为主库IP地址，在测试应用连接。http://www.cndba.cn/dave/article/2660

签到成功

CNDBA社区