Mysql的主从复制、主主复制、半同步复制及复制过滤

作者: 小尛酒窝 | 来源:发表于2018-06-28 14:53 被阅读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上，其他库的更改变更不会进行同步。

Mysql的主从复制、主主复制、半同步复制及复制过滤

一、Mysql的主从复制

1、环境准备

2、构建主从复制

二、Mysql的主主复制

1、环境准备

2、构建主主复制

3、测试主主复制

三、Mysql的半同步复制

1、构建半同步复制

四、Mysql的复制过滤

1、修改Master的过滤变量

2、修改从服务器的过滤变量

3、复制过滤示例

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读