devbox
Li_阴宅
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL5.7 多主一从（多源复制）同步配置_mysql 多主一从

作者：Li_阴宅 | 2024-08-20 22:58:26

赞

踩

mysql 多主一从

主从复制有如下一些优势：

  1. 分担负载：对业务进行读写分离，减轻主库I/O负载，将部分压力分担到从库上，缩短客户查询响应时间。

  2. 增加健壮性：在主库出现问题时，可通过多种方案将从库设置为主库，替换主库支撑业务，缩短停机窗口。

  3. 有利备份：在从库上备份，即不影响主库的事务，也不影响主库性能和磁盘空间。

  4. 查询分析：从库可以作为统计、报表等数据分析工作所使用的的OLAP库。

  5. 异地备份：将从库放置在异地可作为异地数据同步备份所用。

从MySQL的5.7版本开始支持多源主从复制技术（Multi-Source Replication），就是将多个数据库（Master）的数据集中发送到1台从库（Slave）上，该技术也具有刚才上文提到的主从复制的优势，除了这些，它的独特性还在于：

  1. 汇聚数据：尤其是在分库分表的一些场景中，数据集中统计分析操作可以在1台从库服务器上实现。

  2. 节省成本：数据集中存放可避免服务器等软硬件资源浪费，5.7之前1主1从或者1主多从的方案需要为每个主机都安置一台备机；5.7推出多源复制之后，可以将多个从库进行合并，至于是合并存放在高端还是低端服务器上，取决于分析、统计等业务在整体业务中的优先级、繁忙程度等因素。

  3. 集中备份：方便在一台服务器备份所有已收到的数据库数据。

  4. 异地灾备：将从库放在距离远的地方，可用于异地备份项目。

一、本次实验将使用MySQL 5.7.x作为多“主”。

〇 测试环境：
OS：CentOS 7.5
master_1: 192.168.1.185(MySQL 5.7.30)
master_2: 192.168.1.186(MySQL 5.7.30)
slave: 192.168.1.1.187(MySQL 5.7.35)
 


〇 配置：
master_1相关配置：

  1. [mysqld]
  2. server_id    = 185
  3. log-bin      = master_1
  4. log-bin-index    = master_1.index


master_2相关配置：

  1. [mysqld]
  2. server_id    = 186
  3. log-bin      = master_2
  4. log-bin-index    = master_2.index


slave相关配置：

  1. [mysqld]
  2. server_id    = 187
  3. relay-log    = slave
  4. relay-log-index           = slave.index
  6. # 多源复制结构中的slave，官方要求master-info和relay-log-info存放处必须为TABLE.
  7. # 如果为FILE，则在添加多个master时，会失败：ER_SLAVE_NEW_CHANNEL_WRONG_REPOSITORY.
  8. master-info-repository    = TABLE
  9. relay-log-info-repository = TABLE


〇 为master_1 & master_2上建立复制用户：

  1. GRANT REPLICATION SLAVE ON *.* to repl@'192.168.1.187' IDENTIFIED BY 'repl';
  2. FLUSH PRIVILEGES;


〇 测试数据准备：
master_1测试数据：

  1. master_1> FLUSH LOGS;
  2. Query OK, 0 rows affected (0.00 sec)
  4. master_1> SHOW BINARY LOGS; -- 记住当前binlog的name和position
  5. +-----------------+-----------+
  6. | Log_name        | File_size |
  7. +-----------------+-----------+
  8. | master_1.000001 | 166       |
  9. | master_1.000002 | 455       |
  10. | master_1.000003 | 120       |
  11. +-----------------+-----------+
  12. 3 rows in set (0.00 sec)
  14. master_1> CREATE DATABASE master_1;
  15. Query OK, 1 row affected (0.03 sec)


master_2测试数据：

  1. master_2> FLUSH LOGS;
  2. Query OK, 0 rows affected (0.00 sec)
  4. master_2> SHOW BINARY LOGS;    -- 记住当前binlog的name和position
  5. +-----------------+-----------+
  6. | Log_name        | File_size |
  7. +-----------------+-----------+
  8. | master_2.000001 | 166       |
  9. | master_2.000002 | 455       |
  10. | master_2.000003 | 120       |
  11. +-----------------+-----------+
  12. 3 rows in set (0.00 sec)
  14. master_2> CREATE DATABASE master_2;
  15. Query OK, 1 row affected (0.02 sec)


〇 在slave上执行：

  1. salve> CHANGE MASTER TO
  2.     -> MASTER_HOST='192.168.1.185',
  3.     -> MASTER_USER='repl',
  4.     -> MASTER_PORT=3306,
  5.     -> MASTER_PASSWORD='repl',
  6.     -> MASTER_LOG_FILE='master_1.000003',
  7.     -> MASTER_LOG_POS=120
  8.     -> FOR CHANNEL 'master_1';
  9. Query OK, 0 rows affected, 2 warnings (0.02 sec)    -- 此处产生的warnings是一些安全建议和警告，本实验无视。
  11. salve> CHANGE MASTER TO
  12.     -> MASTER_HOST='192.168.1.186',
  13.     -> MASTER_USER='repl',
  14.     -> MASTER_PORT=3306,
  15.     -> MASTER_PASSWORD='repl',
  16.     -> MASTER_LOG_FILE='master_2.000003',
  17.     -> MASTER_LOG_POS=120
  18.     -> FOR CHANNEL 'master_2';
  19. Query OK, 0 rows affected, 2 warnings (0.02 sec)
  21. slave> START SLAVE;
  22. Query OK, 0 rows affected (0.01 sec)
  24. salve> SHOW DATABASES;    -- 此时在master_1和master_2上的binlog events已经被正常的apply了
  25. +--------------------+
  26. | Database            |
  27. +--------------------+
  28. | information_schema |
  29. | master_1           |
  30. | master_2           |
  31. | mysql              |
  32. | performance_schema |
  33. | sys                |
  34. +--------------------+
  35. 6 rows in set (0.00 sec)


