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

最新推荐文章于 2021-10-24 18:01:38 发布

weixin_33769207

最新推荐文章于 2021-10-24 18:01:38 发布

阅读量179

点赞数

原文链接：http://blog.51cto.com/11107124/1899022

版权

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这个库中的数据

[root@node2 ~]# vim /etc/my.cnf

replicate_do_db = discuz

[root@node2 ~]# service mysqld restart

Shutting down MySQL...... SUCCESS!

Starting MySQL............................ SUCCESS!

[root@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 备节点

[root@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

[root@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

可以看到主从的配置几乎没有区分，就算是从它也是备用的主

[root@node1 ~]# mysql -uroot -p

Enter password:

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

+-------------------+----------+--------------+------------------+-------------------+

| 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)

[root@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;

+------------------+----------+--------------+------------------+--------------------------

+------------------+----------+--------------+------------------+--------------------------

| mysql-bin.000003 | 191 | | | 75ade6f5-f4db-11e6-8f88- |000c291c1312:1-4 |

+------------------+----------+--------------+------------------+--------------------------

Executed_Gtid_Set 执行了1到4事务

转载于:https://blog.51cto.com/11107124/1899022

weixin_33769207

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL5.6之复制过滤、多线程复制、利用GTID进行主从复制

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

扫一扫