MYSQL::::Setting Up Selective tables from Master-Slave Replication in MySQL with GTID or NON GTID

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:

1. 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 

2. 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.
3. Restore the Backup:
• Import the exported dump file into the new SLAVE database:

mysql -u root -p demo_db < dump.sql

4. 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.