MySQL 多源複製

lhrbest發表於2020-04-20
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不能一樣，切記！！！）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
[mysqld]
# 指定埠 不指定預設3306
#port=5603
 
# mysql資料存放路徑
datadir= /var/lib/mysql
 
# mysql socker檔案存放路徑
socket= /var/lib/mysql/mysql .sock
 
# 建立符號連結(建議禁用符號連結，以防止各種安全風險。開啟將引數的值設定為1)
symbolic-links=0
 
# 錯誤日誌存放路徑
log-error= /var/log/mysqld .log
 
# 啟動pid檔案存放路徑
pid- file = /var/run/mysqld/mysqld .pid
 
# 設定主從的時候的唯一ID 每臺主機的ID不可重複
server- id =1
 
# #開啟日誌(主機需要開啟)，這個mysql-bin也可以自定義，這裡也可以加上路徑，如：/home/www/mysql_bin_log/mysql-bin 
log-bin=mysql-bin
 
# 設定mysql的複製模式(STATEMENT  ROW  MIXED)
binlog_format=mixed
 
# 繞過密碼驗證可直接使用root登入(用於修改密碼)
#skip-grant-tables=1
 
# 啟動伺服器來禁用主機名快取
skip-host-cache
 
# 如果這個引數設為OFF，則MySQL服務在檢查客戶端連線的時候會解析主機名；如果這個引數設為ON，則MySQL服務只會使用IP，在這種情況下，授權表中的Host欄位必須是IP地址或localhost。
skip-name-resolve
 
# 網路傳輸時單個資料包的大小
max_allowed_packet = 500M
 
# 設定資料庫的時間
default- time -zone =  '+8:00'
 
# 內部記憶體臨時表的最大記憶體
tmp_table_size=200M
 
# MySQL伺服器用來作普通索引掃描、範圍索引掃描和不使用索引而執行全表掃描這些操作所用的快取大小。
join_buffer_size = 32M
 
# 每個會話執行排序操作所分配的記憶體大小。
sort_buffer_size = 1M
 
# 每個客戶端執行緒和連線快取和結果快取互動，每個快取最初都被分配大小為net_buffer_length的容量，並動態增長，直至達到max_allowed_packet引數的大小
net_buffer_length = 8K
 
# 為每個執行緒對MyISAm表執行順序讀所分配的記憶體。如果資料庫有很多順序讀，可以增加這個引數，預設值是131072位元組。
read_buffer_size = 512K
 
# 這個引數用在MyISAM表和任何儲存引擎表隨機讀所使用的記憶體。
read_rnd_buffer_size = 32M
 
# 在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中，MyISAM索引排序使用的快取大小。
myisam_sort_buffer_size = 256M
 
# 設定客戶端的併發連線數量
max_connections = 8000
 
# mysql關閉非互動連線前的等待時間，單位是秒
wait_timeout = 604800
 
# Mysql關閉互動連線前的等待時間，單位是秒
interactive_timeout = 604800
 
# 設定遠端使用者必須回應PORT型別資料連線的最大時間
connect_timeout = 30
 
# 如果客戶端嘗試連線的錯誤數量超過這個引數設定的值，則伺服器不再接受新的客戶端連線。可以通過清空主機的快取來解除伺服器的這種阻止新連線的狀態，通過FLUSH HOSTS或mysqladmin flush-hosts命令來清空快取。
max_connect_errors = 30000
 
# mysql關閉連線前的等待時間，單位是秒
interactive_timeout = 86400
 
# 慢查詢的時間設定，單位為秒
long_query_time = 20
 
# mysql服務快取以重用的執行緒數
thread_cache_size = 120
 
# 為查詢結果所分配的快取
query_cache_size = 256M
 
# 如果一個事務需要的記憶體超過這個引數，就會報錯
max_heap_table_size=2097152000

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
[mysqld]
# 指定埠 不指定預設3306
#port=5603
 
# mysql資料存放路徑
datadir= /var/lib/mysql
 
# mysql socker檔案存放路徑
socket= /var/lib/mysql/mysql .sock
 
# 建立符號連結(建議禁用符號連結，以防止各種安全風險。開啟將引數的值設定為1)
symbolic-links=0
 
# 錯誤日誌存放路徑
log-error= /var/log/mysqld .log
 
# 啟動pid檔案存放路徑
pid- file = /var/run/mysqld/mysqld .pid
 
# 設定主從的時候的唯一ID 每臺主機的ID不可重複
server- id =1
 
# #開啟日誌(主機需要開啟)，這個mysql-bin也可以自定義，這裡也可以加上路徑，如：/home/www/mysql_bin_log/mysql-bin 
log-bin=mysql-bin
 
# 設定mysql的複製模式(STATEMENT  ROW  MIXED)
binlog_format=mixed
 
# 繞過密碼驗證可直接使用root登入(用於修改密碼)
#skip-grant-tables=1
 
# 啟動伺服器來禁用主機名快取
skip-host-cache
 
# 如果這個引數設為OFF，則MySQL服務在檢查客戶端連線的時候會解析主機名；如果這個引數設為ON，則MySQL服務只會使用IP，在這種情況下，授權表中的Host欄位必須是IP地址或localhost。
skip-name-resolve
 
# 網路傳輸時單個資料包的大小
max_allowed_packet = 500M
 
# 設定資料庫的時間
default- time -zone =  '+8:00'
 
