Shulou(Shulou.com)05/31 Report--

This article shows you how to achieve dual-computer hot backup and load balancing in Mysql. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Here are the specific master-slave hot backup steps:

Suppose master server A (master) and 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 primary server and update it. This is done on the primary server, creating a username and password for secondary server access. You can also use the default account and password of the primary server.

two。 Data replication

Copy the existing data on the master to the slave so that the data of the two databases are consistent when the master-slave database is established. Export and import into the database will not be detailed.

3. Configure the primary server

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

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

Note: if the binary log on the primary server is enabled, the previous binary log should be backed up before shutting down and restarting. After restarting, you should use the RESET MASTER statement to empty the previous log.

Reason: all the operations on the database cartrader on master are recorded in the log file, and then the log will be sent to slave,slave to receive the log text from master, and then the corresponding operation will be performed to make the database in slave do the same operation as the master database. Therefore, in order to maintain the consistency of the data, it is necessary to ensure that the log files do not have dirty data.

4. Restart master

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

5. Configure slave

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

[mysqld]

# unique and different from server-id on the primary server.

Server-id=2

# Host name or ip address of the master server

Master-host= 192.168.0.104

# if the primary server is not listening on the default port, you need to determine the master-port option

Master-port=3306

# username and password created in step 2.1

Master-user=root

Master-password=123456

# Database to be replicated against (optional, default is all)

Replicate-do-db=cartrader

# if the connection between the primary and secondary servers often fails, add master-retry-count and master-connect-retry

# number of master-retry-count connection retries

# seconds to wait after a failed master-connect-retry connection

Master-retry-count = 999

Master-connect-retry = 60

5. Restart slave

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

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

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

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

Verify that the starting point for replication operations on the primary-secondary server is the same.

6. Check whether the status of master is consistent with that set.

7. View slave

In general, after restarting slave, the replication function is automatically enabled, which can be viewed through the following statement

On slave

Mysql > show slave status

If waiting for master to send event is displayed, it means it has been started, otherwise it will be run.

Mysql > start slave

To start slave

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

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

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

Mysql > stop slave

The above is how to achieve dual-computer hot backup and load balancing in Mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.