环境

  • mysql 主服务器,centos7.4,192.168.1.10,端口 10000
  • mysql 从服务器,centos7.4,192.168.1.6
  • 要复制的数据库有 data_db、conf_db

在主服务器上创建用于备份的用户 replicator

1
2
grant replication slave  on *.* to 'replicator'@'192.168.1.6' identified by 'password';
flush privileges;

在主服务器上修改 my.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 建议与本机ip地址最后一位一致,与其他互备服务器均不一致
server-id = 10
read-only = 0

# 开启 binlog
log-bin = /var/log/mysql-bin/master
binlog-format = row
#关注要复制的数据库,存在跨库问题
binlog-do-db = data-db
binlog-do-db = conf-db
#忽略的数据库,存在跨库问题
#binlog-ignore-db =
#binlog 有效时间
expire-logs-days =
#GTID 复制模式
#gtid-mode = ON
#enforce-gtid-consistency=true
#双主或多主互备时,会用到以下配置
#自增主键初始值,与其他互备服务器一致
#auto-increment-offset =
#自增主键等差值,与其他互备服务器均不一致
#auto-increment-increment =

在主服务器上重启 mysql,获取 master 状态

1
2
3
-- 如果 mysql 是全新安装,则无须导出数据库初态,直接查看 binlog pos 即可
-- 锁定要导出的数据库表
flush tables with read lock;

导出数据库初态

1
2
3
4
5
#在主服务器的另一个终端中运行
mysqldump -uroot -p data_db > /tmp/data_db.sql
mysqldump -uroot -p conf_db > /tmp/conf_db.sql
#复制到从服务器上
scp /tmp/data_db.sql /tmp/conf_db.sql 192.168.1.6:/tmp/

查看 binary 日志位置

1
2
3
4
5
6
show master status\G
-- 记住输出里的如下类似两行(不记录也可以,这两个信息已经写入了导出的sql文件中)
-- File: mysql-bin.000001
-- Position: 137103822
-- 解锁数据库表
unlock tables;

在从服务器上编辑 my.cnf

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
# 建议与本机ip地址最后一位一致,与其他互备服务器均不一致
server-id = 6
read-only = 1

# 如果该 slave 中也运行了 master,或者使用了 gtid 模式复制,则开启 binlog
#log-bin = mysql-bin
#binlog-format = row
# 把 slave 操作也计入 binlog,用于链式同步
#log-slave-updates = ON
# 指定要复制的数据库,存在跨库问题
#replicate-do-db = data_db
#replicate-do-db = conf_db
# 指定要复制的数据表,无跨库问题
replicate-do-table = db1.t1
replicate-wild-do-table = db1.%
# 忽略的数据库,存在跨库问题
#replicate-ignore-db =
# 忽略的数据表,无跨库问题
#replicate-ignore-table = db1.t1
#replicate-wild-ignore-table = db1.%
# 中继日志
relay-log = /var/lib/mysql-bin/slave
# 多线程复制
slave-parallel-type = logical-clock
slave-parallel-workers = 4
# GTID 模式
#gtid-mode = ON
#enforce-gtid-consistency=true
# 双主或多主互备时,会用到以下配置
# 自增主键初始值,与其他互备服务器一致
#auto-increment-offset =
# 自增主键等差值,与其他互备服务器均不一致
#auto-increment-increment =

在从服务器上重启 mysql,导入初态

1
2
3
-- 创建要导入的数据库
create database data_db default charset utf8mb4;
create database conf_db default charset utf8mb4;

导入数据库

1
2
msyql -uroot -p data_db < /tmp/data_db.sql
mysql -uroot -p conf_db < /tmp/conf_db.sql

开启同步(master_log_file和mater_log_pos无需添加)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
change master to master_host = '192.168.1.10',
                        master_port = 3306,
                        master_user = 'replicator',
                        master_password = 'password',
                        master_log_file = 'mysql-bin.000001',
                        master_log_pos = 137103822;
flush privileges;
-- gtid 模式
change master to master_host = '192.168.1.10',
                        master_port = 3306,
                        master_user = 'replicator',
                        master_password = 'password',
                        master_auto_position = 1;

启动 slave,查看 slave 状态

1
2
3
4
5
6
7
start slave;
-- 在从服务器上查看 slave 状态
show slave status\G
-- 如果看到
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- 则表示 slave 开启成功!

MySQL8 gtid 互为主从配置

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
[mysqld]
# ---- 固定配置 ----
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid
log-timestamps = SYSTEM
log-error = /var/log/mysql/error.log
slow-query-log = TRUE
slow-query-log-file = /var/log/mysql/slow.log
default-authentication-plugin = mysql_native_password

