主从复制高级

过滤复制

1.是什么

当主库上存在多个database，但从库只需要同步一部分的话就需要用到MySQL的复制过滤功能。

比如一个主库承载多个业务数据库，需要将不同业务数据库复制到不同的从库进行查询以做到业务隔离的场景。

通过过滤复制可以灵活的指定哪些库和表需要复制，哪些库不需要同步。

通常建议在从服务器上配置过滤复制，可以减轻主库的负载。

2.怎么配置

主库复制过滤相关配置（仅支持库级别过滤）（建议在从库上配置，这里仅作了解）

· binlog_do_db：白名单，只对指定数据库进行binlog记录与复制，多个数据库设置可以写多行

· binlog_ignore_db：黑名单，此选项中指定的数据库将不进行binlog记录和复制，多个数据库设置可以写多行


[mysqld]
binlog_do_db=yuchao_db
binlog_do_db=chaoge_linux

3.从库修改配置

· replicate_do_db：数据库白名单列表，多个数据库用逗号分隔，该选项指定的数据库会执行主从复制操作

· replicate_ignore_db：数据库黑名单列表，该选项指定的数据库将不会被复制

· replicate_do_table：表级别的白名单

· replicate_ignore_table：表级别的黑名单

· replicate_wild_do_table：可以使用通配符进行指定表，如%代表所有

· replicate_wild_ignore_table：同上

冷配置

# 修改配置文件
replicate_do_db=yuchao_db
replicate_do_db=chaoge_linux

热配置

mysql > stop slave sql_thread; 
mysql > change replication filter replicate_do_db=(yuchao_db,chaoge_linux);
mysql > start slave sql_thread;

检查slave状态

show slave status\G

主库数据写入

# 检查slave状态
show slave status\G

MySQL之GTID复制

GTID是什么

从 MySQL 5.6.5 开始新增了一种基于 GTID 的复制方式。

通过 GTID 保证了每个在主库上提交的事务在集群中有一个唯一的ID。

这种方式强化了数据库的主备一致性，故障恢复以及容错能力。
在原来基于二进制日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步，如果指定错误会造成数据的遗漏，从而造成数据的不一致。

借助GTID，在发生主备切换的情况下，MySQL的其它从库可以自动在新主库上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制位置发生误操作的风险。

另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

GTID长啥样

GTID (Global Transaction ID) 是对于一个已提交事务的编号，并且是一个全局唯一的编号。

GTID 实际上是由 UUID+TID 组成的。

其中 UUID 是一个 MySQL 实例的唯一标识。

TID 代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。下面是一个GTID的具体形式：

形式语法  
GTID = source_id ：transaction_id

具体结果
2E11FA47-61CA-11E1-9E33-C70AA9429562:28

在上面的定义中，每一个GTID均代表一个数据库的事务，等号右边的source_id表示执行事务的源服务器主库的uuid（也就是server_uuid）

而transaction_id是一个从1开始的自增的序列号，表示在这个主库上执行的第n个事务。

只要保证每台数据库的server_uuid全局唯一，以及每台数据库生成的transaction_id自身唯一，就能保证GTID的全局唯一性。

server_uuid是什么

MySQL 5.6用128位的server_uuid代替了原本32位的server_id的大部分功能。

原因很简单，server_id依赖于my.cnf的手工配置，有可能会产生冲突，而自动产生128位uuid的算法可以保证所有的MySQL uuid都不会发生冲突。

在进行首次启动时，MySQL会自动生成一个server_uuid，并且保存到数据库目录下的auto.cnf文件里，这个文件目前存在的唯一目的就是保存server_uuid。

在MySQL再次启动时其会读取auto.cnf文件，继续使用上次生成的server_uuid。

[root@db-51 /linux0224/mysql_3306]$cat /linux0224/mysql_3306/auto.cnf
[auto]
server-uuid=a3bcd21a-194e-11ed-9f8f-000c2947d442

使用GTID

GTID复制原理流程

master进行数据更新时、在事务前产生GTID号、一起记录到binlog日志。
slave的I/O线程将变更的binlog数据，写入到本地中继日志relay_log
slave的SQL线程从中继日志中获取GTID号，和本地的binlog对比查看是否有记录
有记录，说明该GTID事务已执行，slave数据库会忽略
如果没有记录，slave数据库从relay_log中继日志中获取数据，且执行该GTID的事务，记录到binlog中

GTID优缺点

优点

根据GTID可以明确知道事务最开始是在哪个数据库提交的
GTID对于宕机切换，非常方便，明确数据起止点。
缺点

开启了GTID的数据库，和未开启的数据库实例之间、是无法混用复制的

修改db-51配置文件

[root@db-51 /linux0224/mysql_3306]$cat /etc/my.cnf
[mysqld]
autocommit=0
binlog_format=row
gtid-mode=on 
enforce-gtid-consistency=true
log-slave-updates=1

user=mysql
datadir=/linux0224/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306
log_error=/var/log/mysql/mysql.err
server_id=51
log_bin=/binlog/mysql-bin
[mysql] 
socket=/tmp/mysql.sock
[client] 
socket=/tmp/mysql.sock

修改db-52的配置文件

[root@db-52 /linux0224/mysql_3306]$cat /etc/my.cnf 
[mysqld]
autocommit=0
gtid-mode=on 
enforce-gtid-consistency=true
log-slave-updates=1

user=mysql 
datadir=/linux0224/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306 
log_error=/var/log/mysql/mysql.err 
server_id=52
[mysql] 
socket=/tmp/mysql.sock
[client] 
socket=/tmp/mysql.sock

修改db-53的配置文件

[root@db-53 ~]$cat /etc/my.cnf 
[mysqld]
autocommit=0
gtid-mode=on 
enforce-gtid-consistency=true
log-slave-updates=1

user=mysql 
datadir=/linux0224/mysql_3306
basedir=/opt/mysql/
socket=/tmp/mysql.sock
port=3306 
log_error=/var/log/mysql/mysql.err 
server_id=53
[mysql] 
socket=/tmp/mysql.sock
[client] 
socket=/tmp/mysql.sock

主库gtid检查

mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)

从库操作


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.51
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 154
               Relay_Log_File: db-52-relay-bin.000005
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: