Introduction
MySQL replication is a powerful feature used to synchronize data across multiple servers, ensuring high availability and redundancy. Replication filters are a useful tool in managing the replication process, allowing you to choose which databases, tables, or even columns are included or excluded from replication. In this article, we will walk you through the process of setting up replication filters in MySQL with step-by-step examples.
Understanding Replication Filters
Replication filters can be applied at either the master or the slave server, giving you flexibility in determining which data to replicate. Master-side filters are applied on the master server, while slave-side filters are applied on the slave server. The two main types of replication filters are:
a. Database-level filters
b. Table-level filters
Database-level Filters
Database-level filters allow you to specify which databases to include or exclude from the replication process.
a. Master-side Database Filters
To set up master-side database filters, you need to modify the my.cnf file (or my.ini on Windows) and add the following lines under the [mysqld] section:
binlog-do-db=database_to_replicate
binlog-ignore-db=database_to_excludeFor example, if you want to replicate only the ‘sales’ database, use the following line:
binlog-do-db=salesb. Slave-side Database Filters
For slave-side database filters, add the following lines under the [mysqld] section of the my.cnf file (or my.ini on Windows):
replicate-do-db=database_to_replicate
replicate-ignore-db=database_to_excludeTo replicate only the ‘sales’ database, use the following line:
replicate-do-db=salesTable-level Filters
Table-level filters enable you to include or exclude specific tables from the replication process.
a. Master-side Table Filters
Add the following lines under the [mysqld] section of the my.cnf file (or my.ini on Windows):
binlog-do-table=database_name.table_name_to_replicate
binlog-ignore-table=database_name.table_name_to_excludeFor example, to replicate only the ‘orders’ table from the ‘sales’ database, use:
binlog-do-table=sales.ordersb. Slave-side Table Filters
Add the following lines under the [mysqld] section of the my.cnf file (or my.ini on Windows):
replicate-do-table=database_name.table_name_to_replicate
replicate-ignore-table=database_name.table_name_to_excludeTo replicate only the ‘orders’ table from the ‘sales’ database, use:
replicate-do-table=sales.ordersWildcard Filters
Wildcard filters provide an efficient way to include or exclude tables based on naming patterns.
a. Master-side Wildcard Filters
Add the following line under the [mysqld] section of the my.cnf file (or my.ini on Windows):
binlog-wild-do-table=database_name.table_name_pattern
binlog-wild-ignore-table=database_name.table_name_patternFor example, to replicate only tables with names starting with ‘sales_’, use:
binlog-wild-do-table=sales.sales_%b. Slave-side Wildcard Filters
Add the following line under the [mysqld] section of the my.cnf file (or my.ini on Windows):
replicate-wild-do-table=database_name.table_name_pattern
replicate-wild-ignore-table=database_name.table_name_patternFor example, to replicate only tables with names starting with ‘sales_’, use:
replicate-wild-do-table=sales.sales_%Restarting MySQL
After you have configured the replication filters, restart the MySQL service to apply the changes:
For Linux:
sudo service mysql restartFor Windows:
net stop MySQL
net start MySQLMonitoring Replication
Once the replication filters are set up and the MySQL service is restarted, it’s essential to monitor the replication process to ensure it’s working as expected. Use the following command to check the replication status:
SHOW SLAVE STATUS\GLook for the Slave_IO_Running and Slave_SQL_Running values, which should both be ‘Yes’ for successful replication.
Conclusion
Replication filters in MySQL are a powerful way to manage and optimize the replication process, allowing you to include or exclude specific databases, tables, or even columns. By understanding and implementing the different types of filters, you can fine-tune your replication setup to meet your application’s specific requirements. Remember to monitor the replication process and adjust the filters as needed to ensure your data remains consistent and up-to-date across all servers.
