MySQL backup and recovery mysqldump+binlog xtrabackup

How data is backed up:

Physical backup: The directory where the repository and table files are copied CP tar

Logical backup: Backing up SQL statements when creating libraries and tables when inserting records

Data backup strategy:

Full backup: Backs up all libraries, all tables, on the database server.

Differential backup: The new data that is generated after a full backup is backed up.

Incremental backup: Backs up new data that has been generated since the last backup.

Full backup + differential backup

Full backup + Incremental backup

Mysqldump do a full backup

Mysqldump-hser_ip-uusername-ppassword Data name > directory/x.sql

Database backup file ends with. sql

[Email protected] ~]# mysqldump-hlocalhost-uroot-p20140103 mysql >/root/mysql.sql

How the database name is represented:

Database name: Backs up all tables in this database.

--all-databases: Backs up all tables in all databases on the server.

Database Name Table name: Backs up a table in a database.

-B Database Name 1 database name 2 database name N: Make a backup of a few databases.

The backup file name cannot be duplicate, or the previous backup will be overwritten with the current backup.

Data recovery:

Mysql-hser_ip-uusername-ppassword Data Name < backup file name

Using MySQL's Binlog log for incremental backups

records that are executed on the database server. SQL statement that makes the data change .

Enable Binlog log (binary log)

[[email protected] ~]# Vim/etc/my.cnf[mysqld]log-bin=/binlog/plj-bin.log Specify the directory and file name of Binlog

MySQL user must have W permissions to the/binlog directory

When specifying a path, the path and file name must be fully written if the path is changed

Cd/var/lib/mysqllocalhost-bin.000001localhost-bin.index

format: mysqlbinlog [Options] Binlog file

Recovering data using Binlog logs

mysqlbinlog binlog File | mysql-hlocalhost-uroot-p

Recording mode: [Options]

Offset amount

--start-position= Start Position

--stop-position= End Position

Point in time

--start-datetime= ' yyyy-mm-dd HH:MM:SS '

--stop-datetime= ' Yyyy-mm-dd HH:MM:SS '

To manually generate a new Binlog log

Restart the database service (not recommended);

mysql-hlocalhost-uroot-ppassword-e "Flush Logs"

-e Execute SQL statement at command line

mysqldump-hlocalhost-uroot-ppassword--flush-logs Library name > X.sql

Delete Binlog Log

In the state of landing purge master logs to ' binlog filename ' ; // Delete all binlog files before the specified Binlog file.

reset Master; Delete all the Binlog files

Xtrabackup third-party software to do data backup

Mysqldump lock table when backing up and recovering

Binlog logs need to see for themselves which record to recover from and recover to

Xtrabackup Open source, can be hot online backup, in the backup will not lock the library and table

Main components

X TRABACKUP:C Program, support INNODB/XTRADB

Innobackupex: Xtrabackup is encapsulated in Perl scripts and also supports MyISAM ability to do full and incremental backups

Syntax format

xtrabackup_56 Options

option determines what action the command will perform

Options:

--backup Performing a backup operation

--target-dir destination folder for backup

--datadir Original folder for backup (MySQL database location)

--prepare Preparing to recover data

--incremental-basedir incremental backup, specify the path of the full backup for the reference

--incremental-dir when preparing to reply to the directory, specify the path of the incremental backup

Complete backup with xtrabackup (table storage Engine if innodb)

back up only the data in the table you must ensure that the table exists when the table structure is restored.

Full backup

[Email protected] ~]# xtrabackup_56--datadir=/var/lib/mysql/--backup--target-dir=/dbbak/first

Recovery log Information

[Email protected] ~]# xtrabackup_56--prepare--target-dir=/dbbak/first/--datadir=/var/lib/mysql/

Copying data files

[Email protected] ~]# CP/DBBAK/FIRST/DB100/A.IBD/VAR/LIB/MYSQL/DB100/A.IBD

Incremental and incremental recovery with xtrabackup

First full backup [[email protected] ~]# xtrabackup_56--datadir=/var/lib/mysql/--backup--target-dir=/dbbak/first/ First incremental backup after full backup [[email protected] ~]# xtrabackup_56--backup--datadir=/var/lib/mysql/--incremental-basedir=/dbbak/ First--target-dir=/dbbak/two/second incremental backup after full backup [[email protected] ~]# xtrabackup_56--backup--datadir=/var/lib/mysql/-- Incremental-basedir=/dbbak/two--target-dir=/dbbak/three/in turn

Incremental recovery

[Email protected] ~]# xtrabackup_56--prepare--target-dir=/dbbak/first/[[email protected] ~]# xtrabackup_56--prepare --target-dir=/dbbak/first--incremental-dir=/dbbak/two/[[email protected] ~]# xtrabackup_56--prepare--target-dir=/ Dbbak/first--incremental-dir=/dbbak/three/

Copy data

[Email protected] ~]# CP/DBBAK/FIRST/DB200/A.IBD/VAR/LIB/MYSQL/DB200/A.IBD

Check that the data is back to normal

MySQL backup and recovery mysqldump+binlog xtrabackup