Mysqldump for simple backup recovery

Brief introduction

The backup of MySQL database is the most important work in the enterprise, in the absence of professional DBA in the enterprise, this part of the work naturally falls to the head of operation and maintenance. So a qualified OPS must master this part of the skill.

Backup tools

1, mysqldump:mysql Backup tool, belonging to the logical backup;

2, Cp/tar

3, Xtrabackup: Open source tools, support to InnoDB do hot spare, belong to physical backup;

Mysqldump

Mysqldump belongs to MySQL's own tool, its backup strategy is full-volume backup + binary log (binlog). Support for hot spares or Win Bei for InnoDB, MyISAM support is near Win Bei.

How to use:

mysqldump [Options] database [tables] <=== Backup single library, you can back up only a subset of the tables (partial backup); mysqldump [options]--databases [Options] D          B1 [DB2 DB3 ...] <=== backup multi-Library; mysqldump [options]--all-databases [OPTIONS] <=== back up all libraries

Options:

1, MyISAM Storage Engine: Support Win Bei, backup to lock the table;

-X,--lock-all-tables: Locks All tables of all libraries, read locks,-L,--lock-tables: Locks All tables of the specified library;

2, InnoDB storage Engine: Support Win Bei and hot standby;

--single-transaction: Create a transaction to perform a backup based on this snapshot;

3. Other options:

-R,--routines: Backs up stored procedures and stored functions for the specified library;--triggers: Backs up triggers for specified libraries;-E,--events:--master-data[=#] 1: recorded as Ch ANGE Master to statement, this statement is not commented; 2: Record as change MASTER to statement, this statement is commented;--flush-logs: After the lock table is completed, the log refresh operation is performed;

Example:

Test environment:

h1:centos7.3,mariadb5.5,192.168.32.111

h2:centos7.3,mariadb5.5,192.168.32.112

Steps:

1. install a database on two virtual machines and start normally, creating data on H1

>use test>create table student (ID tinyint, name varchar (), age tinyint, sex char (1)), #]gender= (' F ' M ') definition array > For i in {1..20};d o mysql-e "insert into test.student (id,name,age,sex) VALUES (' $i ', ' student$i ', ' $[$RANDOM%80+18] ', ' ${ gender[$RANDOM%2]} ') ";d one

2. Authorized User Rights

> GRANT all on test.* to [email protected] ' 192.168.32.% ' identified by ' CentOS ';

3. Backup Data

H2] #mysqldump-uroot-pcentos-h192.168.32.111--single-transaction-r--triggers-e--databases test >/app/test.sql

4. Recovering data

] #mysql < Test.sql

Summarize

Mysqldump only supports full-scale backup, how to back up the data after the change? It is only possible to use the binary log. But again, we don't know where the binary logs are in full-scale backup, and we can't restore the binary logs. Here you can add an option to the backup--master-data, this statement will be executed, it is best to choose the 2 comment is not executed. One drawback of mysqldump is that it does not support differential and incremental backups.

Mysqldump for simple backup recovery