最后通过start slave status即可查到复制状态

  1. slave> SHOW SLAVE STATUS\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.1.185
  5.                   Master_User: repl
  6.                   Master_Port: 3306
  7.                 ……………………………………………………
  8.              Slave_IO_Running: Yes
  9.             Slave_SQL_Running: Yes
  10.                 ……………………………………………………
  11.              Master_Server_Id: 185
  12.                   Master_UUID: ee1f8704-58c4-11e6-95b5-000c297f23b7
  13.              Master_Info_File: mysql.slave_master_info
  14.                     SQL_Delay: 0
  15.           SQL_Remaining_Delay: NULL
  16.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  17.                ……………………………………………………
  18.                  Channel_Name: master_1
  19.            Master_TLS_Version:
  20. *************************** 2. row ***************************
  21.                Slave_IO_State: Waiting for master to send event
  22.                   Master_Host: 192.168.1.186
  23.                   Master_User: repl
  24.                   Master_Port: 3306
  25.                 Connect_Retry: 60
  26.                ……………………………………………………
  27.              Slave_IO_Running: Yes
  28.             Slave_SQL_Running: Yes
  29.                ……………………………………………………
  30.              Master_Server_Id: 186
  31.                   Master_UUID: 53774f2d-7e14-11e6-8900-000c298e914c
  32.              Master_Info_File: mysql.slave_master_info
  33.                     SQL_Delay: 0
  34.           SQL_Remaining_Delay: NULL
  35.       Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
  36.                ……………………………………………………
  37.                  Channel_Name: master_2
  38.            Master_TLS_Version:
  39. 2 rows in set (0.00 sec)


 


〇 测试：
master_1上操作：

  1. master_1> CREATE TABLE master_1.test_table(id int);
  2. Query OK, 0 rows affected (0.05 sec)
  4. master_1> INSERT INTO master_1.test_table SELECT 666666;
  5. Query OK, 1 row affected (0.01 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0


master_2上操作：

  1. master_2> CREATE TABLE master_2.test_table(massage varchar(16));
  2. Query OK, 0 rows affected (0.02 sec)
  4. master_2> INSERT INTO master_2.test_table SELECT '嘿嘿嘿';
  5. Query OK, 1 row affected (0.00 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0
  8. master_2> INSERT INTO master_2.test_table SELECT '三阳之炎';
  9. Query OK, 1 row affected (0.00 sec)
  10. Records: 1 Duplicates: 0 Warnings: 0


slave上操作：

  1. salve> SELECT id FROM master_1.test_table;
  2. +--------+
  3. | id     |
  4. +--------+
  5. | 666666 |
  6. +--------+
  7. 1 row in set (0.00 sec)
  10. salve> SELECT massage FROM master_2.test_table;
  11. +--------------+
  12. | massage      |
  13. +--------------+
  14. | 嘿嘿嘿        |
  15. | 三阳之炎      |
  16. +--------------+
  17. 2 rows in set (0.00 sec)


 


〇 其他相关语法：

  1. START/STOP/RESET ALL/RESET SLAVE FOR CHANNEL 'XXX';
  3. SHOW SLAVE STATUS FOR CHANNEL 'XXX';


ps.
与上述传统position方式类似，GTID方式配置起来也类似，开启GTID后，需要注意使用FOR CHANNEL 'xxx'关键字即可，比如：

  1. CHANGE MASTER TO
  2.     MASTER_HOST='',
  3.     MASTER_USER='repl',
  4.     MASTER_PORT=3306,
  5.     MASTER_PASSWORD='repl',
  6.     MASTER_AUTO_POSITION = 1
  7.     FOR CHANNEL 'master_1';


多台主机的schema名字不可以一样，（比如master_1为db_00 ... db_09共10库，master_2为db_10 ... db_19，master_3为db_20 ... db_29 ……）

思路：

1： 创建集中库： 主要就是存储历史数据。作为查询使用。

2：创建多个业务库：满足项目高并发的能力。

demo环境：

1： VM ware 虚拟机 - centOS 7

centOS-1： 192.168.194.3 主 100-------业务库

centOS-2： 192.168.194.4 主 200-------业务库

centOS-3： 192.168.194.5 从 300-------相当于集中库

2：mysql 5.7

步骤

1： 主库100

设置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

[mysqld]

lower_case_table_names = 1 # 表名不区分大小写

server-id = 100

log_bin = mysql-bin  #开始binlog记录

binlog_format = MIXED

#每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。

#这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据

#但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，

#同样很大的影响MySQL和IO性能。按需设置。

sync_binlog = 1

# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

expire_logs_days = 7

#binlog_cache_size = 128m

#max_binlog_cache_size = 512m

#max_binlog_size = 256M

# 需要同步库

binlog-do-db = dev

# 不需要同步库

binlog-ignore-db = mysql

binlog_ignore_db = information_schema

binlog_ignore_db = performation_schema

binlog_ignore_db = sys

datadir=/var/lib/mysql

设置slave用户

1

2

3

4

5

6

7

8

9

10

11

12

13

14

# mysql -uroot -p

# password: xxxxxx

# mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234';

# mysql> flush privileges;

# quit;

# systemctl restart mysqld;

# show master status \G

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

             File: mysql-bin.000001

         Position: 886

     Binlog_Do_DB: dev

 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

Executed_Gtid_Set:

1 row in set (0.00 sec)

2： 主库200

设置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

[mysqld]

lower_case_table_names = 1 # 表名不区分大小写

server-id = 200

log_bin = mysql-bin  #开始binlog记录

binlog_format = MIXED

#每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。

#这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据

#但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，

#同样很大的影响MySQL和IO性能。按需设置。

sync_binlog = 1

# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

expire_logs_days = 7

#binlog_cache_size = 128m

#max_binlog_cache_size = 512m

#max_binlog_size = 256M

# 需要同步库

binlog-do-db =dev

# 不需要同步库

binlog-ignore-db = mysql

binlog_ignore_db = information_schema

binlog_ignore_db = performation_schema

binlog_ignore_db = sys

datadir=/var/lib/mysql

设置slave用户

1

2

3

4

5

6

7

8

9

10

11

12

13

14

# mysql -uroot -p

# password: xxxxxx

# mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'root1234';

# mysql> flush privileges;

# quit;

# systemctl restart mysqld;

# show master status \G

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

             File: mysql-bin.000001

         Position: 154

     Binlog_Do_DB: dev

 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

Executed_Gtid_Set:

1 row in set (0.00 sec)

3： 从库300

设置my.cnf。

1

2

3

4

5

6

7

8

9

10

[mysqld]

lower_case_table_names = 1 # 表名不区分大小写

server-id = 300

master_info_repository = table

relay_log_info_repository = table

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock 

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

设置主库信息

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

# mysql -uroot -p

# password: xxxxxx

# 设置主库信息

# mysql> CHANGE MASTER TO 

# ->MASTER_HOST='192.168.194.3',

# ->MASTER_PORT=3306, MASTER_USER='slave',

# ->MASTER_PASSWORD='root1234',

# ->MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=886 for channel '100';

# mysql> CHANGE MASTER TO 

# ->MASTER_HOST='192.168.194.4',

# ->MASTER_PORT=3306, MASTER_USER='slave',

# ->MASTER_PASSWORD='root1234',

# ->MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154  for channel '200';

# 设置主库信息

# mysql> flush privileges;

# start slave;

# mysql>show slave status \G

标识同步成功

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

测试。

随意主库：创建表,插入一条数据。

1

2

3

4

5

6

7

8

9

10

11

CREATE TABLE `t_user`  (

  `id` varchar(32) NOT NULL COMMENT '主键ID',

  `name` varchar(32) CHARACTER SET utf8mb4 NULL COMMENT '用户名称',

  `code` varchar(32) CHARACTER SET utf8mb4 NULL COMMENT '用户编码',

  `phone_number` varchar(300) CHARACTER SET utf8mb4 NULL COMMENT '电话号码',

  `create_date` datetime NULL COMMENT '创建时间',

  `update_date` datetime NULL COMMENT '修改时间',

  PRIMARY KEY (`id`)

) COMMENT = '用户信息表';

INSERT INTO t_user (`id`, `name`, `code`, `phone_number`, `create_date`, `update_date`)

VALUES ('userId_4', '张三', '123456789', '123456789632', '2020-04-27 22:05:00', '2020-04-27 22:05:00');

从库查询

1

2

3

4

5

SELECT * FROM t_user;

------+--------+----------+--------------+---------------------+-------------+

| id       | name   | code     | phone_number | create_date  | update_date |

+-------+--------+----------+--------------+---------------------+----------+

| userId_4 | 张三 |123456789|123456789632|2020-04-27 22:05:00|2020-04-27 22:05:00

Mysql多主一从配置

概要说明

mysql基于docker-compose进行安装,使用服务器（局域网）

Master1：192.168.100.1
Master2：192.168.100.2
Slave：192.168.100.3

异构数据同步配置说明

  1. 安装docker

  2. 安装dosker-compose

  3. Docker相关操作，安装部分进行单独管理，在服务器新建相关管理目录

    a) /usr/local/docker/mysql

    b) mysql下新建目录master（/usr/local/docker/mysql/master）用于挂载mysql配置和数据相关文件，防止docker重启数据丢失

    c) 新建docker-compose.yml文件，内容如下：

