Setup MySQL / MariaDB master slave replication

How to setup a single master with one or more readonly slaves

First configure the master by editing the mysql configuration file, this could be /etc/my.cnf or on Centos 7 it is /etc/my.cnf.d/server.cnf

Find the [server] section and add the following lines:

The binlog-ignore-db line tells the master not to replicate the ‘mysql’ database, you can add additional entries to not replicate other databases if you wish, e.g.

Adding the following additional entries allows you to tweak the performance of your server:

Following any changes to the config files you will need to restart MySQL (or MariaDB if you use that instead), so you would do one of the following:

Now login to your local MySQL server, e.g.

Once logged into the MySQL CLI add a user that the slaves will use to replicate:

Replace 1.2.3.4 with the IP of your slave server. You can run the above command multiple times, once for each slave server. Remember to change ‘password’ to an actual password, a good choice would be 12 (or more) characters consisting of a random mix of upper/lowercase letters, numbers and punctuation characters.

Now we’re ready to take a backup of the master, for this you will need to open a second session on your master server. In the first session, still logged into the MySQL CLI issue the following commands:

You should see something along these lines:

Leave this session in place whilst you move to the second session to backup the databases you want to replicate by using your preferred method, e.g.

Now you can go back to the first window and release the lock:

Now copy this backup to your slave server(s). I use scp for this, e.g.

Now login to the slave server and configure MySQL (or mariadb) to be a slave by editing it’s configuration file, e.g. /etc/my.cnf.d/server.cnf and add the following to the [server] section:

As per the master server, following any changes to the config files you will need to restart MySQL (or MariaDB if you use that instead), so you would do one of the following:

The next step is to login to the MySQL CLI and tell it where to find the master along with the login details and the starting position in the log:

The last thing we need to do is start the slave process, and check it is running. Do this by issuing the following commands on the slave MySQL CLI:

You can repeat the slave part on each server you want to setup replication, just remember to give each slave a different server_id