Mysql的主从复制、主主复制、半同步复制及复制过滤
2018-06-28 本文已影响0人
小尛酒窝
Mysql 的复制架构能够提供一个负载均衡、高可用、备份、数据分布的服务器集群，而在完成这样一个集群之前，我们需要保证数据库服务器能够正常复制同步数据，而完成这样的同步的方式主要有主从复制和主主复制。
一、Mysql的主从复制
1、环境准备
两台Linux虚拟主机。
Linux版本：7.4
ip：192.168.0.82（master）、192.168.0.89（slave）
数据库版本： mariadb-server.x86_64 1:5.5.56-2.el7
注意，在启动mariadb-server服务前，先确保/etc/my.cnf配置文件中配置开启了log-bin选项。此选项必须开启，因为主从复制的数据同步，实际上就是二进制日志在备机上的重现。
配置master服务器的数据库配置文件：
[root@master ~]# vim /etc/my.cnf
log-bin=master.log #启动master服务器的log-bin功能
innodb-file-per-table=ON
sync_binlog = 1 #默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。
server_id=1 #mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有的
配置slave服务器的数据库配置文件：
[root@slave ~]# vim /etc/my.cnf
innodb-file-per-table=ON
relay-log=relay.log #用于保存master服务器的binlog日志
read-only=ON #设置从库为只读模式，但仅对非SUPER权限的用户有效
server_id=2
最后启动数据库服务即可。
2、构建主从复制
数据库服务的安装此处略过，可自行查找相关的安装方式。若是在现有的数据库服务器上配置主从，需确保主从数据一致后再配置主从复制。因为从服务器主要是通过获取master服务器的binlog日志来在从服务器上重新执行，以此同步数据的，若从服务器本身并没有相关的数据库，binlog日志的复现执行可能会失败。
首先在master服务器上创建用于同步的账号：
MariaDB [(none)]> grant REPLICATION SLAVE ON *.* TO 'slave'@'192.168.0.89' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
接着在master服务器上查看对应的二进制日志的名称及日志pos位置：
MariaDB [(none)]> show master status;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| master.000003 | 8154 | | |
+---------------+---------
然后在slave服务器上执行change master 语句：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.82',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master.000003',MASTER_LOG_POS=8154;
MariaDB [hellodb]> start slave; #最后启动slave
通过show slave status可以查看相应的slave状态，查看相应的slave信息及报错情况：
MariaDB [hellodb]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.82
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master.000004
Read_Master_Log_Pos: 733
Relay_Log_File: relay.000002
Relay_Log_Pos: 1014
Relay_Master_Log_File: master.000004
Slave_IO_Running: Yes #此处为slave的IO线程，为yes说明线程已经连接上主服务器，正等待二进制日志事件到达。
Slave_SQL_Running: Yes #此处为slave的SQL线程，为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: 733
Relay_Log_Space: 1298
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服务器的数据是否落后于master
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)
ERROR: No query specified
至此主从复制便完成了。
二、Mysql的主主复制
1、环境准备
两台Linux虚拟主机。
Linux版本：7.4
ip：192.168.0.82（master1）、192.168.0.89（master2）
数据库版本： mariadb-server.x86_64 1:5.5.56-2.el7
需要注意的是，主主复制需要在两个服务器节点上开启binlog和relay-log功能，且server_id必须使用不同的值；若数据库中存在自动增长id的表，为了使得id不冲突，需要自定义其自动增长的方式，如master-1自增长为奇数，master-2的自增长为偶数。
master1的配置文件：
log-bin=master1.log
relay-log=master1-relay.log
innodb-file-per-table=ON
server_id=1
auto_increment_offset=1 #自增长的起始值。一般填第n台主MySQL
auto_increment_increment=2 #自增长的步长值auto_imcrement。一般有n台主MySQL就填n
master2的配置文件：
log-bin=master2.log
innodb-file-per-table=ON
relay-log=master2-relay.log
server_id=2
auto_increment_increment=2
auto_increment_offset=2
2、构建主主复制
在构建主主复制前，先确保服务器之间的数据相同。
首先在两个服务器节点上创建提供给对方做同步的复制账号：
#master1
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'master1-slave'@'192.168.0.89' identified by '123456';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#master2
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'master2-slave'@'192.168.0.82' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
接着分别在master1和master2上查看相应的binlog信息：
#master1
MariaDB [(none)]> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| master1.000001 | 492 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#master2
MariaDB [(none)]> show master status;
+----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| master2.000001 | 492 | | |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)
然后分别在master1和master2上使用change master指定对方为master，然后启动slave即可：
#master1
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.89',MASTER_USER='master2-slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master2.000001',MASTER_LOG_POS=492;
Query OK, 0 rows affected (0.08 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
#master2
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.0.82',MASTER_USER='master1-slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master1.000001',MASTER_LOG_POS=492;
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start slave;
3、测试主主复制
此时分别查看master1和master2的slave状态：
#master1
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.89
Master_User: master2-slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master2.000001
Read_Master_Log_Pos: 492
Relay_Log_File: master1-relay.000002
Relay_Log_Pos: 527
Relay_Master_Log_File: master2.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: 492
Relay_Log_Space: 819
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
1 row in set (0.00 sec)
ERROR: No query specified
#master2
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.82
Master_User: master1-slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1.000001
Read_Master_Log_Pos: 492
Relay_Log_File: master2-relay.000002
Relay_Log_Pos: 527
Relay_Master_Log_File: master1.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: 492
Relay_Log_Space: 819
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
1 row in set (0.00 sec)
ERROR: No query specified
若Slave_IO_Running和Slave_SQL_Running显示为yes，说明主主复制已经启动成功。
在master1上测试创建mydb数据：
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
此时在master2上应该能复制同步创建mydb，在master2上创建mydb库的account表：
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> use mydb;
MariaDB [mydb]> create table account (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,name VARCHAR(30));
Query OK, 0 rows affected (0.01 sec)
此时分别在master1和master2上往account表中插入数据：
#master1
MariaDB [(none)]> use mydb;
MariaDB [mydb]> insert into account(name) values ('charlie'),('jack'),('alice');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
#master2
MariaDB [mydb]> insert into account(name) values ('Ops'),('IT'),('Manager');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
插入完成后在master1和master2上都应该能查看到下述数据：
MariaDB [mydb]> select * from account;
+----+---------+
| id | name |
+----+---------+
| 1 | charlie |
| 3 | jack |
| 5 | alice |
| 6 | Ops |
| 8 | IT |
| 10 | Manager |
+----+---------+
6 rows in set (0.00 sec)
此时说明Mysql的主主复制同步已成功。
三、Mysql的半同步复制
从MySQL5.5开始，MySQL以插件的形式支持半同步复制。如何理解半同步呢？首先我们来看看异步，全同步的概念。
异步复制:MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返给给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
全同步复制:指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
半同步复制:介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。
Mysql半同步复制必须在Mysql 5.5及以上版本使用，并且安装相关的支持插件。
接着我们在此前的主从复制的配置环境下来构建半同步复制。
1、构建半同步复制
首先检查判断Master和slave服务器是否支持动态增加插件：
MariaDB [(none)]> select @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)
接着分别检查其对应的插件目录下是否存在插件semisync_master.so和semisync_slave.so：
MariaDB [(none)]> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value |
+---------------+--------------------------+
| plugin_dir | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.01 sec)
MariaDB [(none)]> quit
Bye
[root@master ~]# ll /usr/lib64/mysql/plugin/ | grep semi
-rwxr-xr-x. 1 root root 41336 8月 5 2017 semisync_master.so
-rwxr-xr-x. 1 root root 15984 8月 5 2017 semisync_slave.so
接着在master服务器上安装插件semisync_master.so，在slave服务器上安装semisync_slave.so插件：
#master
MariaDB [(none)]> install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)
#slave
MariaDB [(none)]> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)
安装完后，可以在mysql库的plugin表中查看到相应的插件：
#master
MariaDB [(none)]> select * from mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
#slave
MariaDB [(none)]> select * from mysql.plugin;
+---------------------+-------------------+
| name | dl |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.00 sec)
接着分别配置master和slave的数据库配置文件，启用半同步功能：
#master
[root@master ~]# vim /etc/my.cnf
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=30000
#slave
[root@slave ~]# vim /etc/my.cnf
rpl_semi_sync_slave_enabled=1
最后重启数据库服务即可。
启动完成后，可以通过下述两个命令来在master主机上查看半同步复制的状态：
MariaDB [mydb]> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 | #半同步的slave节点有多少个
| Rpl_semi_sync_master_net_avg_wait_time | 1127 | #半同步的平均等待时长
| Rpl_semi_sync_master_net_wait_time | 1127 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON | #半同步复制的是否启用
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
MariaDB [(none)]> show variables like '%Rpl%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_recovery_rank | 0 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 30000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
5 rows in set (0.00 sec)
四、Mysql的复制过滤
上述所提到的主从复制、主主复制、异步复制、半同步复制等等，几乎都是复制同步全部的数据库，但是有些情况下可能只需要复制同步一两个指定的数据库。而这时为了复制同步一两个指定的数据库而去同步整个数据库的话，有可能会造成主服务器的负载过大。因此此时我们可以利用Mysql的复制过滤器来实现复制过滤，仅复制指定的数据库，或者不复制指定的数据库，其实现方法有以下两种。
1、修改Master的过滤变量
可以在Master服务器上修改下述两个全局变量：
binlog_do_db= #相当于白名单，只对指定的db做负复制同步
binlog_ignore_db= #黑名单，不复制同步指定的db
这两个全局变量一般不同时指定，因此同时指定的话容易造成干扰冲突。修改完成后，主服务器仅向二进制日志中记录指定的数据库相关的写操作。但是修改Master的方法会使得其他数据库无法使用基于时间节点的恢复的功能，这是一大弊端。
2、修改从服务器的过滤变量
第二种方法，是修改从服务器的过滤变量，由从服务器的SQL线程来过滤出指定的数据库或表的相关事件，并应用于本地。
其相关变量有：
Replicate_Do_DB=
Replicate_Ignore_DB=
Replicate_Do_Table=
Replicate_Ignore_Table=
Replicate_Wild_Do_Table= #通配符
Replicate_Wild_Ignore_Table=
3、复制过滤示例
配置从服务器只复制同步hellodb：
MariaDB [(none)]> stop slave; #在修改复制过滤变量前，需先停止slave
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> set @@global.replicate_do_db=hellodb; #设置只复制同步hellodb
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.82
Master_User: master1-slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master1.000005
Read_Master_Log_Pos: 470
Relay_Log_File: slave.000006
Relay_Log_Pos: 527
Relay_Master_Log_File: master1.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: hellodb #查看slave的status能看到指定的复制同步的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: 470
Relay_Log_Space: 1318
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
1 row in set (0.00 sec)
此时在master做出的修改变更，只有与hellodb相关的事件会复制同步到slave上，其他库的更改变更不会进行同步。
