Shulou(Shulou.com)06/01 Report--

In the last article, we introduced the principles of several MySQL data backup methods, and in this article we will discuss how to use these data backup methods. On the use of data backup strategy, all the gods have sample articles, this paper makes a simple summary and summary.

Mysqldump

Mysqldump is a very useful backup tool that comes with MySQL. It is often used for logical backup of MySQL databases. The databases in MySQL servers are exported in the standard sql language and saved to files. Its syntax is: mysqldump (option).

Commonly used grammars are:

-- add-drop-table: add a statement to delete a database table before each database table statement is created

-- add-locks: lock database tables when backing up database tables

-- all-databases: back up all databases on the MySQL server

-- comments: add comment information

-- compact: compressed mode to produce less output

-- complete-insert: outputs the completed insert statement

-- databases: specify the database to back up

-- default-character-set: specifies the default character set

-- force: continue the backup operation when an error occurs

-- host: specify the server to back up the database

-- lock-tables: lock all database tables before backup

-- no-create-db: forbids the generation of database creation statements

-- no-create-info: forbids generating statements to create database tables

-- password: password to connect to the MySQL server

-- port: Port number of the MySQL server

-- user: the user name that connects to the MySQL server.

A simple example of data backup:

Mysqldump exports the entire database:

Mysqldump-u user name-p database name > exported file name

Example: mysqldump-u linuxde-p smgp_apps_linuxde > linuxde.sql

Mysqldump exports a table

Mysqldump-u user name-p database name table name > exported file name

Example: mysqldump-u linuxde-p smgp_apps_linuxde users > linuxde_users.sql

Mysqldump exports a database structure

Mysqldump-u linuxde-p-d-- add-drop-table smgp_apps_linuxde > linuxde_db.sql

Mydumper

Mydumper is a lightweight third-party open source tool for MySQL database backup. The backup mode is logical backup and supports multi-threading. The backup speed is much higher than the original mysqldump. Usage: mydumper [OPTION...] Multi-threaded MySQL dumping

Common syntax:

-B,-- the library that database needs to back up

-T,-- tables that tables-list needs to back up, separated by commas

-o,-- directory of the outputdir output file

-s,-- statement-size generates the number of bytes of the insert statement. Default is 1000000.

-r,-- rows split into many row block tables

-c,-- compress compressed output file

-e,-- build-empty-files generates an empty file even if the table has no data

-x,-- regex regular expression: 'db.table'

-I,-- ignore-engines ignored storage engine, separated by commas

-m,-- no-schemas does not export table structure

-k,-- no-locks does not perform shared read lock warning: this will result in inconsistent backups

-l,-- long-query-guard sets long query time. Default is 60 seconds.

-kill-long-queries kill drops queries that have been executed for a long time

-b,-- binlogs exports binlog

-D,-- daemon enables daemon mode

-I,-- snapshot-interval dump snapshot interval. Default is 60s, which needs to be in daemon mode.

-L,-- logfile log file

-t,-- number of threads used by threads. Default is 4.

-C,-- compress-protocol uses a compression protocol on mysql connections

-V-- version Show the program version and exit

-v,-- verbose more output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

A simple example of data backup

1 back up a single library

# mydumper-u username-p password-B library name to be backed up-o / tmp/bak

2 backup all databases: all libraries except information_schema and performance_schema will be backed up during the full library backup

# mydumper-u username-p password-o / tmp/bak

-O directory of the output file (the directory specified by the backup output)

3 backup single table

# mydumper-u username-p password-B library name-T table name-o / tmp/bak

-T tables that need to be backed up. Multiple tables are separated by commas.-o specify the output backup file path.

4 back up multiple tables

# mydumper-u username-p password-B library name-T Table 1, Table 2-o / tmp/bak

5. The backup date and time folder is automatically generated in the current directory. If the-o parameter and value are not specified, the default is: export-20150703-145806.

Mydumper-u user name-p password-B database name-T table name

6. Back up the table without table structure

# mydumper-u username-p password-B data name-T table name-m

-m does not export the table structure

7. Back up specific tables

# mydumper-u username-p password-B database name-regex=actor*-o / tmp/bak

Back up only tables that begin with actor*

Xtrabackup

The first two tools are logical backups, while Xtrabackup is a physical backup and an alternative to the commercial backup tool InnoDB Hotbackup, and the two main tools included are xtrabackup and innobackupex. Among them, xtrabackup can only back up the tables of innodb and xtradb engines, but not the tables of myisam engine. Innobackupex is a Perl script that encapsulates xtrabackup, which can back up innodb and myisam at the same time, but a global read lock is needed when backing up myisam, and myisam does not support incremental backup.