# ---- 动态配置 ----
mysqlx = OFF
character-set-server = utf8mb4
default-storage-engine = innodb
lower-case-table-names = 1
#skip-name-resolve = 1
#max-user-connections = 600
#innodb-buffer-pool-size = 8G
#innodb-buffer-pool-instances = 8

# master
# 确认不同节点该 id 唯一
server-id = 1
log-bin = /var/lib/mysql-bin/master
binlog-format = ROW
#binlog-do-db = db1
binlog-expire-logs-seconds = 172800
gtid-mode = ON
enforce-gtid-consistency = TRUE

# slave
replicate-wild-ignore-table = information_schema.%
replicate-wild-ignore-table = mysql.%
replicate-wild-ignore-table = performance_schema.%
replicate-wild-ignore-table = sys.%
slave-parallel-workers = 2
log-slave-updates = FALSE
relay-log = /var/lib/mysql-bin/slave
relay-log-recovery = TRUE
#read-only = ON

MySQL8 gtid 多源复制从库配置

  • 修改 my.cnf

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    
    [mysqld]
    # ---- 固定配置 ----
    datadir = /data/mysql
    socket = /var/lib/mysql/mysql.sock
    pid-file = /var/lib/mysql/mysql.pid
    log-timestamps = SYSTEM
    log-error = /var/log/mysql/error.log
    slow-query-log-file = /var/log/mysql/slow.log
    slow-query-log = TRUE
    default-authentication-plugin = mysql_native_password
        
    # ---- 动态配置 ----
    mysqlx = OFF
    character-set-server = utf8mb4
    default-storage-engine = innodb
    lower-case-table-names = 1
    #skip-name-resolve = 1
    #max-user-connections = 600
    #innodb-buffer-pool-size = 8G
    #innodb-buffer-pool-instances = 8
    
    # master
    server-id = 39
    log-bin = /var/lib/mysql/master
    binlog-format = ROW
    #binlog-do-db = db1
    gtid-mode = ON
    enforce-gtid-consistency = TRUE
    binlog-expire-logs-seconds = 172800
        
    # slave
    #replicate-wild-do-table = db1.%
    replicate-wild-ignore-table = information_schema.%
    replicate-wild-ignore-table = mysql.%
    replicate-wild-ignore-table = performance_schema.%
    replicate-wild-ignore-table = sys.%
    slave-parallel-workers = 2
    log-slave-updates = FALSE
    relay-log = /var/lib/mysql/slave
    relay-log-recovery = TRUE
    super_read_only = ON
    master_info_repository  = table
    relay_log_info_repository = table
  • 导出主库数据

    1
    2
    
    mysqldump -uroot -h<主库1> -p --single-transaction --set-gtid-purged=on --databases db1 > 1_db1.sql
    mysqldump -uroot -h<主库2> -p --single-transaction --set-gtid-purged=on --databases db2 > 2_db2.sql
  • 在导出的文件(1_db1.sql,2_db2.sql)中找到 “SET @@GLOBAL.gtid_purged …” 语句,记录下来,并在该文件中删除

  • 导入 1_db1.sql 和 2_db2.sql

    1
    2
    
    mysql -uroot -p < 1_db1.sql
    mysql -uroot -p < 2_db2.sql
  • 合并这两个 “SET @@GLOBAL.gtid_purged …” 语句(gtid set 做并集),导入 gtid set 并集

    1
    
    set @@global.gtid_purged = '<gtid set 并集>'
  • 加入两个主库的同步配置

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    
    change master to master_host='<主库1>',
        master_port=3306,
        master_user='<主库上的 replication 账户>',
        master_password='<主库上的 replication 账户的密码>',
        master_auto_position=1 for channel '<master_1>';
    change master to master_host='<主库2>',
        master_port=3306,
        master_user='<主库上的 replication 账户>',
        master_password='<主库上的 replication 账户的密码>',
        master_auto_position=1 for channel '<master_2>';
  • 启动从库

    1
    2
    
    start slave for channel '<master_1>';
    start slave for channel '<master_2>';
  • 查看从库

    1
    2
    
    show slave status for channel '<master_1>'\G
    show slave status for channel '<master_2>'\G
  • 停止从库

    1
    2
    3
    4
    
    -- 停止全部 slave
    stop slave;
    -- 停止指定 slave
    stop slave for channel '...';
  • 重置从库

    1
    2
    3
    4
    
    -- 重置全部 slave
    reset slave;
    -- 重置指定 slave
    reset slave for channel '...';
  • 监控表: performance_schema.replication_connection_status