注意，镜像一定指定版本，不要使用last

d) 在master/conf/mycnf目录下新建文件 my.cnf，此文件为多数据库配置关键文件，配置如下：

master-1-mycnf.png

e) 启动镜像(需在当前目录下，含yml文件)

docker-compose up –d

以上命令是yml文件名称为docker-compose的情况下使用，使用其他名称，需要加-f，如：docker-compose –f mysql.yml up –d

注：可以先docker-compose up启动，此时可以查看启动日志，看是否存在问题，确认成功后加 –d ，含义为后台运行

  1. master1和master2都为以上配置

  2. slave配置

    a) mysql安装配置一致

    b) 挂载目录不同 /usr/local/docker/mysql/slave ，目录为slave，其余目录都一致，配置和数据在slave目录下

    c) 在slave/conf/mycnf目录下新建文件 my.cnf，此文件为多数据库配置关键文件，配置如下：

slave-mycnf.png

d) 启动docker镜像

  1. mysql设置主从相关数据权限和配置（内部配置）

    a) 进入mysql容器
    docker exec –it mysql bash

    b) 登录mysql
    mysql –uroot -proot

    c) master 相关配置命令
    新建用户并设置权限（该用户用于主从复制）
    grant replication slave on *.* to 'slave'@'%' identified by '123456';

    d) 刷新mysql权限
    flush privileges;

    e) 主从复制有两种模式，binlog模式 和 GTID模式，两种模式都可以完成复制，此处采用GTID模式，并以此说明，binlog模式有需要了解可以@me

    f) 采用GTID模式，master配置即完成

    g) Slave配置

    命令：
    change master to master_host='192.168.100.221',master_port=3306,master_user='slave', master_password='123456',master_auto_position = 1 for channel 'master-3';

    h) 开启主从复制模式
    start slave for channel master-3;
    i) 查看主从同步状态是否成功
    show slave status\G;

只有【Slave_IO_Running】和【Slave_SQL_Running】都是Yes，则同步是正常的。

如果是No或者Connecting都不行，可查看mysql-error.log，以排查问题。


主库配置如下：

lower_case_table_names = 1
# 表名不区分大小写
server_id = 1533306
log_bin = mysql-bin
#开始binlog记录
binlog_format = MIXED
#每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。
#这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据
#但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，
#同样很大的影响MySQL和IO性能。按需设置。
sync_binlog = 1
auto_increment_increment=2
auto_increment_offset=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days = 7
binlog_cache_size = 128m
max_binlog_cache_size = 512m
max_binlog_size = 256M
# 需要同步库
#binlog-do-db = emp
# 不需要同步库
binlog-ignore-db = mysql
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
# GTID  mode
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates   = 1

主库上创建复制用户如下：

1）创建用户

set global validate_password_policy=0;
set global validate_password_length=1;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
update mysql.user set authentication_string=password('123456') where user='root';
UPDATE mysql.user SET Host='%' WHERE User='root';
flush privileges;
select host,user,authentication_string from mysql.user;

2）创建复制用户

set global validate_password_policy=0;
set global validate_password_length=1;
create user 'rep'@'%' identified by '123456';
grant replication slave, replication client on *.* to 'rep'@'%';
FLUSH PRIVILEGES;    
 

从库配置如下：
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=1563306
log-bin=mysql-bin
#relay-log = mysql-relay-bin
#replicate-wild-ignore-table=mysql.%
#replicate-wild-ignore-table=test.%
#replicate-wild-ignore-table=information_schema.%
#master_info_repository = table
#relay_log_info_repository = table
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
replicate-ignore-db=sys

