SoFunction
Updated on 2025-04-10

Configuration steps for implementing data synchronization between MySQL databases

Data synchronization between mysql databases can be achieved through a variety of methods, including master-slave replication, mirroring and dual-master replication.
Data synchronization between MySQL databases is an important means to ensure data consistency and high availability. The following are several common MySQL data synchronization methods and their detailed steps:

How to implement data synchronization between MySQL databases?

1. Master-Slave Replication

Master-slave replication is the most commonly used data synchronization method in MySQL. By setting one database as the main library and other databases as the slave library, the update operation of the master library will be automatically synchronized to the slave library.

Configuration steps:

1. Configure the main library:

Edit the configuration file (or) of the main library and add the following configuration:

     [mysqld]
     server-id=1
     log-bin=mysql-bin

Restart MySQL service.

Create a user for replication on the main library and grant the necessary permissions:

     CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
     GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
     FLUSH PRIVILEGES;

Get the binary log location of the main library:

  SHOW MASTER STATUS;

2. Configure the slave library:

Edit the configuration file from the library and add the following configuration:

     [mysqld]
     server-id=2

Restart MySQL service.

Execute the following command on the slave library to configure the connection to the master library:

     CHANGE MASTER TO
     MASTER_HOST='Main Library IP',
     MASTER_USER='replica',
     MASTER_PASSWORD='password',
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=120;
     START SLAVE;

Verify the replication status:

SHOW SLAVE STATUSG;

2. Dual-master replication (Master-Master Replication)

Dual master replication refers to the fact that two MySQL servers are master-slave each other. They can write data on library A and synchronize to library B; they can also write data on library B and synchronize to library A.

Configuration steps:

1. Configure two main libraries: enable binary logs in the configuration files of two MySQL servers and set a unique server-id.

   [mysqld]
   log-bin=mysql-bin
   server-id=1 (Server A)
   server-id=2 (Server B)

2. Create a copy user: Create a copy user for each other on two servers.

How to implement data synchronization between MySQL databases?

  # Server A
   CREATE USER 'repl'@'ServerB_IP' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ServerB_IP';
   FLUSH PRIVILEGES;
   # Server B
   CREATE USER 'repl'@'ServerA_IP' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'ServerA_IP';
   FLUSH PRIVILEGES;

3. Export and import data: Export and import data from one server into another server.

   # On Server A
   mysqldump -u root -p --all-databases --master-data > 
   mysql -u root -p -h ServerB_IP <

4. Start replication: Configure the other party as the main library on the two servers and start replication.

   # On Server A
   CHANGE MASTER TO MASTER_HOST='ServerB_IP', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;
   START SLAVE;
   # On Server B
   CHANGE MASTER TO MASTER_HOST='ServerA_IP', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;
   START SLAVE;

3. Cluster Replication

Cluster replication is an advanced data synchronization method of MySQL, mainly including MySQL NDB Cluster and MySQL Group Replication, which achieves high availability and data consistency through multi-master replication and automatic failover mechanisms.

Steps to configure MySQL Group Replication:

1. Install the plug-in: Install the Group Replication plug-in on all nodes.

   INSTALL PLUGIN group_replication SONAME 'group_replication.so';

2. Configure nodes: Enable Group Replication-related settings in the configuration file of each node.

   [mysqld]
   server-id=1
   log-bin=mysql-bin
   binlog-format=ROW
   gtid_mode=ON
   enforce_gtid_consistency=ON
   group_replication_bootstrap_group=OFF
   group_replication_start_on_boot=OFF
   group_replication_local_address="192.168.1.2"
   group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee"

3. Start the cluster: Start MySQL on all nodes and join the cluster.

   group_replication_start_on_boot()

4. Use third-party tools (such as Percona XtraBackup)

Percona XtraBackup is an open source MySQL backup tool that can be used for hot backup and recovery of MySQL databases. It supports physical and incremental backups for scenarios where fast backup and recovery are required.

Basic steps to backup and restore using Percona XtraBackup:

1. Install Percona XtraBackup: Percona XtraBackup can be compiled and installed through the package manager or source code.

