MySQL5.6之复制过滤、多线程复制、利用GTID进行主从复制

ookkl

MySQL数据库复制过滤（有选择性的只复制某个库或者某个表）
有以下两个变量定义复制的白名单和黑名单
master:
binlog-do-db = text1；（此参数可以写多次，是一个复制白名单，仅将指定数据库（text1）的相关操作记入二进制日志）

binlog-ignore-db = text2; （此参数可以写多次，是一个复制黑名单，将指定数据库（text2）的相关参数不写入二进制日志）

但在master进行复制过滤会导致主服务器上的二进制日志记录不完整，一旦主服务器的数据崩溃无法做到还原所有数据

slave：
replicate-do-db = text3 （此参数可以写多次，是一个复制白名单，只将指定数据库（text3）的二进制日志读入中继日志进行复制）
replicate-ignore-db = text4（此参数可以写多次，是一个复制黑名单，将指定数据库（text4）的二进制日志不读入中继日志进行复制）

一般来说为了让主服务器的二进制日志记录完整，都在从服务器上进行复制过滤；而在从服务器进行复制过滤的坏处是：主服务器会发送大量对从服务器来说没有用的二进制日志，占据带宽和存储空间；但无论如何在从服务器进行复制过滤比较好

在表级别进行复制过滤：
replicate-do-table =

repilcate-ignore-table =
下面两个参数允许使用通配符（%和_）来定义那些表可以复制，那些表不复制
replicate_wild-do-table = zxl.tb% （白名单，复制zxl库中所有以tb开头的表）
replicate_wild-ignore-table = fade.tb_ （黑名单，对fade库中tb开头后跟一个字符的表不进行复制）

例：在从server上定义只复制discuz这个库中的数据
[iyunv@node2 ~]# vim /etc/my.cnf
replicate_do_db = discuz
[iyunv@node2 ~]# service mysqld restart
Shutting down MySQL...... SUCCESS!
Starting MySQL............................ SUCCESS!
[iyunv@node2 ~]# mysql -uroot -p
Enter password:
mysql> show variables like 'rep%';
发现找不到replicate_do_db这个变量；说明此变量是只读的，不允许在server运行中进行修改

但是可以查看status
mysql> show slave status\G
Replicate_Do_DB: discuz

进行测试
主：mysql> create database faded;
Query OK, 1 row affected (0.16 sec)
从：mysql> show databases; 可以看到在主节点创建的faded数据库，没有复制到从节点
+--------------------+
| Database       |
+--------------------+
| information_schema |
| mysql         |
| performance_schema |


主：mysql> create databases discuz;可以看到在主节点创建的discuz数据库，复制到了从节点
从mysql> show databases;
+--------------------+
| Database       |
+--------------------+
| information_schema |
| discuz         |
| mysql         |
| performance_schema |
+--------------------+



而在MySQL5.6中引入了GTID和并行复制功能
GTID:是一个主机上的某个事务的表示码；由每个服务器的UUID（全局唯一标示符,是一个128位的随机码，用来标示一个server） + 事务号 ==GTID

所以有了gtid后，在MySQL5.6中会在二进制日志中在每个事务的首部写上gtid标示，gtid使得在主/从之间进行追踪与比较事务变得非常简单；且使得当主服务器的数据崩溃后的恢复变得简单和快速

而对Innodb存储引擎来说，要实现其高可用功能，也要借助gtid来实现
如：B和C为A的从服务器，但是有一天A挂了；你想让B立马顶上去作为主，并让C作为B的从，但由于B和C在作为A的从时，其复制很可能不同步，导致A挂后B和C上面的数据不一致（不一致就无法让B和C作为主/从架构），那么要让B和C成为主从前如何让其数据保持一致？