# 內部記憶體臨時表的最大記憶體
tmp_table_size=200M
 
# MySQL伺服器用來作普通索引掃描、範圍索引掃描和不使用索引而執行全表掃描這些操作所用的快取大小。
join_buffer_size = 32M
 
# 每個會話執行排序操作所分配的記憶體大小。
sort_buffer_size = 1M
 
# 每個客戶端執行緒和連線快取和結果快取互動，每個快取最初都被分配大小為net_buffer_length的容量，並動態增長，直至達到max_allowed_packet引數的大小
net_buffer_length = 8K
 
# 為每個執行緒對MyISAm表執行順序讀所分配的記憶體。如果資料庫有很多順序讀，可以增加這個引數，預設值是131072位元組。
read_buffer_size = 512K
 
# 這個引數用在MyISAM表和任何儲存引擎表隨機讀所使用的記憶體。
read_rnd_buffer_size = 32M
 
# 在REPAIR TABLE、CREATE INDEX 或 ALTER TABLE操作中，MyISAM索引排序使用的快取大小。
myisam_sort_buffer_size = 256M
 
# 設定客戶端的併發連線數量
max_connections = 8000
 
# mysql關閉非互動連線前的等待時間，單位是秒
wait_timeout = 604800
 
# Mysql關閉互動連線前的等待時間，單位是秒
interactive_timeout = 604800
 
# 設定遠端使用者必須回應PORT型別資料連線的最大時間
connect_timeout = 30
 
# 如果客戶端嘗試連線的錯誤數量超過這個引數設定的值，則伺服器不再接受新的客戶端連線。可以通過清空主機的快取來解除伺服器的這種阻止新連線的狀態，通過FLUSH HOSTS或mysqladmin flush-hosts命令來清空快取。
max_connect_errors = 30000
 
# mysql關閉連線前的等待時間，單位是秒
interactive_timeout = 86400
 
# 慢查詢的時間設定，單位為秒
long_query_time = 20
 
# mysql服務快取以重用的執行緒數
thread_cache_size = 120
 
# 為查詢結果所分配的快取
query_cache_size = 256M
 
# 如果一個事務需要的記憶體超過這個引數，就會報錯
max_heap_table_size=2097152000
 
# mysql5.7 多源複製從庫加的配置引數
master-info-repository = table  # 這個引數是必須的
relay-log-info-repository = table  # 這個引數是必須的
report-port = 3306
report-host = 192.168.1.131
replicate- do -db = master1
replicate- do -db = master2
replicate_wild_do_table=master1.%
replicate_wild_do_table=master2.%

4.4:重啟資料庫並且授權

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
重新啟動mysql服務
systemctl restart mysqld
 
升級mysql授權表
mysql_upgrade -uroot -p
 
==========================主庫執行SQL=========================
以下的執行都是在192.168.1.131和192.168.1.132的mysql中執行sql語句
 
登入192.168.1.131和192.168.1.132的mysql
mysql -uroot -p
 
建立mysql的主從授權使用者：
grant replication slave on *.*  to  'slave' @ '192.168.1.%'  identified by  '2017123' ;
 
重新整理生效
FLUSH PRIVILEGES;
  
==========================從庫執行SQL=========================
登入192.168.1.133的mysql執行sql語句：
CHANGE MASTER TO MASTER_HOST= '192.168.1.131' ,
MASTER_USER= 'slave' ,
MASTER_PORT=3306,
MASTER_PASSWORD= '2017123' ,
MASTER_LOG_FILE= 'mysql-bin.000001' ,
MASTER_LOG_POS=1 FOR CHANNEL  'master1' ;
 
CHANGE MASTER TO MASTER_HOST= '192.168.1.132' ,
MASTER_USER= 'slave' ,
MASTER_PORT=3306,
MASTER_PASSWORD= '2017123' ,
MASTER_LOG_FILE= 'mysql-bin.000001' ,
MASTER_LOG_POS=1 FOR CHANNEL  'master2' ;
 
全部顯示sql語句執行ok，然後檢視主從的狀態：
show  slave  status\G;
 
如果要檢視單一通道的複製的詳細狀態，可以使用以下命令：
SHOW SLAVE STATUS FOR CHANNEL  'master1' \G;

　　4.5:檢查測試

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
在主庫(192.168.1.131)例項建立一些資料。
create database master1;
use master1;
CREATE TABLE `test1` (` id ` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test1 values(1,1);
 
在主庫(192.168.1.132)例項建立一些資料。
create database master2;
use master2;
CREATE TABLE `test2` (` id ` int(11) DEFAULT NULL,`count` int(11) DEFAULT NULL);
insert into test2 values(1,1);
 
在從庫(192.168.1.133)例項檢查資料是否成功複製。
select  * from master1.test1;
select  * from master2.test2;
 
列出所有的複製通道的複製狀態概況：
select  * from performance_schema.replication_applier_status_by_worker;
 
在 performance_schema 庫中，提供了複製相關的一些檢視，可供檢視複製相關的資訊。
use performance_schema;
show tables like  '%repl%' ;
 
這些表裡分別有多源通道的配置資訊和多源通道的狀態資訊，另外還有連線配置資訊和連線狀態資訊，如果配置了多執行緒複製的話，還會有多執行緒配置資訊和多執行緒狀態資訊。

五、一些其他的注意點

初次配置耗時較長，需要將各個 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章