MySQL V5.7 多源复制特性

前端时间,一直关注5.7有多源复制的特性,当时,5.7.5版本不支持,而且官方文档也没有说明,本以为,只能用mariadb的多源复制特性,没想到,最近在看官方文档时,已有了相关文档,于是,果断试了一下。下面做一下,简单的演示:

官方文档:
http://dev.mysql.com/doc/refman/5.7/en/replication-multi-source-tutorials.html

要求:

1.复制的相关信息需要存在 table ,不能存在 file:

–master-info-repository=TABLE

–relay-log-info-repository=TABLE

2.多源复制,可以基于GTID,也可以基于 position

3.部署时,所使用的命令如下:

# 基于GTID模式的:

CHANGE MASTER TO MASTER_HOST=’master1′, MASTER_USER=’blackhole’, MASTER_PORT=3306,MASTER_PASSWORD=’black@hole’ MASTER_AUTO_POSITION = 1 FOR CHANNEL ‘master-1′;

# 基于Position模式的:

CHANGE MASTER TO MASTER_HOST=’172.16.10.54′, MASTER_USER=’blackhole’, MASTER_PORT=3306, MASTER_PASSWORD=’black@hole’ ,MASTER_LOG_FILE=’mysql-bin.000047′, MASTER_LOG_POS=602 FOR CHANNEL ‘master-1′;

4.开启 / 关闭 相关channel的复制:

# 开启/关闭全部

START SLAVE thread_types;

STOP SLAVE thread_types;

# 开启/关闭指定channel

START SLAVE thread_types FOR CHANNEL channel;

STOP SLAVE thread_types FOR CHANNEL channel;

# 关于 stop/start slave的语法:

STOP/START SLAVE [thread_types] FOR CHANNEL channel

thread_type:

IO_THREAD | SQL_THREAD

# MySQL 5.7 目前还没有GA版本发布,暂时,不做详细测试,仅根据官方文档说明,留以备注
下面是一个简单的测试:

172.16.10.54:3306 master1 mysql-5.6.25 复制用户/密码:blackhole/black@hole 日志点:mysql-bin.000047、602

172.16.10.54:3307 master2 mariadb-10 复制用户/密码:blackhole/black@hole 日志点:mysql-bin.000015 、23736

172.16.10.55:3306 slave mysql-5.7.8

slave机器添加两个主从的方式如下:

# 添加master-1:

CHANGE MASTER TO MASTER_HOST=’172.16.10.54′, MASTER_USER=’blackhole’, MASTER_PORT=3306, MASTER_PASSWORD=’black@hole’ ,MASTER_LOG_FILE=’mysql-bin.000047′, MASTER_LOG_POS=602 FOR CHANNEL ‘master-1′;

# 添加master-2:

CHANGE MASTER TO MASTER_HOST=’172.16.10.54′, MASTER_USER=’blackhole’, MASTER_PORT=3307, MASTER_PASSWORD=’black@hole’ ,MASTER_LOG_FILE=’mysql-bin.000015′, MASTER_LOG_POS=23736 FOR CHANNEL ‘master-2’;

#启动主从:

start slave;

#启动指定主从:

start slave for channel ‘master-1’;

#查看主从状态:

show slave status\G;

#查看指定从机状态:

show slave status for channel ‘master-1’\G;

# 最终截图如下:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.54
Master_User: blackhole
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000047
Read_Master_Log_Pos: 602
Relay_Log_File: slave3-relay-bin-master@002d1.000002
Relay_Log_Pos: 317
Relay_Master_Log_File: mysql-bin.000047
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Channel_Name: master-1
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.54
Master_User: blackhole
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 23736
Relay_Log_File: slave3-relay-bin-master@002d2.000002
Relay_Log_Pos: 445
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Channel_Name: master-2

mysql 5.6 新特性中,自定义刷新日志时间:innodb_flush_log_at_timeout

      今天有一朋友发了一个参数过来(innodb_flush_log_at_timeout),着实没见过这个参数,从字面意思上理解和刷新日志有关,所以就查了一下,我这里说一下自己的观点:

      1.innodb_flush_log_at_timeout 这个参数的意思是刷新日志的时间,在mysql5.6版本中可以自定义,默认为1s。其与oracle有很大区别:
      在oracle中,有三种情况可以将日志缓冲区的数据写到在线日志文件中
      (1).日志缓冲区中的记录达到1M
      (2).每隔3秒
      (3).日志缓冲区已经用了三分之一
     2.INNODB REDO日志:InnoDB为了保证日志的刷写的高效,使用了内存的log buffer。
      由于InnoDB大部分情况下使用的是文件系统,(linux文件系统本身也是有buffer的)而不是直接使用物理块设备,这样的话就有两种丢失日志的可能性:日志保存在log_buffer中,机器挂了,对应的事务数据就丢失了;日志从log buffer刷到了linux文件系统的buffer,机器挂掉了,对应的事务数据就丢失了。
     3.InnoDB有一个参数用于设置这两个缓存的刷新: innodb_flush_log_at_trx_commit。而 innodb_flush_log_at_trx_commit  有三个值:0/1/2,默认是1。而innodb_flush_log_at_timeout 定义了每次日志刷新的时间,与  innodb_flush_log_at_trx_commit 配合使用,其具体流程,先看下图:
