标签归档:MySQL Replication

FAQ系列 | table id问题导致主从复制失败

0、导读

主从复制环境中,IO、SQL线程都很正常,也没设置过滤规则,但数据就是无法复制到slave上,什么原因?

1、问题描述

事实上,这个案例发生已经有一阵子了,一直拖到现在我才整理。

发现一个主从环境中,slave上的io_thread、sql_thread状态均正常,relay log也正常接收来自master的event,但slave上却无法正常应用这些event,个别表数据没有复制过来。而且slave上的binlog也没有记录这些表上的操作。

2、原因分析

接到现场后,第一反应是是先检查是否设置了ignore/do规则,发现并不是这个原因引起的。

我自己手动测试创建了个新的测试表,写了几条数据,发现在slave上这个表能被创建,但写入的测试数据仍旧无法复制过来。这说明,slave上的复制并不是完全失效的,只是有特殊情形下才会失效。

结合上面的问题,想到了可能是因为binlog format以及事务隔离级别等原因导致失效的,于是做了下面的尝试。

//首先修改事务隔离级别为RR(此前是RC),尽可能保证主从数据一致性

root@imysql [mydb]> set session transaction isolation level repeatable read;

//测试写入2条数据

root@imysql [mydb]> insert into z select 5,5;

root@imysql [mydb]> insert into z select 6,6;

经过观察,这2条数据不可以复制到slave上。

//修改binlog format为statement(此前是row),再写入2条数据

root@imysql [mydb]> set session binlog_format=’statement’;

root@imysql [mydb]> insert into z select 7,7;

root@imysql [mydb]> insert into z select 8,8;

经过观察,这2条数据则可以复制到slave上。

现在至少表面上看起来,是由于binlog format+事务隔离级别综合因素引起的,所以我们来对比下不同binlog format下的binlog有什么区别吧。

tableid1
这些日志中,前两条是row模式下的日志,后两条则是statement模式下的。我们注意到红框中内容是:table_id: 24874588093,正是由于这个原因导致了slave无法正常复制数据。

正常情况下,row模式下的binlog event应该是这样的:

tableid2
在上面的日志中,我们看到的是:table_id: 108,这种情况下就可以正常复制了。

现在问题很明确了,就是由于binlog中table id异常导致无法复制。那么,到底什么原因导致table id出现异常呢。

3、案例建议

搜索了一些资料,发现也有别人遇到同样的问题。我就不多啰嗦了,大家可以看下方参考文章详细了解下。简言之,发生这中问题的原因,主要是因为table cache不够了,导致要频繁打开、关闭table,导致table id急剧增长,因而导致主从数据复制失败。

解决办法有几个:

  1. 加大 table_cache_size,或者 table_open_cache 值,以及 table_definition_cache 选项。一般设置不低于总table数量的1.5倍,更严谨的话,要看 Open_tables 和 Opened_tables 这两个status值。Open_tables 表示当前正被打开的table数量,而 Opened_tables 表示历史上反复打开table的总次数。如果 Opened_tables 值特别高,表明 table cache 很可能不够用所致。
  2. 择机重启主库实例,让table id的值再次从0开始计数。
  3. 临时解决方案:把binlog format改成statement,并且把事务隔离级别改成RR,尽量避免数据不一致的风险。

本文参考:

1. 杨奇龙《【MySQL】再说MySQL中的 table_id 》,http://blog.itpub.net/22664653/viewspace-1158547/

2. yuyue2014《MySQL table_id原理及风险分析》,http://www.cnblogs.com/yuyue2014/p/3721172.html

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达

FAQ系列 | 从MySQL 5.6到5.7复制错误解决

0、导读

在MySQL 5.7下采用多源复制方式,从5.6复制数据过来,会有问题吗?

1、问题描述

Q群里有位朋友想尝鲜5.7的多源复制,于是用MySQL 5.7版本作为slave,把MySQL 5.6作为master,想要将数据进行汇总,发现此路不通。

他在my.cnf中设置了2个选项,开启并发复制:

slave_parallel_type    = LOGICAL_CLOCK

slave_parallel_workers = 4

启动复制线程后,结果在错误日志中不断有类似下面的信息:

Transaction is tagged with inconsistent logical timestamps: sequence_number (823267087) <= last_committed (1301275374434324336)

执行 SHOW SLAVE STATUS\G 查看状态:

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Last_SQL_Errno: 1756

Last_SQL_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 160523 18:49:05

*************************** 2. row ***************************

Slave_IO_State: Waiting for master to send event

Last_Errno: 1756

Last_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

Skip_Counter: 0

Last_SQL_Errno: 1756

Last_SQL_Error: … The slave coordinator and worker threads are stopped, possibly leaving data in inconsistent state. A restart should restore consistency automatically, although using non-transactional storage for data or info tables or DDL queries could lead to problems. In such cases you have to examine your data (see documentation for details).

2、原因分析

上面这些错误提示可以看到,主要原因是:slave端采用了基于 LOGICAL_CLOCK 类型的并行复制,但master端的binlog格式并不支持这种方式,所以slave端无法正确读取binlog并行apply。

3、解决方案

