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

MySQL database has no incremental backup mechanism, but it provides a master-slave backup mechanism, that is, all the data of the master database are written to the backup database at the same time. Realize hot backup of MySQL database.

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

Assume that 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

Authorize the secondary server to connect to the primary server and make updates. This is done on the primary server, creating 1 username and 1 password for secondary server access. You can also use the default account and password of the master server.

2. Data replication

Copy the existing data on master to slave, so that the data of the two databases will remain 1 when the master and slave databases are established. Exporting and importing databases will not be described in detail.

3. Configure the primary server

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

Assign the primary server 1 server-id in the option configuration file, which must be the only 1 value in the range of 1 to 2 ^ 23-1. server-id of primary and secondary servers cannot be the same. You also need to configure the primary server to enable binary logging by adding log-bin startup options to the options configuration file.

Note: If binary logs are enabled for the primary server, you should back up the previous binary logs before shutting down and restarting. After restarting, the previous log should be emptied using the RESET MASTER statement.

Reason: master on the database cartrader 1-cut operations are recorded in the log file, and then the log will be sent to slave, slave after receiving the log file from master will execute the corresponding operation, so that the database in slave does the same operation as the database in master. Therefore, in order to maintain the uniformity of data, it is necessary to ensure that there is no dirty data in the log file.

4. Restart 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. Configure slave

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

[mysqld]
# is only 1 and is different from server-id on the primary server.
server-id=2
# Hostname or ip address of the primary server
master-host= 192.168.0.104
# Determine the master-port option if the primary server is not listening on the default port
master-port=3306
# User name and password created in step 2.1
master-user=root
master-password=123456
# The database to which the replication operation will be directed (optional, default to all)
replicate-do-db=cartrader
# Add master-retry-count and master-connect-retry if the connection between primary and secondary servers often fails
# master-retry-count connection retries
Number of seconds to wait after # master-connect-retry connection fails
master-retry-count = 999
master-connect-retry = 60

5. Restart slave

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

All information about the replication process (about connecting to the primary server and about 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 replication options, delete master. info and restart the MySQL service, recreating the master. info file with the new options in the options configuration file during startup.

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

Certificate the starting point of replication operation on the primary-secondary server.

6. Check the status of master and whether it is set to 1

7. View slave

After restarting slave, the copy function will be turned on automatically, which can be viewed by the following statement

On slave

mysql>show slave status

If waiting for master to send event is displayed, it means it is started, otherwise it is running

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, which is the only way to ensure the normal backup of master and slave functions.

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

mysql>stop slave

Summarize