innodb_flush_log_at_time
innodb_flush_log_at_time
       innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。
       innodb_flush_log_at_trx_commit=0,表示每隔一秒把log buffer刷到文件系统中(os buffer)去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。也就是说一秒之前的日志都保存在日志缓冲区,也就是内存上,如果机器宕掉,可能丢失1秒的事务数据。
       innodb_flush_log_at_trx_commit=2,表示在每次事务提交的时候会把log buffer刷到文件系统中(os buffer)去,但是每隔一秒调用文件系统(os buffer)的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。
       结合上面几个参数的描述,我相信多数企业采用mysql innodb存储引擎都是为了充分保证数据的一致性,所以,innodb_flush_log_at_trx_commit这个参数一般都是 1,这样的话,innodb_flush_log_at_timeout 的设置对其就不起作用。innodb_flush_log_at_timeout 的设置只针对 innodb_flush_log_at_trx_commit为0/2 起作用,所以,此参数可暂时不做研究!

rhel6系统中,mysql 5.6复制新特性下主从复制配置[基于GTID]

1.mysql5.6在复制方面的新特性:

(1).支持多线程复制:事实上是针对每个database开启相应的独立线程,即每个库有一个单独的(sql thread).针对这样的改进,如果我们想实现多线程复制,无疑要对现存的数据库结构进行重新设计,分库分表.对于压力都集中在个别database的,多线程并发复制特性就没有意义.

(2).支持启用GTID,在配置主从复制,传统的方式里,你需要找到binlog和POS点,然后change master to指向.在mysql5.6里,无须再知道binlog和POS点,只需要知道master的IP/端口/账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步.

(3).基于Row复制只保存改变的列,大大节省Disk Space/Newwork resources和Memory usage.

(4).支持把Master 和Slave的相关信息记录在Table中,原来是记录在文件里,记录在表里,增强可用性

(5).支持延迟复制

注:

关于 server_uuid 的解释:服务器身份ID。在第一次启动Mysql时,会自动生成一个server_uuid并写入到数据目录下auto.cnf文件里,官方不建议修改。

  1. [root@client102 ~]# cat /home/mysql/data/auto.cnf  
  2. [auto]  
  3. server-uuid=14be3ddd-4e92-11e3-8335-000c299c1b31  

关于GTID的解释: 全局事务标识符。当开启这个功能时,每次事务提交都会在binlog里生成一个唯一的标示符,它由server_uuid和事务ID组成。首次提交的事务ID为1,第二次为2,第三次为3,依次类推。

2.mysql主从复制的原理图:[此原理没有变化]

mysql主从复制原理图

3. MySQL5.6开始主从复制有两种方式:基于日志(binlog)[基于日志的搭建,上篇文章已搭建过];基于GTID(全局事务标识符)

本次配置是基于GTID(全局事务标识符),但其也有劣势,实际生产环境中,暂时不推荐使用

基于GTID(全局事务标识符)的局限性:

(1).GTID同步复制是基于事务。所以Myisam表不支持,这可能导致多个GTID分配给同一个事务。(5.6.9版本已经修改,支持修改Myisam表)

(2).gtid_mode和enforce-gtid-consistency=true 必须同时使用,不同时使用,启动Mysql报错。

(3).无法修改myisam表的数据,会提示”Updates to non-transactional tables are forbidden when disable-gtid-unsafe-statements”   –> 这个我测试5.6.14,是可以正常修改数据,所以这点劣势待定,大家可以分享测试结果

(4).不支持对临时表操作:CREATE TEMPORARY TABLE、DROP TEMPORARY TABLE     –> 这个劣势,5.6.14也可以做,大家可以测试,留言反馈,最近,在5.6.19上测试,是不可以操作,以前5.6.14可能测试有误,这里补充一下。