replicate-rewrite-db=emp->source
replicate-rewrite-db=emp1->source
replicate-rewrite-db=emp2->source
#replicate-rewrite-db=emp3->source
#replicate-rewrite-db=emp4->source
#replicate-rewrite-db=emp5->source
#replicate-rewrite-db=emp6->source
#replicate-rewrite-db=emp7->source
slave_net_timeout=60
slave_parallel_workers=16
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
slave-skip-errors=1062,1053,1146,1213,1264,1205,1396,1032
master_info_repository = table
relay_log_info_repository = table
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates   = 1

从库上配置主库复制连接如下

主库： show master status;


# 设置主库信息,从库执行；
CHANGE MASTER TO  
MASTER_HOST='192.168.86.153',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=194 for channel 'master153';

CHANGE MASTER TO  
MASTER_HOST='192.168.86.154',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=194  for channel 'master154';

CHANGE MASTER TO  
MASTER_HOST='192.168.86.155',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=194  for channel 'master155';


# 设置主库信息,从库执行；
flush privileges;
start slave;
show slave status \G;

标识同步成功

    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

注： GTID复制MASTER_auto_position=1 

# 设置主库信息,从库执行；
CHANGE MASTER TO  
MASTER_HOST='192.168.86.153',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_auto_position=1 for channel 'master153';

CHANGE MASTER TO  
MASTER_HOST='192.168.86.154',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_auto_position=1  for channel 'master154';

CHANGE MASTER TO  
MASTER_HOST='192.168.86.155',
MASTER_PORT=3306, MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_auto_position=1  for channel 'master155';

stop slave ;
reset slave all ;

change master to MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=1987  for channel 'master153';
change master to MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1388  for channel 'master154';
change master to MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1388  for channel 'master155';
start slave;

二、MySQL多源复制，将多台Master端库以“主从复制”的方式同步到一台Slave端，可以有效的节省主机资源。

操作系统数据库版本角色IP主机名
CentOS 7.6MySQL 8.0.29Master A192.168.1.71host71
CentOS 7.6MySQL 8.0.29Master B192.168.1.72host72
CentOS 7.6MySQL 8.0.29Master C192.168.1.73host73

部署步骤

1. 配置my.cnf（根据个人环境调整其他参数）

  1. server_id = 71 #三台保持唯一性
  2. port = 3380
  3.  
  4. log_bin = /data/mysql/log_bin
  5. binlog_expire_logs_seconds = 259200
  6. gtid_mode = on
  7. log-slave-updates = on
  8. enforce-gtid-consistency = on

2.  host72端备份+创建复制用户repll

  1. [root]# mysqldump host72 > host72.sql
  2. mysql> create user 'rep'@'192.168.1.%' identified by 'Replication@80';
  3. mysql> grant repication slave,replication client on host72.* to 'repl'@'192.168.1.%';

3. host73端备份+创建复制用户repl

  1. mysqldump host73 > host73.sql
  2. mysql> create user 'rep'@'192.168.1.%' identified by 'Replication@80';
  3. mysql> grant repication slave,replication client on host72.* to 'repl'@'192.168.1.%';

4. host71端恢复数据

  1. [root]# mysql -e "reset master;" && mysql < host72.sql
  2. [root]# mysql -e "reset master;" && mysql < host73.sql

5. 配置主从

  1. mysql> change master to MASTER_HOST='192.168.1.72',MASTER_USER='repl',MASTER_PASSWORD='Gaoyu@029',MASTER_PORT=3380,MASTER_AUTO_POSITION=1 for channel 'host72';
  2. mysql> change master to MASTER_HOST='192.168.1.73',MASTER_USER='repl',MASTER_PASSWORD='Gaoyu@029',MASTER_PORT=3380,MASTER_AUTO_POSITION=1 for channel 'host73';

6. 配置复制过滤

配置复制过滤只从192.168.1.72上复制host72库

mysql> change replication filter REPLICATE_DO_DB=(host72) for channel 'host72';

配置复制过滤只从192.168.1.73复制host73库

mysql> change replication filter REPLICATE_DO_DB=(host73) for channel 'host73';

7. 启动slave

  1. mysql> start slave for channel 'host72';
  2. mysql> start slave for channel 'host73';

 8. 查看slave状态

mysql> show slave status\G

三、MySQL5.7 多主一从（多源复制）同步配置

多主一从，也称为多源复制，数据流向：

  • 主库 1 -> 从库 s
  • 主库 2 -> 从库 s
  • 主库 n -> 从库 s

应用场景

  • 数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
  • 读写分离，从库只用于查询，提高数据库整体性能。

部署环境

​ 注：使用 docker 部署 mysql 实例，方便快速搭建演示环境。但本文重点是讲解主从配置，因此简略描述 docker 环境构建 mysql 容器实例。

  • 数据库：MySQL 5.7.x （相比 5.5，5.6 而言，5.7 同步性能更好，支持多源复制，可实现多主一从，主从库版本应保证一致）
  • 操作系统：CentOS 7.x
  • 容器：Docker 17.09.0-ce
  • 镜像：mysql:5.7
  • 主库 300：IP=192.168.10.212; PORT=4300; server-id=300; database=test3; table=user
  • 主库 400：IP=192.168.10.212; PORT=4400; server-id=400; database=test4; table=user
  • 主库 500：IP=192.168.10.212; PORT=4500; server-id=500; database=test5; table=user
  • 从库 10345：IP=192.168.10.212; PORT=4345; server-id=10345; database=test3,test4,test5; table=user

配置约束

  • 主从库必须保证网络畅通可访问
  • 主库必须开启 binlog 日志
  • 主从库的 server-id 必须不同

事前准备

  1. 关闭 selinux
  1.  
  2. vim /etc/sysconfig/selinux 
  3. SELINUX=disabled
  4.  
  5. # 若不关闭，使用docker启动mysql5.7镜像容器时启动不起来，查看日志会有如下错误显示：
  6. ERROR: mysqld failed while attempting to check config
  7. command was: "mysqld --verbose --help"
  8. mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
  9. mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
  10. ERROR: mysqld failed while attempting to check config
  11. command was: "mysqld --verbose --help"
  12. mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
  13. mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
  1. 安装并启动 docker
  1. yum install epel-release
  2. yum -y install docker
  3. systemctl start docker.service
  1. 创建目录
  1. mkdir -p /datavol/mysql-300/{mysql,conf}
  2. mkdir -p /datavol/mysql-400/{mysql,conf}
  3. mkdir -p /datavol/mysql-500/{mysql,conf}
  4. mkdir -p /datavol/mysql-10345/{mysql,conf}

