Setup MySQL Master-Slave Replication on Debian/Ubuntu

Introduction

When you are running a critical website, it is important to make sure that you have at least one redundant backup server. This ensures that your database is syncing in real-time. MySQL refers to database syncing as replication. This short tutorial provides instructions on how to setup a master-slave MySQL replication.

Setup the master node

Edit /etc/mysql/my.cnf to disable IP binding.

Comment out the following lines:

bind-address = 127.0.0.1
skip-networking

Create new settings for replication by running the following commands:

cat >/etc/mysql/conf.d/replication.cnf <<EOF
[mysqld]
server-id = 100
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = YOUR_DATABASE_ONE
binlog-do-db = YOUR_DATABASE_TWO
EOF

Restart MySQL server.

/etc/init.d/mysql restart

Create a slave user in MySQL by running following commands in the MySQL console.

CREATE USER 'slave'@'SLAVE_SERVER_IP_ADDRESS' identified by 'YOUR_SLAVE_PASSWORD';
GRANT ALL ON *.* TO 'slave'@'SLAVE_SERVER_IP_ADDRESS';
FLUSH PRIVILEGES;

Now, lock write access to your database:

FLUSH TABLES WITH READ LOCK;

Get master node status:

SHOW MASTER STATUS;

Note: Write down the values of the “File” and “Position” fields as we will need to reference them later for the slave node.

Open another SSH session and dump out your database using following command:

mysqldump -u MYSQL_USERNAME -pMYSQL_PASSWORD --databases YOUR_DATABASE_ONE YOUR_DATABASE_TWO > database.sql

Return to previous SSH session and issue the following command in MySQL console to unlock write access:

UNLOCK TABLES;

Transfer the database.sql created in previous step to slave node.

Setup the slave node

Edit /etc/mysql/my.cnf to disable IP binding.

Comment out the following lines:

bind-address = 127.0.0.1
skip-networking

Create new settings for replication by running following command:

cat >/etc/mysql/conf.d/replication.cnf <<EOF
[mysqld]
server-id = 101
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = YOUR_DATABASE_ONE
binlog-do-db = YOUR_DATABASE_TWO
EOF

Restart MySQL server.

/etc/init.d/mysql restart

Import database.sql created from master node just now by using this command:

mysql -u MYSQL_USERNAME-pMYSQL-PASSWORD < database.sql

Now, lets start the replication. Open MySQL console, run following commands:

SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='MASTER_SERVER_IP_ADDRESS', MASTER_USER='slave', MASTER_PASSWORD='YOUR_SLAVE_PASSWORD', MASTER_LOG_FILE='FILE_VALUE_FROM_MASTER', MASTER_LOG_POS=POSITION_VALUE_FROM_MASTER;
SLAVE START;

Note: The value for MASTER_LOG_FILE and MASTER_LOG_POS is “File” and “Position” that we wrote down from the master node setup.