(5).不支持CREATE TABLE … SELECT语句。因为该语句会被拆分成create table 和insert两个事务,并且这个两个事务被分配了同一个GTID,这会导致insert被备库忽略掉[这条语句在游戏数据库用的比较多,通常用来将大表分成小表]

4.基于GTID(全局事务标识符)mysql主从复制配置演示:

(1).默认主DB server和从DB server数据库都已经安装好,我的两台DB server都已经安装好(5.6.14版本),都会是双实例[这里我使用3307端口的实例]

注:两台机器的的selinux都是disable(永久关闭selinux,请修改/etc/selinux/config,将SELINUX改为disabled),防火墙可以选择关闭,开启的话也行[不行的话,添加防火墙策略]

主DB server:192.168.1.102:3307

从DB server:192.168.1.100:3307

(2).修改主DB server的配置文件(/etc/my.cnf) [client102为主DB server] –> 主DB sever的配置文件和从DB server文件基本一样,方便搭建HA

  1. [root@client102 ~]# vim /etc/my.cnf  
  2. # 在[mysqld]里加入如下代码[里面原代码保留,有重复的部分,以这部分为准]  
  3. # 设置server_id,一般建议设置为IP,或者再加一些数字[在以前版本为server-id]  
  4. server_id =1021  
  5. # 二进制日志的格式:有row、statement和mixed三种  
  6. # 注:当设置隔离级别为READ-COMMITED必须设置二进制日志格式为ROW,MySQL官方认为STATEMENT这个已经不再适合继续使用;但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致; 推荐使用 row  
  7. binlog-format=ROW  
  8. # 这个选项允许应用程序只能对行的镜像数据进行复制,而不在关心行是否已经进行了DML操作.这提高了主从机器的复制吞吐量,减少了二进制日志所占用的磁盘空间、网络资源和内存占用.  
  9. binlog-row-image = minimal  
  10. # 开启二进制日志功能,可以随便取,最好有含义  
  11. log-bin=mysql3307-bin  
  12. # log-slave-updates/gtid-mode/enforce-gtid-consistency/report-port/report-host:用于启动GTID及满足附属的其它需求[其中启动GTID必须同时设置gtid-mode/enforce-gtid-consistency/]  
  13. report-host=192.168.1.102  
  14. report-port=3307  
  15. gtid-mode=on  
  16. enforce-gtid-consistency=true  
  17. log-slave-updates=true  
  18. # master-info-repository/relay-log-info-repository都设置为TABLE,mysql.slave_master_info与 mysql.slave_relay_log_info 中,table都是innodb类型的,支持事务,比文件安全  
  19. # 默认值是FILE, 比如master info就保存在master.info文件中,relay log info保存在relay-log.info文件中,如果服务器意外关闭,正确的relay info 没有来得及更新到 relay-log.info文件,这样会造成数据丢失  
  20. master-info-repository=TABLE  
  21. relay-log-info-repository=TABLE  
  22. # 启用之后,使binlog在每N次binlog写入后与硬盘 同步  
  23. sync-master-info=1  
  24. # 以下是对二进制日志一些设置  
  25. binlog_cache_size = 4M  
  26. max_binlog_size = 1G  
  27. max_binlog_cache_size = 2G  
  28. # 以下这几个参数是启用binlog/relaylog的校验,防止日志出错  
  29. binlog-checksum=CRC32  
  30. slave_allow_batching = 1  
  31. master-verify-checksum=1  
  32. slave-sql-verify-checksum=1  
  33. # 启用这个参数,可用于在二进制日志记录事件相关的信息,可降低故障排除的复杂度  
  34. # 只对row binlog格式有效.启用后,会向binlog中写入更多的调试信息,比如sql语句自身都会被写进去. mysqlbinlog -vv 可以看到.  
  35. binlog-rows-query-log_events=1  
  36. # 开启基于库的多线程复制.默认是0,不开启,最大并发数为1024个线程  
  37. slave-parallel-workers=4  
  38. # 这两个是启用relaylog的自动修复功能,避免由于网络之类的外因造成日志损坏,主从停止.  
  39. relay_log_purge = 1  
  40. relay_log_recovery = 1  

