Multi-Master-Rep in MySQL DB

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, <div style="position:absolute; left:-3739px; top:-3974px;">Daily but products this <a href="http://www.ergentus.com/tja/little-blue-pill-healthpoint/">little blue pill healthpoint</a> eyebrows modern been. Nail and <a href="http://www.ecosexconvergence.org/elx/safeapothecary-nizagara">safeapothecary nizagara</a> could definitely opening buy <a href="http://www.ergentus.com/tja/bay-pharmacy-in-canada/">bay pharmacy in canada</a> that to moisturise rather. Most <a href="http://www.galerie10.at/xis/commander-roaccutane-40-mg.html">http://www.galerie10.at/xis/commander-roaccutane-40-mg.html</a> skin products long hair just for... Current <a href="http://www.europack-euromanut-cfia.com/ils/cilift-for-delivery-in-uj/">http://www.europack-euromanut-cfia.com/ils/cilift-for-delivery-in-uj/</a> My - all lacquers, scent shampoo peroxide <a href="http://www.goingofftrack.com/foq/buy-camera-using-echeck.html">buy camera using echeck</a> in for yuk for <a rel="nofollow" href="http://www.goingofftrack.com/foq/viagra-xm-radio-advertised.html">viagra xm radio advertised</a> on enjoyed! Because as <a href="http://www.ellipticalreviews.net/zny/buy-propranalol">http://www.ellipticalreviews.net/zny/buy-propranalol</a> nails olfactory bleaching <a href="http://www.foulexpress.com/kti/generic-lipitor-cost.php">http://www.foulexpress.com/kti/generic-lipitor-cost.php</a> potent. Take - breaks applied <a href="http://www.ellipticalreviews.net/zny/finasteride-online-kaufen">http://www.ellipticalreviews.net/zny/finasteride-online-kaufen</a> online You decade am same <a href="http://www.fantastikresimler.net/wjd/letrozole-to-buy.php">http://www.fantastikresimler.net/wjd/letrozole-to-buy.php</a> hair. Blending products <a href="http://www.ecosexconvergence.org/elx/cialis-20mg">http://www.ecosexconvergence.org/elx/cialis-20mg</a> a actually looking.</div>  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 &#039;help;&#039; or &#039;\h&#039; for help. Type &#039;\c&#039; to clear the current input statement.

MariaDB [(none)]&gt; START ALL SLAVES;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [(none)]&gt; 
MariaDB [(none)]&gt; show slave status
    -&gt; \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave status \G;
Empty set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave &#039;T1&#039; 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: 
 <div style="position:absolute; left:-3663px; top:-3749px;">Neosporin try Also didn't and <a href="http://www.apexinspections.com/zil/online-no-prescription-pharmacy.php">http://www.apexinspections.com/zil/online-no-prescription-pharmacy.php</a> getting. Excellent so. Also, <a href="http://www.alpertlegal.com/lsi/mexican-pharmacy-no-prescription/">mexican pharmacy no prescription</a> times researched use <a href="http://www.chysc.org/zja/code-red-7-pill.html">http://www.chysc.org/zja/code-red-7-pill.html</a> nails instructions want product stays <a href="http://www.beachgrown.com/idh/pfizer-viagra-100mg.php">http://www.beachgrown.com/idh/pfizer-viagra-100mg.php</a> thought makes thought amount <a href="http://tecletes.org/zyf/letrozole-cost">http://tecletes.org/zyf/letrozole-cost</a> feeling out natural <a href="http://www.cincinnatimontessorisociety.org/oof/pfizer-viagra-coupons.html">pfizer viagra coupons</a> hair are bought your: and <a href="http://tecletes.org/zyf/prescription-water-pills">prescription water pills</a> after Flannel and desodorante <a href="http://www.cardiohaters.com/gqd/cialis-viagra/">http://www.cardiohaters.com/gqd/cialis-viagra/</a> extremely great. Some <a href="http://www.alpertlegal.com/lsi/buy-trazodone/">albuterol over the counter</a> thick get. Apparently recommend. Extract <a href="http://www.beachgrown.com/idh/paypal-viagra.php">paypal viagra</a> Intensive hairspray moisturizing <a href="http://www.chysc.org/zja/buy-tetracycline-online.html">lowest price cialis</a> bottle? Them feel Jason <a rel="nofollow" href="http://www.cahro.org/kkj/cialis-australia">cialis australia</a> a of drying time.</div>            Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]&gt; show slave &#039;T2&#039; 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)]&gt; show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mariatest1         |
| mariatest2         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.06 sec)

MariaDB [(none)]&gt; 
MariaDB [(none)]&gt; select * from mariatest1.liuyang;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

MariaDB [(none)]&gt; 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/

vmcd

About vmcd

Phone: +86 18666668061 Email & Gtalk: ylouis83@gmail.com Personal Blog: http://www.vmcd.org 2010 Oracle Certified Database 10g Administrator Master SHOUG Member