二十四、Mysql-主从复制

1、介绍

随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改造，MYSQL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来拆分库、拆分表从而进行优化。
MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL主从复制的目的是实现数据库冗余备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用性。

2、原理

Mysql主从复制集群至少需要2台数据库服务器，其中一台为Master库，另外一台为Slave库，MYSQL主从数据同步是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句，整个过程需要开启3个线程，分别是Master开启IO线程，Slave开启IO线程和SQL线程，具体主从同步原理详解如下：
 Slave上执行slave start，Slave IO线程会通过在Master创建的授权用户连接上至Master，并请求master从指定的文件和位置之后发送bin-log日志内容；
 Master接收到来自slave IO线程的请求后，master IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。
 返回的信息中除了bin-log日志内容外，还有master最新的binlog文件名以及在binlog中的下一个指定更新position点；
 Slave IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求；
 Slave Sql线程检测到relay-log中内容有更新，会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句，将解析的SQL语句并在Slave里执行，执行成功后，Master库与Slave库保持数据一致。

3、实战

3.1 准备虚拟机两台，一台作主库、一台作从库

master:10.0.0.134
slave:10.0.0.135

3.2 配置master端

# 安装mariadb/mariadb-server
yum  install mariadb mariadb-server -y

# 修改配置文件 
[root@localhost /]# vim /etc/my.cnf
#在[mysqld]字段下添加以下内容：
log-bin=my-bin
server-id =1

# 重启服务： 
[root@localhost /]#systemctl restart mariadb 

#登录
[root@localhost /]# mysql

#查看二进制功能状态
MariaDB [(none)]> show variables like "%log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
| sql_log_bin   | ON    |
+---------------+-------+
2 rows in set (0.00 sec)

#授权从库，授权my用户从10.0.0.135通过123456密码登录服务器来复制所有数据库所有表格.若授权ip段可使用10.0.0.%
MariaDB [(none)]> grant replication slave on *.* to "my"@"10.0.0.135" identified by "123456";
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

#查看master端状态
MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| my-bin.000003 |      469 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

3.3 配置slave端

# 安装mariadb/mariadb-server
yum  install mariadb mariadb-server -y

# 修改配置文件 
[root@localhost /]# vim /etc/my.cnf
#在[mysqld]字段下添加以下内容：
server-id =2

# 重启服务： 
[root@localhost /]#systemctl restart mariadb 

#登录
[root@localhost /]# mysql

#指定主库，master_log_file和master_log_pos为上面master状态
MariaDB [(none)]> change master to  master_host="10.0.0.134", master_user="my", master_password="123456", master_log_file="my-bin.000003", master_log_pos=469;

#启动slave进程
MariaDB [(none)]> slave start;

#查看同步状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event    I/O线程连接Master状态
                  Master_Host: 10.0.0.138
                  Master_User: my         用于连接Master的用户
                  Master_Port: 3306		 				Master端监听端口
                Connect_Retry: 60	 				主从连接失败，重试时间间隔
              Master_Log_File: my-bin.000003					I/O线程读取的Master二进制日志文件的名称
          Read_Master_Log_Pos: 915                  I/O线程已读取的Master二进制日志文件的位置
               Relay_Log_File: mariadb-relay-bin.000002					SQL线程读取和执行的中继日志文件的名称
                Relay_Log_Pos: 526					SQL线程已读取和执行的中继日志文件的位置
        Relay_Master_Log_File: my-bin.000003 			SQL线程执行的Master二进制日志文件的名称
             Slave_IO_Running: Yes				    I/O线程是否被启动并成功地连接到主服务器上
            Slave_SQL_Running: Yes				SQL线程是否被启动
              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					    SQL_SLAVE_SKIP_COUNTER设置的值
          Exec_Master_Log_Pos: 915
              Relay_Log_Space: 822
              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			    Slave端SQL线程和I/O线程之间的时间差距，单位为秒，常被用于主从延迟检查方法之一
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
1 row in set (0.00 sec)

#查看Slave端IO线程、SQL线程状态均为YES，代表Slave已正常连接Master实现同步
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

#常见问题分析
Slave_IO_Running: Connecting 
# 第一种：主库宕机 
# 第二种：从库指定的用户名与密码错误（与主库授权的用户名和密码不一致） 
# 第三种：关闭防火墙

Slave_IO_Running: No 
# 从库指定的二进制文件有误

Slave_SQL_Running: No 
# pos点问题

至此，mysql主从配置完成，主库进行增、删、改操作从库均会进行同步。

4、常见问题分析

Mysql主从同步集群在生成环境使用过程中，如果主从服务器之间网络通信条件差或者数据库数据量非常大，容易导致MYSQL主从同步延迟。
MYSQL主从产生延迟之后，一旦主库宕机，会导致部分数据没有及时同步至丛库，重新启动主库，会导致丛库与主库同步错误，如何快速恢复主从同步关系呢，如下有两种方法：

（1） 忽略错误后，继续同步:
此种方法适用于主从库数据内容相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况。
Master端执行如下命令，将数据库设置全局读锁，不允许写入新数据：

flush  tables  with  read  lock;

Slave端停止Slave I/O及sql线程，同时将同步错误的SQL跳过1次，跳过会导致数据不一致，最后启动start slave，同步状态恢复，命令如下：

stop slave;
set  global sql_slave_skip_counter =1;
start slave;

（2） 重新做主从同步，完全同步：
此种方法适用于主从库数据内容相差很大，或者要求数据完全统一的情况，数据需完全保持一致。
Master端执行如下命令，将数据库设置全局读锁，不允许写入新数据：

flush  tables  with  read  lock;

Master端基于mysqldump、xtrabackup工具进行数据库将完整的数据库备份，也可以用shell脚本或python脚本实现定时备份，备份成功之后，将完整的数据导入至从库，重新配置主从关系，当Slave端的IO线程、SQL线程均为YES之后，最后将Master端读锁解开即可，解锁命令如下：

unlock tables;