MySQL 多源複製

MySQL多源複製使slave能夠同時從多個源master接收事務。 多源複製可用於將多個伺服器備份到單個伺服器，合併表分片，以及將來自多個伺服器的資料合併到單個伺服器。 應用事務時，多源複製不會實現任何衝突檢測或解決，如果需要，這些任務將留給應用程式。 在多源複製拓撲中，slave伺服器應從每個接收事務的主伺服器建立複製通道。

一.1.1.1  MySQL安裝
### 搭建3主1從
docker pull mysql:5.7.29
docker network create --subnet=172.72.0.0/24 mysql-network
 
 
mkdir -p /usr/local/mysql/master1/conf.d
mkdir -p /usr/local/mysql/master1/data
mkdir -p /usr/local/mysql/master2/conf.d
mkdir -p /usr/local/mysql/master2/data
mkdir -p /usr/local/mysql/master3/conf.d
mkdir -p /usr/local/mysql/master3/data
mkdir -p /usr/local/mysql/slave/conf.d
mkdir -p /usr/local/mysql/slave/data
 
 
 
docker run -d --name mysql5729M33065 \
   -h master1 -p 33065:3306 --net=mysql-network --ip 172.72.0.10 \
   -v /usr/local/mysql/master1/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/master1/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.29
 
docker run -d --name mysql5729M33066 \
   -h master2 -p 33066:3306 --net=mysql-network --ip 172.72.0.11 \
   -v /usr/local/mysql/master2/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/master2/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.29
 
docker run -d --name mysql5729M33067 \
   -h master3 -p 33067:3306 --net=mysql-network --ip 172.72.0.12 \
   -v /usr/local/mysql/master3/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/master3/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.29
 
docker run -d --name mysql5729S33068 \
   -h slave1 -p 33068:3306 --net=mysql-network --ip 172.72.0.13 \
   -v /usr/local/mysql/slave/conf.d:/etc/mysql/conf.d -v /usr/local/mysql/slave/data:/var/lib/mysql/ \
   -e MYSQL_ROOT_PASSWORD=lhr \
   mysql:5.7.29
 
 
vi /usr/local/mysql/master1/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 572933065
log-bin = 
binlog_format=row
expire_logs_days = 30
max_binlog_size  = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
skip-name-resolve
auto-increment-increment=2
auto-increment-offset=1
gtid-mode=ON
enforce-gtid-consistency=on
skip_name_resolve
 
 
 
vi /usr/local/mysql/master2/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 572933066
log-bin = 
binlog_format=row
expire_logs_days = 30
max_binlog_size  = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
 
 
vi /usr/local/mysql/master3/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 572933067
log-bin = 
binlog_format=row
expire_logs_days = 30
max_binlog_size  = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
 
 
 
vi /usr/local/mysql/slave/conf.d/my.cnf
[mysqld]
user=mysql
port=3306
character_set_server=utf8mb4
secure_file_priv=''
server-id = 572933068
log-bin = 
binlog_format=row
expire_logs_days = 30
max_binlog_size  = 100M
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
binlog-ignore-db = sys
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
log-slave-updates=1
gtid-mode=ON
enforce-gtid-consistency=ON
skip_name_resolve
read-only=1
master-info-repository = table
relay-log-info-repository = table
skip-slave-start = true
 
 
 
 
docker restart mysql5729M33065
docker restart mysql5729M33066
docker restart mysql5729M33067
docker restart mysql5729S33068
docker ps
 
docker exec -it mysql5729M33065 bash
docker exec -it mysql5729M33065 mysql -uroot -plhr
 
mysql -uroot -plhr -h121.36.78.6 -P33065 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33066 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33067 -e "select @@hostname,@@server_id,@@server_uuid"
mysql -uroot -plhr -h121.36.78.6 -P33068 -e "select @@hostname,@@server_id,@@server_uuid"
 
一.1.1.2  主庫配置
--在3臺主庫
mysql -uroot -plhr -h121.36.78.6 -P33065
mysql -uroot -plhr -h121.36.78.6 -P33066
mysql -uroot -plhr -h121.36.78.6 -P33067
grant replication slave on *.* to repl@'%' identified by 'lhr';
select user,host,grant_priv,password_last_changed,authentication_string from mysql.user; 
 
 
 
show master status \G;
show slave hosts;
select @@hostname,@@server_id,@@server_uuid;
 
