一.数据库主从复制之MySQL半同步实现

1.简介

MySQL通过复制（Replication）实现存储系统的高可用。目前，MySQL支持的复制方式有：

1. 异步复制（Asynchronous Replication）：原理最简单，性能最好。但是主备之间数据不一致的概率很大。
2. 半同步复制（Semi-synchronous Replication）：相比异步复制，半同步复制牺牲了一定的性能，提升了主备之间数据的一致性（有一些情况还是会出现主备数据不一致）。
3. 组复制（Group Replication）：基于Paxos算法实现分布式数据复制的强一致性。只要大多数机器存活就能保证系统可用。相比半同步复制，Group Replication的数据一致性和系统可用性更高。

2.半同步复制的基本流程

MySQL半同步复制的实现是建立在MySQL异步复制的基础上的。MySQL支持两种略有不同的半同步复制：AFTER_SYNC和AFTER_COMMIT（受rpl_semi_sync_master_wait_wait_point控制）。

开启半同步复制时，Master在返回之前会等待Slave的响应或超时。当Slave超时时，半同步复制退化成异步复制。这也是MySQL半同步复制存在的一个问题

3.实现半同步需要满足如下条件

1. MySQL 5.5及以上版本
2. 变量have_dynamic_loading为YES
3. 异步复制已经存在

4.配置master服务器

1.修改主配置文件

vim /etc/my.cnf
#在[mysql]配置块下添加如下两行配置
[mysql]
log_bin               #开启二进制日志功能
server_id=1           #为当前节点设置一个全局惟一的ID号 
innodb_file_per_table #数据于表结构分离，存放在两个不同文件

2.重启MySQL服务，使配置生效

~]# /etc/init.d/mysqld restart

3.创建有复制权限的用户账号

mysql> grant replication slave on *.* to 'rep'@'172.16.1.%' identified by '123456';
命令解析：
'repuser'@'HOST' :设置用户名即主机ip或网段，网段用%表示 例如172.16.1.%
IDENTIFIED BY:设置密码
*.* ：表示所有数据库，所有表
GRANT REPLCATION SLAVE:就是允许该用户复制数据

该命令作用就是授权rep-user能拷贝数据库的所有内容

4.在Master装一个插件

在master装一个插件”semisync_master.so”，这个插件默认就有

#登入mysql终端执行
#查看插件目录
mysql> show variables like "%plugin_dir%";
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| plugin_dir    | /application/mysql/lib/plugin/ |
+---------------+--------------------------------+
1 row in set (0.00 sec)
#查看插件
~]# ll /application/mysql/lib/plugin/semisync_master.so
-rwxr-xr-x 1 mysql mysql 421068 Nov 28 2016 /application/mysql/lib/plugin/semisync_master.so
1.安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 
2.加载插件
mysql> set global rpl_semi_sync_master_enabled = 1; 
3.查看状态
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |    #NO表示开启半同步功能，OFF表示关闭
| rpl_semi_sync_master_timeout       | 10000 |    #超时时间微秒为单位
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
4 rows in set (0.00 sec)

5.半同步的slave配置

1.修改配置文件

 vim /etc/my.cnf
 #在[mysql]配置块中添加如下两行配置
 [mysqld] 
 server_id=3           #为当前节点设置一个全局惟一的ID号
 read_only=ON          #限制从服务器为只读."注意：此限制对拥有SUPER权限的用户均无效"
 innodb_file_per_table #数据于表结构分离，存放在两个不同文件

2.重启MySQL服务，使配置生效

~]# /etc/init.d/mysqld restart

3.登入MySQL终端安装插件

1.安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; 
2.加载插件
mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1; 
3.查看状态
mysql> SHOW GLOBAL VARIABLES LIKE '%semi%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.01 sec)

4.使用有复制权限的用户账号连接至主服务器，并启动复制线程

#进行主库数据全备
~]# mysqldump -uroot -A -B --events|gzip >/opt/db.sql.gz
#scp一份到从库进行导入
~]# scp /opt/db.sql.gz root@118.190.201.69:/opt/
#从库导入全备数据
~]# mysql3306 </opt/db.sql

mysql> CHANGE MASTER TO MASTER_HOST=‘xxx’,MASTER_USER='replicant',MASTER_PASSWORD=‘xxx’, MASTER_LOG_FILE='bin.000050', MASTER_LOG_POS=191; ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.
#出现如上错误：
原因是主从都开启了gtid，在设置从库的时候遇到了问题
解决的办法
vim /etc/my.cnf
#注释下面内容主库从库
#gtid_mode=ON
#log_slave_updates
#enforce_gtid_consistency

#或者执行下面的操作
mysql> change master to master_auto_position=0;
Query OK, 0 rows affected (0.34 sec)

mysql> CHANGE MASTER TO MASTER_HOST=‘xxx’,MASTER_USER='xxx',MASTER_PASSWORD=‘xxx’, MASTER_LOG_FILE='bin.0000xx', MASTER_LOG_POS=xxx;
Query OK, 0 rows affected, 2 warnings (0.51 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.08 sec)

mysql> SHOW SLAVE STATUS \G

#执行如下语句主从同步
CHANGE MASTER TO 
MASTER_HOST='172.16.1.68',             #指定中继master主机IP
MASTER_PORT=3306,                      #指定中继master主机端口
MASTER_USER='rep',                     #指定master被授权的用户名
MASTER_PASSWORD='123456',              #指定被master授权的用户密码 
MASTER_LOG_FILE='mysql-bin.000013',    #指定master服务器的哪个二进制日志开始复制
MASTER_LOG_POS=120;                    #二进制日志位置，可以在master服务器上执行该命令查看，show master status; 

5.从库Slave上重新启动IO_THREAD

mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

6.查看slave服务器线程状态

mysql> show slave status\G
*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 172.16.1.68
 Master_User: rep
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000013
 Read_Master_Log_Pos: 120
 Relay_Log_File: relay-bin.000002
 Relay_Log_Pos: 283
 Relay_Master_Log_File: mysql-bin.000013
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

7.补充说明

MySQL复制过滤器：
   master：
     binlog_do_db= ##白名单要过滤那些数据库
     binlog_ignore_db= ##黑名单要剔除那些数据库
   slave:
     replicate_do_db=  ##指定复制指定库
     replicate_ignore_db= ##不需要那些库
进行表过滤：
     replicate_do_table= db_name.table_name
     replicate_ignore_table=
通配符过滤:
     replicate_wild_do_table=
     replicate_wild_ignore_table=

主从复制，数据不一致，解决办法
     1.重新备份并在从服务器导入数据；
     2.pt-table-sync
       wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm
       yum install percona-toolkit-2.2.19-1.noarch.rpm
为了提高复制时数据的安全性，在主服务器上设置：
     sync_binlog = 1
     innodb_flush_log_at_trx_commit = 1
       次参数的值设置为1，性能下降会较严重：一般设定为2或0，主服务器崩溃依然有可能导致从服务器无法获取全部的二进制日志事件；
为了复制的安全性：
     sync_master_info = 1
     sync_relay_log = 1
     sync_relay_log_info = 1
从服务器意外崩溃，建议使用命令：pt-start-slave来启动slave； 

 

AD：《熊孩子博客》熊孩子QQ:443060965