How to Sync Two MySQL Databases Using Airbyte?

MySQL is the most widely used Relational Database Management System (RDBMS), holding approximately 42.11% of the total relational database market share. Its robust architecture enables you to handle enormous datasets while ensuring efficient data storage and retrieval.

However, like any other database management solution, MySQL may encounter downtime challenges, which can negatively impact business performance. A common strategy to mitigate the effect of downtime is data replication, which involves synchronizing data across various locations to ensure high accessibility for users.

This guide comprehensively explains how to perform MySQL synchronization using Airbyte, a popular data integration platform, along with an alternative method.

Types of MySQL Synchronization

Here are the two prevalent types of MySQL synchronization methods:

One-Time Sync

One-time sync refers to replicating data between MySQL databases only once. After the completion of the data synchronization process, not a single connection is retained for future captures. This process is beneficial for applications that don’t frequently require updated data.

Continuous Sync

Continuous sync refers to the process of continuously updating the MySQL database (target database) with real to near real-time changes from the source system. It ensures a stable and uninterrupted connection between two or more databases, allowing up-to-date data to be reflected in the required system.

How to Sync MySQL Databases Using Airbyte?

Airbyte is one of the most prominent no-code data synchronization tools. It offers more than 550 pre-built connectors that enable you to move data from various sources to your preferred destination, including SQL-based databases. If the connector you seek is unavailable, Airbyte offers a Connector Builder and a collection of Connector Development Kits (CDKs) for custom connector development.

To start syncing your MySQL databases with Airbyte Cloud, follow these straightforward steps:

Step 1: Connect Your MySQL Database as Source

• Login or Sign up for the Airbyte Cloud.
• Navigate to Sources.
• In the Search box, type MySQL and click on the specific connector.

• On the Create a source page, fill in MySQL database details, including the Host name, Port, User, Password, and Database name. Next, select the Encryption method from the drop-down and select how you want to extract data from your MySQL method from the Update Method. Refer to this Airbyte MySQL source connector documentation for a simplified setup.

• After filling in all the mandatory fields, click the Set up source button.

Step 2: Connect Your MySQL Database as Destination

• Once you configure your MySQL source database, navigate to the Destinations tab on the left-hand panel.
• In the Search box, enter MySQL and select the available connector.

• On the Create a destination page, enter all the mandatory fields, including the Host, Port, DB name, and User.

• Select the SSH Tunnel Method and click on the Set up destination button.

Step 3: Sync MySQL Databases

• Click on the Connections tab on the left panel.
• Choose the MySQL option on the Define source page.
• Similarly, select MySQL destination on the Define destination page.
• Select the data streams that you intend to replicate on the Select stream page.
• On the Configure connection page, specify the Connection name, Schedule Type, Replication Frequency, and Destination Namespace fields according to your data integration requirements.
• Finally, click on the Finish & Sync button to initiate MySQL data synchronization.

Following these straightforward steps can enable you to synchronize two distinct MySQL databases using Airbyte Cloud.

How to Sync MySQL Databases Manually?

Let’s explore how you can perform MySQL synchronization manually. This process involves establishing a Master, a primary database server, and one or more Slaves, secondary servers. The changes made to the Master are recorded in a binary log. The Slave database then synchronizes based on that log.

Step 1: Configure Network Access

• Open port 3306 on the Master server to allow connections with Slave servers.
• Ensure the network connectivity between all the servers involved in the replication process.

Step 2: Create a Replication User Account

Create a dedicated MySQL user for replication on the Master server. Replace the slave, slaveDomain, and slavePassword placeholders with your secondary database credentials.

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'slaveDomain'  IDENTIFIED BY 'slavePassword';

Step 3: Define MySQL Configuration Properties

To synchronize the Master and Slave databases, you must define specific configurations in the .cnf extension file. For instance, Master requires Binary Logging enabled, while both databases need to have a server-ID setup.

When MySQL initiates, it reads the connection settings from the my.cnf file. Ensure that this file exists for every database server. For example, the Master’s .cnf file must contain:

[mysqld]
bind-address = source_server_address
log-bin = /var/log/mysql/mysql-bin.log
log-slave-updates
binlog-do-db = database_name
server-id=1

In the above file format:

