How to implement cross-server and cross-database operations of data in MySQL?

MySQL is an open source relational database management system that is widely used for data storage and management of web applications. In actual application scenarios, we often need to implement cross-server and cross-database operations on data, such as data synchronization, data migration, or distributed management of data between multiple databases.

The following introduces the methods and sample codes on how to implement cross-server and cross-database operations of data in MySQL.

1. Using Federated storage engine
   Federated storage engine is a storage engine provided by MySQL. Through this storage engine, we can create a table in a MySQL instance that is connected to other MySQL servers. , thereby realizing cross-server operations of data.

First, we need to enable the Federated storage engine on the MySQL server. Add the following configuration in the my.cnf configuration file:

[mysqld]
federated = ON

Then create a Federated table in the database and specify the information of the remote MySQL server to be connected:

CREATE TABLE federated_table (
    id INT(11) AUTO_INCREMENT,
    data VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=FEDERATED
DEFAULT CHARSET=utf8
CONNECTION='mysql://[用户名]:[密码]@[远程MySQL服务器IP地址]:[端口号]/[数据库名]/[远程表名]';

In the above code, [User name], [Password], [Remote MySQL server IP address], [Port number], [Database name], [Remote table name] should be replaced according to the actual situation. After the creation is successful, we can perform cross-server data operations by operating this Federated table.

2. Using the Replication replication function
   MySQL's Replication replication function can copy data on one MySQL server to other MySQL servers to achieve cross-server data operations.

First, configure on the main database. Add the following configuration in the my.cnf configuration file:

[mysqld]
server-id=1
log_bin=mysql-bin
binlog_format=row

Then restart the MySQL service.

Configure on the slave database. Add the following configuration in the my.cnf configuration file:

[mysqld]
server-id=2

Then restart the MySQL service.

Create a replication account on the master database and grant replication permissions:

CREATE USER 'repl_user'@'从数据库IP地址' IDENTIFIED BY 'repl_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'从数据库IP地址';

Among them, the slave database IP address needs to be replaced with the actual slave database IP address.

Then check the file name and location of the binary log on the main database:

SHOW MASTER STATUS;

Record the values ​​of File and Position.

Set the replicated information on the slave database:

CHANGE MASTER TO
MASTER_HOST='主数据库IP地址',
MASTER_USER='repl_user',
MASTER_PASSWORD='repl_password',
MASTER_LOG_FILE='二进制日志的文件名',
MASTER_LOG_POS=二进制日志的位置;

Among them, the master database IP address needs to be replaced with the actual master database IP address, and the file name and location of the binary log should be recorded using the previous step. value.

Then start the replication process on the slave database:

START SLAVE;

You can view the status of the replication process through the SHOW SLAVE STATUSG command.

In this way, the data modification operations on the master database will be synchronized to the slave database, realizing cross-server data operations.

To sum up, MySQL can realize cross-server and cross-database operations of data through the Federated storage engine and Replication replication function. Developers can choose appropriate methods to perform cross-server and cross-database data operations based on actual needs.

The above are methods and sample codes for implementing cross-server and cross-database operations of data in MySQL. I hope it will be helpful to you.

The above is the detailed content of How to implement cross-server and cross-database operations of data in MySQL?. For more information, please follow other related articles on the PHP Chinese website!