Backup Software > Backup > Database Backup Software > Sync MySQL Database

MySQL Database Synchronization

MySQL synchronization is a process of making two MySQL databases having identical contents. It’s a general idea that is realized differently in clustering, replication and other applications. Handy Backup synchronizes MySQL tables via backup and recovery.

Download for Free

Version 8.5.8 , built on October 31, 2024. 118 MB
30-day full-featured trial period

The Role of Handy Backup for Sync MySQL Databases

Handy Backup is the professional data backup and synchronization software solution. It contains an advanced MySQL plug-in plug-in that lets you dumping your databases into readable text files.

Handy Backup can be helpful in two aspects: it provides a fast way to perform the operation, and creates clearly readable backup files (database dumps) that you can modify by hand, which lets you easily change storage engine for your Slave Servers.

To learn more, please refer to MySQL Replication.

Advantages and Functions of Handy Backup

Expanded MySQL Synchronization Tool

MySQL synchronize two databases via Handy Backup by creating a dump (backup) of one base and then by restoring it. Thus, you can combine your MySQL synchronization with backup, encryption, advanced storage options (such as copying to FTPS, SFTP or an S3 cloud), batch processing etc.

Sync MySQL Databases by Schedule

You can tune a pair of MySQL database synchronization tasks to run at an exactly pre-defined time, with an exact interval from a single minute (which we not recommend due too small gap between backup and restore parts of MySQL sync) to several months. You can also force missed tasks to run.

Simple Interface for Complex MySQL Sync Actions

Simple Interface for Complex Actions

You can control all operations in Handy Backup with a simple-looking, understandable GUI, which can control all aspects of working for MySQL database synchronization tools. For a server edition, this GUI is a single management panel, controlling all MySQL synchronization actions on remote servers.etc.

Note: Besides MySQL synchronization, you can apply all materials from this and other MySQL-related articles t backup and sync MariaDB (except using the “MariaDB” plug-in instead of “MySQL”).

Trivia for MySQL Synchronization

Basically, there are two methods to sync MySQL databases:

One-way synchronization is used when a database being synchronized doesn’t receive any changes from MySQL clients, or receives an insignificant amount of changes that can be lost.

For example, in a Master-to-Slave replication configuration, there is a Master database that receives data from MySQL-based applications and acts as data source for Slave databases that act as read-only storage.

Two-way synchronization implies that both databases can be modified by MySQL clients and any record made to one database must be inserted into the other.

Learn more about MySQL Backup Software.

Data Synchronization in Clustering Configuration

MySQL clusters consist of several MySQL servers (SQL nodes), configured for sharing all storage, processing and management operations between these servers.

Storage Engines

The storage engine used in all tables is called Network DataBase (NDB): it’s an in-memory engine which tracks changes made to any clustered server and automatically synchronizes all other servers, so at any time they have identical contents.

Caveat! Synchronization in a MySQL cluster is performed automatically and does not require any additional setup. It is important, however, to remember that having a cluster does not remove the need for regular backup.

Clustering enables you to increase the database’s speed and availability, but it doesn’t protect from SQL injections and user mistakes. For instance, if you accidentally delete a table in one database, it will be deleted on all clustered servers too.

Synchronization in Replication Configuration

Unlike clustering, MySQL replication configuration doesn’t expect all nodes to share one in-memory storage engine and be synchronized all the time. Oppositely, it is often recommended to set different storage engines for different servers, to distribute the roles between these servers.

Note: An optimal configuration for servers is the one where one of the servers processes all INSERTs and UPDATEs, while the others handle all reading operations.

Using the Replication

The reason for assigning different server roles is that storage engines perform much slower under concurrent reads and writes. Replication is a built-in feature of the database management system, in which synchronization of MySQL servers is made differently than through common SQL updates.

This technique analyzes changes made to the Master database and reproducing them on the Slave databases.

Caveat! When trying to synchronize MySQL databases, the replication engine doesn’t verify that they are identical, which can lead to unexpected errors. A common approach to setting up replication is to take a full backup of the Master Server and restore it on the Slave Servers.

Physical (Cold) Backup and Synchronization of MySQL DBMS

There is an interesting approach that enables you to backup and synchronize MySQL databases very quickly by simply stopping the DBMS and copying its binary files to a different place.

The definition for this method is often physical backup, because it does not work with the database’s programming interfaces, but treats it as common HDD files.

For many businesses the cost of a downtime of several minutes may result in huge losses, and this is another situation where MySQL replication proves its worth. An example scenario of combining replication and “cold” backups could be as follows:

Set up Master-to-Slave replication configuration.
When you need to make backups, stop the Slave Server.
Let MySQL dump all data from the memory to the disk.
Copy binary files of the Slave Server’s database to a safe place.
Re-run the Slave server.

Note: Among other powerful features, Handy Backup can run third-party scripts and applications prior to and after running backup tasks. In the scenario described above, you can set this function to stop the MySQL server for “cold” backup and to restart it after the copying is complete.

MySQL Synchronization Basics: Video Example

From the next video, you can learn about how to create MySQL backup and recovery tasks, the necessary components for automatic MySQL synchronization between servers. To make an automatic MySQL sync, just create a recovery task in advanced mode and change a location on Step 3.

Download and install our software today - making your first MySQL backup will only take two minutes!

Licensing Information

As described above, synchronization of MySQL databases can’t replace backups. The recommended way to set up automated backup of your MySQL databases is to use one of the business-oriented editions of our software.

Try MySQL Backup in Small Business and Server Network editions - 30 days trial!
If you have only one server running a MySQL database, then you need Handy Backup Small Business
If you want to back up and synchronize multiple MySQL servers at once, then you need Handy Backup Server Network
To see pricing on these and other editions of our software, please refer to the Order section.