MySQL database backup, recovery documentation

Description

In order to enhance the security of the online database, to avoid the misuse of research and development personnel resulting in data loss, the production of this document. First-line operators can refer to!

First, data backup:

Dedicated database backup server, scheduled to hot-spare, cold the database, that is, master-slave settings, mysqldump cold, mysql-bin-log log backup.

1. Master and slave settings and primary and Standby switching: (Please refer to the documentation: MySQL master-slave configuration && keepalived-based primary and standby switching http://swht1278.blog.51cto.com/7138082/1716812)

2. Database cold: Use mysqldump for Cold backup

Mysqldump-h 10.124.x.x-u user-p Passwd--all-databases >/opt/data/all_databases.sql

#mysqldump--host=x.x.x.x--port=port--user=user--password Passwd--all-databases--events--routines--master-data=2 > Dump.sql

3.bin-log Log Backup:

1) Turn on the Binlog function:

Vim/etc/my.cnf

=================================

Log_bin =/var/log/mysql/mysql-bin.log

Expire_logs_days = 10

Max_binlog_size = 100M

=================================

Service MySQL Restart

2) Binlog log backup:

From database: Stop slave state:

mysql-h[from library IP]-u user-p

Mysql>stop slave;

Main database: Tar czf mysql_m_binlog_$date.tar.gz/home/mysql/data/mysql-bin*

Scp-p Port mysql_m_binlog_$date.tar.gz [email protected][backup Server]:/data/mysql/

3) Binlog Backup with Mysqlbinlog tool

Mysqlbinlog--read-from-remote-server--host=x.x.x.x--port=port--user=user--password= $passwd--result-file=/ backup/--raw--stop-never mysql-bin.000001

Note: The--read-from-remote-serve parameter indicates that a second binary log is requested to connect to the server;

The--raw parameter indicates a file in the output binary format rather than a text format;

The--stop-never parameter keeps the backup command connected for continuous backup (by default, the backup is completed and then disconnected);

The--result-file=/backup/parameter specifies that the file is stored under the specified directory/backup/mysql-bin.000001, and the log file name is not changed by default, if--result-file= is used X will output xmysql-bin.000001, so this parameter also has the function of modifying the file prefix;

4) Automate regular backup scripts:

... Subsequent updates

Second, data recovery:

Data recovery involves sequencing and involves writing data to the program, so be sure to be aware of it when recovering data. In addition, SQL files backed up by mysqldump can only recover data for a specified time period in a database, and the data is not recoverable when the structure of the database is compromised. Bin-log backup restores can be accurate to the second level of data, and the entire database structure can be preserved intact.

Binlog Data Recovery "Log rollback", if the Bin-log log is not very lost, then the likelihood of database recovery is basically not large. So for the Bin-log log backup work must pay attention to up!

1. Stop the program:

This step accesses the user calling program to write data to the database, causing great errors before and after the database, or causing problems in the structure of the database.

2. Restore the backup file using mysqldump:

Use recent backups to recover data if data loss occurs

MySQL--host=x.x.x.x-uuser-p-P Port < Dump.sql

According to Dump.sql backup file--change MASTER to master_log_file= ' mysql-bin.000018 ', master_log_pos= The binary log file name in the 27284 statement and the location re-executes the event in the binary log backup file after the coordinates.

3. Use the Mysqlbinlog tool to recover the latest Binlog log data:

If the latest binary log backup file is mysql-bin.000020, re-execute the log event as follows

Mysqlbinlog--start-position=27284 mysql-bin.000018 mysql-bin.000019 mysql-bin.000020| MySQL--host=x.x.x.x-uuser-p-P port

4. If you are using the Mysqlbinlog continuous backup method, use the following command:

Perform the following command to revert to the database state starting July 1, 2015 13 o'clock 30

Mysqlbinlog--start-date= "2015-07-01 13:30" Mysqlbinlogxxx |mysql-uuser-p

Perform the following command to revert to July 1, 2015 13 o'clock 30 database status as of

Mysqlbinlog--stop-date= "2015-07-01 13:30" Mysqlbinlogxxx |mysql-uuser-p

Perform the following command to revert to the 368312 database status as at point

Mysqlbinlog--stop-position= "368312"/home/mysql/mysql-bin.000020 | Mysql-u root-pmypwd

Perform the following command to restore the 368315 database state to the start point

Mysqlbinlog--start-position= "368315"/home/mysql/mysal-bin.000020 | Mysql-u root-pmypwd

Reference:

Mysql_binlog Backup Recovery mode: http://blog.chinaunix.net/uid-25492475-id-195432.html

MySQL data backup and recovery with Mysqlbinlog and mysqldump (for smaller data volumes): http://blog.csdn.net/zyz511919766/article/details/14229335

MySQL data recovery method by Bin-log: http://www.111cn.net/database/mysql/51632.htm

This article is from "Dolphin Watching" blog, please be sure to keep this source http://swht1278.blog.51cto.com/7138082/1718658

MySQL database backup, recovery documentation