Detailed explanation of the implementation steps of MySQL dual-machine hot standby and load balancing

The MySQL database does not have an incremental backup mechanism, but it provides a master-slave backup mechanism, which is to write all the data in the master database to the backup database at the same time. Implement hot backup of MySQL database.

The following are the specific steps for master-slave hot backup:

Assume the master server is A (master) and the slave server is B (slave)

A: 192.168.0.104

B: 192.168.0.169

1. Master server authorization

The authorized secondary server can connect to the primary server and perform updates. This is done on the primary server, creating a username and password for use when accessing the secondary server. You can also use the default account and password of the primary server.

2. Data replication

Copy the existing data on the master to the slave so that the data in the two databases remain consistent when the master-slave database is established. Exporting and importing databases will not be described in detail.

3. Configure the master server

Modify the my.ini configuration file in the root directory of mysql on the master

Assign the master server a server-id in the option configuration file. The id must be a unique value in the range of 1 to 2^23-1. The server-id of the primary server and the secondary server cannot be the same. In addition, you also need to configure the master server to enable binary logging, that is, add the log-bin startup option in the option configuration file.

Note: If the binary log of the master server is enabled, the previous binary log should be backed up before shutting down and restarting. After restarting, the previous log should be cleared using the RESET MASTER statement.

Reason: All operations on the cartrader database on the master are recorded in the log file, and then the log is sent to the slave. After receiving the log file from the master, the slave will perform the corresponding operations, so that the database in the slave performs the same operation as the master database. Therefore, in order to maintain data consistency, it is necessary to ensure that the log file does not contain dirty data.

4. Restart the master

After configuring the above options, restart the MySQL service and the new options will take effect. Now, all updates to the information in the database will be written to the log.

5. Configuring slaves

Add the following parameters to the MySQL option configuration file on the slave server:

[mysqld]
#Unique and different from the server-id on the primary server.
server-id=2
#Host name or IP address of the primary server
master-host = 192.168.0.104
#If the master server is not listening on the default port, you need to determine the master-port option
master-port=3306
#The username and password created in step 2.1
master-user=root
master-password=123456
#The database to which the copy operation is to be applied (optional, defaults to all)
replicate-do-db=cartrader
#If the connection between the primary and secondary servers often fails, you need to increase master-retry-count and master-connect-retry
#master-retry-count The number of times the connection is retried
#master-connect-retry The number of seconds to wait after a connection failure
master-retry-count = 999
master-connect-retry = 60

5. Restart the slave

After the MySQL service is restarted on the secondary server, a master.info file is created in the data directory, which contains

All information about the replication process (information about connecting to the master server and exchanging data with the master server). After the initial startup, the slave server will check the master.info file to obtain relevant information.

If you want to change replication options, delete master.info and restart the MySQL service. The master.info file is recreated during the startup process using the new options in the option configuration file.

Import the database script file (cartrader.sql) backed up on the primary server into the secondary server database to preserve

Ensure that the starting points for replication operations on the primary and secondary servers are the same.

6. Check whether the status of the master is consistent with the settings

7. View slave

Generally, after restarting the slave, the replication function will be automatically enabled, which can be viewed through the following statement

On the slave

mysql>show slave status

If it shows "waiting for master to send event", it means it has been started. Otherwise, it is running.

mysql>start slave

To start the slave

After the SHOW SLAVE STATUS command output, the value corresponding to Slave_IO_Running should be YES.

The corresponding value of Slave_SQL_Running is YES. Only in this way can the master and slave functions be backed up normally.

The command to temporarily stop the master-slave hot backup is:

mysql>stop slave

Summarize

The above are the implementation steps of MySQL dual-machine hot standby and load balancing introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!