【主库 300】操作及配置

配置 my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-300/conf

  1. [client]
  2. port = 3306
  3. default-character-set = utf8mb4
  4.  
  5. [mysql]
  6. port = 3306
  7. default-character-set = utf8mb4
  8.  
  9. [mysqld]
  10. ##########################
  11. # summary
  12. ##########################
  13. #bind-address = 0.0.0.0
  14. #port = 3306
  15. #datadir=/datavol/mysql/data #数据存储目录
  16.  
  17. ##########################
  18. # log bin
  19. ##########################
  20. server-id = 300			#必须唯一
  21. log_bin = mysql-bin 	#开启及设置二进制日志文件名称
  22. binlog_format = MIXED
  23. sync_binlog = 1
  24. expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
  25.  
  26. #binlog_cache_size = 128m
  27. #max_binlog_cache_size = 512m
  28. #max_binlog_size = 256M
  29.  
  30. binlog-do-db = test3 	#要同步的数据库
  31.  
  32. binlog-ignore-db = mysql 	#不需要同步的数据库 
  33. binlog_ignore_db = information_schema
  34. binlog_ignore_db = performation_schema
  35. binlog_ignore_db = sys
  36. 			
  37.  
  38. ##########################
  39. # character set
  40. ##########################
  41. character-set-server = utf8mb4
  42. collation-server = utf8mb4_unicode_ci

安装启动

  1. [root@localhost ~]# docker run -d -p 4300:3306 --name=mysql-300 -v /datavol/mysql-300/conf:/etc/mysql/conf.d -v /datavol/mysql-300/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
  2. 5691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad
  3. [root@localhost ~]# docker ps
  4. CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
  5. 5691bac538e6        mysql:5.7                           "docker-entrypoint..."   44 seconds ago      Up 43 seconds       33060/tcp, 0.0.0.0:4300->3306/tcp                    mysql-300
  6. [root@localhost ~]# docker exec -it mysql-300 /bin/bash
  7. root@5691bac538e6:/# mysql -u root -p
  8. Enter password:

注：若不熟悉 docker，可使用传统方式安装 mysql，效果相同。

创建授权用户

连接 mysql 主数据库，键入命令 mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限

  1. mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
  5. Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

  1. mysql> show variables like 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin       | ON    |
  6. +---------------+-------+
  7. 1 row in set

查看 master 状态

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3.              File: mysql-bin.000003
  4.          Position: 438
  5.      Binlog_Do_DB: test3
  6.  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库 400】配置及操作

配置 my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-400/conf

  1. [client]
  2. port = 3306
  3. default-character-set = utf8mb4
  4.  
  5. [mysql]
  6. port = 3306
  7. default-character-set = utf8mb4
  8.  
  9. [mysqld]
  10. ##########################
  11. # summary
  12. ##########################
  13. #bind-address = 0.0.0.0
  14. #port = 3306
  15. #datadir=/datavol/mysql/data #数据存储目录
  16.  
  17. ##########################
  18. # log bin
  19. ##########################
  20. server-id = 400			#必须唯一
  21. log_bin = mysql-bin 	#开启及设置二进制日志文件名称
  22. binlog_format = MIXED
  23. sync_binlog = 1
  24. expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
  25.  
  26. #binlog_cache_size = 128m
  27. #max_binlog_cache_size = 512m
  28. #max_binlog_size = 256M
  29.  
  30. binlog-do-db = test4 		#要同步的数据库
  31.  
  32. binlog-ignore-db = mysql 	#不需要同步的数据库 
  33. binlog_ignore_db = information_schema
  34. binlog_ignore_db = performation_schema
  35. binlog_ignore_db = sys
  36. 			
  37.  
  38. ##########################
  39. # character set
  40. ##########################
  41. character-set-server = utf8mb4
  42. collation-server = utf8mb4_unicode_ci

安装启动

  1. [root@localhost ~]# docker run -d -p 4400:3306 --name=mysql-400 -v /datavol/mysql-400/conf:/etc/mysql/conf.d -v /datavol/mysql-400/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
  2. 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
  3. [root@localhost ~]# docker ps
  4. CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
  5. 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4400->3306/tcp                    mysql-400
  6. [root@localhost ~]# docker exec -it mysql-400 /bin/bash
  7. root@19e93b6d93ca:/# mysql -u root -p
  8. Enter password:

创建授权用户

创建用户用于从库同步复制，授予复制、同步访问的权限

  1. mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
  5. Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

  1. mysql> show variables like 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin       | ON    |
  6. +---------------+-------+
  7. 1 row in set

查看 master 状态

  1. mysql> show master status \G;
  2. *************************** 1. row ***************************
  3.              File: mysql-bin.000003
  4.          Position: 438
  5.      Binlog_Do_DB: test4
  6.  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【主库 500】配置及操作

配置 my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-500/conf

  1. [client]
  2. port = 3306
  3. default-character-set = utf8mb4
  4.  
  5. [mysql]
  6. port = 3306
  7. default-character-set = utf8mb4
  8.  
  9. [mysqld]
  10. ##########################
  11. # summary
  12. ##########################
  13. #bind-address = 0.0.0.0
  14. #port = 3306
  15. #datadir=/datavol/mysql/data #数据存储目录
  16.  
  17. ##########################
  18. # log bin
  19. ##########################
  20. server-id = 500			#必须唯一
  21. log_bin = mysql-bin 	#开启及设置二进制日志文件名称
  22. binlog_format = MIXED
  23. sync_binlog = 1
  24. expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
  25.  
  26. #binlog_cache_size = 128m
  27. #max_binlog_cache_size = 512m
  28. #max_binlog_size = 256M
  29.  
  30. binlog-do-db = test5 		#要同步的数据库
  31.  
  32. binlog-ignore-db = mysql 	#不需要同步的数据库 
  33. binlog_ignore_db = information_schema
  34. binlog_ignore_db = performation_schema
  35. binlog_ignore_db = sys
  36. 			
  37.  
  38. ##########################
  39. # character set
  40. ##########################
  41. character-set-server = utf8mb4
  42. collation-server = utf8mb4_unicode_ci

