Teach you how to achieve dual-system hot backup and load balancing in Mysql

I believe that many friends don’t know. In fact, MySQL database does not have an incremental backup mechanism, but it will provide a master-slave backup mechanism, which means that all the data of the master database is written in the backup database at the same time. Hot backup of MySQL database. So, how to implement dual-system hot backup and load balancing in MySQL? Next, let Maduyun teach you how to operate it. I hope that the friends will be helpful after reading it.

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

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

A: 192.168.0.104

B: 192.168.0.169

1. Master server authorization

The authorized secondary server can connect to the main server and update. This is done on the primary server, creating a username and password for the secondary server to access. You can also use the default account and password of the main server.

2. Data replication

Copy the existing data on the master to the slave so that the data in the two databases remains consistent when the master-slave database is established. The export and import database will not be detailed.

3. Configure the main server

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

Give the main 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 need to configure the main server to enable binary logging, that is, add the log-bin startup option to the option configuration file.

Note: If the binary log of the master server has been enabled, the previous binary log should be backed up before shutting down and restarting it. After restarting, you should use the RESET MASTER statement to clear the previous log.

Reason: All operations on the database cartrader on the master are recorded in the log file, and then the log will be sent to the slave. After receiving the log file from the master, the slave will perform the corresponding operation to make the database in the slave do the same with the master The same operation as the database. Therefore, in order to maintain data consistency, it is necessary to ensure that the log file has no 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 into the log.

5. Configure slave

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

Insert picture description here

6. Restart the slave

After the MySQL service on the secondary server restarts, a master.info file is also created in the data directory, which contains all the information about the replication process (related information about connecting to the primary server and information about exchanging data with the primary server). After the initial startup, the secondary server will check the master.info file to get relevant information.

If you want to modify the replication options, delete master.info and restart the MySQL service. During the startup process, use the new options in the options configuration file to recreate the master.info file.

Import the database script file (cartrader.sql) backed up on the primary server into the secondary server database to ensure that the starting point of the replication operation on the primary and secondary servers is the same.

7. Check whether the status of the master is consistent with the setting

8. View slave

Under normal circumstances, after restarting the slave, the replication function will be automatically turned on, which can be checked by the following statement

On slave

mysql>show slave status

If it shows waiting for master to send event, it means it has started, otherwise it will run

mysql>start slave

To start the slave

After the SHOW SLAVE STATUS command is output, it should contain the value corresponding to Slave_IO_Running as YES, and the value corresponding to Slave_SQL_Running as 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

The above is about "How to achieve dual-system hot backup and load balancing in MySQL" related content, dear friends, have you learned, if you are unclear, or want to know more related knowledge, you can follow Modu Cloud, more dry goods are waiting for you to learn!