MySQL database dual-Machine hot backup

MySQL database dual-Machine hot backup 1, MySQL database does not have incremental backup mechanism

Backup is a big problem when the amount of data is too large. Fortunately, MySQL database provides a mechanism of master-slave backup, in fact, all the data of the primary database is written to the backup database at the same time. Implement a hot backup of the MySQL database.

2, to realize the dual-machine hot-standby first to understand the master-slave database server version requirements.

To achieve a hot standby version of MySQL is higher than 3.2, there is also a basic principle is that the database version from the database can be higher than the version of the primary server database, but not lower than the primary server version of the database.

3. Set the primary database server: A. First look at the version of the master server

First, check to see if the version of the master server is a hot standby version. Then look at my.cnf ( Unix-like) or My.ini (windows) mysqld configuration block configuration There is no Log-bin (logging database change log), because the replication mechanism of MySQL is a log-based replication mechanism, So the primary server must support the change log. Then set up the database to write to the log, or do not write to the log. This only changes to the database that you are interested in will be written to the log in the database.

B. Configuration1.Create a synced user

Mysql> GRANT REPLICATIONSLAVE on *.*     -  to 'Repl'@'%'Identified by 'Slavepass'; 4.0.2Previous version because it is not supportedREPLICATIONto use the following statement to implement this function MySQL> GRANT FILE  on *.*     -  to 'Repl'@'%'Identified by 'Slavepass';

2. Master-slave mode: a->b

　　A is master
Modify a MySQL My.ini file. Add the following configuration to the MYSQLD configuration:

The following is the old version of the wording, I use this configuration, MySQL 5.6 Start not up,

    server-ID=1// database ID This should be the default is 1 do not change    // log file name, here can also make a log to another directory If you do not set a log name for the default host name binlog-do    // log database    //  A database that does not log logs

Since I am using the 5.6 version, the configuration is as follows:

binlog_do_db=1log_bin=f:/db-data/binlog/binlog.loglog-error =f:/db-data/ logs/mysql-1=f:/db-data/logs/mysql.log

C. Restart the database service.

Use the Show Master Status command to see the log condition.

Mysql> Show master status; +---------------+----------+--------------+------------------+-------------------+| File          | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+---------------+----------+--------------+------------------+-------------------+| BinLog. 000001 |       | Test         |                  |                   | +---------------+----------+--------------+------------------+-------------------+1 in Set ( 0.00 sec)

　　b for Slave
The old version can modify the b mysql my.ini file. Add the following configuration to the MYSQLD configuration:

　　

Server-Id=2master-host=115.29. 36.149 #同步用户帐号master-User=Cmaster-Password=Cmaster-Port=3306#预设重试间隔60秒master-Connect-Retry= -#告诉slave只做backup数据库的更新Replicate-Do-Db=Test binlog_ignore_db=Mysql

But MySQL version from 5.1.7 does not support the "master-host" similar parameters;

Helpless, from the database MY.CNF modified to the following configuration; (Note ID 1 in the primary database and join the library to be synchronized)

Server-id=2

Then execute the command from the library:

 to Master_host='***.**.**.***', master_user='  xx', Master_password='xx', master_connect_retry=  ;

Lock the existing database and back up the current data

D. Locking the database

Mysql> FLUSH TABLES with READ LOCK;

Back up the database there are two ways to go directly to the MySQL data directory and then package you need to back up the database folder, the second is to use the mysqldump way to back up the database but to add "--master-data" this parameter, We recommend that you use the first method to back up the database

C. Viewing the status of the primary server

MySQL database dual-Machine hot backup