安装启动

  1. [root@localhost ~]# docker run -d -p 4500:3306 --name=mysql-500 -v /datavol/mysql-500/conf:/etc/mysql/conf.d -v /datavol/mysql-500/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
  2. 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
  3. [root@localhost ~]# docker ps
  4. CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
  5. 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4500->3306/tcp                    mysql-500
  6. [root@localhost ~]# docker exec -it mysql-500 /bin/bash
  7. root@19e93b6d93ca:/# mysql -u root -p
  8. Enter password:

创建授权用户

创建用户用于从库同步复制，授予复制、同步访问的权限

  1. mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
  5. Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

  1. mysql> show variables like 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin       | ON    |
  6. +---------------+-------+
  7. 1 row in set

查看 master 状态

  1. mysql> show master status \G
  2. *************************** 1. row ***************************
  3.              File: mysql-bin.000003
  4.          Position: 438
  5.      Binlog_Do_DB: test5
  6.  Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

【从库 10345】配置及操作

配置 my.cnf

把该文件放到主库所在配置文件路径下：/datavol/mysql-10345/conf

  1. [client]
  2. port = 3306
  3. default-character-set = utf8mb4
  4.  
  5. [mysql]
  6. port = 3306
  7. default-character-set = utf8mb4
  8.  
  9. [mysqld]
  10. ##########################
  11. # summary
  12. ##########################
  13. #bind-address = 0.0.0.0
  14. #port = 3306
  15. #datadir=/datavol/mysql/data 	#数据存储目录
  16.  
  17. ##########################
  18. # log bin
  19. ##########################
  20. server-id = 10345
  21. master_info_repository      = table
  22. relay_log_info_repository   = table		
  23.  
  24. ##########################
  25. # character set
  26. ##########################
  27. character-set-server = utf8mb4
  28. collation-server = utf8mb4_unicode_ci

安装启动

  1. [root@localhost ~]# docker run -d -p 4345:3306 --name=mysql-10345 -v /datavol/mysql-10345/conf:/etc/mysql/conf.d -v /datavol/mysql-10345/mysql:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
  2. 19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
  3. [root@localhost ~]# docker ps
  4. CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
  5. 19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4345->3306/tcp                    mysql-10345
  6. [root@localhost ~]# docker exec -it mysql-10345 /bin/bash
  7. root@19e93b6d93ca:/# mysql -u root -p
  8. Enter password:

设置【主库】信息

登录【从库 10345】，进入 mysql 命令行。

  1. mysql> stop slave;
  2. Query OK, 0 rows affected
  3.  
  4. mysql> CHANGE MASTER TO 
  5. MASTER_HOST='192.168.10.212',
  6. MASTER_PORT=4300,
  7. MASTER_USER='slave',
  8. MASTER_PASSWORD='123456',
  9. MASTER_LOG_FILE='mysql-bin.000003',
  10. MASTER_LOG_POS=438 
  11. for channel '300';
  12. Query OK, 0 rows affected
  13.  
  14. mysql> CHANGE MASTER TO 
  15. MASTER_HOST='192.168.10.212',
  16. MASTER_PORT=4400,
  17. MASTER_USER='slave',
  18. MASTER_PASSWORD='123456',
  19. MASTER_LOG_FILE='mysql-bin.000003',
  20. MASTER_LOG_POS=438 
  21. for channel '400';
  22. Query OK, 0 rows affected
  23.  
  24. mysql> CHANGE MASTER TO 
  25. MASTER_HOST='192.168.10.212',
  26. MASTER_PORT=4500,
  27. MASTER_USER='slave',
  28. MASTER_PASSWORD='123456',
  29. MASTER_LOG_FILE='mysql-bin.000003',
  30. MASTER_LOG_POS=438 
  31. for channel '500';
  32. Query OK, 0 rows affected
  33.  
  34. mysql> start slave;
  35. Query OK, 0 rows affected

stop slave; // 停止同步 start slave; // 开始同步 // 必须和【主库】的信息匹配。

CHANGE MASTER TO MASTER_HOST='192.168.10.212', // 主库 IP MASTER_PORT=4300, // 主库端口 MASTER_USER='slave', // 访问主库且有同步复制权限的用户 MASTER_PASSWORD='123456', // 登录密码 //【关键处】从主库的该 log_bin 文件开始读取同步信息，主库 show master status 返回结果 MASTER_LOG_FILE='mysql-bin.000003', //【关键处】从文件中指定位置开始读取，主库 show master status 返回结果 MASTER_LOG_POS=438 **for channel '300'; // 定义通道名称 **

查看同步状态

mysql> show slave status \G

可以看见设置三个的主从同步通道的所有状态信息。 只有【Slave_IO_Running】和【Slave_SQL_Running】都是 Yes，则同步是正常的。 如果是 No 或者 Connecting 都不行，可查看 mysql-error.log，以排查问题。

  1. mysql> show variables like 'log_error%';
  2. +---------------------+--------+
  3. | Variable_name       | Value  |
  4. +---------------------+--------+
  5. | log_error           | stderr |
  6. | log_error_verbosity | 3      |
  7. +---------------------+--------+
  8. 2 rows in set

配置完成，则【从库 10345】开始自动同步。

若需要单独启动或停止某个同步通道，可使用如下命令： start slave for channel '300'; // 启动名称为 300 的同步通道 stop slave for channel '300'; // 停止名称为 300 的同步通道

验证数据同步

建库

使用 root 账号登录【主库 300】，创建 test3 数据库

  1. mysql> CREATE DATABASE test3;
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. mysql> USE test3;
  5. Database changed

建表

在【主库 300】中创建 user 表

  1. CREATE TABLE `user` (
  2.   `id` bigint(20) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  4.   `age` tinyint(3) unsigned NOT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

新增

在【主库 300】中向 user 表插入一条数据：

  1. mysql> use test3;
  2. Database changed
  3. mysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18);
  4. Database changed
  5. mysql> SELECT * FROM user;
  6. +-----+------+-----+
  7. | id  | name | age |
  8. +-----+------+-----+
  9. | 300 | Tom  |  18 |
  10. +-----+------+-----+
  11. 1 row in set (0.00 sec)

在【从库 10345】中查询 user 表数据：

  1. mysql> use test3;
  2. Database changed
  3. mysql> SELECT * FROM user;
  4. +-----+------+-----+
  5. | id  | name | age |
  6. +-----+------+-----+
  7. | 300 | Tom  |  18 |
  8. +-----+------+-----+
  9. 1 row in set (0.00 sec)

新增记录同步成功。

