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)]>
多源复制，每个源应该使用不同的数据，多源复制目前不支持同步复制；
