MySQL 基于 binglog 和 GTID 模式的2种主从复制方式

  MySQL在5.6版本之前复制一直是基于二进制日志的复制，到了MySQL5.6时开始支持基于事务（GTIDs）的复制，并且开始支持多线程复制；但MySQL5.6版本的多线程只能基于多库。这就牵扯到了一个应用场景，就是从基于日志的复制在线变更到基于事务的复制，在MySQL5.6版本时这一动作只能重启主服务器才可以做到。但是到了MySQL 5.7版本时已经可以支持在线变更复制类型了，也就是在线从基于二进制日志的复制变更为基于事务的复制。当然MySQL5.7在复制方面的改进不止这一点，还做到了基于表的多线程复制，以及多源复制。这篇文章只针对在线把基于日志的复制变更为基于事务的复制，其他方面的改进，如多线程复制和多源复制可以看其他文章。

一、实验环境

mysql-master: 192.168.0.11
mysql-slave : 192.168.0.12


# docker-compose.yml
version: '2'
services:
  mysql:
    image: mysql:5.7.10
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root123
    volumes:
    - ./data:/var/lib/mysql
    - /etc/localtime:/etc/localtime
    - ./conf:/etc/mysql
    ports:
    - 3306:3306/tcp

二、配置MySQL配置文件

  • mysql-master
[mysqld]
############################basic settings#################
port                    =   3306
bind-address            =   0.0.0.0
datadir                 =   /var/lib/mysql
pid-file                =   /var/run/mysqld/mysqld.pid
socket                  =   /var/run/mysqld/mysqld.sock
user                    =   mysql
server-id               =   1
character_set_server    =   utf8mb4
skip_name_resolve       =   1
max_allowed_packet      =   16777216
max_connections         =   800
tmp_table_size          =   67108864
join_buffer_size        =   134217728
interactive_timeout     =   1800
wait_timeout            =   1800
read_buffer_size        =   16777216
read_rnd_buffer_size    =   33554432
sort_buffer_size        =   33554432
key_buffer_size         =   256M
thread_cache_size       =   8
transaction_isolation   =   READ-COMMITTED

###########################log settings#####################
log-bin                 =   /var/lib/mysql/mysql-bin
log_bin_index           =   /var/lib/mysql/mysql-bin.index
expire_logs_days        =   7
binlog_format           =   ROW
log-error               =   /var/lib/mysql/mysql-error.log
slow_query_log          =   1
long_query_time         =   2
slow-query-log-file     =   /var/lib/mysql/mysql-slow.log

##########################innodb settings###################
innodb_buffer_pool_size             =   512m
innodb_sort_buffer_size             =   27108864 
innodb_buffer_pool_load_at_startup  =   1
innodb_buffer_pool_dump_at_shutdown =   1
innodb_lock_wait_timeout            =   5
innodb_flush_method                 =   O_DIRECT
innodb_file_format                  =   Barracuda
innodb_file_format_max              =   Barracuda
innodb_thread_concurrency           =   24
innodb_flush_neighbors              =   1
innodb_purge_threads                =   4
innodb_large_prefix                 =   1
innodb_print_all_deadlocks          =   1
innodb_strict_mode                  =   1
innodb_file_per_table               =   ON
innodb_flush_log_at_trx_commit      =   2

##########################start gtid###########################
gtid-mode                   =   on
enforce-gtid-consistency    =   true
master-info-repository      =   table
relay-log-info-repository   =   table
log-slave-updates           =   true
binlog-checksum             =   CRC32
master-verify-checksum      =   1
slave-sql-verify-checksum   =   1
slave_allow_batching        =   1
  • mysql-slave
[mysqld]
############################basic settings#################
port                    =   3306
bind-address            =   0.0.0.0
datadir                 =   /var/lib/mysql
pid-file                =   /var/run/mysqld/mysqld.pid
socket                  =   /var/run/mysqld/mysqld.sock
user                    =   mysql
server-id               =   2
character_set_server    =   utf8mb4
skip_name_resolve       =   1
max_allowed_packet      =   16777216
max_connections         =   800
tmp_table_size          =   67108864
join_buffer_size        =   134217728
interactive_timeout     =   1800
wait_timeout            =   1800
read_buffer_size        =   16777216
read_rnd_buffer_size    =   33554432
sort_buffer_size        =   33554432
key_buffer_size         =   256M
thread_cache_size       =   8
transaction_isolation   =   READ-COMMITTED

###########################log settings#####################
slow_query_log          =   1
long_query_time         =   2
slow-query-log-file     =   /var/lib/mysql/mysql-slow.log
log-error               =   /var/lib/mysql/mysql-error.log
relay-log               =   /var/lib/mysql/relay-log
relay-log-index         =   /var/lib/mysql/relay-log-index
relay-log-info-file     =   /var/lib/mysql/relay-log.info

