Mysql real time backup implementation method

At present, the mature real-time backup is dual-machine (master/slave), which is based on synchronous log events. How can a single machine achieve incremental backup? The principle of dual machine can be borrowed, which is very simple. The implementation steps are as follows:
Mysql version: mysql4.0+

1, vi my cfg

[mysqld]

log-update=/home/backup/update # Add the bank 

service mysql restart

In/home/backup/update00001 files, all SQL content changes for the database (no select)

3, complete every day, es28EN4.0 + the simplest is to back up data directory.

service mysql stop

tar -czf data( The date of ).tar.gz mysql/data

service mysql start

When mysql starts up, the system will automatically create update0000* files in /home/backup/, so we can use this file as a full and real-time incremental backup for the day.

4. Data restoration

service mysql stop

tar -zxvf data( The date of ).tar.gz mysql/

service mysql start

mysqladmin -u -p /home/backup/update0000*

If you want to restore the data of yesterday and the day before yesterday, you only need to find the corresponding update0000* to restore :)

The following is the supplement:

1. MYSQL database provides a master-slave backup mechanism, which is to write all the data of the master database into the backup database at the same time, so as to realize the real-time backup of MYSQL database.
2. Version requirements: First of all, make sure that the MYSQL version of the master server and the slave server are both higher than 3.2; in addition, the slave database version can be higher than the master server, but not lower than the master server.
3. Primary server Settings:
First, modify the setting of ES60en-ES61en in ES58en.ES59en. This is the log that records database changes. Since the replication mechanism of MYSQL is based on logs, the primary server must support the change log.
You then set up the database to write to, or not to write to, to tell MYSQL which library needs to be backed up and which does not.
Here are the configuration details:

server-id =1 // database id this should be 1 by default
log-bin =log_name // The name of the log file. Here you can specify the log to another directory. If not set, the default hostname is 1 log name
binlog-do-db =db_name // Database for logging
binlog-ignore-db =db_name // A database that does not log

The above binlog-ES91en-ES92en and ES93en-ES94en-ES95en can be set up as multiple databases, with each database name separated by ", ".
The next step is to set up the user account for the synchronous database
mysql > GRANT REPLICATION SLAVE ON *.*
- > TO 'backup username '@' can only login' IDENTIFIED BY 'backup username' from this IP;

After setting up, restart 1 database service.
B, locks existing data and backs it up
The database lock command is:
mysql > FLUSH TABLES WITH READ LOCK;
Then go to data's data directory and package the database directory you need to back up.
Now you can check the status of the primary server:
The order is as follows:
mysql > show master status\G;
The return result will look something like this
+ + - - - - - - - - - + + -- -- -- -- -- � +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ + - - - - - - - - - + + -- -- -- -- -- � +
| mysql-bin.003 | 73 | test | manual,mysql |
+ + - - - - - - - - - + + -- -- -- -- -- � +
This table, of course, shows the configuration that you just wrote in ES136en.INI.
Then unlock the database:
mysql > UNLOCK TABLES;

4, from the server Settings
Again, change the database configuration file, MY.INI
Configuration details are as follows:
server-id =n // Setting database id default primary server is 1 you can set it arbitrarily but it cannot be repeated if there are multiple slave servers.
master-host = ES157en-ES158en.mycompany.com // the IP address or domain name of the primary server
master-port=3306 // Port number of the primary database
master-user =pertinax // Sync database users
master-password =freitag // Password for synchronization database
master-connect-retry =60 // The time difference between reconnecting if the slave server discovers that the master server is down
report-host = ES180en-ES181en. mycompany. com // Server reporting error

Then copy the database file you just packaged into your slave database directory.
Restart from the database server.
Then stop SLAVE's service

mysql > slave stop; // Stop slave's services

After you stop, again at the mysql prompt, set the various parameters for the primary server
The order is as follows:
mysql > CHANGE MASTER TO
- > MASTER_HOST='master_host_name', // IP address of the primary server
- > MASTER_USER='replication_user_name', // Sync database user
- > MASTER_PASSWORD='replication_password', // Synchronize database password
- > MASTER_LOG_FILE='recorded_log_file_name', // The file name of the primary server 2 base log (previous parameter to remember)
- > MASTER_LOG_POS=recorded_log_position; // The start location of the log file (the previous parameter to remember)

Then start the process that synchronizes the database
mysql > slave start;

If there are no surprises, basically by this step, double library synchronization has been achieved.