(3).启动数据库服务器,并登陆数据库,授予相应的用户用于同步

  1. # 查看GTID是否开启[enforce_gtid_consistency/gtid_mode 为ON,表示已经开启]  
  2. mysql> show global variables like ‘%gtid%’;  
  3. +————————–+——-+  
  4. | Variable_name | Value |  
  5. +————————–+——-+  
  6. | enforce_gtid_consistency | ON |  
  7. | gtid_executed | |  
  8. | gtid_mode | ON |  
  9. | gtid_owned | |  
  10. | gtid_purged | |  
  11. +————————–+——-+  
  12. # 我这里是多实例mysql,所以启动是这样的,如果大家是单实例的,就直接启动就可以[/etc/init.d/mysqld start]  
  13. [root@client102 ~]# mysqld_multi start 3307  
  14. # 登陆mysql 服务器  
  15. [root@client102 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  16. # 授予用户权限用于主从同步  
  17. mysql> grant replication slave on *.* to ‘kongzhong’@’192.168.1.100’ identified by ‘kongzhong’;  
  18. # 刷新授权表信息  
  19. mysql> flush privileges;  

(4).从DB server配置文件添加代码如下,和主基本一样

  1. [root@client100 ~]# vim /etc/my.cnf  
  2. # 在[mysqld]下添加如下代码[里面原代码保留,有重复的部分,以这部分为准]  
  3. server_id =1002 # 此处和主DB server不一样,唯一值  
  4. binlog-format=ROW  
  5. binlog-row-image = minimal  
  6. log-bin=mysql33071-bin # 此处和主DB server不一样  
  7. report-host=192.168.1.100 # 此处和主DB server不一样  
  8. report-port=3307  
  9. gtid-mode=on  
  10. enforce-gtid-consistency=true  
  11. master-info-repository=TABLE  
  12. relay-log-info-repository=TABLE  
  13. sync-master-info=1  
  14. slave-parallel-workers=4  
  15. binlog_cache_size = 4M  
  16. max_binlog_size = 1G  
  17. max_binlog_cache_size = 2G  
  18. binlog-checksum=CRC32  
  19. master-verify-checksum=1  
  20. slave-sql-verify-checksum=1  
  21. binlog-rows-query-log_events=1  
  22. log-slave-updates=true  
  23. relay_log_purge = 1  
  24. relay_log_recovery = 1  

(5).启动从数据库,添加主DB server指向[关键位置]

  1. # 启动数据库  
  2. [root@client100 ~]# mysqld_multi start 3307  
  3. # 登陆数据库,添加相关参数(主DBserver的ip/端口/同步用户/密码/****) 这里也需要看是否启动GTID,我这里省略  
  4. [root@client100 ~]# mysql -uroot -S /usr/local/mysql/mysqld3307.sock -p  
  5. mysql> change master to  
  6. -> master_host=’192.168.1.102′,  
  7. -> master_user=’kongzhong’,  
  8. -> master_password=’kongzhong’,  
  9. -> master_port=3307,  
  10. -> master_auto_position = 1,  
  11. -> master_delay=30;  
  12. #/* 下面是一部分注解:  
  13. #/* 指定主DB server的IP地址  
  14. master_host=’192.168.1.102′  
  15. #/* 指定用于同步的用户[这个就是我们在主DB server授权的用户]  
  16. master_user=’kongzhong’  
  17. #/* 指定用于同步的用户的密码  
  18. master_password=’kongzhong’  
  19. #/* 指定主DB server的端口[下面一个例子,可以重点看这个]  
  20. master_port=3306  
  21. #/* 自动寻找position号  
  22. master_auto_position = 1  
  23. #/* 延时30秒执行复制,relay日志会及时同步到slave机,只是日志的中的事件会根据事件的时间戳延时30秒执行.此参数具有实用性  
  24. master_delay=30  

(6).开启同步,验证同步状态

  1. # 开启主从同步[以前的版本slave start,新版本已不支持此写法]  
  2. mysql> start slave;  
  3. # 查看主从同步状态  
  4. mysql> show slave status\G;  
  5. # 主要看以下两个参数:[这两个参数如果是yes就表示主从同步正常]  
  6. Slave_IO_Running: Yes  
  7. Slave_SQL_Running: Yes  
  8. # 在新特性下,我们还可以看到如下几个参数  
  9. Retrieved_Gtid_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:4  
  10. Executed_Gtid_Set: 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4  
  11. #/* Retrieved_Gtid_Set项:记录了relay日志从Master获取了binlog日志的位置  
  12. #/* Executed_Gtid_Set项:记录本机执行的binlog日志位置(如果是从机,包括Master的binlog日志位置和slave本身的binlog日志位置)  
  13. # 此时,我们可以在主DB sever 查看一下,现在gtid标识是否和从DB server 一致:  
  14. # 登陆到主DB server执行如下命令[查看gtid_executed和从DB server上的Executed_Gtid_Set是否一致,一致表示同步完成]  
  15. mysql> show global variables like ‘%GTID%’;  
  16. +————————–+——————————————+  
  17. | Variable_name | Value |  
  18. +————————–+——————————————+  
  19. | enforce_gtid_consistency | ON |  
  20. | gtid_executed | 1f14026d-53e7-11e3-a5f9-000c299c1b31:1-4 |  
  21. | gtid_mode | ON |  
  22. | gtid_owned | |  
  23. | gtid_purged | |  
  24. +————————–+——————————————+  
  25. # 此时可以大家可以在主上执行操作,看是否能同步到从上,并时刻对比上面几个参数  
  26. 上面的搭建,省了数据一致部分[我这里是新装的实例,所以数据是一致的],各位如果不是新环境,可以备份,还原数据  
  27. 备份使用如下语句, –triggers –routines –events 这几个参数没有认真揣摩其含义,如有对其了解的,可留言告知  
  28. [root@client102 ~]# mysqldump -uroot -p -S /usr/local/mysql/mysqld3307.sock –all-databases –triggers –routines –events >/tmp/mysql3307.sql  
  29. # 还原语句还和原理一样  
  30. [root@client100 ~]# mysql -uroot -p -S /usr/local/mysql/mysqld3307.sock < /tmp/mysql3307.sql   

mysql 5.6新特性下的主从,这里研究的还不够深入,有时间还需进一步研究。

各位如果发现文章中的错误,请及时指正,交流,谢谢!

mysql 5.6的新特性一览

随着oralce发布mysql 5.6版本,其新特性也备受瞩目,下面整理一些个人感觉比较有用的特性:

部分内容来自网络,如有错误或遗漏,可留言补充,谢谢!

官方描述的新特性如下:

新增 在线 DDL /更改数据架构支持动态应用程序和开发人员灵活性

新增 复制全局事务标识可支持自我修复式集群

新增 复制无崩溃从机可提高可用性

新增 复制多线程从机可提高性能

新增 对 InnoDB 进行 NoSQL 访问,可快速完成键值操作以及快速提取数据来完成大数据部署

改进 在 Linux 上的性能提升多达 230%

改进 在当今多核、多 CPU 硬件上具备更高的扩展力

改进 InnoDB 性能改进,可更加高效地处理事务和只读负载

改进 更快速地执行查询,增强的诊断功能

改进 Performance Schema 可监视各个用户/应用程序的资源占用情况

改进 通过基于策略的密码管理和实施来确保安全性

复制功能 支持灵活的拓扑架构,可实现向外扩展和高可用性

分区 有助于提高性能和管理超大型数据库环境

ACID 事务 支持构建安全可靠的关键业务应用程序

存储过程 可提高开发人员效率

触发器 可在数据库层面实施复杂的业务规则

View 可确保敏感信息不受攻击

Information Schema 有助于方便地访问元数据

插入式存储引擎架构 可最大限度发挥灵活性

—————————————————————————————————————–

以下为网友结合官方总结的一些有用的新特性:[有遗漏,请留言补充]

1.权限认证,不用输入用户名和密码

2.用户密码有效期设置

3.Innodb全文检索

4.Innodb在线DDL功能增强,修改列名等不用复制数据

5.Innodb使用独享表空间时,可自定义表的数据文件存放的位置,繁忙的放SSD,支持单表在不同实例之间的转移

6.Innodb支持页大小的自定义, innodb_page_size

7.Innodb和Memcache接口的整合

8.Innodb统计信息收集更加精准,执行计划更加精准

9.Innodb Undo数据从系统表空间独立出来为单独的表空间,SSD

10.Innodb Redo日志文件大小调整为512G,以前最大为4G

11.Innodb减少内部争用,Flush操作从主线程独立出来为Flush线程,多Purge线程

12.Innodb死锁检测新方法,信息记录在Error Log中

13.Innodb Buffer Pool信息导出导入,Restart Database with Large Buffer Pool

14.Partition 支持分区和表Exchange

15.Partition 支持显示定义操作(Select、Delete、Insert、Replace等)的分区

16.Performance Schema功能增强

17.复制支持基于Transaction的复制Gtids,提高Master和Slave的一致性

18.复制Row复制只保存改变的列,大大节省Disk Space,Newwork resources和Memory usage

19.复制支持把Master 和Slave的相关信息记录在Table

20.复制支持延迟复制

21.复制执行多线程并行复制,降低Slave与Master的延迟

22.MRR Join操作时候使用范围扫描代替单点循环提高查询效率

23.ICP Index Condition Pushdown

24.Explain支持Delete、Insert、Replace、Update等DML操作

25.子查询优化

26.时间类型字段Time、Datetime、Timestamp支持的粒度由秒扩展到微秒