有了GTID之后，每个事务在记录入二进制日志中时，这个二进制日志会明确标明属于哪个gtid,且gtid中有事务的号码，所以B和C之间只需比较一下，看C中的那些事务在C中已经完成而在B中没有完成，然后将其同步给B，这样就可以快速让B和C的数据一致，从而将B转为主，C转为B的从，从而实现了MySQL的高可用
在从节点的IO线程连接主节点的dump进程时，要执行下面的命令
change master to master_host='192.168.139.2',master_user='faker',master_password='123',master_log_file='master-bin.000004',master_log_pos=579;
而有了GTID后，再也不需要指定 master_log_file='',master_log_pos='';而是主从服务器可以通过协议进行协商，决定从哪里进行数据复制

MySQL5.6的多线程复制
多线程复制变量 slave-parallel-workers = 2 开启两个sql复制线程
当主服务器的多个CPU运行多个事务时，从服务器只能靠一个sql线程读取中继日志进行一个个的复制，这样会导致从服务器比主服务器慢许多
而多线程复制中，从服务器可以启动多个sql线程进行数据复制，但需要注意的是对每个库来说只能有一个sql线程进行复制（否则容易出现数据交叉），所以如果只对一个库中数据进行复制，开启多线程复制无意义



MySQL 5.6中要使用复制功能（主要指GTID复制），其服务配置段[mysqld]中于少应该定义如下选项：

binlog-format：二进制日志的格式，有row、statement和mixed几种类型；

需要注意的是：当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW，现在MySQL官方认为STATEMENT这个已经不再适合继续使用；但mixed类型在默认的事务隔离级别下，可能会导致主从数据不一致；
log-slave-updates： 从服务器从中继日志进行数据读取，进行本地数据写入复制时，是否记录二进制日志，一般来讲为了gtid的复制安全，这项是要启用的（一般来说从如果不是其他从的主不用记录二进制）
gtid-mode： 是否启用gtid这种功能
enforce-gtid-consistency： 是否强制gtid具有一致性（有些涉及数据库修改的特殊语句可能不会记录入二进制日志，但可以靠其他的一些特性使数据库保持一致性功能）
report-port： 从服务器的端口
report-host：从服务器的ip

启用下面两项，可用于实现在崩溃时保证二进制及从服务器安全的功能；
master-info-repository：用于让主服务器记录每个从服务器的连接信息（从服务器之所以能自动连接到主，就是靠master.info文件），及每个从服务器复制的二进制日志文件名，及相关事件位置等信息，记录位置有file和table（默认为file）
relay-log-info-repository：让从服务器自己记录连接的主服务器是谁，记录的那个二进制日志及事件位置等信息；记录到table|file中


sync-master-info：从服务器应该随时将启动信息记录进master.info文件中，启用之可确保无信息丢失；
slave-paralles-workers：设定从服务器的SQL线程数；0表示关闭多线程复制功能；
binlog-checksum、master-verify-checksum和slave-sql-verify-checksum：启用复制有关的所有校验功能；
binlog-rows-query-log-events：启用之可用于在二进制日志记录事件相关的信息，可降低故障排除的复杂度；但会导致记录的数据量过大
log-bin：启用二进制日志，这是保证复制功能的基本前提；
server-id：同一个复制拓扑中的所有服务器的id号必须惟一；

简单主从模式配置步骤

1、配置主从节点的服务配置文件

1.1、配置master节点：
[mysqld]
binlog-format=ROW
log-bin=master-bin
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=1
report-port=3306
port=3306
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=192.168.139.2

1.2、配置slave节点：
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
server-id=11
report-port=3306
port=3306
log-bin=mysql-bin.log
datadir=/mydata/data
socket=/tmp/mysql.sock
report-host=192.168.139.4

2、创建复制用户

mysql> GRANT REPLICATION SLAVE ON *.* TO faker@192.168.139.4 IDENTIFIED BY '123';

说明：192.168.139.4是从节点服务器；如果想一次性授权更多的节点，可以自行根据需要修改；

3、为备节点提供初始数据集

