MariaDB多源复制数据汇总的详解

MariaDB多源复制数据汇总,如下图所示，在某些场景中，有A和b两个节点数据库，从数据分别读取ab两个节点的数据到一台slave数据库中

主A和主B：

[root@master local]# tar xf mariadb-10.0.10-linux-x86_64.tar.gz
[root@master local]# ln -sv mariadb-10.0.10-linux-x86_64 mysql
`mysql' -> `mariadb-10.0.10-linux-x86_64'
[root@master local]# cd mysql/
[root@master mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
[root@master mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf
修改server-id,二进制log-bin开启，

datadir = /mydata/data
basedir = /usr/local/mysql
innodb_file_per_table = on
skip_name_resolve = on
log-bin=/mydata/binlogs/master-1
binlog_format=mixed
server-id       = 1
在主A和主B上分别创建复制账号：

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user'@'192.168.%.%' IDENTIFIED BY 'pass';
从服务器：
注释掉log-bin
添加relay-log

#log-bin=mysql-bin
server-id       = 10
relay-log = relay-bin
在从服务器上分别如下：
从服务器需要开启中继日志，修改server_id号，从服务器仍然不能写入，只能读取

CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass';
CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass';
如下：

MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start all slaves;
当OK后，使用一下命令查看！show all slaves statusG

由于是第一次使用空数据，可能需要手动在从服务器上指定，如下所示！
关闭slaves后，在master1和2上分别查看后并且重新change

MariaDB [(none)]> stop all slaves;
master-1

MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| master-1.000003 |      325 |              |                  |
+-----------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-1.000003',MASTER_LOG_POS=325;
master-2

MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| master-2.000001 |      518 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-2.000001',MASTER_LOG_POS=518;
开启slaves;

MariaDB [(none)]> start all slaves;
查看

MariaDB [(none)]> show all slaves statusG
*************************** 1. row ***************************
              Connection_name: m1
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-1.000003
          Read_Master_Log_Pos: 532
               Relay_Log_File: relay-bin-m1.000003
                Relay_Log_Pos: 534
        Relay_Master_Log_File: master-1.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: 532
              Relay_Log_Space: 1321
              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_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 12
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-1-2141
*************************** 2. row ***************************
              Connection_name: m2
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-2.000001
          Read_Master_Log_Pos: 518
               Relay_Log_File: relay-bin-m2.000003
                Relay_Log_Pos: 534
        Relay_Master_Log_File: master-2.000001
             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: 518
              Relay_Log_Space: 1114
              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: 3
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: No
                  Gtid_IO_Pos:
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 10
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-1-2141
2 rows in set (0.00 sec)

MariaDB [(none)]>
如果数据不一致，则可以reset slave 刷新试试

MariaDB [(none)]> reset slave 'm1';
Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> START ALL SLAVES;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

MariaDB [(none)]>
多源复制，每个源应该使用不同的数据，多源复制目前不支持同步复制；