Week 20 - day87 - database day09 - backup recovery

Chapter 7 Backup and Recovery

1. What does DBA (Operations and Maintenance) need to do in backup recovery

1.1 Design backup strategy

1.1.1 backup cycle

Day, week, month

1.1.2 Backup Mode

Full, incremental...

1.1.3 Backup What?


1.1.4 Backup Type

Cold, warm, hot

1.1.5 Backup Tool


1.2 Check Backup

It's a good habit to check every day at work.
Backup file size
Backup content header

1.3 Regular resumption of drills

Normal restore to test library
Simulated Fault Recovery Exercise

1.4 Fast and Complete Failure Recovery

1.5 Work on upgrading and migration


2. Backup type (written test)

2.1 cold backup

Business stop or database shutdown, backup, business impact is greatest

2.2 warm backup

Lock table backup, read-only backup, block all change operations, read only.

2.3 Hot Standby hot backup

No lock table backup, only for transactional engine tables (e.g. InnoDB), business impact is minimal.


3. Introduction of Backup Tools

3.1 mysqldump : MDP


  • Logical Backup Tool,
  • Text format, compression ratio
  • All the backups are SQL.
  • It is readable.
  • It is convenient for secondary treatment.
  • Self-contained tools


  • Relatively slow: transfer data from disk - > memory - > to SQL - > xxx. SQL

3.2 Xtrabackup(percona) XBK, PBK

Official website download address - Click
Physical backup tools, backup data files (similar to cp), poor readability, not easy to handle


  • Fast backup speed
  • Supporting hot standby
  • Incremental backup function


  • Need to install
  • Poor readability
  • Inconvenient to handle
  • Low compression ratio

3.3 Selection Recommendations

Less than 100G: MDP, XBK
100G - 1T : XBK

	PB magnitude 1PB = 1024TB
 A common 4TB disk 1500RMB,
1PB=256*4TB=256*1500=384000‬ RMB o(* ̄︶ ̄*)o


4. Backup strategy

4.1 Backup mode:

Full-time: Full-database backup, backup all data
 Incremental: Backing up changed data
 Logical backup = mysqldump+binlog
 Physical backup = XBK_full+xbk_inc+binlog or xtrabackup_full+binlog

4.2 Backup cycle:

Design backup cycle based on data volume
 For example: full Sunday, incremental week 1-6


5. mysqldump Logical Backup Tool

5.1 Client General Parameters


5.2 Basic Backup Parameters

5.2.1-A Full Library Backup

[root@db01 ~]# mkdir -p /backup
[root@db01 ~]# mysqldump -uroot -p123456 -A > /backup/full.sql
[root@db01 ~]# ll /backup/
total 49816
-rw-r--r-- 1 root root 50828204 Aug 15 17:57 full.sql

5.2.2-B Single or Multiple Library Backup

Example: Backup only world and test library

[root@db01 ~]# mysqldump -uroot -p123456 -B world test /backup/db.sql
[root@db01 ~]# ll /backup/
total 98672
-rw-r--r-- 1 root root 50025056 Aug 15 18:02 db.sql
-rw-r--r-- 1 root root 50828204 Aug 15 17:57 full.sql

5.2.3 Single or Multiple Table Backup

                                          Library Name Table1   surface2
[root@db01 ~]# mysqldump -uroot -p123456 world city country > /backup/tab.sql
[root@db01 ~]# mysqldump -uroot -p123456 world > /backup/a.sql
[root@db01 ~]# ll /backup/
total 98952
-rw-r--r-- 1 root root   244810 Aug 15 18:10 a.sql
-rw-r--r-- 1 root root 50025056 Aug 15 18:02 db.sql
-rw-r--r-- 1 root root 50828204 Aug 15 17:57 full.sql+
-rw-r--r-- 1 root root   217203 Aug 15 18:09 tab.sql

5.3 Special Functional Parameters

(1) R--triggers-E database special object backup parameters
 - R stored procedure function
 --triggers flip-flop
 - E Event 
(2) --master-data=2
  1. Record the binlog file name and position number (the starting point for intercepting binary logs) when backing up in the form of annotations
  2. Automatic lock table function, add-single-transaction, reduce lock table.
  3. Automatic unlocking
(3) --single-transaction 
  Snapshot backup, hot standby.

Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does);

(4) --set-gtid-purged=OFF (parameters unique to GTID mode)
  Function, remove all gtid information, in the daily backup recovery can be added.
  This parameter cannot be added to master-slave replication applications.
(5) --max-allowed-packet=512M

[root@db01 ~]# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --set-gtid-purged=OFF --max-allowed-packet=256M > /backup/full.sql

Keywords: SQL mysqldump Database snapshot

Added by Damien_Buttler on Thu, 15 Aug 2019 14:31:44 +0300