##########################innodb settings###################
innodb_buffer_pool_size             =   512m
innodb_sort_buffer_size             =   27108864 
innodb_buffer_pool_load_at_startup  =   1
innodb_buffer_pool_dump_at_shutdown =   1
innodb_lock_wait_timeout            =   5
innodb_flush_method                 =   O_DIRECT
innodb_file_format                  =   Barracuda
innodb_file_format_max              =   Barracuda
innodb_thread_concurrency           =   24
innodb_flush_neighbors              =   1
innodb_purge_threads                =   4
innodb_large_prefix                 =   1
innodb_print_all_deadlocks          =   1
innodb_strict_mode                  =   1
innodb_file_per_table               =   ON
innodb_flush_log_at_trx_commit      =   2

##########################start gtid###########################
gtid_mode                   =   ON
skip-slave-start            =   true
read_only                   =   ON
slave-sql-verify-checksum   =   1
enforce_gtid_consistency    =   ON
master-info-repository      =   table
relay-log-info-repository   =   table
relay-log-recovery          =   ON
report-port                 =   3306
report-host                 =   192.168.0.11
replicate-do-db             =   test2, test   #  指定同步的数据库（多个用逗号分离）
replicate_wild_do_table     =   test2.%, test.% # 指定同步数据库下的数据表


# 基于 binlog 主从复制指定数据库时也可以作如下配置
# replicate-do-db    设定需要复制的数据库（多数据库使用逗号，隔开）
# replicate-do-table  设定需要复制的表
# replicate-wild-do-table 同replication-do-table功能一样，但是可以通配符

# replicate-ignore-db 设定需要忽略的复制数据库 （多数据库使用逗号，隔开）
# replicate-ignore-table 设定需要忽略的复制表 
# replicate-wild-ignore-table 同replication-ignore-table功能一样，但是可以加通配符

三、配置主从同步

  1. 验证主从服务器上的 GTID 是否开启
  • mysql-master
# root @ test1 in ~ [19:16:58] 
$ mysql -e "show variables like '%gtid%'"
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
  • mysql-slave
# root @ test2 in ~ [19:16:00] 
$ mysql -e "show variables like '%gtid%'"
---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| gtid_next     | AUTOMATIC |
+---------------+-----------+

简单说下几个常用参数的作用：
gtid_executed：
    在当前实例上执行过的 GTID 集合，实际上包含了所有记录到 binlog 中的事务。设置 set sql_log_bin=0 后执行的事务不会生成 binlog 事件，也不会被记录到 gtid_executed 中。执行 RESET MASTER 可以将该变量置空。
gtid_purged：
    binlog 不可能永远驻留在服务上，需要定期进行清理(通过 expire_logs_days 可以控制定期清理间隔)，否则迟早它会把磁盘用尽。
    gtid_purged 用于记录本机上已经执行过，但是已经被清除了的 binlog 事务集合。它是 gtid_executed 的子集。只有 gtid_executed 为空时才能手动设置该变量，此时会同时更新 gtid_executed 为和 gtid_purged 相同的值。
    gtid_executed 为空意味着要么之前没有启动过基于 GTID 的复制，要么执行过 RESET MASTER。执行 RESET MASTER 时同样也会把 gtid_purged 置空，即始终保持 gtid_purged 是 gtid_executed 的子集。
gtid_next：会话级变量，指示如何产生下一个GTID。可能的取值如下:
    AUTOMATIC：自动生成下一个 GTID，实现上是分配一个当前实例上尚未执行过的序号最小的 GTID。
    ANONYMOUS：设置后执行事务不会产生GTID。
    显式指定的GTID：可以指定任意形式合法的 GTID 值，但不能是当前 gtid_executed 中的已经包含的 GTID，否则下次执行事务时会报错。

  1. master 上创建用于同步的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';
mysql> flush privileges;
  1. slave 上启动主从同步
 CHANGE MASTER TO MASTER_HOST='192.168.0.11',MASTER_USER='repl',MASTER_PASSWORD='000000',MASTER_AUTO_POSITION=1;
  1. 查看主从同步状态
# root @ test2 in ~ [20:37:03] 
$ mysql -e "show slave status\G;"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 2301
               Relay_Log_File: relay-log.000010
                Relay_Log_Pos: 941
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test2,test
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test2.%,test.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2301
              Relay_Log_Space: 1182
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0   # 判断主从同步是否有延迟，数字越大延迟的数据越多
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 155d09a1-96f5-11e8-9e2a-0242ac120002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 155d09a1-96f5-11e8-9e2a-0242ac120002:27-28 # 接收的 GTID 合集
            Executed_Gtid_Set: 155d09a1-96f5-11e8-9e2a-0242ac120002:1-28  # 执行过的 GTID 合集
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

