Sometimes you find yourself needing some form of high availability or fail over for your application that uses a MySQL/MariaDB backend. If you’re like me you’ll probably like the idea of doing this the Master – Master way which, by the way, is not the recommended way BUT works quite well.
Here’s what needs doing:
Step1.
Install MySQL / MariaDB on 2 different servers / VMs and make sure it is running and configured to start on boot (OS is irrelevant here also the version of MySQL / MariaDB you want to use)
Step2.
Edit my.cnf file (usual path would be /etc/my.cnf) and: make sure each server has a different server-id value set (ex. server-id=10 on server1 and server-id=20 on server2) and also make sure you have binary logging enabled (you should have an entry for log-bin in my.cnf, if you don’t then add it there)
Step3.
Decide what you want to replicate. In my case I prefer to replicate everything but if you want to replicate specific databases or tables you might want to look at adding binlog-do-db to my.cnf on both servers with the databases you want to replicate.
Step4.
Restart MySQL/MariaDB on both nodes to apply the settings you made above
Step5.
Connect as root using the mysql client and create the user that both servers will use for replication like so: (run this on both servers)
GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;
*.* means all databases and all tables, if you want to be more specific you can replace with for example specificdatabase.* which means allow access to specificdatabase and all tables
replace slave_user and password with the values you want to use
Step6.
You’ll need to get the name of the binary log and the current position from each server and then configure replication. If you’re doing this on “fresh” servers you’ll get the same values for both.
SHOW MASTER STATUS;
which will look similar with:
+--------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +--------------------+----------+--------------+------------------+ | mariadb-bin.000016 | 475 | | | +--------------------+----------+--------------+------------------+
Different MySQL / MariaDB versions will start at different posititons. Also File (the name of the binary log) will depend on the “log-bin” value set in my.cnf
If doing this for existing server, before looking at the current position, you have to dump the databases you want to replicate (on the existing server), then run the command above to get the values, then import them on the second server AND on the second server you need to run RESET MASTER; to clear the changes to the binary log caused by the import itself and then run shor master status on it to get correct position.
Step7.
To start replication between the nodes you will have to run the following query on both nodes BUT with correct values specific for other node. Before you start replication on both nodes, make sure the MySQL/MariaDB servers are configured to listen on the correct network interfaces and the firewall allows remote connections to that port. (some ppl add skip-networking to my.cnf to have mysql running only on localhost – this will interfere with your ability to do replication)
running on server1
CHANGE MASTER TO MASTER_HOST=’ip_of_server2′, MASTER_USER=’slave_user_created_on_server2′, MASTER_PASSWORD=’password_for_slave_user’, MASTER_LOG_FILE=’filename_from_show_master_status_on_server2′, MASTER_LOG_POS=Position_from_show_master_status_on_server2;
START SLAVE;
running on server2
CHANGE MASTER TO MASTER_HOST=’ip_of_server1′, MASTER_USER=’slave_user_created_on_server1′, MASTER_PASSWORD=’password_for_slave_user’, MASTER_LOG_FILE=’filename_from_show_master_status_on_server1′, MASTER_LOG_POS=Position_from_show_master_status_on_server1;
START SLAVE;
If everything went well, you’re done. This was it. You have configured Master – Master replication running between the servers. Now you can connect to either of them and run queries.
If it did not go well, SHOW SLAVE STATUS; should report what’s wrong and you should be able to troubleshoot.
Notes
Do be careful when using replication because it has a tendency to break under certain conditions and if you’re not monitoring its state you might find yourself in some pretty nasty situations.