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 changed
23: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 machine create 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 changed
23: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.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 10.0.10-MariaDB-log MariaDB Server
Copyright (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 specified
MariaDB [(none)]> show slave status \G;
Empty set (0.00 sec)
ERROR: No query specified
MariaDB [(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: 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: 6
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(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: 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: 5
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
1 row in set (0.00 sec)
ERROR: No query specified
MariaDB [(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/