In addition, there are two relatively niche tools are xbcrypt and xbstream. Xbcrypt is used for encryption and decryption. Xbstream, similar to tar, is a stream file format implemented by Percona itself that supports concurrent writes.

Commonly used parameters

1) innobackupex parameter options

-- defaults-file= [MY.CNF] / / specifies the configuration file: default options can only be read from a given file. And must be the first option on the command line; it must be a real file, it cannot be a symbolic link.

-- databases=# / / specifies the database and table to be backed up in the format:-- database= "db1 [.tb1] db2 [.tb2]" separated by spaces between multiple libraries. If this option is not specified, all databases will be backed up.

-- include is passed to xtrabackup-- tables.

-- tables-file=FILE / / the argument to this option needs to be a file name, and each line in this file contains the full name of the table to be backed up in the format databasename.tablename. This option is passed to xtrabackup-- tables-file, and unlike the-- tables option, only the library of the table to be backed up is created.

-- compact / / create a compact backup, ignore all secondary index pages, and only back up data page; by rebuilding the index in-- apply-log-- rebuild-indexs.

-- compress / / this option instructs xtrabackup to compress the backed-up InnoDB data file and generate a * .qp file.

-- decompress / / extract the qp file. In order to extract the file, you must install the qpress tool.

-- no-timestamp / / specifies this option that backups will be stored directly in the BACKUP-DIR directory without creating a timestamp folder.

To see more parameters, please stamp:

Https://www.percona.com/doc/perconaxtrabackup/2.4/innobackupex/innobackupex_option_reference.html

2) xtrabackup parameter options

-- apply-log-only / / this option causes only the redo phase to be performed when preparing a backup (prepare), which is important for incremental backups.

-- backup / / back up and put it into

-- check-privileges / / this option checks whether Percona XtraBackup has all the necessary permissions.

-- compact / / Skip the secondary index page to create a compressed backup.

To see more parameters, please stamp:

Https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html

A simple example of data backup

Innobackupex full backup

# back up all databases: the folder named by the generation date in the backup directory

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123 / home/zhoujy/xtrabackup/

# backup all databases: specify backup directory

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp / home/zhoujy/xtrabackup/

# backup specifies the database name, and multiple databases are separated by spaces

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-databases= "dba_test xtra_test" / home/zhoujy/xtrabackup/

# back up the specified table

Back up different tables under different libraries

1:innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-databases= "dba_test.tb1 xtra_test.M" / home/zhoujy/xtrabackup/

Back up the tables under a library and support rules, such as:-- include=' ^ mydatabase [.] mytable'

2:innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-include='xtra_test.I' / home/zhoujy/xtrabackup/

Back up the table in the specified file. The format of each line in the file is: dbname.tbname

3:innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-tables-file=/tmp/tbname.txt / home/zhoujy/xtrabackup/

Innobackupex incremental backup

# full backup. Here is an example of a single table, or you can specify several libraries or even all libraries.

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-databases= "xtra_test.I" / home/zhoujy/xtrabackup/

# incremental backup 1

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-databases= "xtra_test.I"-incremental-basedir=/home/zhoujy/xtrabackup/-incremental / home/zhoujy/increment_data/

# incremental backup 2

Innobackupex-defaults-file=/etc/mysql/my.cnf-user=root-password=123-no-timestamp-databases= "xtra_test.I"-incremental-basedir=/home/zhoujy/increment_data/-incremental / home/zhoujy/increment_data1/

Full backup of Xtrabackup

1: specify-- defaults-file

Xtrabackup-defaults-file=/etc/mysql/my.cnf-user=root-password=123-backup-target-dir=/home/zhoujy/xtrabackup/

2: replace-- defaults-file with-- datadir

Xtrabackup-user=root-password=123-backup-datadir=/var/lib/mysql/-target-dir=/home/zhoujy/xtrabackup/

Xtrabackup incremental backup

# backup, where several libraries and tables are specified, or all libraries

1: full backup of the library

Xtrabackup-user=root-password=123-datadir=/var/lib/mysql/-backup-parallel=3-databases= "xtra_test dba_test"-target-dir=/home/zhoujy/xtrabackup/

2: incremental backup

Xtrabackup-user=root-password=123-datadir=/var/lib/mysql/-backup-parallel=3-databases= "xtra_test dba_test"-target-dir=/home/zhoujy/xtrabackup1/-incremental-basedir=/home/zhoujy/xtrabackup/

Note: if there are multiple incremental backups, the second increment needs to specify the directory of the first increment. Same as innobackupex.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.