Several ways to back up in MySQL

Objective:

Not every company is tall, not every company will use some very cutting-edge technology to do backup this piece, some enterprises or some industries or the team itself for various reasons use simple or complex way to do backup this piece, this document is a summary of the previous work, Take a look at the pros and cons of various backup methods

1 mysqldump

Common Backup Parameters

-R--events--triggers=true--single-transaction--master-data=2

-R (--routines): Export stored procedures and custom functions

--events: Exporting Events

--triggers=true: Export trigger. Enabled by default, disabled with--skip-triggers

--single-transaction: This option submits a BEGIN SQL statement before exporting the data, and begin does not block any applications and guarantees consistent state of the database everywhere, and is mutually exclusive to--lock-tables. Because lock Tables causes any pending transactions to be implicitly committed

--master-data=2: This option appends the location and file name of the Binlog to the output file, if 1 will output the Change Master command, and if 2, the output of the Change Master command money to add comment information. This option turns on the--lock-all-tables option, unless--single-transaction is also specified (in which case the global read lock gets a short time to start the export), which automatically turns off the--lock-tables option

#/opt/app/mysql/bin/mysqldump-uroot-p-R--events--triggers=true--single-transaction--master-data=2 sakila > Sak Ila.sql

2 Data Catalog copy----This slightly

3 Xtarbackup-based backup

RPM-UHV http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Yum-y Install Percona-xtrabackup Post-installation packages are [Email protected]_03 bin]# RPM-QL percona-xtrabackup /usr/bin/innobackupex /usr/bin/xbcloud /usr/bin/xbcloud_osenv /usr/bin/xbcrypt /usr/bin/xbstream /usr/bin/xtrabackup /usr/share/doc/percona-xtrabackup-2.3.3 /usr/share/doc/percona-xtrabackup-2.3.3/copying /usr/share/man/man1/innobackupex.1.gz /usr/share/man/man1/xbcrypt.1.gz /usr/share/man/man1/xbstream.1.gz /usr/share/man/man1/xtrabackup.1.gz

Specific usage

1 Creating a backup user

Mysql> Grant Reload,lock tables,replication Client on * * to ' dbbak ' @ ' localhost ' identified by ' bk2016 ';
mysql> flush Privileges;
Make the database fully prepared

Mkdir-pv/data/dbbak

Cd/data/dbbak

Use the following parameters for a full-library backup

[Email protected]_03 dbbak]# Innobackupex--user=dbbak--password=bk2016/data/dbbak/

A timestamp-based directory will be generated in the Dbbak directory

2016-02-03_15-48-02

There is a file in this directory

[Email protected]_03 dbbak]# Cat 2016-02-03_15-48-02/xtrabackup_checkpoints Backup_type = full-backuped FROM_LSN = 0 TO_LSN = 1095181486 LAST_LSN = 1095181486 Compact = 0 Recover_binlog_info = 0

Incremental backup create a table in a library (the build table will cause the LSN number to change)

[Email protected]_03 dbbak]# Innobackupex--user=dbbak--password=bk2016--incremental/data/dbbak/-- incremental-basedir=/data/dbbak/2016-02-03_15-48-02/

Generate a new directory

2016-02-03_16-01-28

Compare this checkpoint

[Email protected]_03 dbbak]# Cat 2016-02-03_16-01-28/xtrabackup_checkpoints Backup_type = Incremental FROM_LSN = 1095181486 TO_LSN = 1095181486 LAST_LSN = 1095181486 Compact = 0 Recover_binlog_info = 0

4 MySQL Enterprise-class backup Mysqlbakcup

MYSQLBACKUP--USER=DBA--password--port=3306--with-timestamp--backup-dir=/export/backups Backup

5 LVM-based snapshot backup---slightly

Several ways to back up in MySQL