Mysql dual-machine hot standby practice-an Heng network manager note [figure] _ MySQL

Mysql dual-machine hot standby practice-an Heng network manager's note [figure] network manager

Mysql implements dual-machine hot standby. The principle is to use mysql's SELECT File operation function to capture host update logs in real time. of course, this is only the principle, in fact, we don't need to process logs by ourselves. after understanding the principles, 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, when dual-machine hot backup was implemented, the file permission was used.

From mysql4, a special permission was added for hot backup. this permission should be essentially a file read permission, but 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 permission. in this way, the mysql related to hot standby has two permissions, log capturing 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/my. cnf
[Mysqld]
Server-id = 1
Log-bin
Binlog-do-db = pa
Max_binlog_size = 104857600
------------------------------------------ In this way, mysql will place the update log of the pa Library in the data directory. Wait for the slave to capture the data.

Client settings:
/Etc/mysql/my. cnf
Master-host = 192.168.1.1
Master-user = backup
Masters-password = 12345
Master-port = 3306
Master-connect-retry = 60
Replicate-do-db = pa

The client captures the update log of the pa database on the server to update the local pa database.

Several mysql commands related to hot standby: (the mysql command line interface or query is required)

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. this will cause data inconsistency between the host and slave, cause error set global SQL _SLAVE_SKIP_COUNTER = n # run the client to skip several events. it can be executed only when the synchronization process stops due to an error. Reset master # run on the host to clear all logs. This command is the original flush master reset slave.

# Run from the server, clear the log synchronization location mark, and regenerate master.info