How to configure MySQL database dual-host hot backup _ MySQL

How to configure dual-machine hot backup for MySQL database 1. there is no incremental backup mechanism for mysql database. backup is a big problem when the data volume is too large. Fortunately, the mysql database provides a master-slave backup mechanism, which is to write all the data of the master database to the backup database at the same time. Achieve hot backup of mysql database.

2. to achieve dual-host hot standby, you must first understand the version requirements of the master-slave database server. To enable hot standby mysql versions, both must be higher than 3.2. another basic principle is that the database version of the slave database can be higher than that of the master server database, but it cannot be earlier than the database version of the master server.

3. set the master database server:

A. check whether the master server version supports hot standby. View my. cnf (unix-like) or my. in ini (windows), does the configuration of mysqld configuration block have log-bin (record database change logs), because mysql replication mechanism is log-based replication mechanism, therefore, the master server must support log changes. Set the database to which logs are to be written or the database to which logs are not to be written. In this way, only changes to the database you are interested in are written into the database log.

Server-id = 1 // database id. the default value is 1 and no changes are required.

Log-bin = log_name // the name of the log file. you can specify a log name for the default host name if it is not set to another directory.

Binlog-do-db = db_name // The database that records logs

Binlog-ignore-db = db_name // database without logs

If multiple databases are separated by commas (,), set the user account for database synchronization.

Mysql> grant replication slave on *.*

-> TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';

In versions earlier than 4.0.2, because REPLICATION is not supported, use the following statement to implement this function.

Mysql> grant file on *.*

-> TO 'repl' @ '% .mydomain.com' identified by 'slavepass ';