The way to achieve table-level replication in MySQL is to use the combination of two replication parameters named replicate-do-db and replicate-ignore-table along with other mandatory replication parameters. You need to identify the name of the database of your tables you need to replicate and from the database opt out the tables that you don’t want to replicate using the two parameters.
- Take a backup of your master server by the mysqldump utility or the MySQL Workbench which is comfortable to you.
- On the master server stop MySQL service, from the windows service choose MySQL80, right-click, and select stop.
- Locate the MySQL configuration file “my.ini” on the windows file system, under “C:\ProgramData\MySQL\MySQL Server 8.0”
- Edit the my.ini file under the [mysqld] section, add tables that should not be replicated to the replication server to the parameter replicate-ignore-table with the format db_name.table_name with comma-separated values. Here we are replicating all the tables for the fortune sample database except the mentioned tables in the ignoring list.
server-id=1 log-bin=mysql-bin #if your server logging is already enabled keep the setting binlog-format=row log-replica-updates gtid-mode=on enforce-gtid-consistency replicate-do-db= fortune replicate-ignore-table=fortune.dbchangelog, fortune.dbchangeloglock, fortune.authority, fortune.charity
- Start the MySQL service from the windows service choose the MySQL80, right-click and select start.
- Create the replication user from MySQL command line client on the master server, allowing only the replication server IP to maintain security.
create user ‘replication’@’replication-host-ip’ identified by ‘Password’; grant replication slave on *.* to ‘replication’@’replication-host-ip’; flush privileges;
- The master server has been configured, now configure the slave server.
- Stop MySQL service on the slave, from the windows service choose the MySQL80, right-click and select stop.
- Locate the my.ini file on the slave server, under “C:\ProgramData\MySQL\MySQL Server 8.0”.
- Edit the my.ini file, do the same as mentioned at step-5 to ignore tables that should not be replicated:
server-id=2 log-bin=mysql-bin #if your server logging is already enabled keep the setting relay-log=mysql-relay-log log-replica-updates gtid-mode=on enforce-gtid-consistency replicate-do-db= fortune replicate-ignore-table=fortune.dbchangelog, fortune.dbchangeloglock, fortune.authority, fortune.charity
- Start the MySQL service from the windows service choose the MySQL80, right-click and select start.
- From the MySQL command line on the slave server execute the following command:
CHANGE MASTER TO MASTER_HOST = 'master-host-ip', MASTER_PORT = 3306, MASTER_USER = ‘replication’, MASTER_PASSWORD ='Password', MASTER_AUTO_POSITION = 1, GET_MASTER_PUBLIC_KEY=1;
- Start the replication process on the slave server from the MySQL command-line client.
start replica;
- Check the replica is working correctly by executing the following command on the master server.
show master status;
- Execute the below on the replication server.
show replica status\G;
- Perform some DML on any table that is configured to be replicated and check to see the changes are reflected on the replication server.
Notes:
- Monitor your replication by “show replica status\G;” on the replication server to see it is running properly or any error is there. On the master server execute “show master status;” Match the “Executed_Gtid_Set” from both outputs, if it same or has few lags then it is OK.
- If replication caught any error resolve it and execute the “start replica;” again.