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

1.1.2 Backup Mode

1.1.3 Backup What?

1.1.4 Backup Type

1.1.5 Backup Tool

mysqldump 
XBK(xtrabackup)
replication
mysqlbinlog

1.2 Check Backup

1.3 Regular resumption of drills

1.4 Fast and Complete Failure Recovery

1.5 Work on upgrading and migration

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

2. Backup type (written test)

2.1 cold backup

2.2 warm backup

2.3 Hot Standby hot backup

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

3. Introduction of Backup Tools

3.1 mysqldump : MDP

3.2 Xtrabackup(percona) XBK, PBK

3.3 Selection Recommendations

FB :  MDP
	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

-u 
-p 
-h 
-P
-S 

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