Setup MySQL / MariaDB master slave replication

mysql -u root -p < dump.sql

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:

bind-address = 12.34.56.78 # replace 12.34.56.78 with the IP of your server
log_bin
server_id = 1
log_basename = master1
datadir = /var/lib/mysql
binlog-ignore-db = mysql

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.

binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema

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

max_allowed_packet=64M
max_heap_table_size = 64M
tmp_table_size = 128M
join_buffer_size = 128M
innodb_buffer_pool_size = 256M
innodb_doublewrite = OFF
innodb_additional_mem_pool_size = 128M
innodb_flush_log_at_timeout = 4
innodb_read_io_threads = 48
innodb_write_io_threads = 32
max_connections = 128

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:

systemctl restart mysqld
systemctl restart mariadb

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

mysql -u root -p

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

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'1.2.3.4' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

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:

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

You should see something along these lines:

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| master1-bin.000001 |   456    |              | mysql            |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

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.

mysqldump -u root -p --all-databases > dump.sql

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

UNLOCK TABLES;
QUIT;

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

scp dump.sql username@12.34.56.78:.

Now login to the slave server and import the dumped databases:

mysql -u root -p < dump.sql

Now 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:

server-id = 2
datadir = /var/lib/mysql
relay-log = /var/lib/mysql/mysql-relay-bin.log
log_bin = /var/lib/mysql/mysql-bin.log

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:

systemctl restart mysqld
systemctl restart mariadb

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:

CHANGE MASTER TO MASTER_HOST='12.34.56.78',MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=456;

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:

START SLAVE;
SHOW SLAVE STATUS\G

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