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 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 to 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.