更新

在【主库 300】中修改刚才插入的数据：

  1. mysql> UPDATE user SET name='Peter' where id=300;
  2. Query OK, 1 row affected (0.01 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0
  4.  
  5. mysql> select * from user;
  6. +-----+-------+-----+
  7. | id  | name  | age |
  8. +-----+-------+-----+
  9. | 300 | Peter |  18 |
  10. +-----+-------+-----+
  11. 1 row in set (0.00 sec)

在【从库 10345】中查询 user 表数据：

  1. mysql> select * from user;
  2. +-----+-------+-----+
  3. | id  | name  | age |
  4. +-----+-------+-----+
  5. | 300 | Peter |  18 |
  6. +-----+-------+-----+
  7. 1 row in set (0.00 sec)

更新记录同步成功。

删除

在【主库 300】中删除刚才更新的数据：

  1. mysql> DELETE FROM user WHERE id=300;
  2. Query OK, 1 row affected (0.00 sec)
  4. mysql> select * from user;
  5. Empty set (0.00 sec)

在【从库 10345】中查询 user 表数据：

  1. mysql> select * from user;
  2. Empty set (0.00 sec)

删除记录同步成功。 注：【主库 400】、【主库 500】的验证操作与上述类似。

补充：

  • 如果【主服务器】重启 mysql 服务，【从服务器】会等待与【主服务器】重连。当主服务器恢复正常后，从服务器会自动重新连接上主服务器，并正常同步数据。
  • 如果某段时间内，【从数据库】服务器异常导致同步中断（可能是同步点位置不匹配），可以尝试以下恢复方法：进入【主数据库】服务器（正常），在 bin-log 中找到【从数据库】出错前的 position，然后在【从数据库】上执行 change master，将 master_log_file 和 master_log_pos 重新指定后，开始同步。
  • 若是不需要同步，登录从库执行：stop slave;
  • 若需要单独启动或停止某个同步通道，可使用如下命令： start slave for channel '300'; // 启动名称为 300 的同步通道 stop slave for channel '300'; // 停止名称为 300 的同步通道
  • 通常多主一从，主库之间尽量不使用相同的 databaseName，避免冲突。

四、设置MYSQL主从同步场景

1.业务场景

  • 场景1：实现服务器负载均衡
    若我们所有的业务代码存于一个服务器上，而这个服务器有的时候回宕机，导致业务停顿，造成影响。这个时候 就需要做高可用，在数据库的处理上如果两个mysql实现高可用，避免单点问题。
  • 场景2：读写分离；master写，其他slave读，这种架构最大问题I/O压力集中在Master上;
  • 场景3：通过复制实现数据的异地备份
    可以定期的将数据从主服务器上复制到从服务器上，这无疑是先了数据的异地备份。在传统的备份体制下，是将数据备份在本地。此时备份 作业与数据库服务器运行在同一台设备上，当备份作业运行时就会影响到服务器的正常运行。有时候会明显的降低服务器的性能。同时，将备份数据存放在本地，也 不是很安全。如硬盘因为电压等原因被损坏或者服务器被失窃，此时由于备份文件仍然存放在硬盘上，数据库管理员无法使用备份文件来恢复数据。这显然会给企业 带来比较大的损失。

2.实现的目标

一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。对于多级复制，数据库服务器即可充当主机，也可充当从机。
一句话表示就是，主数据库做什么，从数据库就跟着做什么。

3.原理

我们的主从复制基于Mysql Replication来进行实现

3.1 什么是Mysql Replication

Replication可以实现将数据从一台数据库服务器（master）复制到一台或多台数据库服务器（slave）

  • 默认情况下属于异步复制，无需维持长连接
  • 通过配置，可以复制所有的库或者几个库，甚至库中的一些表
  • 是MySQL内建的，本身自带的

3.2 Mysql Replication的原理

简单的说就是master将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志进行数据操作。

注意：

  • DML表示SQL操作语句如：update, insert，delete
  • Relay log :中继日志

整体上来说，复制有3个步骤：
（1） master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）
（2） slave将master的binary log events拷贝到它的中继日志(relay log)
（3） slave重做中继日志中的事件，修改salve上的数据。

3.3 My Replication的作用

  • Fail Over 故障切换
  • Backup Server 备份服务，无法对SQL语句执行产生的故障恢复，有限的备份
  • High Performance 高性能，可以多台slave,实现读写分离

3.4 MySQL主从复制

第一步：master记录二进制日志

在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

第二步：slave将master的binary log拷贝到它自己的中继日志

首先，slave开始一个工作线程—I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经执行完master产生的所有文件，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

第三步：SQL slave thread（SQL从线程）处理该过程的最后一步

SQL线程从中继日志读取事件，并重新执行其中的事件而更新slave的数据，使其与master中的数据一致。

4 My Replication常见方案

One master and Muti salve 一主多备（MS）

一般用来做读写分离的，master写，其他slave读，这种架构最大问题I/O压力集中在Master上<多台同步影响IO>

M-S-S

使用一台slave作为中继，分担Master的压力，slave中继需要开启bin-log,并配置log-slave-updates

Slave中继可使用Black-hole存储引擎，不会把数据存储到磁盘，只记录二进制日志。

M-M 双主互备（互为主从）

每个服务器需要做同样的同步更新，破坏了事物的隔离性和数据的一致性。

M-M-M

监控三台机器互相作对方的master

天生的缺陷：复制延迟，slave上同步要慢于master，如果大并发的情况那延迟更严重。
mysql在5.6已经自身可以实现fail over故障切换

One slave Muti master 一从对多主

好处：节省成本，将多个master数据自动化整合
缺陷：对库和表数据的修改较多

5.实现

5.1 环境准备

本地安装两个mysql，或者使用虚拟机，或者使用docker安装，需要准备两个mysql，本文使用docker安装
在docker中先创建两个mysql服务器
主服务器：3308
从服务器：3309

5.2 修改 my.cnf配置文件

  • master的my.cnf配置文件
#mysql master1 config 
[mysqld]
server-id = 1        # 节点ID，确保唯一
# log config
log-bin = mysql-bin     #开启mysql的binlog日志功能
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小
max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
 

auto-increment-offset = 1     # 自增值的偏移量
 

auto-increment-increment = 1  # 自增值的自增量
 

slave-skip-errors = all #跳过从库错误
 

 

  • slave1的my.cnf配置文件
 

[mysqld]
 

server-id = 2
 

log-bin=mysql-bin
 

relay-log = mysql-relay-bin
 

replicate-wild-ignore-table=mysql.%
 

replicate-wild-ignore-table=test.%
 

replicate-wild-ignore-table=information_schema.%
 

6.进入master数据库，创建复制用户并授权
 

依次执行
 

CREATE USER repl_user IDENTIFIED BY 'root';
 

CREATE USER 'repl_user'@'172.17.0.1' IDENTIFIED BY 'root';
 

CREATE USER 'repl_user'@'192.168.1.2' IDENTIFIED BY 'root';
 

root表示该用户的用户密码
 

你只需要修改的是将 192.168.1.2改为你自己主机的ip地址即可
 查看自己创建用户是否成功
 

 SELECT User, Host FROM mysql.user;
 

 

6.1 赋予该用户复制的权利
 

依次执行
 

grant replication slave on *.* to 'repl_user'@'%';
 

grant replication slave on *.* to 'repl_user'@'172.17.0.1';
 

grant replication slave on *.* to 'repl_user'@'192.168.1.2';
 

FLUSH PRIVILEGES;
 


 注意：我们接下来进行一个很重要的步骤，就是修改主库repl_user的plugin
 依次执行以操作：
 

alter user 'repl_user'@'%' identified with mysql_native_password by 'root';
 

alter user 'repl_user'@'172.17.0.1' identified with mysql_native_password by 'root';
 

alter user 'repl_user'@'192.168.1.2' identified with mysql_native_password by 'root';
 

 

6.2 查看master的状态
 

show master status;
 

 

7.配置从库
 

登录进入从库的mysql服务内执行以下命令
 

CHANGE MASTER TO 
 

MASTER_HOST = 'localhost',  
 

MASTER_USER = 'repl_user', 
 

MASTER_PASSWORD = 'root',
 

MASTER_PORT = 3308,
 

MASTER_LOG_FILE='binlog.000002',
 

MASTER_LOG_POS=1374,
 

MASTER_RETRY_COUNT = 60,
 

MASTER_HEARTBEAT_PERIOD = 10000; 
 

 

注意你要修改以下内容：
 

7.1 启动从库slave进程
 

start slave;
 

附：停止从库进程：stop slave;
 

7.2 查看是否配置成功
 

show slave status\G
 

 

当这两个信息 都为yes时说明你配置成功啦。
 

8.测试主从同步
 

我用navicat连接到主库和从库
 

接下来我先在master中创建一个数据库test1
 

然后刷新salve
 然后会发现salve中也会出现一个test1数据库
 

 

 

4 主从复制原理
 

4.1 主从复制涉及的文件和线程
 

4.1.1 线程
 

主库：
 

  • Binlog_dump_Thread： 
  
    • 作用：用来接收从库的请求，并且投递binlog给从库，启用几个从库开启几个线程
    •  
      查看：mysql> show processlist;
       
       
       
 

从库：
 

  • IO Thread 
  
    • 作用：请求binlog，接收binlog
    • 查看：mysql> show slave status
  • SQL Thread 
  
    • 作用：回放relay-log
    • 查看：mysql> show slave status
 

4.1.2 文件
 

主库：
 

  • 主库binlog文件：mysql-bin.000001
 

从库：
 

  1. relay-log文件：
 

  • 文件名：db01-relay.000001
  • 作用：存储从主库接收的binlog
  • 位置：默认存储在数据目录下，可手工定义参数relay_log_basename=/data/3308/data/db01-relay-bin，从而指定文件位置和前缀名称
 

  1. master.info：
 

  • 作用：记录连接主库的信息，已经接收到的binlog位置点信息
  • 位置：默认存储在数据目录下，可手工定义参数master_info_repository=FILE/TABLE来指定信息存在文件中或存在表中
 

  1. relay-log.info：
 

  • 作用：记录从库已经回放到的relay-log的位置点
  • 位置：默认存储在数据目录下，可手工定义参数relay_log_info_repository=FILE/TABLE来指定信息存在文件中或存在表中
 

4.2 主从复制原理图
 

 

 

 


文字说明：
 

 

  1. S：执行Change master to，IP、Port、USER、Password、binlog位置信息写入到master.info文件，执行start slave（启动SQL、IO线程）
  2. S：连接主库
  3. M：分配Dump_Thread，专门和S的IO_Thread通讯，启用几个从库开启几个线程
  4. S：IO线程请求新binlog日志
  5. M：Dump线程，接收请求，截取binlog日志返回给S的IO线程
  6. S：IO线程接收binlog，日志放在TCP/IP缓存中，此时网络层面返回ACK给主库。主库工作完成。
  7. 与第8步一起进行
  8. S：IO线程将接收的binlog最终写入到relay-log当中，并更新master.info文件的binlog位置信息。IO线程工作结束
  9. S：SQL线程读取relay-log.info，获取上次执行到的位置点
  10. S：SQL线程向后执行新的relay-log
  11. S：SQL线程再次更新relay-log.info
 

小细节：
 

  1. S：relay-log 参数relay_log_purge=ON，定期删除应用过的relay-log
  2. M：Dump线程实时监控主库binlog的变化，如果有新变化，发信号给从库。
 

5 主从监控
 

5.1 主库方面
 

mysql> show processlist;
 mysql> show slave hosts;
 

5.2 从库方面
 

mysql> show slave status \G;
 

 

监视多源复制
 

要监视复制通道的状态，存在以下选项：
 使用复制性能架构表。这些表的第一列是Channel_Name。可以根据Channel_Name键编写复杂的查询。
 

SHOW REPLICA | SLAVE STATUS FOR CHANNELchannel
 

在 performance_schema 库中，提供了复制相关的一些视图，可供查看复制相关的信息。
 

 

  1. USE performance_schema;
  2. select * from replication_applier_configuration;
  3. select * from replication_applier_status;
  4. select * from replication_applier_status_by_coordinator;
  5. select * from replication_applier_status_by_worker;
  6. select * from replication_connection_configuration;
  7. select * from replication_connection_status;
 

 
 
 
  
 
  
 
   
 
    
 
    
 
     
 
     
 
    
 
   
 
  
 
 
 


                

                 本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读
相关标签
Copyright ©  2003-2013 www.wpsshop.cn  版权所有，并保留所有权利。

  
闽ICP备14008679号