MySQL Configuration to Set Up Master-Slave Replication

Abhishek Ghosh

By Abhishek Ghosh October 6, 2019 10:14 pm Updated on October 6, 2019

MySQL Configuration to Set Up Master-Slave Replication

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.

MySQL Configuration to Set Up Master-Slave Replication

---

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.

[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

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 :

[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

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 :

#

mysqld --defaults-file=/path/to/mysql/master/master.cnf &

# slave server

mysqld --defaults-file=/path/to/mysql/slave/slave.cnf &
#

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 :

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';

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 :

# mysql -u root -p
mysql> grant replication slave on *.* TO [replication_username]@'[IP of slave]' identified by '[some password]';
mysql> flush privileges;
mysql> quit

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 :

slave> CHANGE MASTER TO MASTER_HOST='master-ip-address',
 -> MASTER_USER='repl_user',
 -> MASTER_PASSWORD='your-password',
 -> MASTER_LOG_FILE='',
 -> MASTER_LOG_POS=4;

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 :

mysql> start slave;

1	mysql> start slave;

Restart both the MySQL servers. You can test the system by creating database on the master :

# 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

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:

# mysql -u root -p
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

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:

# mysql -u root -p
mysql> UNLOCK TABLES;

1 2	# mysql -u root -p mysql> UNLOCK TABLES;

Complete the slave replication steps:

# 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

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 :

https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

1	https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/setting_up_replication.html

About Abhishek Ghosh

Here’s what we’ve got for you which might like :

Take The Conversation Further ...

Required MySQL Configuration

Conclusion

This Article Has Been Shared 4376 Times!

About Abhishek Ghosh

Here’s what we’ve got for you which might like :

Articles Related to MySQL Configuration to Set Up Master-Slave Replication

Take The Conversation Further ...