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.
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.
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!