一.1.1.3  從庫配置
MySQL 5.7 有了通訊渠道的概念，每一個通訊渠道都是一個從伺服器到主伺服器獲得二進位制日誌的連結。這意味著每個通訊渠道都得有一個 IO_THREAD。對於每一個主伺服器，我們需要執行不同的 CHANGE MASTER 命令和FOR CHANNEL 這個引數來分別提供不同通訊連結名字。
下面開始設定需要同步的源，同步兩個主伺服器的資料到從伺服器上。
設定同步源到 Master1 (在 MySQL 從伺服器上執行)
mysql -uroot -plhr -h121.36.78.6 -P33068
change master to master_host='172.72.0.10',master_port=3306,master_user='repl',master_password='lhr',master_auto_position=1  FOR CHANNEL 'Master1';
 
change master to master_host='172.72.0.11',master_port=3306,master_user='repl',master_password='lhr',master_auto_position=1  FOR CHANNEL 'Master2';
 
change master to master_host='172.72.0.12',master_port=3306,master_user='repl',master_password='lhr',master_auto_position=1  FOR CHANNEL 'Master3';
 
 
# 啟動所有SLAVE
mysql> START SLAVE;
# 也可以單獨啟動需要同步的通道
mysql> START SLAVE FOR CHANNEL 'master1';
mysql> START SLAVE FOR CHANNEL 'master2';
 
start slave; 
show slave status \G;
 
MySQL [(none)]> show slave status ;
+----------------+-------------+-------------+-------------+---------------+-----------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File                  | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB                             | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------+-------------+-------------+-------------+---------------+-----------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
|                | 172.72.0.10 | repl        |        3306 |            60 |                 |                   4 | slave1-relay-bin-master1.000001 |             4 |                       | No               | No                |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                   0 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL |                         |              86400 |             |                         |                          |                |                    |                    |                   |             1 |                      | master1      |                    |
|                | 172.72.0.11 | repl        |        3306 |            60 |                 |                   4 | slave1-relay-bin-master2.000001 |             4 |                       | No               | No                |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                   0 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL |                         |              86400 |             |                         |                          |                |                    |                    |                   |             1 |                      | master2      |                    |
|                | 172.72.0.12 | repl        |        3306 |            60 |                 |                   4 | slave1-relay-bin-master3.000001 |             4 |                       | No               | No                |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                   0 |             154 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL | No                            |             0 |               |              0 |                |                             |                0 |             | mysql.slave_master_info |         0 |                NULL |                         |              86400 |             |                         |                          |                |                    |                    |                   |             1 |                      | master3      |                    |
+----------------+-------------+-------------+-------------+---------------+-----------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+-------------+-------------------------+-----------+---------------------+-------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
3 rows in set (0.07 sec)
 
MySQL [(none)]> START SLAVE;
Query OK, 0 rows affected (0.04 sec)
 
MySQL [(none)]> show slave status ;
+----------------------------------+-------------+-------------+-------------+---------------+--------------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File                  | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB                             | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File        | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set                     | Executed_Gtid_Set                                                                                                      | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-------------+-------------+-------------+---------------+--------------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | 172.72.0.10 | repl        |        3306 |            60 | master1-bin.000001 |                 434 | slave1-relay-bin-master1.000002 |           651 | master1-bin.000001    | Yes              | Yes               |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                 434 |             867 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |        572933065 | b91e95de-82d2-11ea-86c2-0242ac48000a | mysql.slave_master_info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    | b91e95de-82d2-11ea-86c2-0242ac48000a:1 | b91e95de-82d2-11ea-86c2-0242ac48000a:1,
bc908664-82d2-11ea-b1a9-0242ac48000b:1,
bd1e55e8-82d2-11ea-9622-0242ac48000c:1 |             1 |                      | master1      |                    |
| Waiting for master to send event | 172.72.0.11 | repl        |        3306 |            60 | master2-bin.000001 |                 429 | slave1-relay-bin-master2.000002 |           646 | master2-bin.000001    | Yes              | Yes               |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                 429 |             862 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |        572933066 | bc908664-82d2-11ea-b1a9-0242ac48000b | mysql.slave_master_info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    | bc908664-82d2-11ea-b1a9-0242ac48000b:1 | b91e95de-82d2-11ea-86c2-0242ac48000a:1,
bc908664-82d2-11ea-b1a9-0242ac48000b:1,
bd1e55e8-82d2-11ea-9622-0242ac48000c:1 |             1 |                      | master2      |                    |
| Waiting for master to send event | 172.72.0.12 | repl        |        3306 |            60 | master3-bin.000001 |                 429 | slave1-relay-bin-master3.000002 |           646 | master3-bin.000001    | Yes              | Yes               |                 | information_schema,performance_schema,mysql,sys |                    |                        |                         |                             |          0 |            |            0 |                 429 |             862 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |        572933067 | bd1e55e8-82d2-11ea-9622-0242ac48000c | mysql.slave_master_info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    | bd1e55e8-82d2-11ea-9622-0242ac48000c:1 | b91e95de-82d2-11ea-86c2-0242ac48000a:1,
bc908664-82d2-11ea-b1a9-0242ac48000b:1,
bd1e55e8-82d2-11ea-9622-0242ac48000c:1 |             1 |                      | master3      |                    |
+----------------------------------+-------------+-------------+-------------+---------------+--------------------+---------------------+---------------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+----------------------------------------+------------------------------------------------------------------------------------------------------------------------+---------------+----------------------+--------------+--------------------+
3 rows in set (0.06 sec)
 
