Our Use-Case of Master-Slave Replication is For WordPress. Master-Slave Replication For WordPress Makes it Fail-Proof to WordPress Database Errors Making the Website Fully Unreadable (which we have discussed in our article on Difference Between Standalone Database and Distributed Database). The main reason for so much discussion is for one big reason – the master MySQL database will read and write like a normal MySQL database while the slave MySQL database only is readable to WordPress. It does mean – easy switch to one-server setup. So, this kind of setup needs work on WordPress settings file too. One can use the same methodology to setup MySQL replication for other purposes other than for using WordPress. A WordPress setup with MySQL master-slave replication has three parts :
- WordPress configuration part
- Server configuration part
- MySQL configuration part
Among the server configuration, there are optional matters like using Percona XtraBackup to initially copy the backup. As the initial step, you should have at least two servers running MySQL with identical optimization except the configaration part we are going to discuss. One of the servers probably your live WordPress website which will act as master. Please note that we are using InnoDB engine. Make sure that you have allowed TCP port 3306 on Iptables.
We will suggest using two fresh cloud server instances to learn than directly using a live website’s MySQL database as a master. You can keep the instances running for 1-2 days to get the grasp. We have guide to install Percona MySQL on Ubuntu. You must resolve debian-sys-maint user matters related to Debian, Ubuntu and MySQL.
---
Required MySQL Configuration
A default my.cnf is provided with any flavour of MySQL but we will use the master.cnf and slave.cnf configuration files when starting up the MySQL servers at least for testing. We are mentioning only the minimum required parameters for [mysqld] sections.
1 2 3 4 5 6 7 8 | [mysqld] server-id=1 log-bin=master-slave.log datadir=/path/to/mysql/master/data innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-ignore-db = "mysql" bind-address = 0.0.0.0 |
and this is of slave :
1 2 3 4 5 6 7 8 9 10 | [mysqld] server-id=2 master-host = [private-IP-of-master] master-user = [replication-username] master-password = [replication-password] master-connect-retry = 60 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin datadir=/path/to/mysql/slave/data bind-address = 0.0.0.0 |
Edit the /path/to/mysql/.. in both.
innodb_flush_log_at_trx_commit=1 and sync_binlog=1 options are used for the greater durability and consistency in a replication setup using InnoDB with transactions. Now, we can start the MySQL servers :
1 2 3 4 5 6 7 8 | # mysqld --defaults-file=/path/to/mysql/master/master.cnf & # slave server mysqld --defaults-file=/path/to/mysql/slave/slave.cnf & # |
We need to create replication user on the master server that the slave server can use to connect :
1 2 3 | mysql -u root --prompt='master> ' master> CREATE USER repl_user@slave-ip-address; master> GRANT REPLICATION SLAVE ON *.* TO repl_user@slave-ip-address IDENTIFIED BY 'your-password'; |
OR, depending on the flavour you may do in this way :
1 2 3 4 | # mysql -u root -p mysql> grant replication slave on *.* TO [replication_username]@'[IP of slave]' identified by '[some password]'; mysql> flush privileges; mysql> quit |
We can now start replication on the slave :
1 2 3 4 5 | slave> CHANGE MASTER TO MASTER_HOST='master-ip-address', -> MASTER_USER='repl_user', -> MASTER_PASSWORD='your-password', -> MASTER_LOG_FILE='', -> MASTER_LOG_POS=4; |
You can start replication on the slave by issuing this command :
1 | mysql> start slave; |
Restart both the MySQL servers. You can test the system by creating database on the master :
1 2 3 4 5 6 7 | # mysql -u root -p mysql> create database testing; mysql> use testing mysql> create table users(id int not null auto_increment, primary key(id), username varchar(30) not null); mysql> insert into users (username) values ('foo'); mysql> insert into users (username) values ('bar'); mysql> exit |
In case, the master is a MySQL database of a live website, use the following command to ensure that nothing can write to the master database during a database dump:
1 2 3 | # mysql -u root -p mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; |
Also, you need to note the filename and position of the binary log to complete the replication configuration on the slave server. After creating a database dump, lift the read lock from the master:
1 2 | # mysql -u root -p mysql> UNLOCK TABLES; |
Complete the slave replication steps:
1 2 3 4 5 6 7 8 9 | # mysql -u root -p mysql> SLAVE STOP; mysql> CHANGE MASTER TO MASTER_HOST='[IP-of-master]', MASTER_USER='[replication-username]', MASTER_PASSWORD='[replication-password]', MASTER_LOG_FILE='[file-listed-on-master]', MASTER_LOG_POS=[log-position-listed-on-master]; mysql> START SLAVE; mysql> SHOW SLAVE STATUS\G |
Conclusion
After the test setup works, move the perfect settings to production. Setup of MySQL master-slave replication is just a matter of getting used. Also, you may read different guides on the topic for extra tips, like that from Percona :
1 | https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html |