• bind-address sets the source MySQL instance.‍
• log-bin enables the Binary Logging.‍
• log-slave-updates option, which is essential for chained replication to pass the updates from the Master. Here, chained replication implies that the Slave can also operate as a Master for another database server. With the log-slave-updates option, the Slave of the primary server writes the replication changes in its log file.‍
• binlog-do-db stores the name of the database you want to replicate.‍
• server-id is a unique server identifier.

For the Slave’s .cnf file, use:

[mysqld]
server-id=2
log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db = database_name
relay-log = /var/log/mysql/mysql-relay-bin.log

In the above file format, all the properties remain the same as those of the source database other than the server-id and relay-log. The relay-log property represents the Slave server’s log file location.

After making these changes, restart MySQL on all the servers and navigate to the Master’s directory. The directory must contain a Binary Log in “-bin.001” format.

Step 4: Take a Snapshot of the Master Database

This step involves copying the Master data file and recording the status of the Binary Log. For this, you must lock the tables on Master to restrict changes while taking a snapshot.

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Output:‍

Record the File and the Position values. While keeping the Read Lock mode enabled, open a new terminal and archive the Master’s data directory.

sudo cp -R -p /usr/local/mysql/data ~/Desktop

Now, you can release the Read Lock, using:

UNLOCK TABLES;

Step 5: Prepare the Data for the Slave Server

Locate the archived file, and clean the data by removing unnecessary information that you do not want in the Slave database. After preparing the data, create an archive of the data directory in the Master’s tmp directory. Copy the archive file in the /tmp directory on the Slave server host.

cd ~/Desktop/data
tar -cvf /tmp/mysql-snapshot.tar .
scp /tmp/mysql-snapshot.tar user@myslave.domain.com:/tmp/

Step 6: Import Data to the Slave Server

Access the Slave database server and shut down the MySQL server. Extract the data to the Slave’s data directory.

cd /usr/local/mysql/data/
tar -xvf /tmp/mysql-snapshot.tar

Restart MySQL on the Slave server.

Step 7: Initiate the Data Replication Process

Issue a statement to configure MySQL for the Slave database server.

CHANGE MASTER TO
MASTER_HOST='masterHostName',
MASTER_USER='replicationUserName',
MASTER_PASSWORD='replicationPassword',
MASTER_LOG_FILE='recordedLogFileName',
MASTER_LOG_POS=recordedLogPosition;

Start the Slave process:

START SLAVE;

You can monitor the status of the replication task on the Master and the Slave using the following command:

SHOW PROCESSLIST;

When to Use Each Method?

If you wish to achieve a connection between two MySQL databases without complex configurations, you can go with Airbyte. Along with MySQL database integration, it is also helpful for scenarios that require you to move data to different locations, like MySQL to Postgres.

One of Airbyte’s key features is the support for flexible synchronization methods. It offers two synchronization modes, including incremental and full refresh. The incremental sync mode lets you capture source changes since the last sync job, while full refresh aids in syncing the entire source data. By automating these processes, Airbyte mitigates the requirement of manual intervention, freeing you from the stress of infrastructure maintenance.

Conversely, the manual method provides you with more control over data movement. With this approach, you can define custom data migration and transformation logic, altering the setup based on your specific requirements. If you prefer a similar level of control with enhanced security, you can opt for the Airbyte Enterprise Edition. 

MySQL Sync Use Cases

Let’s review some of the use cases of MySQL synchronization.

Data Backups

Data backups are an essential part of modern applications working with large amounts of information. In case your primary source of data gets corrupted, you can rely on the replicas to retrieve data. This helps provide high availability to the end users.

System Upgrades

To keep the servers updated with the latest versions of MySQL, you can test and validate upgrades on your replicas. This is a better alternative than running a single server environment, where applications are taken offline for updates.

Analytical Workloads

Performing MySQL synchronization is crucial for handling analytical workloads while maintaining high performance. Analytical solutions require data migration from a database to a warehouse. However, organizations relying on single database storage might face performance degradation when data is parsed into a warehouse. To overcome this challenge, you can use replicas for managing analytical workloads.

Conclusion

MySQL synchronization is an effective way to enhance the data availability. While both Airbyte and manual methods can efficiently synchronize data, the complexity involved in the manual approach can be overwhelming and time-consuming. To overcome the configuration and coding challenges, you can utilize Airbyte with its prominent features. Its pre-built connectors make it almost effortless to replicate information across numerous platforms.

‍Streamline data movement within your organization using Airbyte. Sign up now!