檢視SLAVE資訊
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
確認 Slave_IO_Running 和 Slave_SQL_Running 兩個引數都為 Yes 狀態。
如果要檢視單一通道的複製的詳細狀態，可以使用以下命令：
mysql> SHOW SLAVE STATUS FOR CHANNEL 'master1'\G;
 
MySQL [(none)]> select a.master_log_pos,a.host,a.user_name,a.user_password,a.port,a.uuid,a.channel_name from  mysql.slave_master_info a;
+----------------+-------------+-----------+---------------+------+--------------------------------------+--------------+
| master_log_pos | host        | user_name | user_password | port | uuid                                 | channel_name |
+----------------+-------------+-----------+---------------+------+--------------------------------------+--------------+
|            154 | 172.72.0.10 | repl      | lhr           | 3306 | b91e95de-82d2-11ea-86c2-0242ac48000a | master1      |
|            154 | 172.72.0.11 | repl      | lhr           | 3306 | bc908664-82d2-11ea-b1a9-0242ac48000b | master2      |
|            154 | 172.72.0.12 | repl      | lhr           | 3306 | bd1e55e8-82d2-11ea-9622-0242ac48000c | master3      |
+----------------+-------------+-----------+---------------+------+--------------------------------------+--------------+
3 rows in set (0.03 sec)
 
在 performance_schema 庫中，提供了複製相關的一些檢視，可供檢視複製相關的資訊。
USE performance_schema;
select * from replication_applier_configuration;
select * from replication_applier_status;
select * from replication_applier_status_by_coordinator;
select * from replication_applier_status_by_worker;
select * from replication_connection_configuration;
select * from replication_connection_status;
select * from replication_group_member_stats;
select * from replication_group_members;
這些表裡分別有多源通道的配置資訊和多源通道的狀態資訊，另外還有連線配置資訊和連線狀態資訊，如果配置了多執行緒複製的話，還會有多執行緒配置資訊和多執行緒狀態資訊。
 
一.1.1  測試多源複製
 
---測試多源
create database master1;
use master1;
CREATE TABLE `test1` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test1 values(1,1);
 
 
create database master2;
use master2;
CREATE TABLE `test2` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test2 values(2,2);
 
 
 
