Setup MySQL Master-Slave Replication on Debian/Ubuntu

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 set up 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 the MySQL server.

/etc/init.d/mysql restart

Create a slave user in MySQL by running the 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 the following command:

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

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

UNLOCK TABLES;

Transfer the database.sql created in the previous step for the 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 the 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 the MySQL server.

/etc/init.d/mysql restart

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

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

Now, let's start the replication. Open the MySQL console, and run the 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 values for MASTER_LOG_FILE and MASTER_LOG_POS are File and Position that we wrote down from the master node setup.