Mysql基于GTID主从复制

时间:2023-03-08 16:44:32
Mysql基于GTID主从复制

Mysql5.6基于GTID全局事务的复制

什么是GTID

  GTID(Global Transaction Identifiers)是全局事务标识
当使用GTIDS时,在主上提交的每一个事务都会被识别和跟踪,并且运用到所有从MySQL,而且配置主从或者主从切换时不再需要指定 master_log_files和master_log_pos;由于GTID-base复制是完全基于事务的,所以能很简单的决定主从复制的一致性;
官方建议Binlog采用Row格式

MySQL 5.1.12 开始,可以用以下三种模式来实现:
基于SQL语句的复制(statement-based replication, SBR),
基于行的复制(row-based replication, RBR),
混合模式复制(mixed-based replication, MBR)。

相应地,binlog的格式也有三种:STATEMENT,ROW,MIXED。 MBR 模式中,SBR 模式是默认的。

基于混合模式的复制:它是根据事件的类型实时的改变binlog的格式。当设置为混合模式时,默认为基于语句的格式,但在特定的情况下它会自动的转变为基于行的模式。

SBR 的优点:
历史悠久,技术成熟
binlog文件较小
binlog中包含了所有数据

库更改信息,可以据此来审核数据库的安全等情况
binlog可以用于实时的还原,而不仅仅用于复制
主从版本可以不一样,从服务器版本可以比主服务器版本高

SBR 的缺点:
不是所有的UPDATE语句都能被复制,尤其是包含不确定操作的时候。
调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制:
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
INSERT ... SELECT 会产生比 RBR 更多的行级锁
复制需要进行全表扫描(WHERE 语句中没有使用到索引)的 UPDATE 时,需要比 RBR 请求更多的行级锁
对于有 AUTO_INCREMENT 字段的 InnoDB表而言,INSERT 语句会阻塞其他 INSERT 语句
对于一些复杂的语句,在从服务器上的耗资源情况会更严重,而 RBR 模式下,只会对那个发生变化的记录产生影响
存储函数(不是存储过程)在被调用的同时也会执行一次 NOW() 函数,这个可以说是坏事也可能是好事
确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行,否则可能会导致复制出错
执行复杂语句如果出错的话,会消耗更多资源

RBR 的优点:
任何情况都可以被复制,这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
多数情况下,从服务器上的表如果有主键的话,复制就会快了很多
复制以下几种语句时的行锁更少:
* INSERT ... SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或 DELETE 语句
执行 INSERT,UPDATE,DELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能

RBR 的优点:
任何情况都可以被复制,这对复制来说是最安全可靠的
和其他大多数数据库系统的复制技术一样
多数情况下,从服务器上的表如果有主键的话,复制就会快了很多
复制以下几种语句时的行锁更少:
* INSERT ... SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 没有附带条件或者并没有修改很多记录的 UPDATE 或
DELETE 语句
执行 INSERT,UPDATE,DELETE 语句时锁更少
从服务器上采用多线程来执行复制成为可能

RBR 的缺点:
binlog 大了很多
复杂的回滚时 binlog 中会包含大量的数据
主服务器上执行 UPDATE 语句时,所有发生变化的记录都会写到 binlog 中,而 SBR 只会写一次,这会导致频繁发生 binlog 的并发写问题
UDF 产生的大 BLOB 值会导致复制变慢
无法从 binlog 中看到都复制了写什么语句(加密过的)
当在非事务表上执行一段堆积的SQL语句时,最好采用 SBR 模式,否则很容易导致主从服务器的数据不一致情况发生
另外,针对系统库 mysql 里面的表发生变化时的处理规则如下:
如果是采用 INSERT,UPDATE,DELETE 直接操作表的情况,则日志格式根据 binlog_format 的设定而记录
如果是采用 GRANT,REVOKE,SET PASSWORD 等管理语句来做的话,那么无论如何都采用 SBR 模式记录
注:采用 RBR 模式后,能解决很多原先出现的主键重复问题。

GTID的表示方式

source_id:transaction_id
source_id
:表示执行事务的主库的UUID(server_uuid:Mysql5.6的data目录下启动时会生成auto.cnf文件记录了uuid,重启后uuid不变,删除文件后会重新生成新的uuid);
transaction_id:是一个从1开始自增的计数,表示在这个主库上执行的第n个事务;
由于每台Mysql的uuid是全球唯一的,transaction_id自身唯一,就保证了GTID全局唯一性

mysql> show variables like 'server_uuid';

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

| Variable_name | Value |

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

| server_uuid | 4468c0e8-ef6f-11e3-9c2c-0200c0a80ad8 |

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

1 row in set (0.00 sec)

2.9.3、基于GTID的复制配置

master10.0.10.201
slave 10.0.10.202

步骤:
修改主从my.cnf增加GTID支持—>主只读—>拷贝数据到从数据目录—>重启主从—>在从上进行配置
1.修改主从my.cnf增加GTID支持

主10.0.10.201 Mysql配置:

(1)     # vim /etc/my.cnf 增加一下部分