create database master3;
use master3;
CREATE TABLE `test3` (`id` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test3 values(3,3);
 
 
 
--從庫查詢
show databases;
SELECT * FROM master1.test1;
SELECT * FROM master2.test2;
SELECT * FROM master3.test3;

一、什麼是多源複製

MySQL 5.7釋出後，在複製方面有了很大的改進和提升。比如開始支援多源複製(multi-source)以及真正的支援多執行緒複製了。多源複製可以使用基於二進位制日誌的複製或者基於事務的複製。下面我們說一說如何配置基於二進位制日誌的多源複製。

首先，我們要清楚幾種常見的複製模式：

1. 一主一從
2. 一主多從
3. 級聯複製
4. multi-master

MySQL 5.7 之前只能支援一主一從，一主多從或者多主多從的複製。如果想實現多主一從的複製 只能使用mariadb，但是mariadb又與官方的mysql版本不相容。

MySQL 5.7 開始支援了多主一從的複製方式也就是多源複製。MySQL 5.7 版本相比之前的版本，無論是功能還是效能還是安全等方面都已經有了不少的提升。

首先我們要清楚multi-master OR multi-source 複製不是一樣的。multi-master複製通常是環形的複製，可以在任意的主機上將資料複製給其他的主機。如圖：

multi-source 是不同的。簡單的說，多源複製就是將多個主庫同步到一個從庫，從而增加從的利用率，節省了機器。如圖：

 

 

二、多源複製的使用場景

資料分析部門會需要各個業務部門的部分資料做資料分析，這個時候就可以使用到多源複製把各個主資料庫的資料複製到統一的資料庫中

在從伺服器進行資料的彙總，如果我們的主伺服器進行了分庫分表的操作，為了實現後期的一些資料的統計功能，往往要把資料彙總在一起在進行統計

在從伺服器對所有主伺服器的資料進行備份，在MySQL 5.7之前每個主伺服器都需要一臺從伺服器，這樣很容易造成資源的浪費，同時也加大了DBA的維護成本，但MySQL 5.7引入的多源複製，可以把多個主伺服器的資料同步到一臺從伺服器進行備份。

三、多源複製的必要條件

不管是使用基於二進位制日誌的複製或者基於事務的複製，要開啟多源複製功能必須要在從庫上設定master-info-repository AND relay-log-info-repository 這兩個引數。

這兩個引數是用來儲存同步資訊的，可以設定的值為FILE和TABLE，預設是FILE。比如master-info就儲存在master.info檔案中，relay-log-info儲存在relay-log.inf檔案中伺服器如果意外關閉，正確的relay-log-info沒有來的及更新帶relay-log.info檔案，這樣就會造成資料的丟失。

為了資料更加安全，通常設定為TABLE。這些表都是innodb型別的，支援事務。相對檔案儲存安全得多。在MySQL庫下可以看到這兩個表的資訊，分別是mysql.slave_master_info AND mysql.slave_relay_log_info 

這兩個引數也是可以動態調整的。

SET GLOBAL master_info_repository = 'TABLE';

SET GLOBAL relay_log_info_repository = 'TABLE';

如果要啟用enhanced multi-threaded slave(多執行緒複製)，可以設定一下引數

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8

relay_log_recovery=ON

如果SLAVE已經為開啟狀態，那麼需要首先關閉SLAVE(stop slave;)

四、配置多源複製

4.1:環境準備

hostname	IP	mysql
dev-master-01	192.168.1.131	master
dev_node-01	192.168.1.132	master
dev-node-02	192.168.1.133	slave

4.2:安裝MySQL

　　 具體的安裝步驟在這裡： https://www.cnblogs.com/zhujingzhi/p/9609861.html  你可以選擇你想用的安裝方式進行安裝

4.3:配置配置檔案my.cnf

　　1.   修改192.168.1.131和192.168.1.132下面的/etc/my.cnf檔案，修改如下：記得修改配置引數中的server-id（192.168.1.131為1，那麼192。168.1.132就得是2，每臺伺服器中的這個id不能一樣，切記！！！）

　　 2.  修改192.168.1.133也就是從庫的/etc/my.cnf 配置引數，配置如下：（記得修改server-id為3，不能和其他伺服器id一樣，切記！！！）

4.4:重啟資料庫並且授權

　　4.5:檢查測試

五、一些其他的注意點

初次配置耗時較長，需要將各個 master 的資料 dump 下來，再 source 到 slave 上。

需要考慮各 master 資料增長頻率，slave 的資料增長頻率是這些資料的總和。如果太高，會導致大量的磁碟IO，造成資料更新延遲，最嚴重的是會影響正常的查詢。

如果多個主資料庫例項中存在同名的庫，則同名庫的表都會放到一個庫中；

如果同名庫中的表名相同且結構相同，則資料會到一起；如果結構不同，則先建的有效。

---

---

About Me

........................................................................................................................ ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除 ● 本文在itpub、部落格園、CSDN和個人微 信公眾號（ DB寶）上有同步更新 ● 本文itpub地址： http://blog.itpub.net/26736162 ● 本文部落格園地址： http://www.cnblogs.com/lhrbest ● 本文CSDN地址： https://blog.csdn.net/lihuarongaini ● 本文pdf版、個人簡介及小麥苗雲盤地址： http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答： http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址： http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ........................................................................................................................ ● QQ群號： 230161599 、618766405 ● 微 信群：可加我微 信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友 （ 646634621 ），註明新增緣由 ● 於 2020-04-01 06:00 ~ 2020-04-30 24:00 在西安完成 ● 最新修改時間：2020-04-01 06:00 ~ 2020-04-30 24:00 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ........................................................................................................................ ● 小麥苗的微店： https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書： http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麥苗OCP、OCM、高可用網路班： http://blog.itpub.net/26736162/viewspace-2148098/ ● 小麥苗騰訊課堂主頁： https://lhr.ke.qq.com/ ........................................................................................................................ 使用 微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（ DB寶）及QQ群（DBA寶典）、新增小麥苗微 信， 學習最實用的資料庫技術。 ........................................................................................................................

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2687226/，如需轉載，請註明出處，否則將追究法律責任。