MySQL Backup restore

MySQL Backup restore 1.1 Backup Recovery-Description

the core of operational work is simply a summary of two things:

The first: is to protect the company's data. The second: is to allow the site to provide 7* 24 Hours of service (user experience).

Introduction to 1.1.1 Backup

Logical Backup (Text representation: SQL statement) physical backup (binary copy of data file) snapshot-based backup incremental backup based on replication (flush binary log)

1.2 Mysqldump Backup Tool detailed

MySQL native comes with a very useful logical backup tool

1.2.1 Mysqldump Common parameters

Back up the Xxx.sql file that is created with the updated SQL statement.

-A; --all-databases full Library backup parameters- B : It is recommended that you add this parameter to one or more libraries (table)
Note:    ? A and B should not be used at the same time. ?    -B automatically adds the Create and use statements in the backup. SQL, if not-B need to go to MySQL to create libraries and use libraries. -d table-only structure-T data only--compact reduces useless data output (Debug)-R,--routines backing up stored procedures and function data--triggers two parameters for backup trigger data redundancy-l use for single-table backup-X used when making a full-library backup--master-data={1|2  tells you the Binlog location after the backup time. 2 Notes 1non-commented, to be performed (master-slave copy)--single- transaction only supports INNODB engines and supports hot standby. A single transaction is opened for backup, which is implemented using snapshot technology for transactions. 

1.2.2 Logical Backup-sql

#Export a single database (recommended plus b)[[email protected] data]#mysqldump-uroot-p123456-b World >/tmp/world.sql#export multiple databases (must plus B)[[email protected] data]#mysqldump-uroot-p123456-b World MySQL >111.sql#export a single database table (cannot add B)[[email protected] data]#mysqldump-uroot-p123456 World City >111.sql#Export all databases (-A or--all-databases)[[email protected] data]#mysqldump-u root-p123456-a >all.sql#Backup-General parameters (Binlog location, hot standby-snapshot)[[email protected] data]#mysqldump-a-R--triggers--master-data=2--single-transaction |gzip >/opt/all_$ (date +%F). sql.gz

1.2.3 Incremental Backup-binlog

Incremental Data is the data that was updated by the database after the last full backup of the data until the next full

For mysqldump, binlog is the incremental data.

Incremental backup implementation

#preparatory workOpen Binary Log vi/etc/My.cnflog-bin=/data/mysql/mysql-bin#in the global open BinlogSet sql_log_bin=0#modify at session level to temporarily close#Full BackupMysqldump-b--master-data=2--single-transaction oldboy|gzip>/data/backup/oldboy_$ (Date +%F). sql.gz#Incremental Backup (the Binlog->.sql file will be followed)Zcat oldboy_2017-06-22.sql.gz >oldboy_2017-06-22. sqlsed-N'22p'Oldboy_2017-06-22.sql#View number#binlog->.sql Filemysqlbinlog-d Oldboy--start-position=339 oldboy-bin.000008-r bin.sql

1.2.4 Restore (Restore)

 French one : source (recommended) #Add +b optionMysql> source/tmp/World.sql#do not add +b option[[Email protected] ~]#mysql-uroot-p66666Mysql>CREATE DATABASE Xzy;mysql>Use xzymysql> source/tmp/World.sql Method two : command line<MySQL>CREATE DATABASE Xzy; [[Email protected]~]#mysql-uroot-p666666 Xzy </tmp/world.sql

1.3 Xtrabackup Backup Tool detailed 1.3.1 Xtrabackup introduction

Xtrabackup is a Percona company CTO Vadim to participate in the development of a InnoDB based on Open source , free , support online hot standby , backup recovery fast , taking advantage of small disk space, etc.

Xtrabackup Hot Preparation Principle (guaranteed data consistency)-INNODB engine

At the time of the data modification, the data pages in the data file are backed up, and the redo and undo in the backup process are prepared (rolled back or rolled forward). When recovering, the data page LSN is guaranteed to match the redo LSN to achieve consistency.

For the MyISAM table, the automatic lock table copy file is implemented.

Comparison of characteristics between 1.3.2 Mysqldump and Xtrabackup

Mysqldump Insufficient:

The mysqldump backup method is a logical backup, the biggest drawback is the slow backup and recovery, if the database is larger than 50g,mysqldump backup is not suitable.

Xtrabackup Advantages

1) Fast backup speed, reliable physical backup

2) The backup process does not interrupt the executing transaction (no lock table required)

3) Ability to save disk space and traffic based on functions such as compression

4) Automatic backup check

5) Fast restore speed

6) can be circulated to transfer the backup to another machine

1.3.3 Installation

# Install the dependent package yum-y Install perl perl-devel libaio libaio-devel perl-time-hires perl-dbd-MySQL#  Download the installation package wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/ Percona-xtrabackup-24-2.4.4-1. El6.x86_64.rpmyum localinstall Percona-xtrabackup-24-2.4.4-1.el6.x86_64. Rpm

1.3.4 xtrabackup Backup Recovery

1. Backup #first time-full-scale backupInnobackupex--defaults-file=/etc/my.cnf--user=root--password=oldboy123--socket=/application/mysql-5.6.34/tmp/ Mysql.sock--no-timestamp/server/backup/Base_full#First incremental backup-(based on first-time full amount)Innobackupex--defaults-file=/etc/my.cnf--user=root--password=oldboy123--socket=/application/mysql-5.6.34/tmp/ Mysql.sock--no-timestamp--incremental-basedir=/server/backup/base_full--incremental/server/backup/One_inc#Second incremental backup, (based on first increment)Innobackupex--defaults-file=/etc/my.cnf--user=root--password=oldboy123--socket=/application/mysql-5.6.34/tmp/ Mysql.sock--no-timestamp--parallel=3--incremental-basedir=/server/backup/one_inc/--incremental/server/backup/Two_inc2. Prepare for incremental recovery: #Application full log (--apply-log, do not need to do rollback operation temporarily--redo-only)Innobackupex--apply-log--use-memory=32m--redo-only/server/backup/base_full/#Merge first increment to full standbyInnobackupex--apply-log--use-memory=32m--redo-only--incremental-dir=/server/backup/one_inc/server/backup/base_ full/#Merge second increment to full standby (without--redo-only)Innobackupex--apply-log--use-memory=32m--incremental-dir=/server/backup/two_inc/server/backup/base_full/#Innobackupex--apply-log/backup/ Full#confirm Binlog starting pointcat/backup/xbackup/inc2/xtrabackup_binlog_info MySQL-bin.000001 1121#intercept the Binlog before the drop operationMysqlbinlog--start-position=1121/tmp/mysql-bin.000003find the event and postion number before the drop to do the log interception, if1437Mysqlbinlog--start-position=1121--stop-position=1437/tmp/mysql-bin.000003 >/tmp/Incbinlog.sql shutting down the database/etc/init.d/mysqld StopDeletemove MySQL all data CD/application/mysql/data/RM-RF *Recover Data Recovery xtrabackup data Innobackupex--copy-back/backup/xbackup/full/Chown-R mysql.mysql/application/mysql/data//etc/init.d/mysqld start recovery binlogset sql_log_bin=0source/tmp/incbinlog.sql

MySQL Backup restore