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

作者: w也不知道 | 来源:发表于2018-08-04 20:40 被阅读0次

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功能一样，但是可以加通配符

三、配置主从同步

验证主从服务器上的 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，否则下次执行事务时会报错。

master 上创建用于同步的用户

mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';
mysql> flush privileges;

slave 上启动主从同步

 CHANGE MASTER TO MASTER_HOST='192.168.0.11',MASTER_USER='repl',MASTER_PASSWORD='000000',MASTER_AUTO_POSITION=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 的主从复制

master 上创建用于主从复制的用户

mysql> grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';
mysql> flush privileges;

把需要同步的数据库从主库上通过mysqldump备份后恢复到从库上
从库开启主从复制

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 的互为主从复制

配置文件配置

[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.%

创建进行主从同步的用户

grant replication slave on *.* to 'repl'@'192.168.0.%' identified by '000000';（两台都创建）

启动主从同步

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 `查看

启动 slave

start slave;

查看 master 和 slave 的状态

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

一、实验环境

二、配置MySQL配置文件

三、配置主从同步

四、MySQL 基于 binlog 的主从复制

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

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

我爱编程