四、MySQL 基于 binlog 的主从复制

  1. master 上创建用于主从复制的用户
mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';
mysql> flush privileges;
  1. 把需要同步的数据库从主库上通过mysqldump备份后恢复到从库上
  2. 从库开启主从复制
CHANGE MASTER TO 
MASTER_HOST='xxx.xxx.xxxx.xxx',         # 这是主库的IP（域名也可以需要做解析）
MASTER_PORT=3306,                       # 主库的端口，从库端口和主库不可以相同
MASTER_USER='repl',                     # 这是主库上创建用来复制的用户repl
MASTER_PASSWORD='123456'                # repl 的密码
MASTER_LOG_FILE='mysql-bin.000025',     # 这里是show master status 时看到的查询二进制日志文件名称，这里不能多空格
MASTER_LOG_POS=9155;                    # 这里是show master status 时看到的二进制日志偏移量，不能多空格


# 启动主从复制
start slave;
# 操作完成后会生成 /var/lib/mysql/master.info 文件

五、MySQL 基于 GTID 的互为主从复制

  1. 配置文件配置
[mysqld]
############################basic settings#################
port                    =   3306
bind-address            =   0.0.0.0
datadir                 =   /var/lib/mysql
pid-file                =   /var/run/mysqld/mysqld.pid
socket                  =   /var/run/mysqld/mysqld.sock
user                    =   mysql
server-id               =   2  # 另一台上改为 1
character_set_server    =   utf8mb4
skip_name_resolve       =   1
max_allowed_packet      =   16777216
max_connections         =   800
tmp_table_size          =   67108864
join_buffer_size        =   134217728
interactive_timeout     =   1800
wait_timeout            =   1800
read_buffer_size        =   16777216
read_rnd_buffer_size    =   33554432
sort_buffer_size        =   33554432
key_buffer_size         =   256M
thread_cache_size       =   8
transaction_isolation   =   READ-COMMITTED
lower_case_table_names =   1

###########################log settings#####################
slow_query_log          =   1
long_query_time         =   2
slow-query-log-file     =   /var/lib/mysql/mysql-slow.log
log-error               =   /var/lib/mysql/mysql-error.log
relay-log               =   /var/lib/mysql/relay-log
relay-log-index         =   /var/lib/mysql/relay-log-index
relay-log-info-file     =   /var/lib/mysql/relay-log.info

log-bin                 =   /var/lib/mysql/mysql-bin
log_bin_index           =   /var/lib/mysql/mysql-bin.index
expire_logs_days        =   7   
binlog_format           =   ROW 

##########################innodb settings###################
innodb_buffer_pool_size             =   512m
innodb_sort_buffer_size             =   27108864 
innodb_buffer_pool_load_at_startup  =   1
innodb_buffer_pool_dump_at_shutdown =   1
innodb_lock_wait_timeout            =   5
innodb_flush_method                 =   O_DIRECT
innodb_file_format                  =   Barracuda
innodb_file_format_max              =   Barracuda
innodb_thread_concurrency           =   24
innodb_flush_neighbors              =   1
innodb_purge_threads                =   4
innodb_large_prefix                 =   1
innodb_print_all_deadlocks          =   1
innodb_strict_mode                  =   1
innodb_file_per_table               =   ON
innodb_flush_log_at_trx_commit      =   2

##########################start gtid###########################
gtid_mode                   =   ON
enforce-gtid-consistency    =   true
master-info-repository      =   table
relay-log-info-repository   =   table
log-slave-updates           =   true
binlog-checksum             =   CRC32
master-verify-checksum      =   1
slave-sql-verify-checksum   =   1
slave_allow_batching        =   1

skip-slave-start            =   true
read_only                   =   ON
slave-sql-verify-checksum   =   1
master-info-repository      =   table
relay-log-info-repository   =   table
relay-log-recovery          =   ON
report-port                 =   3306
report-host                 =   192.168.0.11（另一台服务器上改为192.168.0.12）
replicate-do-db             =   test2
replicate_wild_do_table     =   test2.%
replicate-do-db             =   test
replicate_wild_do_table     =   test.%
  1. 创建进行主从同步的用户
grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';（两台都创建）
  1. 启动主从同步
CHANGE MASTER TO MASTER_HOST='192.168.0.11',MASTER_USER='repl',MASTER_PASSWORD='000000',MASTER_AUTO_POSITION=1;（两台都执行）

MASTER_AUTOO_POSITION 可以用 `show master status `查看
  1. 启动 slave
start slave;

  1. 查看 master 和 slave 的状态


最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,921评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 87,635评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 151,393评论 0赞 338
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,836评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,833评论 5赞 368
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,685评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,043评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,694评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 42,671评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,670评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,779评论 1赞 332
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,424评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,027评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,984评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,214评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,108评论 2赞 351
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,517评论 2赞 343