Multi-master-rep
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/