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.