Implementation of Mysql real-time backup and mysql real-time backup

Implementation of Mysql real-time backup and mysql real-time backup

Currently, mature real-time backup is based on synchronization log events. How can a single machine implement Incremental backup? The dual-host mechanism can be used, which is very simple. The implementation steps are as follows:
Mysql version: mysql4.0 +

1. vi my. cfg

Copy codeThe Code is as follows:
[Mysqld]
Log-update =/home/backup/update # Add this row

2. service mysql restart

In the/home/backup/update00001 file, the content is all the SQL statements with database changes (no select)

3. For full backup every day, mysql4.0 + is the simplest to back up the data directory.

Copy codeThe Code is as follows:
Service mysql stop
Tar-czf data(date.tar.gz mysql/data
Service mysql start

When mysql is started, the system will automatically create an update0000 * file in/home/backup/. This file can be used as an incremental real-time backup for full backup on the current day.

4. data restoration
Copy codeThe Code is as follows:
Service mysql stop
Tar-zxvf data(date.tar.gz mysql/
Service mysql start
Mysqladmin-u-p/home/backup/update0000 *

To restore data from yesterday and the day before yesterday, you only need to find the corresponding update0000 * to restore the data :)

The following is a supplement:

1. 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 to implement real-time backup of the MYSQL database.
2. version requirements: ensure that the MYSQL version of the master server and slave server is 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. master server settings:
A. modify MY first. the setting of log-bin in INI is the log that records database changes. because MYSQL replication mechanism is based on logs, the master server must support log changes.
Next, set the database for which logs are to be written or the database for which logs are not to be written. This is to tell MYSQL which database needs to be backed up and which one does not need to be written.
The configuration details are as follows:

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

The preceding binlog-do-db and binlog-ignore-db can be set to multiple databases. The names of each database are separated by commas.
The next step is to set the user account for database synchronization.
Mysql> grant replication slave on *.*
-> TO 'backup Username '@', you can only log on TO 'identified BY 'backup user password' from this IP address ';

After the settings, restart the database service.
B. Lock existing data and back up the data
The database lock command is:
Mysql> flush tables with read lock;
Enter the mysql data DIRECTORY, and then package the database directory you want to back up.
C. Now you can view the status of the master server:
The command is as follows:
Mysql> show master status \ G;
The returned result is like this.
+ ----- + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ----- + ---- + ----- + ------ +
| Mysql-bin.003 | 73 | test | manual, mysql |
+ ----- + ---- + ----- + ------ +
Of course, this table shows the configuration you just wrote in MY. INI.
Then unlock the database:
Mysql> unlock tables;

4. slave server settings
Modify the database configuration file, that is, MY. INI.
The configuration details are as follows:
Server-id = n // set the database id. The default master server is 1, which can be set at will, but it cannot be repeated if there are multiple slave servers.
Master-host = db-master.mycompany.com // ip address or domain name of the master server
Master-port = 3306 // The port number of the master database
Master-user = pertinax // the user who synchronizes the database
Master-password = freitag // password for Database Synchronization
Master-connect-retry = 60 // time difference between the reconnection if the master server is disconnected from the slave server
Report-host = db-slave.mycompany.com // server that reports errors

Then copy the database file you just packed to your slave database directory.
Restart the slave database server.
Then stop the SLAVE service.

Mysql> slave stop; // stop the slave Service

After it is stopped, set the parameters of the master server at the mysql prompt.
The command is as follows:
Mysql> CHANGE MASTER
-> MASTER_HOST = 'master _ host_name ', // ip address of the master server
-> MASTER_USER = 'replication _ user_name ', // Database Synchronization user
-> MASTER_PASSWORD = 'replication _ password', // password for Database Synchronization
-> MASTER_LOG_FILE = 'recorded _ log_file_name ', // File Name of the binary log of the master server (the parameter to be remembered earlier)
-> MASTER_LOG_POS = recorded_log_position; // the start position of the log file (the parameters that need to be remembered earlier)

Start the Database Synchronization Process.
Mysql> slave start;

If nothing happens, the dual-Database Synchronization has been implemented.

Articles you may be interested in:

• Quick Setting of mysql master-slave Synchronization
• Master-slave synchronization backup procedure for mysql Databases in windows (one-way synchronization)
• Mysql master-slave synchronous backup policy sharing
• Install two mysql instances in win2003 and configure the synchronization service.
• An example of mysql master-slave synchronous replication error Solution
• Steps for configuring MySQL master-slave synchronization and read/write splitting
• How to quickly configure mysql master-slave synchronization in centos
• Configuration instance for master-slave synchronization of the mysql database server in linux
• MySQL 5.5 master-slave synchronization settings Note sharing
• How to solve the Relay log read failure error in MySQL5.1 master-slave Synchronization
• Use Shell scripts to monitor MySQL master-slave Synchronization
• Principles of MySQL master-slave Synchronization