锁定主表，备份主节点上的数据，将其还原至从节点；如果没有启用GTID，在备份时需要在master上使用show master status命令查看二进制日志文件名称及事件位置，以便后面启动slave节点时使用。

4、启动从节点的复制线程

如果启用了GTID功能，则使用如下命令：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.139.2', MASTER_USER='faker', MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;

没启用GTID，需要使用如下命令：
slave> CHANGE MASTER TO MASTER_HOST='192.168.139.2',
-> MASTER_USER='faker',
-> MASTER_PASSWORD='123',
-> MASTER_LOG_FILE='master-bin.000002',
-> MASTER_LOG_POS=279;


例： 192.168.139.2 node1 主节点
   192.168.139.4 node2 备节点
[iyunv@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/mydata/data
innodb_file_per_table = 1
log-bin = master-bin
log-bin-index = master-bin.index
server_id = 1
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
binlog-format=ROW
log-slave-updates=true
gtid-mode=on  必须开启gtid复制
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
port=3306
report-host=192.168.139.2

[iyunv@node2 ~]# vim /etc/my.cnf
[mysqld]
datadir=/mydata/data
innodb_file_per_table = ON
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2 开启两个sql线程进行复制
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
port=3306
log-bin=mysql-bin.log
report-host=1192.168.139.4
relay-log = relay-log

relay-log-index = relay-bin.index
server_id = 11
read_only = ON
socket=/var/lib/mysql/mysql.sock
user=mysql

可以看到主从的配置几乎没有区分，就算是从它也是备用的主
[iyunv@node1 ~]# mysql -uroot -p
Enter password:
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000006 |    151 |              |                  |                  
+-------------------+----------+--------------+------------------+-------------------+
可以看到比原来多了 Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set

mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                     |
+---------------+--------------------------------------+
| server_uuid  | 75ade6f5-f4db-11e6-8f88-000c291c1312 |
+---------------+--------------------------------------+
1 row in set (0.19 sec)

[iyunv@node2 ~]# mysql -u root -p
mysql>  show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                     |
+---------------+--------------------------------------+
| server_uuid  | 5bf2ec4b-f4e2-11e6-8fb5-000c295f682f |
+---------------+--------------------------------------+

可以看到主和从的uuid不同，且差别很大 uuid + 事务id 就是gtid
创建用户fade，并授权复制二进制日志中的事件
主mysql> mysql> grant replication slave on *.* to 'fade'@'192.139.%.%' identified by '123';
Query OK, 0 rows affected (0.23 sec)

在从节点进行连接主节点的dump进程

mysql> change master to master_host='192.168.139.2',master_user='faker',master_password='123',master_auto_position=1;

Query OK, 0 rows affected, 2 warnings (0.40 sec)

mysql> show warnings; 查看警告信息（由于没有进行ssl加密，为明文传输）
+-------+------+---------------------------------------------------------------------------
| Level | Code | Message                                                                                                        
+-------+------+---------------------------------------------------------------------------
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                              
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER
+-------+------+---------------------------------------------------------------------------
mysql> start slave;
mysql> show slave status\G
......
Master_Log_File: master-bin.000007
Read_Master_Log_Pos: 191
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 363
Relay_Master_Log_File: master-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Executed_Gtid_Set: 75ade6f5-f4db-11e6-8f88-000c291c1312:1
........
在主节点上进行数据修改
mysql> drop database mydb;
Query OK, 0 rows affected (0.01 sec)
从节点进行了复制
mysql> show databases;
+--------------------+
| Database       |
| information_schema |
| mydb          |

| mysql         |
| performance_schema |
+--------------------+
9 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+--------------------------
| File        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                  
+------------------+----------+--------------+------------------+--------------------------
| mysql-bin.000003 |    191 |         |             | 75ade6f5-f4db-11e6-8f88-                                            |000c291c1312:1-4 |
+------------------+----------+--------------+------------------+--------------------------
Executed_Gtid_Set 执行了1到4事务