Sometimes it’s very useful to skip well-known master-slave replication scenario and focus on master-master possibility 🙂
1) Prerequisities
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).
VirtualBox is set to network with host only =>
M1 = 192.168.56.101, source database (sakila in this case), primary, The One 🙂
M2 = 192.168.56.102, new master with no data, secondary master.
2) Setup
2.1 Configuration settings
Main MySQL/MariaDB configuration file is /etc/mysql/my.cnf (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:
|
1 2 3 4 5 6 7 8 9 10 |
server_id = 1 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 1 |
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,….. As you can imagine, collision with another master will be critical 🙂 Yup, odd and even numbers will be sufficient in 2 masters envrionment.
Similar to M1 we will setup my.cnf for M2 server:
|
1 2 3 4 5 6 7 8 9 10 |
server_id = 2 log_bin = /var/log/mysql/mysql-bin.log log_bin_index = /var/log/mysql/mysql-bin.log.index relay_log = /var/log/mysql/mysql-relay-bin relay_log_index = /var/log/mysql/mysql-relay-bin.index expire_logs_days = 10 max_binlog_size = 100M log_slave_updates = 1 auto-increment-increment = 2 auto-increment-offset = 2 |
Server_id is set to 2, increment values starting with 2, increment 2 = 2,4,6,8,….
2.2 Data transfer
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
MariaDB [sakila]> show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000004 | 326 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) MariaDB [sakila]> flush tables with read lock; Query OK, 0 rows affected (0.00 sec) [j@manjaro001 ~]$ mysqldump -u root -p sakila > ~/sakila.sql Enter password: [j@manjaro001 ~]$ ll *sql -rw-r–r– 1 j users 3350638 15.11.2014 10:12 sakila.sql [j@manjaro001 ~]$ |
Small 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.
Mysqldump command is well documented.
There are a lot of ways how this sql server is transfered to M2 machine such as scp, ftp, etc.
Following actions are done in M2 environment:
|
1 2 3 4 5 6 7 8 9 10 |
[j@manjaro002 ~]$ mysqladmin -u root -p create sakila Enter password: [j@manjaro002 ~]$ time pv sakila.sql | mysql -u root -p sakila Enter password: 3.2MiB 0:00:09 [ 349KiB/s] [====================================================>] 100% real 0m10.030s user 0m0.053s sys 0m0.020s [j@manjaro002 ~]$ |
Ok now we have same data on M1 and M2. In next part we will….
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.
M1 command:
|
1 2 |
MariaDB [mysql]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.56.102' IDENTIFIED BY 'eagle'; Query OK, 0 rows affected (0.00 sec) |
M2 command:
|
1 2 |
MariaDB [mysql]> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.56.101' IDENTIFIED BY 'eagle'; Query OK, 0 rows affected (0.00 sec) |
3) Run, Forrest, run!
3.1 M1(Master)->M2(Slave) setup
Ok, now we need to tell M2 to behave as slave for M1. So in M2 environment run:
|
1 2 |
MariaDB [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; Query OK, 0 rows affected (0.26 sec) |
Ok, setup is done, we can start slave:
|
1 2 |
MariaDB [mysql]> start slave; Query OK, 0 rows affected (0.11 sec) |
And check its status:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
MariaDB [mysql]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.101 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 535 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 744 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: 535 Relay_Log_Space: 1041 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: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec) |
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):
|
1 2 3 4 5 6 7 |
MariaDB [mysql]> show master status; +——————+———-+————–+——————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————+———-+————–+——————+ | mysql-bin.000005 | 3349558 | | | +——————+———-+————–+——————+ 1 row in set (0.00 sec) |
3.2 M2(Master)->M1(Slave) setup
Ok, now we need to tell M1 to behave as slave for M2. So in M1 environment run:
|
1 2 |
MariaDB [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; Query OK, 0 rows affected (0.26 sec) |
Ok, setup is done, we can start slave:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
MariaDB [mysql]> start slave; Query OK, 0 rows affected (0.08 sec) MariaDB [mysql]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.102 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 3349558 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 535 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: 3349558 Relay_Log_Space: 832 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: 2 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: 1 row in set (0.00 sec) MariaDB [mysql]> |
Replication setup is done. Now we can verify our environment.
4) Verification
We can reuse sakila code on M2:
|
1 2 3 4 5 6 |
[j@manjaro002 ~]$ mysqladmin -u root -p create sakila2 Enter password: [j@manjaro002 ~]$ pv sakila.sql | mysql -u root -p sakila2 Enter password: 3.2MiB 0:00:13 [ 240KiB/s] [====================>] 100% [j@manjaro002 ~]$ |
And now we can query within M1 machine:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
[j@manjaro001 ~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 13 Server version: 10.0.15-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)]> MariaDB [(none)]> MariaDB [(none)]> MariaDB [(none)]> use sakila2 Database changed MariaDB [sakila2]> show tables -> ; +—————————-+ | Tables_in_sakila2 | +—————————-+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +—————————-+ 23 rows in set (0.00 sec) MariaDB [sakila2]> |
You can verify that it´s working also other way = you can drop database sakila2 from M1:
|
1 2 |
MariaDB [mysql]> drop database sakila2; Query OK, 23 rows affected (1.40 sec) |
And now on M2 you can verify that connection is working:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[j@manjaro002 ~]$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 11 Server version: 10.0.15-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)]> MariaDB [(none)]> MariaDB [(none)]> use sakila2 ERROR 1049 (42000): Unknown database ‘sakila2′ MariaDB [(none)]> |
And that´s all. I can recommend MySQL High Availability book, full of descripiton, recipes and very valuable information.
-a-