Sometimes it’s very useful to skip well-known master-slave replication scenario and focus on master-master possibility \ud83d\ude42<\/p>\n
<\/p>\n
1) Prerequisities<\/strong><\/span><\/p>\n 2 linux distributions with functional mysql server installed. In my case double Manjaro 0.8.11 Openbox will be working fine. Manjaro is riding modern wave of replacement MySQL->MariaDB + using systemd (please don’t kill me, sysvinit fanatics).<\/p>\n VirtualBox is set to network with host only =><\/p>\n M1 = 192.168.56.101, source database (sakila in this case), primary, The One \ud83d\ude42<\/p>\n M2 = 192.168.56.102, new master with no data, secondary master.<\/p>\n <\/p>\n 2) Setup<\/strong><\/span><\/p>\n 2.1 Configuration settings<\/span><\/p>\n Main MySQL\/MariaDB configuration file is \/etc\/mysql\/my.cnf\u00a0 (path can differ, indeed). Find a section [mysqld], check if there are old value – if it is so, rewrite them into new values and add new variables. Values for M1: Just very quick info about tricky part – server_id must be unique within replication environment; auto-increment-increment + auto-increment-offset = auto-increment values are starting with 1, step is 2 = 1,3,5,7,…..\u00a0 As you can imagine, collision with another master will be critical \ud83d\ude42 Yup, odd and even numbers will be sufficient in 2 masters envrionment.<\/p>\n Similar to M1 we will setup my.cnf for M2 server: <\/p>\n 2.2 Data transfer<\/span><\/p>\n As I mentioned, i have sakila database on M1 already. As we are not running any replication now, data are still just on M1. As whole this setup we made can’t run „in a past“, we will move data by mysqldump and every following action will be recorded by our replication environment. Sounds fair enough. All actions performed in M1 environment: MariaDB [sakila]> flush tables with read lock; [j@manjaro001 ~]$ mysqldump -u root -p sakila > ~\/sakila.sql Mysqldump command is well documented.<\/p>\n There are a lot of ways how this sql server is transfered to M2 machine such as scp, ftp, etc.<\/p>\n Following actions are done in M2 environment: real 0m10.030s Ok now we have same data on M1 and M2. In next part we will….<\/p>\n <\/p>\n 2.2 Setup mysql internal permissions Now we create user named „replication“ with „eagle“ password. Please beware while writing ip address – M1 is linked to M2 account and reversed.<\/p>\n M1 command: M2 command: 3) Run, Forrest, run!<\/strong><\/span><\/p>\n 3.1 M1(Master)->M2(Slave) setup<\/span><\/p>\n Ok, now we need to tell M2 to behave as slave for M1. So in M2 environment run: As you can see from the first line, slave is waiting for master actions = now we can check master status on M2 to check log position (for finishing crossing and having 2 masters): 3.2 M2(Master)->M1(Slave) setup<\/span><\/p>\n Ok, now we need to tell M1 to behave as slave for M2. So in M1 environment run: MariaDB [mysql]> show slave status\\G MariaDB [mysql]> Replication setup is done. Now we can verify our environment.<\/p>\n <\/p>\n 4) Verification<\/strong><\/span><\/p>\n We can reuse sakila code on M2: Welcome to the MariaDB monitor. Commands end with ; or \\g. Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.<\/p>\n Type \u2018help;\u2019 or \u2018\\h\u2019 for help. Type \u2018\\c\u2019 to clear the current input statement.<\/p>\n MariaDB [(none)]> MariaDB [sakila2]> You can verify that it\u00b4s working also other way = you can drop database sakila2 from M1: Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.<\/p>\n Type \u2018help;\u2019 or \u2018\\h\u2019 for help. Type \u2018\\c\u2019 to clear the current input statement.<\/p>\n MariaDB [(none)]> -a-<\/p>\n","protected":false},"excerpt":{"rendered":" Sometimes it’s very useful to skip well-known master-slave replication scenario and focus on master-master possibility \ud83d\ude42<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[13],"tags":[],"_links":{"self":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/722"}],"collection":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=722"}],"version-history":[{"count":0,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=\/wp\/v2\/posts\/722\/revisions"}],"wp:attachment":[{"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=722"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=722"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/it.tuxie.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=722"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n[crayon lang=“shell“]
\nserver_id = 1
\nlog_bin = \/var\/log\/mysql\/mysql-bin.log
\nlog_bin_index = \/var\/log\/mysql\/mysql-bin.log.index
\nrelay_log = \/var\/log\/mysql\/mysql-relay-bin
\nrelay_log_index = \/var\/log\/mysql\/mysql-relay-bin.index
\nexpire_logs_days = 10
\nmax_binlog_size = 100M
\nlog_slave_updates = 1
\nauto-increment-increment = 2
\nauto-increment-offset = 1
\n[\/crayon]<\/p>\n
\n[crayon lang=“shell“]
\nserver_id = 2
\nlog_bin = \/var\/log\/mysql\/mysql-bin.log
\nlog_bin_index = \/var\/log\/mysql\/mysql-bin.log.index
\nrelay_log = \/var\/log\/mysql\/mysql-relay-bin
\nrelay_log_index = \/var\/log\/mysql\/mysql-relay-bin.index
\nexpire_logs_days = 10
\nmax_binlog_size = 100M
\nlog_slave_updates = 1
\nauto-increment-increment = 2
\nauto-increment-offset = 2
\n[\/crayon]
\nServer_id is set to 2, increment values starting with 2, increment 2 = 2,4,6,8,….<\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [sakila]> show master status;
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n| mysql-bin.000004 | 326 | | |
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n1 row in set (0.00 sec)<\/p>\n
\nQuery OK, 0 rows affected (0.00 sec)<\/p>\n
\nEnter password:
\n[j@manjaro001 ~]$ ll *sql
\n-rw-r\u2013r\u2013 1 j users 3350638 15.11.2014 10:12 sakila.sql
\n[j@manjaro001 ~]$
\n[\/crayon]
\nSmall explanation – first command is saying that 4th binary log file, 326th position is having the last transaction in sakila database. Followed by next locking command we are ensured that data will be consistent and continuing (dump + binary log). This number + file name will be used at the end of setting.<\/p>\n
\n[crayon lang=“shell“]
\n[j@manjaro002 ~]$ mysqladmin -u root -p create sakila
\nEnter password:
\n[j@manjaro002 ~]$ time pv sakila.sql | mysql -u root -p sakila
\nEnter password:
\n3.2MiB 0:00:09 [ 349KiB\/s] [====================================================>] 100%<\/p>\n
\nuser 0m0.053s
\nsys 0m0.020s
\n[j@manjaro002 ~]$
\n[\/crayon]<\/p>\n
\n<\/span><\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> GRANT REPLICATION SLAVE ON *.* TO ‚replication’@’192.168.56.102‘ IDENTIFIED BY ‚eagle‘;
\nQuery OK, 0 rows affected (0.00 sec)
\n[\/crayon]<\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> GRANT REPLICATION SLAVE ON *.* TO ‚replication’@’192.168.56.101‘ IDENTIFIED BY ‚eagle‘;
\nQuery OK, 0 rows affected (0.00 sec)
\n[\/crayon]
\n <\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> CHANGE MASTER TO master_host=’192.168.56.101′, master_port=3306, master_user=’replication‘, master_password=’eagle‘, master_log_file=’mysql-bin.000004′, master_log_pos=326;
\nQuery OK, 0 rows affected (0.26 sec)
\n[\/crayon]
\nOk, setup is done, we can start slave:
\n[crayon lang=“shell“]
\nMariaDB [mysql]> start slave;
\nQuery OK, 0 rows affected (0.11 sec)
\n[\/crayon]
\nAnd check its status:
\n[crayon lang=“shell“]
\nMariaDB [mysql]> show slave status\\G
\n*************************** 1. row ***************************
\n Slave_IO_State: Waiting for master to send event
\n Master_Host: 192.168.56.101
\n Master_User: replication
\n Master_Port: 3306
\n Connect_Retry: 60
\n Master_Log_File: mysql-bin.000004
\n Read_Master_Log_Pos: 535
\n Relay_Log_File: mysql-relay-bin.000002
\n Relay_Log_Pos: 744
\n Relay_Master_Log_File: mysql-bin.000004
\n Slave_IO_Running: Yes
\n Slave_SQL_Running: Yes
\n Replicate_Do_DB:
\n Replicate_Ignore_DB:
\n Replicate_Do_Table:
\n Replicate_Ignore_Table:
\n Replicate_Wild_Do_Table:
\n Replicate_Wild_Ignore_Table:
\n Last_Errno: 0
\n Last_Error:
\n Skip_Counter: 0
\n Exec_Master_Log_Pos: 535
\n Relay_Log_Space: 1041
\n Until_Condition: None
\n Until_Log_File:
\n Until_Log_Pos: 0
\n Master_SSL_Allowed: No
\n Master_SSL_CA_File:
\n Master_SSL_CA_Path:
\n Master_SSL_Cert:
\n Master_SSL_Cipher:
\n Master_SSL_Key:
\n Seconds_Behind_Master: 0
\nMaster_SSL_Verify_Server_Cert: No
\n Last_IO_Errno: 0
\n Last_IO_Error:
\n Last_SQL_Errno: 0
\n Last_SQL_Error:
\n Replicate_Ignore_Server_Ids:
\n Master_Server_Id: 1
\n Master_SSL_Crl:
\n Master_SSL_Crlpath:
\n Using_Gtid: No
\n Gtid_IO_Pos:
\n1 row in set (0.00 sec)
\n[\/crayon]<\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> show master status;
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n| mysql-bin.000005 | 3349558 | | |
\n+\u2014\u2014\u2014\u2014\u2014\u2014+\u2014\u2014\u2014-+\u2014\u2014\u2014\u2014\u2013+\u2014\u2014\u2014\u2014\u2014\u2014+
\n1 row in set (0.00 sec)
\n[\/crayon]
\n <\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> CHANGE MASTER TO master_host=’192.168.56.102′, master_port=3306, master_user=’replication‘, master_password=’eagle‘, master_log_file=’mysql-bin.000005′, master_log_pos=3349558;
\nQuery OK, 0 rows affected (0.26 sec)
\n[\/crayon]
\nOk, setup is done, we can start slave:
\n[crayon lang=“shell“]
\nMariaDB [mysql]> start slave;
\nQuery OK, 0 rows affected (0.08 sec)<\/p>\n
\n*************************** 1. row ***************************
\n Slave_IO_State: Waiting for master to send event
\n Master_Host: 192.168.56.102
\n Master_User: replication
\n Master_Port: 3306
\n Connect_Retry: 60
\n Master_Log_File: mysql-bin.000005
\n Read_Master_Log_Pos: 3349558
\n Relay_Log_File: mysql-relay-bin.000002
\n Relay_Log_Pos: 535
\n Relay_Master_Log_File: mysql-bin.000005
\n Slave_IO_Running: Yes
\n Slave_SQL_Running: Yes
\n Replicate_Do_DB:
\n Replicate_Ignore_DB:
\n Replicate_Do_Table:
\n Replicate_Ignore_Table:
\n Replicate_Wild_Do_Table:
\n Replicate_Wild_Ignore_Table:
\n Last_Errno: 0
\n Last_Error:
\n Skip_Counter: 0
\n Exec_Master_Log_Pos: 3349558
\n Relay_Log_Space: 832
\n Until_Condition: None
\n Until_Log_File:
\n Until_Log_Pos: 0
\n Master_SSL_Allowed: No
\n Master_SSL_CA_File:
\n Master_SSL_CA_Path:
\n Master_SSL_Cert:
\n Master_SSL_Cipher:
\n Master_SSL_Key:
\n Seconds_Behind_Master: 0
\nMaster_SSL_Verify_Server_Cert: No
\n Last_IO_Errno: 0
\n Last_IO_Error:
\n Last_SQL_Errno: 0
\n Last_SQL_Error:
\n Replicate_Ignore_Server_Ids:
\n Master_Server_Id: 2
\n Master_SSL_Crl:
\n Master_SSL_Crlpath:
\n Using_Gtid: No
\n Gtid_IO_Pos:
\n1 row in set (0.00 sec)<\/p>\n
\n[\/crayon]<\/p>\n
\n[crayon lang=“shell“]
\n[j@manjaro002 ~]$ mysqladmin -u root -p create sakila2
\nEnter password:
\n[j@manjaro002 ~]$ pv sakila.sql | mysql -u root -p sakila2
\nEnter password:
\n 3.2MiB 0:00:13 [ 240KiB\/s] [====================>] 100%
\n[j@manjaro002 ~]$
\n[\/crayon]
\nAnd now we can query within M1 machine:
\n[crayon lang=“shell“]
\n[j@manjaro001 ~]$ mysql -u root -p
\nEnter password:<\/p>\n
\nYour MariaDB connection id is 13
\nServer version: 10.0.15-MariaDB-log MariaDB Server<\/p>\n
\nMariaDB [(none)]>
\nMariaDB [(none)]>
\nMariaDB [(none)]> use sakila2
\nDatabase changed
\nMariaDB [sakila2]> show tables
\n -> ;
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| Tables_in_sakila2 |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n| actor |
\n| actor_info |
\n| address |
\n| category |
\n| city |
\n| country |
\n| customer |
\n| customer_list |
\n| film |
\n| film_actor |
\n| film_category |
\n| film_list |
\n| film_text |
\n| inventory |
\n| language |
\n| nicer_but_slower_film_list |
\n| payment |
\n| rental |
\n| sales_by_film_category |
\n| sales_by_store |
\n| staff |
\n| staff_list |
\n| store |
\n+\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-+
\n23 rows in set (0.00 sec)<\/p>\n
\n[\/crayon]<\/p>\n
\n[crayon lang=“shell“]
\nMariaDB [mysql]> drop database sakila2;
\nQuery OK, 23 rows affected (1.40 sec)
\n[\/crayon]
\nAnd now on M2 you can verify that connection is working:
\n[crayon lang=“shell“]
\n[j@manjaro002 ~]$ mysql -u root -p
\nEnter password:
\nWelcome to the MariaDB monitor. Commands end with ; or \\g.
\nYour MariaDB connection id is 11
\nServer version: 10.0.15-MariaDB-log MariaDB Server<\/p>\n
\nMariaDB [(none)]>
\nMariaDB [(none)]> use sakila2
\nERROR 1049 (42000): Unknown database \u2018sakila2\u2032
\nMariaDB [(none)]>
\n[\/crayon]
\nAnd that\u00b4s all. I can recommend MySQL High Availability book<\/a>, full of descripiton, recipes and very valuable information.<\/p>\n