In this topic I will discuss the steps on how to set up MySQL replication. I will show you the commands and configurations you will need on both Master and Slave databases.
Enable Binary logging in Master Database
The first step is to open the my.cnf or my.ini file and modify the [mysqld] block to have the following configuration. Change the
bind-address to become the master server IP Address.
[mysqld] innodb_flush_log_at_trx_commit=1 sync_binlog=1 log-bin=mysql-bin server-id=1 bind-address=0.0.0.0
Notice the assigned server-id value is 1, that is because we need to assign a unique server-id on each of your master and slave configurations. Then restart the MySQL daemon on your host server.
systemctl restart mysqld
For security reasons, it is recommended to create a separate user that has limited access only to replication tasks. Change
slave.example.com to become the slave server IP Address.
CREATE USER 'slave'@'slave.example.com' IDENTIFIED BY 'somerandompassword'; GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slave.example.com'; GRANT SELECT, LOCK TABLES ON website.* TO 'slave'@'slave.example.com'; FLUSH PRIVILEGES;
To start the slave replication process at the correct point, you need to take note of the master’s current coordinates within its binary log. If you are using InnoDB, this procedure uses FLUSH TABLES WITH READ LOCK, which blocks COMMIT operations for InnoDB tables.
FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
Open another terminal while keeping the READ LOCK in effect on the current terminal (quitting the open connection will unlock the tables). And then, on the other terminal create a snapshot of the database.
mysqldump --all-databases --master-data > dbdump.db
You can also create a dump of a specific database only.
mysqldump website --master-data > dbdump.db
Unlock the tables after you have successfully created a snapshot of your databases. Unlock this in the initial terminal opened.
Slave Host Server
Subsequently, login to your slave host server and enable the slave configuration. Edit the
my.ini file and add the following under the [mysqld] block. Furthermore, notice that the assigned server-id value this time is 2.
Restart the MySQL daemon on your slave host server.
systemctl restart mysqld
Load your database snapshots after the slave configuration has been set up.
mysql < dbdump.db
If you created a snapshot for a specific database, you first need to create an empty database on your slave.
CREATE DATABASE `website` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; exit;
mysql website < dbdump.db
Slave to Master Host Connection
To configure the slave connection to the master, execute the following statement on the slave, replacing the option value with the actual value relevant to your system. And also remember the output of “SHOW MASTER STATUS” we got from the master.
mysql CHANGE MASTER TO MASTER_HOST = '184.108.40.206', MASTER_PORT = 3306, MASTER_USER = 'slave', MASTER_PASSWORD = 'somerandompassword', MASTER_LOG_FILE='mysql-bin.001238', MASTER_LOG_POS=154;
If you are replicating only a specific database or table, you will need to apply the filter that matches the database or table names.
mysql CHANGE REPLICATION FILTER REPLICATE_DO_DB = (website); CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE=(‘website.%');
Start slave thread.
Check your slave thread if it is running properly.
SHOW SLAVE STATUS\G;
Pay attention to the value of
Slave_SQL_Running_State. You should see an indication that the replication is running without an error.
Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Last_IO_Error: Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
That’s it! We just covered the process on How to set up MySQL replication. You now have a working replication to backup your data. Check out also my other tutorial about Database Charset and Collation Fix in MySQL.