How to Implement hot standby for MySQL

The combination of MySQL database and PHP is the best combination of MySQL dual-machine Hot Standby. The principle is to use MySQL database (the best combination with PHP) through log updates) the SELECT File to operate related functions, the slave machine captures the host Update log in real time. Of course, this is only its principle. In fact, we do not need to process logs by ourselves, understand the principle, and implement

The combination of MySQL database and PHP is the best combination of MySQL dual-machine Hot Standby. The principle is to use MySQL database (the best combination with PHP) through log updates) the SELECT File to operate related functions, the slave machine captures the host Update log in real time. Of course, this is only its principle. In fact, we do not need to process logs by ourselves, understand the principle, and implement

The combination of MySQL database and PHP is the best combination of MySQL dual-machine Hot Standby. The principle is to use MySQL database (the best combination with PHP) through log updates) the SELECT File to operate related functions, the slave machine captures the host Update log in real time.

Of course, this is only its principle. In fact, we do not need to process logs by ourselves. After understanding the principle, implementation is easier to understand.

In this way, you need to create an account on the host. This account is used by the slave machine to capture host update logs. The file access permission is required. In the early stage, the File Permission was used to implement MySQL hot standby.

From MySQL (the best combination with PHP) 4, a special permission is added for hot backup. In essence, this permission should still be the file read permission, however, it should only be used to read logs to prevent some vulnerabilities.

For the client, set this account, password, master server address, and database name to be synchronized. This is only one-way. If you configure another peering synchronization channel, two-way hot backup is supported.

Through hot backup, you can also load all data from the master end. This can be used when a synchronization error occurs. The load permission is a separate MySQL (the best combination of PHP and PHP) Permission, so there are two permissions for MySQL (the best combination of PHP and PHP) related to hot standby, log Capture and data loading (replication slave, replication client)

Open a backup account on the server

Grant replication slave, replication client on *. * TO "backup" @ "192.168.1.2" identified by "*****" WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;

Enable log on the server

/Etc/MySQL (the best combination with PHP)/my. cnf

[MySQL dual-machine Hot Standby (the best combination with PHP) d]

 
 

  
  
server-id = 1 
  
  
log-bin 
  
  
binlog-do-db = pa 
  
  
max_binlog_size = 104857600 
 
 

In this way, MySQL (the best combination with PHP) will place the Update log of the database pa in the data directory. Wait for the slave to capture the data.

Client settings:

/Etc/MySQL (the best combination with PHP)/my. cnf

 
 

  
  
master-host=192.168.1.1 
  
  
master-user=backup 
  
  
master-password=12345 
  
  
master-port=3306 
  
  
master-connect-retry=60 
 
 

The replicate-do-db = pa client captures the Update log of the pa database on the server to update the local pa database.

Several MySQL (best combination with PHP) commands related to Hot Standby: (you need to use the command line interface or query in MySQL (the best combination with PHP)

Stop slave # stop Synchronization

Start slave # start synchronization and update from the log termination location.

SET SQL _LOG_BIN = 0 | 1 # run on the host. The super permission is required to enable or stop the log. If the log is enabled or disabled at will, the data on the host slave will be inconsistent, resulting in errors.

Set global SQL _SLAVE_SKIP_COUNTER = n # Run the client to skip several events. It can be executed only when the synchronization process stops when an error occurs.

Reset master # run on the host to clear all logs. This command is the original flush master.

Reset slave # Run from the machine, clear the log synchronization location mark, and regenerate master.info

Although master.info is re-generated, it cannot be used. It is best to restart the MySQL (the best combination with PHP) Process on the slave machine,

Load table tblname from master # run on the slave machine. The data in the specified TABLE can be re-viewed FROM the host. Only one TABLE can be read at a time. Due to the time limit of timeout, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.

Load data from master # Run FROM the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.

Change master to master_def_list # online CHANGE of some host settings. Separate multiple settings with commas (,). For example

 
 

  
  
CHANGE MASTER TO 
  
  
MASTER_HOST='master2.mycompany.com', 
  
  
MASTER_USER='replication', 
  
  
MASTER_PASSWORD='bigs3cret' 
 
 

MASTER_POS_WAIT () # Run from the slave machine

Show master status # Run the host and view the log export information

Show slave hosts # Run the host to check the connected SLAVE machine.

 
 

  
  
SHOW SLAVE STATUS (slave) 
  
  
SHOW MASTER LOGS (master) 
  
  
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] 
  
  
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date' 
 
 

Q & A Time:

How to configure MySQL dual-machine hot standby? Configure a reverse update as shown above.

You don't have to worry that the local machine changes will be rolled back, because server_id is used to identify this.

How to implement multi-machine hot standby? Several MySQL (the best combination with PHP) servers are like snakes connected at the beginning and end to form a ring assembly, in addition, you can also make several one-way updates to relieve the read operation pressure such as select, because most of the MySQL (best combination with PHP) operations are