虽然仍旧可以采用MySQL 5.7作为slave,但就无法开启基于 LOGICAL_CLOCK 类型的并行复制了。需要改回传统模式就可以了:

slave_parallel_type    = DATABASE

此外,在MySQL复制方案中,强烈建议不要让主从大版本不一样,很容易出现各种各样的问题。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 直达

FAQ系列 | 列类型被自动修改导致复制失败

0、导读

在复制环境中,有个表的列类型总是被修改,导致复制进程报错停止

1、问题描述

问题发生在朋友的数据库上,做了主从复制,其中某表有一列类型是INT,但是该表上的INSERT事件在BINLOG中却总被记录为MEDIUMINT类型,导致这个事件在SLAVE上执行失败。

相关现场信息见下:

MySQL版本:官方5.5.版本。

表DDL定义:

CREATE TABLE `t` (

`userid` int(10) unsigned NOT NULL DEFAULT 0,

这个表上的INSERT事件在BINLOG中的记录:

### INSERT INTO `imysql`.`t`

### SET

###   @1=207 /* MEDIUMINT meta=0 nullable=0 is_null=0 */

我们看到BINLOG中,这个列类型显示为MEDIUMINT,这个事件在SLAVE上就会报告下面的错误,导致SLAVE无法继续复制:

Column 0 of table ‘imysql.t’ cannot be converted from type ‘mediumint’ to type ‘int(10) unsigned

又是一个看起来很奇葩的案例。

2、原因分析

经过沟通排查,了解到他们的业务模式有点特殊,是从一个旧的空表中复制表结构生成每天日志表,然后再将当天的日志写入该表。也就是大概做法是:

1、创建每天日志表

CREATE TABLE t SELECT t_orig;

2、写入日志

INSERT INTO t SELECT * FROM t_orig;

其实问题就出在每天创建新表的过程中,源表结构像是这样的:

CREATE TABLE `t_orig` (

`userid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

从源表复制到新表之后,又执行了ALTER TABLE,把 userid 列类型从 MEDIUMINT 改为 INT,创建存储过程等其他工作。

生成新表后,再写入生成的日志。但是呢,写入日志却又是采用INSERT…SELECT的用法。一般情况下当然没问题,但这个例子中,源表、目标表的 userid 列类型恰好不一样(源是MEDIUMINT,目标是INT),结果导致在 binglog 中记录event时,将 userid 列类型强制转换为 MEDIUMINT 了。这个 INSERT 在 MASTER 端可以正常执行完毕,但却引发了 SLAVE 检测到二者数据类型不一致,写入失败,复制异常中断。

3、问题建议

遇到这种案例也真的是醉了,从源表每天克隆一个新表做法没问题,采用INSERT…SELECT也没问题,但为啥要源表和新表使用不同数据类型呢,直接把源表的也改成INT不就行了吗,只能说某些人懒得不像样了。

4、类似案例

FAQ系列 | 列类型被自动修改导致复制失败

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

 

FAQ系列 | SLAVE为什么一直不动了

导读

遇到SLAVE延迟很大,binlog apply position一直不动的情况如何排查?

问题描述

收到SLAVE延迟时间一直很大的报警,于是检查一下SLAVE状态(无关状态我给隐去了):

          Slave_IO_State: Waiting for master to send event
         Master_Log_File: mysql-bin.000605
     Read_Master_Log_Pos: 1194
          Relay_Log_File: mysql-relay-bin.003224
           Relay_Log_Pos: 295105
   Relay_Master_Log_File: mysql-bin.000604
        Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
              Last_Errno: 0
              Last_Error: 
     Exec_Master_Log_Pos: 294959
         Relay_Log_Space: 4139172581
   Seconds_Behind_Master: 10905

可以看到,延迟确实很大,而且从多次show slave status的结果来看,发现binlog的position一直不动。

     Read_Master_Log_Pos: 1194
          Relay_Log_File: mysql-relay-bin.003224
           Relay_Log_Pos: 295105
   Relay_Master_Log_File: mysql-bin.000604
     Exec_Master_Log_Pos: 294959
         Relay_Log_Space: 4139172581

从processlist的中也看不出来有什么不对劲的SQL在跑:

******************** 1. row ******************
     Id: 16273070
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 4828912
  State: Waiting for master to send event
   Info: NULL
********************* 2. row *****************
     Id: 16273071
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 9798
  State: Reading event from the relay log
   Info: NULL

在master上查看相应binlog,确认都在干神马事:

[yejr@imysql.com]# mysqlbinlog -vvv --base64-output=decode-rows -j 294959 mysql-bin.000604 | more

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
**# at 294959**
#160204  6:16:30 server id 1  end_log_pos 295029     **Query    thread_id=461151**    **exec_time=2144**    error_code=0
SET TIMESTAMP=1454537790/*!*/;
SET @@session.pseudo_thread_id=461151/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 295029
# at 295085
# at 296040
# at 297047
# at 298056
# at 299068
# at 300104

上面这段内容的几个关键信息:

# at 294959   — binlog起点
thread_id=461151    — master上执行的线程ID
exec_time=2144    — 该事务执行总耗时

再往下看都是一堆的binlog position信息,通过这种方式可读性不强,我们换一种姿势看看:

[yejr@imysql.com (test)]> show binlog events in 'mysql-bin.000604' from 294959 limit 10;
+------------------+--------+-------------+-----------+-------------+----------------------------+
| Log_name         | Pos    | Event_type  | Server_id | End_log_pos | Info                       |
+------------------+--------+-------------+-----------+-------------+----------------------------+
| mysql-bin.000604 | 294959 | Query       |         1 |      295029 | BEGIN                      |
| mysql-bin.000604 | 295029 | Table_map   |         1 |      295085 | table_id: 84 (bacula.File) |
| mysql-bin.000604 | 295085 | Delete_rows |         1 |      296040 | table_id: 84               |
| mysql-bin.000604 | 296040 | Delete_rows |         1 |      297047 | table_id: 84               |
| mysql-bin.000604 | 297047 | Delete_rows |         1 |      298056 | table_id: 84               |
| mysql-bin.000604 | 298056 | Delete_rows |         1 |      299068 | table_id: 84               |
| mysql-bin.000604 | 299068 | Delete_rows |         1 |      300104 | table_id: 84               |
| mysql-bin.000604 | 300104 | Delete_rows |         1 |      301116 | table_id: 84               |
| mysql-bin.000604 | 301116 | Delete_rows |         1 |      302147 | table_id: 84               |
| mysql-bin.000604 | 302147 | Delete_rows |         1 |      303138 | table_id: 84               |

+—————————+————+——————-+—————-+——————-+——————————————+

可以看到,这个事务不干别的,一直在删除数据。
这是一个Bacula备份系统,会每天自动删除一个月前的过期数据。
事实上,这个事务确实非常大,从binlog的294959开始,一直到这个binlog结束4139169218,一直都是在干这事,总共大概有3.85G的binlog要等着apply。

-rw-rw---- 1 mysql mysql 1.1G Feb  3 03:07 mysql-bin.000597
-rw-rw---- 1 mysql mysql 1.1G Feb  3 03:19 mysql-bin.000598
-rw-rw---- 1 mysql mysql 2.1G Feb  3 03:33 mysql-bin.000599
-rw-rw---- 1 mysql mysql 1.4G Feb  3 03:45 mysql-bin.000600
-rw-rw---- 1 mysql mysql 1.8G Feb  3 04:15 mysql-bin.000601
-rw-rw---- 1 mysql mysql 1.3G Feb  3 04:53 mysql-bin.000602
-rw-rw---- 1 mysql mysql 4.5G Feb  4 06:16 mysql-bin.000603
-rw-rw---- 1 mysql mysql 3.9G Feb  4 06:52 mysql-bin.000604
-rw-rw---- 1 mysql mysql 1.2K Feb  4 06:52 mysql-bin.000605

可以看到上面的历史binlog,个别情况下,一个事务里一次性要删除数据量太大了,导致binlog文件远超预设的1G,最大的达到4.5G之多。

怎么解决

由于这是Bacula备份系统内置生成的大事务,除非去修改它的源码,否则没有太好的办法。

对于我们一般的应用而言,最好是攒够一定操作后,就先提交一下事务,比如删除几千条记录后提交一次,而不是像本例这样,一个删除事务消耗了将近3.9G的binlog日质量,这种就非常可怕了。

除了会导致SLAVE看起来一直不动以外,还可能会导致某些数据行(data rows)被长时间锁定不释放,而导致大量行锁等待发生。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

FAQ系列 | 如何保证主从复制数据一致性

导读

MySQL主从复制环境中,如何才能保证主从数据的一致性呢?

关于主从复制

现在常用的MySQL高可用方案,十有八九是基于 MySQL的主从复制(replication)来设计的,包括常规的一主一从、双主模式,或者半同步复制(semi-sync replication)。

我们常常把MySQL replication说成是MySQL同步(sync),但事实上这个过程是异步(async)的。大概过程是这样的:

  1. 在master上提交事务后,并且写入binlog,返回事务成功标记;
  2. 将binlog发送到slave,转储成relay log;
  3. 在slave上再将relay log读取出来应用。

步骤1和步骤3之间是异步进行的,无需等待确认各自的状态,所以说MySQL replication是异步的。

MySQL semi-sync replication在之前的基础上做了加强完善,整个流程变成了下面这样:

  1. 首先,master和至少一个slave都要启用semi-sync replication模式;
  2. 某个slave连接到master时,会主动告知当前自己是否处于semi-sync模式;
  3. 在master上提交事务后,写入binlog后,还需要通知至少一个slave收到该事务,等待写入relay log并成功刷新到磁盘后,向master发送“slave节点已完成该事务”确认通知;
  4. master收到上述通知后,才可以真正完成该事务提交,返回事务成功标记;
  5. 在上述步骤中,当slave向master发送通知时间超过rpl_semi_sync_master_timeout设定值时,主从关系会从semi-sync模式自动调整成为传统的异步复制模式。

半同步复制看起来很美好有木有,但如果网络质量不高,是不是出现抖动,触发上述第5条的情况,会从半同步复制降级为普通复制;此外,采用半同步复制,会导致master上的tps性能下降非常严重,最严重的情况下可能会损失50%以上。

这样来看,除非需要非常严格保证数据一致性等迫不得已的场景,就不太建议使用半同步复制了。当然了,事实上我们也可以通过加强程序端的逻辑控制,来避免主从数据不一致时发生逻辑错误,比如说如果在从上读取到的数据和主不一致的话,那么就触发主从间的一次数据修复工作。或者,我们也可以用 pt-table-checksum & pt-table-sync 两个工具来校验并修复数据,只要运行频率适当,是可行的。

真想要提高多节点间的数据一致性,可以考虑采用PXC方案。现在已知用PXC规模较大的有qunar、sohu,如果团队里初期没有人能比较专注PXC的话,还是要谨慎些,毕竟和传统的主从复制差异很大,出现问题时需要花费更多精力去排查解决。

如何保证主从复制数据一致性

上面说完了异步复制、半同步复制、PXC,我们回到主题:在常规的主从复制场景里,如何能保证主从数据的一致性,不要出现数据丢失等问题呢?

在MySQL中,一次事务提交后,需要写undo、写redo、写binlog,写数据文件等等。在这个过程中,可能在某个步骤发生crash,就有可能导致主从数据的不一致。为了避免这种情况,我们需要调整主从上面相关选项配置,确保即便发生crash了,也不能发生主从复制的数据丢失。

1. 在master上修改配置

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

上述两个选项的作用是:保证每次事务提交后,都能实时刷新到磁盘中,尤其是确保每次事务对应的binlog都能及时刷新到磁盘中,只要有了binlog,InnoDB就有办法做数据恢复,不至于导致主从复制的数据丢失。

2. 在slave上修改配置

master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1

上述前两个选项的作用是:确保在slave上和复制相关的元数据表也采用InnoDB引擎,受到InnoDB事务安全的保护,而后一个选项的作用是开启relay log自动修复机制,发生crash时,会自动判断哪些relay log需要重新从master上抓取回来再次应用,以此避免部分数据丢失的可能性。

通过上面几个选项的调整,就可以确保主从复制数据不会发生丢失了。但是,这并不能保证主从数据的绝对一致性,因为,有可能设置了ignore\do\rewrite等replication规则,或者某些SQL本身存在不确定因素,或者人为在slave上修改数据,最终导致主从数据不一致。这种情况下,可以采用pt-table-checksumpt-table-sync 工具来进行数据的校验和修复。

MySQL高可用方案选型参考

本次专题是 MySQL高可用方案选型,这个专题想必有很多同学感兴趣。

高可用的意义以及各种不同高可用等级相应的停机时间我就不必多说了,直接进入主题。

可选MySQL高可用方案

MySQL的各种高可用方案,大多是基于以下几种基础来部署的:

  1. 基于主从复制;
  2. 基于Galera协议;
  3. 基于NDB引擎;
  4. 基于中间件/proxy;
  5. 基于共享存储;
  6. 基于主机高可用;

在这些可选项中,最常见的就是基于主从复制的方案,其次是基于Galera的方案,我们重点说说这两种方案。其余几种方案在生产上用的并不多,我们只简单说下。

基于主从复制的高可用方案

双节点主从 + keepalived/heartbeat

一般来说,中小型规模的时候,采用这种架构是最省事的。
两个节点可以采用简单的一主一从模式,或者双主模式,并且放置于同一个VLAN中,在master节点发生故障后,利用keepalived/heartbeat的高可用机制实现快速切换到slave节点。

在这个方案里,有几个需要注意的地方:

  • 采用keepalived作为高可用方案时,两个节点最好都设置成BACKUP模式,避免因为意外情况下(比如脑裂)相互抢占导致往两个节点写入相同数据而引发冲突;
  • 把两个节点的auto_increment_increment(自增步长)和auto_increment_offset(自增起始值)设成不同值。其目的是为了避免master节点意外宕机时,可能会有部分binlog未能及时复制到slave上被应用,从而会导致slave新写入数据的自增值和原先master上冲突了,因此一开始就使其错开;当然了,如果有合适的容错机制能解决主从自增ID冲突的话,也可以不这么做;
  • slave节点服务器配置不要太差,否则更容易导致复制延迟。作为热备节点的slave服务器,硬件配置不能低于master节点;
  • 如果对延迟问题很敏感的话,可考虑使用MariaDB分支版本,或者直接上线MySQL 5.7最新版本,利用多线程复制的方式可以很大程度降低复制延迟;
  • 对复制延迟特别敏感的另一个备选方案,是采用semi sync replication(就是所谓的半同步复制)或者后面会提到的PXC方案,基本上无延迟,不过事务并发性能会有不小程度的损失,需要综合评估再决定;
  • keepalived的检测机制需要适当完善,不能仅仅只是检查mysqld进程是否存活,或者MySQL服务端口是否可通,还应该进一步做数据写入或者运算的探测,判断响应时间,如果超过设定的阈值,就可以启动切换机制;
  • keepalived最终确定进行切换时,还需要判断slave的延迟程度。需要事先定好规则,以便决定在延迟情况下,采取直接切换或等待何种策略。直接切换可能因为复制延迟有些数据无法查询到而重复写入;
  • keepalived或heartbeat自身都无法解决脑裂的问题,因此在进行服务异常判断时,可以调整判断脚本,通过对第三方节点补充检测来决定是否进行切换,可降低脑裂问题产生的风险。

双节点主从+keepalived/heartbeat方案架构示意图见下:

MySQL双节点高可用架构

图解:MySQL双节点(单向/双向主从复制),采用keepalived实现高可用架构。

多节点主从+MHA/MMM

多节点主从,可以采用一主多从,或者双主多从的模式。
这种模式下,可以采用MHA或MMM来管理整个集群,目前MHA应用的最多,优先推荐MHA,最新的MHA也已支持MySQL 5.6的GTID模式了,是个好消息。
MHA的优势很明显:

  • 开源,用Perl开发,代码结构清晰,二次开发容易;
  • 方案成熟,故障切换时,MHA会做到较严格的判断,尽量减少数据丢失,保证数据一致性;
  • 提供一个通用框架,可根据自己的情况做自定义开发,尤其是判断和切换操作步骤;
  • 支持binlog server,可提高binlog传送效率,进一步减少数据丢失风险。

不过MHA也有些限制

  • 需要在各个节点间打通ssh信任,这对某些公司安全制度来说是个挑战,因为如果某个节点被黑客攻破的话,其他节点也会跟着遭殃;
  • 自带提供的脚本还需要进一步补充完善,当然了,一般的使用还是够用的。

多节点主从+etcd/zookeeper

在大规模节点环境下,采用keepalived或者MHA作为MySQL的高可用管理还是有些复杂或麻烦。
首先,这么多节点如果没有采用配置服务来管理,必然杂乱无章,线上切换时很容易误操作。
在较大规模环境下,建议采用etcd/zookeeper管理集群,可实现快速检测切换,以及便捷的节点管理。

基于Galera协议的高可用方案

Galera是Codership提供的多主数据同步复制机制,可以实现多个节点间的数据同步复制以及读写,并且可保障数据库的服务高可用及数据一致性。
基于Galera的高可用方案主要有MariaDB Galera Cluster和Percona XtraDB Cluster(简称PXC),目前PXC用的会比较多一些。

PXC的架构示意图见下:

pxc overview

(图片源自网络),图解:在底层采用wsrep接口实现数据在多节点间的同步复制。

 

pxc certification

(图片源自网络),图解:在PXC中,一次数据写入在各个节点间的验证/回滚流程。

PXC的优点

  • 服务高可用;
  • 数据同步复制(并发复制),几乎无延迟;
  • 多个可同时读写节点,可实现写扩展,不过最好事先进行分库分表,让各个节点分别写不同的表或者库,避免让galera解决数据冲突;
  • 新节点可以自动部署,部署操作简单;
  • 数据严格一致性,尤其适合电商类应用;
  • 完全兼容MySQL;

虽然有这么多好处,但也有些局限性:

  • 只支持InnoDB引擎;
  • 所有表都要有主键;
  • 不支持LOCK TABLE等显式锁操作;
  • 锁冲突、死锁问题相对更多;
  • 不支持XA;
  • 集群吞吐量/性能取决于短板;
  • 新加入节点采用SST时代价高;
  • 存在写扩大问题;
  • 如果并发事务量很大的话,建议采用InfiniBand网络,降低网络延迟;

事实上,采用PXC的主要目的是解决数据的一致性问题,高可用是顺带实现的。因为PXC存在写扩大以及短板效应,并发效率会有较大损失,类似semi sync replication机制。

其他高可用方案

  • 基于NDB Cluster,由于NDB目前仍有不少缺陷和限制,不建议在生产环境上使用;
  • 基于共享存储,一方面需要不太差的存储设备,另外共享存储可也会成为新的单点,除非采用基于高速网络的分布式存储,类似RDS的应用场景,架构方案就更复杂了,成本也可能更高;
  • 基于中间件(Proxy),现在可靠的Proxy选择并不多,而且没有通用的Proxy,都有有所针对,比如有的专注解决读写分离,有的专注分库分表等等,真正好用的Proxy一般要自行开发;
  • 基于主机高可用,是指采用类似RHCS构建一个高可用集群后,再部署MySQL应用的方案。老实说,我没实际用过,但从侧面了解到这种方案生产上用的并不多,可能也有些局限性所致吧;

以DBA们的聪明才智,肯定还有其他我不知道的方案,也欢迎同行们间多多交流。

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

[MySQL FAQ]系列 — 不同复制模式下,如何忽略某些binlog事件

导读

在MySQL复制中,如何忽略slave节点上发生的主键冲突、数据不存在等错误。

在MySQL复制中,如果slave节点上遇到错误,比如数据不存在或者主键冲突等错误时,想要忽略这些错误,可以采用以下几种方法:

1、未启用GTID模式时

只需通过设定 SQL_SLAVE_SKIP_COUNTER 的值,即可忽略一些复制事件。例如:

#需要先关闭SLAVE服务
root@imysql.com [test]> STOP SLAVE;

#忽略N个事件(event),通常一个SQL是一个事件
root@imysql.com [test]> SET SQL_SLAVE_SKIP_COUNTER=N;

#再次启动SLAVE服务
root@imysql.com [test]> START SLAVE;

2、启用GTID模式时

启用GTID,想要忽略某些错误事件就稍微麻烦一点点了。
首先,我们需要先查看当前SLAVE复制的进度:

mysql> SHOW SLAVE STATUS\G

从中看到,当前SLAVE复制的GTID进展是:

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
Last_Error: …Duplicate…key ‘PRIMARY’, Error_code: 1062;…
Master_UUID: f2b6c829-9c87-11e4-84e8-deadeb54b599
Retrieved_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-33
Executed_Gtid_Set: 3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-31
Auto_Position: 1

从上面的信息可以看到,当前从MASTER取到了1-33的事务列表,并且已执行(看Executed_Gtid_Set)到了31这个事务GTID位置,在这下一个位置(32)上发生错误。

这时候,我们需要手工调整SLAVE已清除的GTID列表 GTID_PURGED,人为通知SLAVE哪些事务已经被清除了,后续可以忽略:

root@imysql.com [test]> STOP SLAVE;
root@imysql.com [test]> RESET MASTER;
root@imysql.com [test]> SET @@GLOBAL.GTID_PURGED = “3a16ef7a-75f5-11e4-8960-deadeb54b599:1-283,f2b6c829-9c87-11e4-84e8-deadeb54b599:1-32”;
root@imysql.com [test]> START SLAVE;

上面这些命令的用意是,忽略 f2b6c829-9c87-11e4-84e8-deadeb54b599:32 这个GTID事务,下一次事务接着从 33 这个GTID开始,即可跳过上述错误。

3、无论是否启用GTID,使用pt-slave-restart工具

首先不得不说,percona toolkit工具集对DBA而言实在太方便了。pt-slave-restart工具的作用是监视某些特定的复制错误,然后忽略,并且再次启动SLAVE进程(Watch and restart MySQL replication after errors)。
简单用法示例:

#忽略所有1062错误,并再次启动SLAVE进程
[yejr@imysql.com ]# pt-slave-resetart -S./mysql.sock —error-numbers=1062

#检查到错误信息只要包含 test.yejr,就一概忽略,并再次启动 SLAVE 进程
[yejr@imysql.com ]# pt-slave-resetart -S./mysql.sock —error-text=”test.yejr”

综上,我们虽然可以利用工具来快速忽略复制错误,但还是要掌握如何人为忽略复制错误的方法,在没有工具的时候也能了然于胸。

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

MySQL 5.7版本新特性连载(四)

本文是基于MySQL-5.7.7-rc版本,未来可能 还会发生更多变化。

1、SQL MODE变化
a. 默认启用 STRICT_TRANS_TABLES 模式
b. 对 ONLY_FULL_GROUP_BY 模式实现了更复杂的特性支持,并且也被默认启用;
c. 其他被默认启用的sql mode还有 NO_ENGINE_SUBSTITUTION;

【iMySQL建议】
对广大MySQL使用者而言,以往不是那么严格的模式还是很方便的,在5.7版本下可能会觉得略为不适,慢慢习惯吧。比如向一个20字符长度的VARCHAR列写入30个字符,在以前会自动截断并给个提示告警,而在5.7版本下,则直接抛出错误了。个人认为这倒是一个好的做法,避免各种奇葩的写法。

【新特性实践】

-- 查看默认的 sql_mode
[yejr@imysql.com]> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------+

-- 插入50个字符
[yejr@imysql.com]> insert into t_char select 0, repeat('x',50);
ERROR 1406 (22001): Data too long for column 'uname' at row 1

-- 修改本 session 的 sql_mode
[yejr@imysql.com]> set sql_mode = 'ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

-- 去掉 STRICT_TRANS_TABLES 模式后
[yejr@imysql.com]> select @@sql_mode;
+---------------------------------------------------------------+
| @@sql_mode |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

[yejr@imysql.com]> insert into t_char select 0, repeat('x',50);
Query OK, 1 row affected, 1 warning (0.00 sec)  -- 提示有告警信息
Records: 1 Duplicates: 0 Warnings: 1

[yejr@imysql.com]> show warnings;
+---------+------+--------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------+
| Warning | 1265 | Data truncated for column 'uname' at row 1 |
+---------+------+--------------------------------------------+

因为 uname 字段的长度为 40 个字符。

2、优化online操作例如修改buffer pool修改索引名(非主键)、修改REPLICATION FILTER、修改MASTER而无需关闭SLAVE线程 等众多特性。

可以在线修改buffer pool对DBA来说实在太方便了,实例运行过程中可以动态调整,避免事先分配不合理的情况,不过 innodb_buffer_pool_instances 不能修改,而且在 innodb_buffer_pool_instances 大于 1 时,也不能将 buffer pool 调整到 1GB 以内,需要稍加注意。

如果是加大buffer pool,其过程大致是:

1、以innodb_buffer_pool_chunk_size为单位,分配新的内存pages;
2、扩展buffer pool的AHI(adaptive hash index)链表,将新分配的pages包含进来;
3、将新分配的pages添加到free list中;

如果是缩减buffer pool,其过程则大致是:

1、重整buffer pool,准备回收pages;
2、以innodb_buffer_pool_chunk_size为单位,释放删除这些pages(这个过程会有一点点耗时);
3、调整AHI链表,使用新的内存地址。

实际测试时,发现在线修改 buffer poo 的代价并不大,SQL命令提交完毕后都是瞬间完成,而后台进程的耗时也并不太久。在一个并发128线程跑tpcc压测的环境中,将 buffer pool 从32G扩展到48G,后台线程耗时 3秒,而从 48G 缩减回 32G 则耗时 18秒,期间压测的事务未发生任何锁等待。

-- 演示1:从 1G 扩大到 16G
[yejr@imysql.com]> SET GLOBAL innodb_buffer_pool_size = 51539607552; 
Query OK, 0 rows affected (0.00 sec)

-- 看看日志记录
09:21:19.460543Z 0 [Note] InnoDB: Resizing buffer pool from 1073741824 to 17179869184. (unit=134217728)
09:21:19.468069Z 0 [Note] InnoDB: disabled adaptive hash index.
09:21:20.760724Z 0 [Note] InnoDB: buffer pool 0 : 60 chunks (491511 blocks) were added.
09:21:21.922869Z 0 [Note] InnoDB: buffer pool 1 : 60 chunks (491520 blocks) were added.
09:21:21.935114Z 0 [Note] InnoDB: buffer pool 0 : hash tables were resized.
09:21:21.947264Z 0 [Note] InnoDB: buffer pool 1 : hash tables were resized.
09:21:22.203031Z 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
09:21:22.203062Z 0 [Note] InnoDB: Completed to resize buffer pool from 1073741824 to 17179869184.
09:21:22.203075Z 0 [Note] InnoDB: Re-enabled adaptive hash index.

-- 演示2:从 16G 缩减到 1G
[yejr@imysql.com]> SET GLOBAL innodb_buffer_pool_size = 1073741824;
Query OK, 0 rows affected (0.00 sec)

-- 看看日志记录
09:22:55.591669Z 0 [Note] InnoDB: Resizing buffer pool from 17179869184 to 1073741824. (unit=134217728)
09:22:55.680836Z 0 [Note] InnoDB: disabled adaptive hash index.
09:22:55.680864Z 0 [Note] InnoDB: buffer pool 0 : start to withdraw the last 491511 blocks.
09:22:55.765778Z 0 [Note] InnoDB: buffer pool 0 : withdrew 489812 blocks from free list. Tried to relocate 1698 pages (491510/491511).
09:22:55.774492Z 0 [Note] InnoDB: buffer pool 0 : withdrew 0 blocks from free list. Tried to relocate 1 pages (491511/491511).
09:22:55.782745Z 0 [Note] InnoDB: buffer pool 0 : withdrawn target 491511 blocks.
09:22:55.782786Z 0 [Note] InnoDB: buffer pool 1 : start to withdraw the last 491520 blocks.
09:22:55.892068Z 0 [Note] InnoDB: buffer pool 1 : withdrew 489350 blocks from free list. Tried to relocate 2166 pages (491517/491520).
09:22:55.900743Z 0 [Note] InnoDB: buffer pool 1 : withdrew 0 blocks from free list. Tried to relocate 2 pages (491519/491520).
09:22:55.908257Z 0 [Note] InnoDB: buffer pool 1 : withdrew 0 blocks from free list. Tried to relocate 0 pages (491519/491520).
09:22:55.915778Z 0 [Note] InnoDB: buffer pool 1 : withdrew 0 blocks from free list. Tried to relocate 1 pages (491520/491520).
09:22:55.923836Z 0 [Note] InnoDB: buffer pool 1 : withdrawn target 491520 blocks.
09:22:56.149172Z 0 [Note] InnoDB: buffer pool 0 : 60 chunks (491511 blocks) were freed.
09:22:56.308997Z 0 [Note] InnoDB: buffer pool 1 : 60 chunks (491520 blocks) were freed.
09:22:56.316258Z 0 [Note] InnoDB: buffer pool 0 : hash tables were resized.
09:22:56.324027Z 0 [Note] InnoDB: buffer pool 1 : hash tables were resized.
09:22:56.393589Z 0 [Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
09:22:56.393616Z 0 [Note] InnoDB: Completed to resize buffer pool from 17179869184 to 1073741824.
09:22:56.393628Z 0 [Note] InnoDB: Re-enabled adaptive hash index.

 

再来看下在线修改非主键索引名,直接用 ALTER TABLE RENAME INDEX 语法即可。

【新特性实践】

例如下面的SQL语法:

[yejr@imysql.com]> ALTER TABLE orders RENAME INDEX idx1 TO idxxx1; 

Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

可以看到,几乎瞬间完成,尽管我在执行这个SQL时正跑着64个并发tpcc压测。

 

延伸阅读:

整理的比较仓促,若有遗漏或失误,请留言回复,谢谢!

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yuhongli.com 获得专属优惠

 

 

[MySQL优化案例]系列 — slave延迟很大优化方法

mysql replication
备注:插图来自网络搜索,如果觉得不当还请及时告知 :)

一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发。简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master。

ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制。不过,它只能支持一个实例下多个 database 间的并发复制,并不能真正做到多表并发复制。因此在较大并发负载时,slave还是没有办法及时追上master,需要想办法进行优化。

另一个重要原因是,传统的MySQL复制是异步(asynchronous)的,也就是说在master提交完后,才在slave上再应用一遍,并不是真正意义上的同步。哪怕是后来的Semi-sync Repication(半同步复制),也不是真同步,因为它只保证事务传送到slave,但没要求等到确认事务提交成功。既然是异步,那肯定多少会有延迟。因此,严格意义上讲,MySQL复制不能叫做MySQL同步(处女座的面试官有可能会在面试时把说成MySQL同步的一律刷掉哦)。

另外,不少人的观念里,slave相对没那么重要,因此就不会提供和master相同配置级别的服务器。有的甚至不但使用更差的服务器,而且还在上面跑多实例。

综合这两个主要原因,slave想要尽可能及时跟上master的进度,可以尝试采用以下几种方法:

  1. 采用MariaDB发行版,它实现了相对真正意义上的并行复制,其效果远比ORACLE MySQL好的很多。在我的场景中,采用MariaDB作为slave的实例,几乎总是能及时跟上master。如果不想用这个版本的话,那就老实等待官方5.7大版本发布吧;
    关于MariaDB的Parallel Replication具体请参考:Replication and Binary Log Server System Variables#slave_parallel_threads – MariaDB Knowledge Base
  2. 每个表都要显式指定主键,如果没有指定主键的话,会导致在row模式下,每次修改都要全表扫描,尤其是大表就非常可怕了,延迟会更严重,甚至导致整个slave库都被挂起,可参考案例:mysql主键的缺少导致备库hang
  3. 应用程序端多做些事,让MySQL端少做事,尤其是和IO相关的活动,例如:前端通过内存CACHE或者本地写队列等,合并多次读写为一次,甚至消除一些写请求;
  4. 进行合适的分库、分表策略,减小单库单表复制压力,避免由于单库单表的的压力导致整个实例的复制延迟;
  5. 其他提高IOPS性能的几种方法,根据效果优劣,我做了个简单排序:
    • 更换成SSD,或者PCIe SSD等IO设备,其IOPS能力的提升是普通15K SAS盘的数以百倍、万倍,甚至几十万倍计;
    • 加大物理内存,相应提高InnoDB Buffer Pool大小,让更多热数据放在内存中,降低发生物理IO的频率;
    • 调整文件系统为 XFS 或 ReiserFS,相比ext3可以极大程度提高IOPS能力。在高IOPS压力下,相比ext4有更稳健的IOPS表现(有人认为 XFS 在特别的场景下会有很大的问题,但我们除了剩余磁盘空间少于10%时引发丢数据外,其他的尚未遇到);
    • 调整RAID级别为raid 1+0,它相比raid1、raid5等更能提高IOPS性能。如果已经全部是SSD设备了,可以2块盘做成RAID 1,或者多快盘做成RAID 5(并且可以设置全局热备盘,提高阵列容错性),甚至有些土豪用户直接将多块SSD盘组成RAID 50;
    • 调整RAID的写cache策略为WB或FORCE WB,详情请参考:常用PC服务器阵列卡、硬盘健康监控 以及 PC服务器阵列卡管理简易手册
    • 调整内核的io scheduler,优先使用deadline,如果是SSD,则可以使用noop策略,相比默认的cfq,个别情况下对IOPS的性能提升至少是数倍的。

其他更多方法,欢迎大家帮忙补充 :)

[MySQL FAQ]系列 — 不同的binlog_format会导致哪些SQL不会被记录

我们都知道binlog_format有三种可选配置:STATEMENT、ROW、MIXED,相应地,基于这三种模式的Replication分别称为SBR(STATEMENT BASED Replication)、RBR、MBR。 同时,我们也知道,MySQL Replication可以支持比较灵活的binlog规则,可以设置某些库、某些表记录或者忽略不记录。

通常地,我们强烈建议不要设置这些规则,默认都记录就好,在Slave上也是如此,默认所有库都进行Replicate,不要设置DO、IGNORE、REWRITE规则。 如果非要设置这些规则的话,可能会导致某些场景下或者某些特定的SQL无法被记录,就需要特别注意了。

我经过比较简单的测试,不同的binlog_format可能会导致某些SQL不被记录的情况总结如下:

mysql-faq-how-binlog-format-affect-replication
上面的测试区分了两种模式,一种是连接时指定了其他数据库,一种是连接时未指定任何数据库,相当于下面的两种方式:
#假设do/ignore规则中的DB名字叫DoDB/IgnoreDB/RewriteDB的话,OtherDB是规则之外的其他DB

#一种是:连接时指定了do/ignore/rewrite规则之外的其他DB
mysql -h host -u user -p passwd -p port -A OtherDB

#还有一种是:连接时不指定任何DB
mysql -h host -u user -p passwd -p port -A

#tips,加上 -A 是--no-auto-rehash的缩写,其作用是连接后不读取数据库、表、字段信息。与其相反的选项是 --auto-rehash,也就是连接后会读取数据库、表、字段信息,以便自动补齐。
更多情况请读者自行进行测试吧 :)