We’ll configure one MySQL instance as the source database and another as its replica. Replication allows data synchronization between these separate databases.
- Obviously install MySQL on 2 servers.
- Enable Binary Logging
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
log-bin = /var/log/mysql/mysql-bin.log
- Restart services with
susdo systemctl restart mysql
- Create a unique server id for the source.
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following line in that or equivilent file:
server-id = 1
- Edit replica config file
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following lines in that or equivilent file:
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
log-bin = /var/log/mysql/mysql-bin.log
- Restart with
sudo systemctl restart mysql
- On the source create a user run:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
- Lock the master database to take a snapshot:
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
- Dump the master database:
mysqldump -u root -p --all-databases --master-data > master_dump.sql
- Unlock the master db with
UNLOCK TABLES;
- Copy the dump file to the replica:
scp master_dump.sql user@repllica_server:/path/to/dump/
- On the replica import the data:
mysql -u root -p < master_dump.sql
- Configure replication on the replica:
CHANGE MASTER TO
MASTER_HOST = 'source_server_ip',
MASTER_USER = 'replication_user',
MASTER_PASSWORD = 'your_password',
MASTER_LOG_FILE = 'master_bin_log_file',
MASTER_LOG_POS = master_bin_log_position;
START SLAVE;
- Check the replica status with
SHOW SLAVE STATUS\G
- Ensure that
Slave_IO_Running
andSlave_SQL_Running
are bothYes
.
That's it!
NOTE: Make sure to update the firewall if one is running.
Replication with docker containers
docker-compose.yml
${TZ}
,${MYSQL_USER}
,${MYSQL_PASSWORD}
, and${MYSQL_ROOT_PASSWORD}
with your desired values..env.master
and.env.slave
files with the necessary environment variablesdocker-compose up -d
to start the master and slave containers.docker-compose exec mysql-master bash
.