Simple Dual-machine hot backup solution for MySQL database

This article details how to implement dual-machine hot backup for MySQL databases.

1. There is no Incremental backup mechanism for MySQL databases. 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. You do not need to change the name of log-bin = log_name // log file, // you can specify the log to another directory. If no log is set, binlog-do-db = db_name // binlog-ignore-db = db_name/ /databases that do not record 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';

4. MySQL versions earlier than, because REPLICATION is not supported, use the following statement to implement this function:

MySQL> GRANT FILE ON *.*-> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

Set the configuration file of the master server and restart the database.

1 2 3 Next Page

[Content navigation]
Page 1st: Simple Dual-machine hot backup solution for MySQL Databases	Page 2nd: Simple Dual-machine hot backup solution for MySQL Databases
Page 3rd: Simple Dual-machine hot backup solution for MySQL Databases