multi-master-rep
two masters:-- 192.168.126.128 mysql 5.6.12-- 192.168.126.129 mysql 5.6.12
slave :-- 192.168.126.130 MariaDB 10.0
from one master machine create new db and insert one record:23:44:55 (none)> show master status -> ;+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000004 | 544 | | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.07 sec)23:45:51 (none)> create database mariatest1;Query OK, 1 row affected (0.21 sec)23:50:14 (none)> use mariatest1;Database changed23:52:16 mariatest1> create table liuyang (id bigint);Query OK, 0 rows affected (1.42 sec)23:52:30 mariatest1> 23:52:31 mariatest1> insert into liuyang values (1);Query OK, 1 row affected (0.17 sec)23:52:37 mariatest1> commit;Query OK, 0 rows affected (0.01 sec)
from secoend master machinecreate new db and insert one record:23:47:23 (none)> show master status/G*************************** 1. row *************************** File: mysql-bin.000005 Position: 550 Binlog_Do_DB:Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)23:47:29 (none)> create database mariatest2;Query OK, 1 row affected (0.13 sec)23:50:27 (none)> use mariatest2;Database changed23:52:48 mariatest2> create table liuyang (id int);Query OK, 0 rows affected (0.65 sec)23:53:43 mariatest2> insert into liuyang values (1);Query OK, 1 row affected (0.12 sec)23:53:55 mariatest2> commit;Query OK, 0 rows affected (0.00 sec)
change Mariadb slave connect to these two masters:MariaDB [(none)]> SET @@default_master_connection='t1';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> CHANGE MASTER 't1' TO MASTER_HOST = '192.168.126.128', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000005',MASTER_LOG_POS = 550;Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> SET @@default_master_connection='t2';Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> CHANGE MASTER 't2' TO MASTER_HOST = '192.168.126.129', MASTER_USER = 'repluser', MASTER_PASSWORD = 'rep', MASTER_PORT = 3333 ,MASTER_LOG_FILE = 'mysql-bin.000004',MASTER_LOG_POS = 544;Query OK, 0 rows affected (0.01 sec)
[mysql@mariadb ~]$ mysql--socket=/mysql3333/mysql/data/mysql.sockWelcome to the MariaDB monitor.Commands end with ; or /g.Your MariaDB connection id is 13Server version: 10.0.10-MariaDB-log MariaDB ServerCopyright (c) 2000, 2014, Oracle, SkySQL Ab and others.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.MariaDB [(none)]> START ALL SLAVES;Query OK, 0 rows affected, 1 warning (0.00 sec)MariaDB [(none)]> MariaDB [(none)]> show slave status-> /G;Empty set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave status /G;Empty set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave 'T1' status /G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.126.128 Master_User: repluser Master_Port: 3333 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 550 Relay_Log_File: GSS-01-relay-bin-t1.000002 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-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: 550 Relay_Log_Space: 708 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: 0Master_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: 6 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show slave 'T2' status /G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.126.129 Master_User: repluser Master_Port: 3333 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 544 Relay_Log_File: GSS-01-relay-bin-t2.000002 Relay_Log_Pos: 407 Relay_Master_Log_File: mysql-bin.000004 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: 544 Relay_Log_Space: 708 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: 0Master_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: 5 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec)ERROR: No query specifiedMariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mariatest1 || mariatest2 || mysql || performance_schema || test |+--------------------+6 rows in set (0.06 sec)MariaDB [(none)]> MariaDB [(none)]> select * from mariatest1.liuyang;+------+| id |+------+|1 |+------+1 row in set (0.00 sec)MariaDB [(none)]> select * from mariatest2.liuyang;+------+| id |+------+|1 |+------+1 row in set (0.00 sec)
REF:https://mariadb.com/kb/en/change-master-to/
https://mariadb.com/kb/en/multi-source-replication/
https://mariadb.com/kb/zh-cn/installing-mariadb-binary-tarballs/
