mysql 5.7如何實現多主一從的多源復制

這篇文章主要介紹了mysql 5.7如何實現多主一從的多源復制，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

主服務器創建用戶

主1

多源同步參數

#multi replication

master_info_repository=TABLE

relay_log_info_repository=TABLE

開啟GTID

[mysqld]

#GTID parameter

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

report-port=3306

report-host=192.168.56.212

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=10

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

[mysql@ray ~]$ /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/3306/soket/mysql.sock

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.05 sec)

主2

多源同步參數

#multi replication

master_info_repository=TABLE

relay_log_info_repository=TABLE

開啟GTID

[mysqld]

#GTID parameter

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

report-port=3307

report-host=192.168.56.212

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=10

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

[mysql@ray ~]$ /usr/local/mysql/bin/mysql -uroot -p123456 -S /data/3307/soket/mysql.sock

mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.05 sec)

從服務器連接主服務器

多源同步參數

#multi replication

master_info_repository=TABLE

relay_log_info_repository=TABLE

開啟GTID

[mysqld]

#GTID parameter

log-slave-updates=true

gtid-mode=on

enforce-gtid-consistency=true

report-port=3308

report-host=192.168.56.212

master-info-repository=TABLE

relay-log-info-repository=TABLE

sync-master-info=1

slave-parallel-workers=10

binlog-checksum=CRC32

master-verify-checksum=1

slave-sql-verify-checksum=1

binlog-rows-query-log_events=1

連接主1：

change master to  

master_host='192.168.56.212',  

master_user='rep',  

master_password='123456',  

master_port=3306,  

master_auto_position = 1

for channel 'm1';  

連接主2

change master to  

master_host='192.168.56.212',  

master_user='rep',  

master_password='123456',  

master_port=3307,  

master_auto_position = 1

for channel 'm2';

啟動slave進程

mysql> start slave;

查看復印進程

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.212

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: ray-bin.000005

          Read_Master_Log_Pos: 194

               Relay_Log_File: localhost-relay-bin-m1.000004

                Relay_Log_Pos: 403

        Relay_Master_Log_File: ray-bin.000005

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 194

              Relay_Log_Space: 1260

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 97e8847a-ffdf-11e6-87ed-08002736c224

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 97e8847a-ffdf-11e6-87ed-08002736c224:1-2

            Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2,

97e8847a-ffdf-11e6-87ed-08002736c224:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name: m1

           Master_TLS_Version:

*************************** 2. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.56.212

                  Master_User: rep

                  Master_Port: 3307

                Connect_Retry: 60

              Master_Log_File: ray-bin.000003

          Read_Master_Log_Pos: 194

               Relay_Log_File: localhost-relay-bin-m2.000005

                Relay_Log_Pos: 403

        Relay_Master_Log_File: ray-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 194

              Relay_Log_Space: 1083

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 2

                  Master_UUID: 517e04ac-ffe3-11e6-a7ea-08002736c224

             Master_Info_File: mysql.slave_master_info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2

            Executed_Gtid_Set: 517e04ac-ffe3-11e6-a7ea-08002736c224:1-2,

97e8847a-ffdf-11e6-87ed-08002736c224:1-2

                Auto_Position: 1

         Replicate_Rewrite_DB:

                 Channel_Name: m2

           Master_TLS_Version:

2 rows in set (0.00 sec)

啟動和停止單獨slave進程

mysql> start slave for channel 'm1';

mysql> stop slave for channel 'm1';

mariaDB:

CHANGE MASTER 'm1' TO

  MASTER_HOST='192.168.56.91',

  MASTER_USER='rep',

  MASTER_PASSWORD='123456',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='ray-bin.000007',

  MASTER_LOG_POS=396;

mysql:

CHANGE MASTER TO

  MASTER_HOST='192.168.56.91',

  MASTER_USER='rep',

  MASTER_PASSWORD='123456',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='ray-bin.000007',

  MASTER_LOG_POS=396

  for channel="m1";

感謝你能夠認真閱讀完這篇文章，希望小編分享的“mysql 5.7如何實現多主一從的多源復制”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!