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

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

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.6	MySQL 8.0.29	Master A	192.168.1.71	host71
CentOS 7.6	MySQL 8.0.29	Master B	192.168.1.72	host72
CentOS 7.6	MySQL 8.0.29	Master C	192.168.1.73	host73

部署步骤

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

server_id = 71 #三台保持唯一性
port = 3380

log_bin = /data/mysql/log_bin
binlog_expire_logs_seconds = 259200
gtid_mode = on
log-slave-updates = on
enforce-gtid-consistency = on

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

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

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

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

4. host71端恢复数据

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

5. 配置主从

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';
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

mysql> start slave for channel 'host72';
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

vim /etc/sysconfig/selinux 
SELINUX=disabled

# 若不关闭，使用docker启动mysql5.7镜像容器时启动不起来，查看日志会有如下错误显示：
ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help"
mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
ERROR: mysqld failed while attempting to check config
command was: "mysqld --verbose --help"
mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 13 - Permission denied)
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!

1. 安装并启动 docker

yum install epel-release
yum -y install docker
systemctl start docker.service

1. 创建目录

mkdir -p /datavol/mysql-300/{mysql,conf}
mkdir -p /datavol/mysql-400/{mysql,conf}
mkdir -p /datavol/mysql-500/{mysql,conf}
mkdir -p /datavol/mysql-10345/{mysql,conf}

【主库 300】操作及配置

配置 my.cnf

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

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 300			#必须唯一
log_bin = mysql-bin 	#开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test3 	#要同步的数据库

binlog-ignore-db = mysql 	#不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
			

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动

[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
5691bac538e646db00273e3cad5b350dbe6cce0bd176346b7eefd9a6f9e3a9ad
[root@localhost ~]# docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
5691bac538e6        mysql:5.7                           "docker-entrypoint..."   44 seconds ago      Up 43 seconds       33060/tcp, 0.0.0.0:4300->3306/tcp                    mysql-300
[root@localhost ~]# docker exec -it mysql-300 /bin/bash
root@5691bac538e6:/# mysql -u root -p
Enter password: 

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

创建授权用户

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

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看 master 状态

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

【主库 400】配置及操作

配置 my.cnf

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

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 400			#必须唯一
log_bin = mysql-bin 	#开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test4 		#要同步的数据库

binlog-ignore-db = mysql 	#不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
			

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动

[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
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4400->3306/tcp                    mysql-400
[root@localhost ~]# docker exec -it mysql-400 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password: 

创建授权用户

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

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看 master 状态

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

【主库 500】配置及操作

配置 my.cnf

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

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data #数据存储目录

##########################
# log bin
##########################
server-id = 500			#必须唯一
log_bin = mysql-bin 	#开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7		#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

#binlog_cache_size = 128m
#max_binlog_cache_size = 512m
#max_binlog_size = 256M

binlog-do-db = test5 		#要同步的数据库

binlog-ignore-db = mysql 	#不需要同步的数据库 
binlog_ignore_db = information_schema
binlog_ignore_db = performation_schema
binlog_ignore_db = sys
			

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动

[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
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4500->3306/tcp                    mysql-500
[root@localhost ~]# docker exec -it mysql-500 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password: 

创建授权用户

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

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'slave'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

log_bin 是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set

查看 master 状态

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

【从库 10345】配置及操作

配置 my.cnf

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

[client]
port = 3306
default-character-set = utf8mb4

[mysql]
port = 3306
default-character-set = utf8mb4

[mysqld]
##########################
# summary
##########################
#bind-address = 0.0.0.0
#port = 3306
#datadir=/datavol/mysql/data 	#数据存储目录

##########################
# log bin
##########################
server-id = 10345
master_info_repository      = table
relay_log_info_repository   = table		

##########################
# character set
##########################
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

安装启动

[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
19e93b6d93ca4e6ca0d540e3f6c831b835cdbb35362733867c3977aee4d33bf7
[root@localhost ~]# docker ps
CONTAINER ID        IMAGE                               COMMAND                  CREATED             STATUS              PORTS                                                NAMES
19e93b6d93ca        mysql:5.7                           "docker-entrypoint..."   4 seconds ago       Up 3 seconds        33060/tcp, 0.0.0.0:4345->3306/tcp                    mysql-10345
[root@localhost ~]# docker exec -it mysql-10345 /bin/bash
root@19e93b6d93ca:/# mysql -u root -p
Enter password: 

设置【主库】信息

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

mysql> stop slave;
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.10.212',
MASTER_PORT=4300,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438 
for channel '300';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.10.212',
MASTER_PORT=4400,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438 
for channel '400';
Query OK, 0 rows affected

mysql> CHANGE MASTER TO 
MASTER_HOST='192.168.10.212',
MASTER_PORT=4500,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=438 
for channel '500';
Query OK, 0 rows affected

mysql> start slave;
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，以排查问题。

mysql> show variables like 'log_error%';
+---------------------+--------+
| Variable_name       | Value  |
+---------------------+--------+
| log_error           | stderr |
| log_error_verbosity | 3      |
+---------------------+--------+
2 rows in set

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

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

验证数据同步

建库

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

mysql> CREATE DATABASE test3;
Query OK, 1 row affected (0.00 sec)

mysql> USE test3;
Database changed

建表

在【主库 300】中创建 user 表

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

新增

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

mysql> use test3;
Database changed
mysql> INSERT INTO user (id, name, age) VALUES (300, 'Tom', 18);
Database changed
mysql> SELECT * FROM user;
+-----+------+-----+
| id  | name | age |
+-----+------+-----+
| 300 | Tom  |  18 |
+-----+------+-----+
1 row in set (0.00 sec)

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

mysql> use test3;
Database changed
mysql> SELECT * FROM user;
+-----+------+-----+
| id  | name | age |
+-----+------+-----+
| 300 | Tom  |  18 |
+-----+------+-----+
1 row in set (0.00 sec)

新增记录同步成功。

更新

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

mysql> UPDATE user SET name='Peter' where id=300;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+-----+-------+-----+
| id  | name  | age |
+-----+-------+-----+
| 300 | Peter |  18 |
+-----+-------+-----+
1 row in set (0.00 sec)

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

mysql> select * from user;
+-----+-------+-----+
| id  | name  | age |
+-----+-------+-----+
| 300 | Peter |  18 |
+-----+-------+-----+
1 row in set (0.00 sec)

更新记录同步成功。

删除

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

mysql> DELETE FROM user WHERE id=300;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> select * from user;
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;