How to configure the replication function of MySQL database?

How to configure the replication function of MySQL database?

The replication function of MySQL database is a very important and commonly used function. It can synchronize data from a master database to multiple slave databases to ensure high availability and fault tolerance of data. This article will introduce how to configure the replication function of the database in MySQL, and attach some code examples.

1. Ensure that MySQL has enabled the replication function
   Before configuring the replication function of the MySQL database, you first need to ensure that the MySQL server has enabled the replication function. In the MySQL configuration file (my.cnf or my.ini), find and add the following configuration items:

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

   Copy after login

   Among them, log-bin is the option to enable binary logs, which is used to store the log files of the main database. ;server-id is the unique identifier of the MySQL server, and the server-id of each database instance must be unique. After configuration, restart the MySQL server.

2. Create replication user
   In order to perform replication operations, you need to create a user for replication in the master database and the slave database, and give corresponding permissions. Open the MySQL client and execute the following command:

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

   Copy after login

   Replace replication_user with your required username and your_password with your required password.

3. Get the binary log location of the main database
   Execute the following command in the main database to get the location of the binary log:

   SHOW MASTER STATUS;

   Copy after login

   Note the values ​​of File and Position , which will be used later in the slave database.

4. Configuring the slave database
   Open the configuration file of the slave database and add the following configuration items:

   [mysqld]
   server-id=2

   Copy after login

   Among them, the value of server-id must be unique. Save and restart the slave database.

5. Start the replication process
   Execute the following command in the command line of the slave database:

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

   Copy after login

   Fill the relevant information of the master database into the corresponding location. After execution, execute the following command to start the replication process:

   START SLAVE;

   Copy after login

6. Check the replication status
   Execute the following command in the slave database to ensure that replication has started normally:

   SHOW SLAVE STATUSG;

   Copy after login

   In the output results, the values ​​of Slave_IO_Running and Slave_SQL_Running should be "Running", indicating that the replication process is running normally.

At this point, the replication function of the MySQL database has been successfully configured. Addition, deletion, modification and query operations can be performed in the master database, and the slave database will automatically synchronize these operations.

Summary:
The replication function of the MySQL database is a very important and commonly used function, through which data synchronization between the master database and the slave database can be achieved. This article describes the steps to configure the MySQL database replication function and gives corresponding code examples. What needs to be noted during the configuration process is to ensure that the replication function is enabled on the MySQL server, that the server-ids of the primary database and the secondary database are unique, and that the replication user has the corresponding permissions. After the configuration is completed, you can use the SHOW SLAVE STATUS command to check the replication status to ensure that the replication process has started normally. By properly configuring and using the replication function of the MySQL database, the availability and reliability of data can be improved.

The above is the detailed content of How to configure the replication function of MySQL database?. For more information, please follow other related articles on the PHP Chinese website!