Setting Up Selective Master-Slave Replication in MySQL with GTID
Introduction
This blog post will guide you through the process of setting
up selective master-slave replication in MySQL, specifically when both the
master and slave databases are using GTID (Global Transaction Identifier)
replication. This technique allows you to replicate only specific tables from
the master to the slave database, optimizing resource usage and improving
performance.
Prerequisites:
- A
running MySQL master-slave replication setup with GTID enabled on both the
master and other slave servers.
- A
list of tables that you want to replicate.
Steps:
- Backup
Specific Tables:
- Use
mysqldump to export the selected tables from the production database:
mysqldump -u root -p --master-data --databases production_db --tables table1 table2 table3 ... table115 > dump.sql
- Modify
MySQL Configuration:
- Open
the MySQL configuration file (usually my.cnf on Linux or my.ini on
Windows).
- Under
the [mysqld] section, add the following lines to specify the tables to
replicate:
- replicate-do-table=production_db.table1
- replicate-do-table=production_db.table2
- #
Add more tables as needed
- Ensure
that gtid_mode is set to ON and enforce_gtid_consistency is set to ON in
the slave server's configuration(my.ini file).
- Save
and restart the new SLAVE MySQL server.
- Restore
the Backup:
- Import
the exported dump file into the new SLAVE database:
mysql -u root -p demo_db < dump.sql
- Configure
Replication:
- Set
up the replication configuration on the slave database:
CHANGE MASTER TO
MASTER_HOST='production_host',
MASTER_PORT=3306,
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
START SLAVE;
SELECT SLEEP(10);
SHOW REPLICA STATUS\G;
- Replace
the placeholders with your actual production server details and
replication credentials.
Additional Considerations:
- If
you encounter the error ERROR 3546 (HY000) at line 24:
@@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not
overlap with @@GLOBAL.GTID_EXECUTED, you can try resetting the GTID state
on the slave server using the RESET MASTER command prior running import
command in new slave database.
- Ensure
that the replication user has the necessary privileges on both the master
and slave servers.
- Consider
using replication filters to further control which transactions are
replicated.
- MASTER_DELAY=7200
in MySQL replication sets a delay of 7200 seconds (2 hours) between the
master and slave servers. This means that the slave will intentionally lag
behind the master by 2 hours.
- If
we specify replicate-do-table only in mysql config file, it will replicate
only those tables in the list, and ignore the rest. Behavior: Only
the tables listed in replicate-do-table will be replicated. All other
tables will be ignored.
- If
we specify replicate-ignore-table only in mysql config file, it will
ignore those tables in the list, and replicate the rest. Behavior:
All tables will be replicated except those listed in replicate-ignore-table.
- If NON GTID replication, Then Replication command will be " CHANGE MASTER TO MASTER_HOST='your_master_host', MASTER_PORT=3306, MASTER_USER='your_master_user', MASTER_PASSWORD='your_master_password', MASTER_LOG_FILE='', MASTER_LOG_POS=;" Master log file and master log pos can be fetched from opening the MYSQL exported dump file from source.
- The
SELECT @@GLOBAL.GTID_EXECUTED
command in MySQL at new SLAVE database is used to retrieve the set of Global Transaction Identifiers (GTIDs) that have been executed on the current server. This information is particularly useful in GTID-based replication setups.
By following these steps and addressing the potential issues
mentioned, you can successfully set up selective master-slave replication in
MySQL with GTID, optimizing resource usage and improving performance.