server-id=
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=
slave-parallel-workers=
binlog-checksum=CRC32
master-verify-checksum=
slave-sql-verify-checksum=
binlog-rows-query-log_events=

(2)     # /etc/init.d/mysql restart 重启服务

(3)     # mysql -e "show master status;" 已执行过的GTID集 Executed_Gtid_Set

mysql5.6以后每台mysql服务器都有一个全局唯一的ID号叫做uuid,GTID就是由当前节点的UUID(一个128位的随机数)和为当前节点生成的随机数(TID)组成的,因此只要UUID不同再在此基础上保证事务ID不同就保证全局不一样。

(4)     # mysql -e "show global variables like '%uuid%';"

(5)     创建复制用户:

mysqlàgrant replication slave,replication client on *.* to repluser@'10.0.10.%'identified by 'replpass';

mysqlà flush privileges;

从10.0.0.202 Mysql配置:

(1)     # vim /etc/my.cnf 

同上面主10.0.10.201 ,,只需要修改server_id,必须保证server_id不一样

(2): 从库连接主库

change master to

master_host='10.0.10.201',

master_user='repluser',

master_password='replpass',

master_auto_position=1;

(3)启动Slave

mysqlàstart slave;

mysqlàshow slave status\G;

配置选项说明:

server-id       =
log-bin=mysql-bin
binlog_format=row 二进制格式改为行row模式,三种模式statement语句模式,row行模式,mixed混合模式
当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,现在MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;
#添加以下这些选项
log-slave-updates=true slave更新是否记入日志
gtid-mode=on 启用gtid类型,否则就是普通的复制架构
enforce-gtid-consistency=true 强制GTID的一致性
master-info-repository=TABLE 主服信息记录库=表/文件
relay-log-info-repository=TABLE 中继日志信息记录库
sync-master-info= 同步主库信息
slave-parallel-workers= 从服务器的SQL线程数,要复制库数目相同
binlog-checksum=CRC32 校验码
master-verify-checksum= 主服校验
slave-sql-verify-checksum= 从服校验
binlog-rows-query-log_events= 二进制日志详细记录事件
report-port= 提供复制报告端口可选
report-host=station20.example.com 提供复制报告主机可选

常见错误场景描述以及解决方法

GTID复制模式手动跳过复制错误

1.场景描述:

A 服务器(Master)

B 服务器(Slave)

在A 服务器上创建一个库叫做aatest

在B 服务器上查看是否同步,

2.错误场景描述开始:

在B 服务器(Slave)将aatest删除掉.

在A 服务器(Master)将aatest删除掉

在个时候到B服务器使用show slave status\G;发现SQL 线程阻塞.

3.开始尝试恢复,使用binlog的跳过方式,尝试跳过恢复(并未得到解决,但是给出了提示)

当备库复制出错时,传统的跳过错误的方法是设置sql_slave_skip_counter,然后再START SLAVE。

但如果打开了GTID,就会设置失败:

mysql> set global sql_slave_skip_counter = 1;

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

提示的错误信息告诉我们,可以通过生成一个空事务来跳过错误的事务。

我们手动产生一个备库复制错误:

Last_SQL_Error: Error ‘Unknown table ‘test.t1” on query. Default database: ‘test’. Query: ‘DROP TABLE `t1` /* generated by server */’

查看binlog中,该DDL对应的GTID为7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131

4.通过GTID的方式跳过事务,得到解决。

4.1、步骤1:

在备库上执行:

mysql>show slave status\G; 查看一下

Master_Log_File: mysql-bin.000003 这里得到A 服务器(Master)的binlog日志文件

4.2、步骤2:

在主库上执行:

# mysqlbinlog mysql-bin.000003

找到drop database aatest这个语句动作的上下文,

我们可以看到有两个SET @@SESSION.GTID_NEXT。

(1)第一个是drop database aatest之上的:SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:31

(2)第二个是drop database aatest之下的:SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:32

<< 我们将第2个SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:32 复制一下>>

#160817  1:08:06 server id 1  end_log_pos 5484 CRC32 0x963858ea      GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:31'/*!*/;

# at 5484

#160817  1:08:06 server id 1  end_log_pos 5565 CRC32 0x96ff64da        Query       thread_id=44exec_time=0         error_code=0

SET TIMESTAMP=1471367286/*!*/;

drop database aatest

/*!*/;

# at 5565

#160817  1:08:22 server id 1  end_log_pos 5613 CRC32 0x14d35459      GTID [commit=yes]

SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:32'/*!*/;

4.3、步骤3:

在备库上执行:

mysql> select @@GTID_NEXT  先查询GTID_NEXT 的值

mysql> STOP SLAVE;  先停止掉SLAVE

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT= '59ebdf10-63c8-11e6-9d86-000c2916dc3f:32‘; 将步骤2复制的粘贴运行,

Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN; COMMIT;    给一个空的事务,然后在提交。

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION GTID_NEXT = AUTOMATIC; 重新设置自动提交事务GTID。

Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;

再查看show slave status,就会发现错误事务已经被跳过了。这种方法的原理很简单,空事务产生的GTID加入到GTID_EXECUTED中,这相当于告诉备库,这个GTID对应的事务已经执行了。