MySQL Data backup and recovery

First, MySQL data backup and recovery
1. Data backup method:
Physical backup: CP Tar mysqlhotcopy
Logical backup: mysqldump MySQL

Backup policy:
Full backup differential backup incremental backup

Common backup combinations:
Full backup + differential backup
Full backup + Incremental backup


2. Full backup
Disadvantage: Unable to recover the data generated after the full backup, the data can only be restored to the state of the backup.

Example 1
Backup: Mysqldump-hlocalhost-uroot-p Source Library name > Path/xxx.sql
Recovery: mysql-hlocalhost-uroot-p Target Library name < path/xxx.sql

The representation of the source library name:
All libraries:--all-databases
Specify a single library: library name
Specify a single table: the Library Name Table name (note: There is no in the middle.)
Backing up multiple libraries:-B Library 1 Library 2


3. Incremental backup
Start MySQL binlog log for incremental backup of data)
Binary log (binlog log) records SQL statements that change data (except for SQL statements other than queries)

3.1 Enable logging methods:
Vim/etc/my.cnf
[Mysqld]
Log-bin
#log-bin= Log Name
#log-bin= directory Name/log name
Service MySQL Restart
Note:
Default directory/var/lib/mysql/
Log name: hostname-bin.000001 (when the log volume is greater than 500M automatically creates a new log)
Xxx-bin.index record the current binlog log file name

3.2 Viewing the contents of the log:
Mysqlbinlog Host name-bin.000001
Mysqlbinlog Host Name-bin.000001 | Mysql-hip-u User name-p password database name

3.3 Create a new Binlog log manually
Flush logs;
Mysql-hip-u User name-p password-e "flush Logs"
Mysqldump-hip-u User name-p password--flush-logs database name > backup file name
Service MySQL Restart

3.4 Recovering data based on logs
The default Mysqlbinlog command reads the contents of the file from the beginning to the end
Syntax format
Mysqlbinlog Host Name-bin.000001 | mysql-uroot-p999
Mysqlbinlog $ (Cat/var/lib/mysql/mail-bin.index) | mysql-uroot-p999

Options
Character offset
--start-position=num Start offset
--stop-position=num End Offset

Mysqlbinlog--start-position=310 Host name-bin.000001
Mysqlbinlog--stop-position=578 Host name-bin.000001
Mysqlbinlog--start-position=201--stop-position=740 mail-bin.000004

Point in time
--start-datetime= "Yyyy-mm-dd hh:mm:ss" Start time
--stop-datetime= "Yyyy-mm-dd hh:mm:ss" End time


Mysqlbinlog--start-datetime= "2014-11-11 23:17:51"--stop-datetime "=2014-11-11 23:17:52"/var/lib/mysql/mail-bin.00 0006 | mysql-uroot-p999

3.5 Delete Binlog logs older than the specified version
PURGE MASTER LOGS to ' Binlog file ';
PURGE MASTER LOGS to "mail-bin.000004";

Delete all Binlog logs, rebuild new log
RESET MASTER;

This article is from the "Zhengerniu" blog, make sure to keep this source http://liufu1103.blog.51cto.com/9120722/1656834

MySQL Data backup and recovery