How to use MySQL for cross-database data synchronization?

How to use MySQL for cross-database data synchronization?

In modern software development, the use of databases is ubiquitous. As software projects grow, data synchronization and backup become more and more important. MySQL is a powerful relational database management system, and it also provides some reliable methods to achieve data synchronization across databases. This article will introduce how to use MySQL for cross-database data synchronization and illustrate it with code examples.

1. Create data source

First, we need to create two databases, one is the data source database and the other is the target database. In MySQL, you can use the following SQL statement to create a database:

CREATE DATABASE datasource;
CREATE DATABASE target;

2. Create data table

Next, we need to create the same in the data source and target database data sheet. You can use the following SQL statement to create a data table named user:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. Configure the data source database

In the data source database, We need to enable binary logging to log all database changes. This can be achieved by editing the MySQL configuration file (generally my.cnf or my.ini). Find the following section and uncomment it:

# For MySQL 5.1.8 or later use 'SERVER' instead of 'STANDALONE'
# binary logging format - mixed recommended
# binlog_format=mixed

After the configuration is completed, restart the MySQL database for the configuration to take effect.

4. Configure the target database

In the target database, we need to set up master-slave replication to receive data synchronization from the data source. This can be achieved by editing the MySQL configuration file (generally my.cnf or my.ini). Find the following section and uncomment it:

# server-id = 1
# log_bin = /var/log/mysql/mysql-bin.log

After the configuration is completed, restart the MySQL database for the configuration to take effect.

5. Start the binary log of the data source database

In the data source database, we need to start the binary log to record all database changes. You can use the following SQL statement to start the binary log:

FLUSH LOGS;

6. Configure master-slave replication of the target database

In the target database, we need to configure master-slave replication to receive data from Data synchronization of data sources. You can use the following SQL statement to configure master-slave replication:

CHANGE MASTER TO
  MASTER_HOST='数据源数据库IP地址',
  MASTER_USER='数据源数据库用户名',
  MASTER_PASSWORD='数据源数据库密码',
  MASTER_PORT=数据源数据库端口号,
  MASTER_LOG_FILE='数据源数据库二进制日志文件名',
  MASTER_LOG_POS=数据源数据库二进制日志位置;

Among them, Data source database IP address is the IP address of the data source database, Data source database user name is the user name of the data source database, data source database password is the password of the data source database, data source database port number is the port number of the data source database, data source database The binary log file name is the binary log file name of the data source database, and the binary log location of the data source database is the binary log location of the data source database.

7. Start master-slave replication of the target database

In the target database, we need to start master-slave replication to receive data synchronization from the data source. You can use the following SQL statement to start master-slave replication:

START SLAVE;

8. Verify data synchronization

Now, we can test data synchronization. We can insert a piece of data into the user table in the data source database and verify whether the data is synchronized in the target database.

In the data source database, you can use the following SQL statement to insert a piece of data:

INSERT INTO datasource.user (name, age) VALUES ('张三', 25);

In the target database, you can use the following SQL statement to query whether the user table has Newly inserted data:

SELECT * FROM target.user;

If the synchronization is successful, you will see that there is also a piece of data named 张三 and age 25 in the target database.

At this point, we have completed cross-database data synchronization using MySQL. By enabling the binary log of the data source database and configuring the master-slave replication of the target database, we can achieve real-time synchronization and backup of data to ensure data security and consistency.

The above is the detailed content of How to use MySQL for cross-database data synchronization?. For more information, please follow other related articles on the PHP Chinese website!