2. Backup the database: Use xtrabackup to back up.

   xtrabackup --backup --target-dir=/path/to/backup/dir

3. Prepare to restore the environment: Stop MySQL service and prepare to restore the environment.

   xtrabackup --prepare --apply-log-only --target-dir=/path/to/backup/dir

4. Recover the database: Use xtrabackup to recover.

   xtrabackup --copy-back --target-dir=/path/to/backup/dir

5. Start MySQL service: After the recovery is completed, start MySQL service.

   systemctl start mysqld

5. Use MySQL Workbench for data synchronization

MySQL Workbench provides a tooled interface that can help you implement data synchronization between different databases, mainly through data transfer functions (Data Import/Export).

How to implement data synchronization between MySQL databases?

Steps to use MySQL Workbench for data synchronization:

1. Open the work environment: Start MySQL Workbench and connect to the source database and the target database.

2. Design data import/export: Select "Data" -> "Data Export Wizard" or "Data Import Wizard" in the toolbar, which will guide you to create a new data transfer task.

3. Select the operation type: Select "Import from a database" If you want to transfer the data from the source database to the target library, or select "Export to a file", and then import it to another database in another step.

4. Configure connection information: Enter the connection details of the source database and the target database, including the host name, user name, password, etc.

5. Select tables and fields: In the export or import interface, you can select the specific tables and related columns that need to be synchronized, which can be a one-to-one mapping or a partial mapping.

6. Setting options: Specify data conversion rules (such as character set conversion), insertion policies (such as ignoring errors or stopping tasks), and other customization options.

7. Run the synchronization task: After confirming that all settings are correct, click the "Start" button to perform the synchronization operation.

8. Monitor progress and verification: Workbench will display the real-time status of the synchronization process. After completion, you can check whether the target database has correctly filled the data of the source database.

6. Custom scripts for data synchronization

In addition to the above methods, you can also write custom scripts (such as Python scripts) to regularly extract data from the source database, and then synchronize them to the target database after calculation processing. This method provides flexibility and can customize synchronization logic according to specific needs.

Python script example:

import 
import time
def fetch_data_from_master():
    master_conn = (
        host='master_host',
        user='master_user',
        password='master_password',
        database='your_database'
    )
    cursor = master_conn.cursor()
    ("SELECT * FROM your_table")
    data = ()
    ()
    master_conn.close()
    return data
def sync_to_slave(data):
    slave_conn = (
        host='slave_host',
        user='slave_user',
        password='slave_password',
        database='your_database'
    )
    cursor = slave_conn.cursor()
    ("INSERT INTO your_table (columns) VALUES (%s, %s, ...)", data)
    slave_conn.commit()
    ()
    slave_conn.close()
def main():
    while True:
        data = fetch_data_from_master()
        sync_to_slave(data)
        (3600)  # Synchronize once an hourif __name__ == "__main__":
    main()

FAQs Q&A session:

Q1: How to implement timed data synchronization between two databases in MySQL?

A1: There are many ways to achieve timing data synchronization, including MySQL's own replication function, third-party tools (such as pt-table-sync), scripting (such as Python scripts), and ETL tools. MySQL master-slave replication is a common and mature solution. By setting up the master and slave server, data changes on the master server are automatically synchronized to the slave server. You can also write timing tasks using Python scripts, obtain data from the source database every hour or every day and insert it into the target database.

Q2: What are the advantages and disadvantages of MySQL master-slave replication?

A2: The main advantages of MySQL master-slave replication include high availability, data backup, read-write separation and disaster recovery capabilities. It also has some disadvantages, such as delay problems (the data synchronization of the slave library will have certain delays), single point of failure (the failure of the master library will cause the entire replication link to be interrupted), and complexity (requires a high technical level for maintenance). When choosing a data synchronization plan, various factors need to be considered comprehensively based on specific needs and environment.

Original article, author: Weixi, if reprinted, please indicate the source: /ask/

Articles and pictures published or reproduced on this website are from the Internet, and their originality and the views and judgments expressed in the article do not represent this website. If you have any questions, please contact customer service to deal with it.

This is the article about the configuration steps for implementing data synchronization between MySQL databases. For more related content on MySQL data synchronization, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!