-
Setting Up Replication on MariaDB
Setting up a master and slave MariaDB server for replication is super easy I highly recommend it. About 10 years ago (MySQL days), I had a master>slave setup for the company I worked for and the main hard drive failed in the master server. This server ran the website and some internal applications so it was fairly mission critical. I knew of the failure about 2 minutes after it took place and after a few minutes of diagnostics, realized it was a hard drive failure and that I wouldn’t be booting it anytime soon. Within 15 minutes of the failed drive, the Slave server was the savior! I changed the IP of it and it picked up without any issues at all. This was in the middle of the afternoon and I never even received a call from anyone asking if there was a problem.
Obviously technology has improved and drive arrays are more affordable today so a hard drive failure shouldn’t cause problems but if your are on a budget and can’t afford dual power supplies, what happens if you loose your power supply? Grab an old box and put a slave server on it! It may just save your ass one day!
How to Set Up MariaDB/MySql Replication
I’m going to assume you already have a MariaDB server setup and running. If you don’t and are installing a fresh install, you don’t really need to do anything different. The setup process as a master will come after it’s installed. Heck, you can even clone your existing machine if you want to make it easy since all the databases will already be setup.
Setting up the Slave Server
Lets go ahead and setup the slave server by installing MariaDB as you normally would. Before starting MariaDB, lets edit the server.cnf file located at /etc/my.cnf.d/server.cnf. You can use vi or nano to made these changes. You need to add a server_id number, I just use 2 as the slave and 1 as the master. Find [mysqld] and place the following line under it.
[mysqld]
server_id=2
Now save the file and start/restart MariaDB to insure it will start.
sudo /etc/init.d/mysql start
Setting Up the Master Server
You will need to add the following lines to your /etc/my.cnf.d/server.cnf file. You can use vi or nano to do this.
log-basename=master
log-bin
binlog-format=row
server_id=1
Save this file and restart MariaDB.
Now you need to create a user that has access. Log back into MariaDB using
mysql – u root -p
(Enter Password)You should be sitting at the MariaDB/MySQL prompt which looks like MariaDB [(none)]>
GRANT REPLICATION SLAVE ON *.* TO replication_username;
(Where replication_username is the username you wish to use for your replication user.)
Copy the Databases Over
The first thing I do is go to the master and run;
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Be sure to note the log file name and the position of the log file since you will need this later!
Now I stop the server so I can copy over the databases…
sudo /etc/init.d/mysql stop
Now you need to somehow get the databases copied over from your master to your slave server. I personally just sFTP into both machines with both MariaDB STOPPED and copy the databases over. This is the simplest way in my opinion but you could do a mysqldump of the databases and load them that way or use whatever method you prefer. It doesn’t matter how you do it as long as the data in the files is the same on both servers. I haven’t ever had much luck with mysqldump, especially if I’m doing a resync.
If you are copying over InnoDB databases, you will also need to copy over the files in the data root that start with ib_ and the iddata file. In my case there was ib_logfile0, iblogfile1 and ibdata1.
If you are using the sFTP method, you will also need to apply the correct permissions for the Mysql user to be able to access the files.
sudo chown -R _mysql /var/lib/mysql (or wherever your database files are stored)
Now you can restart start the master server:
sudo /etc/init.d/mysql start
and be sure to unlock the tables so your database can be written to again!!!
UNLOCK TABLES;
NOTE: I have been able to copy the databases on some servers without having to stop the server so you can also try that. Just be sure to keep them locked until you get them off your master and then unlock them.
ON THE SLAVE
The slave should have been shut down when you copied over the databases. Go ahead and start it with:
sudo /etc/init.d/mysql start
Now stop the slave (replication service) within mysql (mysql -u root -p first to get into Mysql/MariaDB).
stop slave;
On the slave server, you will enter the following (where the host is the ip or name of your master server and the blue areas are from your settings):
CHANGE MASTER TO MASTER_HOST='192.1.1.1', MASTER_USER='replication_username', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='aps-mariadb-bin.000002', MASTER_LOG_POS=1078, MASTER_CONNECT_RETRY=10;
(note, my log file name is different than master but if you followed the directions above and used log-basename=master, yours would probably be something like MASTER_LOG_FILE='master-bin.000002 instead)
(If you copy and paste this, be sure to retype the apostrophes or it will not work for some reason! I just copy and paste into notepad and retype it all…)
Now you are ready to start the slave service again…
start slave;
Now you can check the status if the slave server by using the following:
show slave status;
Go to your master server and enter:
show master status;
If it’s working correctly, you shouldn’t see any errors on the slave server and it should show Waiting for master to send event. You should also be able to see the log file position update on both servers as it becomes updated. You should also be able to make a change on your master server and see it replicated onto your slave server almost instantly. For example, create a test database and it should appear on the slave server.
ERRORS
I recently ran into an issue where I was getting the following error:
Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’
The issue was I had the MASTER_LOG_FILE named incorrectly. If you receive this error, check to be sure you are pulling the right file name, the one that was shown on the master when you did SHOW MASTER STATUS;. If it’s not correct, change it in the CHANGE MASTER TO statement and rerun that.
Monitoring your Replication Easily
There are many ways to monitor your replication status…. web pages, cron jobs and more. Since I build web applications, I generally have a dashboard page that connects to both master and slave and read a table that I know has new records all the time (product views, completed orders, etc). I compare the record ID in descending order on these and if they are off by more than 2, I have an alert message that pops up.
