1.備份類型 1

1.1.冷備 1

1.2.熱備 2

2. 備份工具 2

2.1. mysqldump 2

2.1.1.特點 2

2.1.2.使用 2

2.1.3.mysqldump備份流程 2

2.2. mydumper 5

2.2.1.特點 5

2.2.2.使用 5

2.3. Xtrabackup 6

2.3.1. 簡介 6

2.3.2. 安裝 6

2.3.3. mysql完全恢複原理 7

2.3.4. 完全恢複在生産中的應用 22

2.3.5. 單表的完全恢複原理 25

2.3.6. 單表的完全恢複在生産中的應用 38

2.4. binlog2sql 41

2.4.1.誤删資料的單表恢複 41

2.4.2.安裝 41

2.4.3.使用前提 43

2.4.4.優缺點 43

2.4.5.使用恢複案例 44

附錄 48

1、 myfullbackup.sh 48

2、 myincrbackup.sh 49

3、 complete_recover.sh 51

4、 Table_Recovery.sh 55

1.備份類型

1.1.冷備

冷備即是關閉庫後拷貝相關檔案，包括配置檔案、資料檔案、binlog日志檔案等。生産中一般沒有實際意義，用的很少。

1.2.熱備

可以使用快照、複制或專有方法，最大限度地減小對 MySQL和應用程式的影響；

對于某些存儲引擎，更好的辦法是暫時鎖定資料庫，進行備份，然後再将資料庫解鎖，鎖在熱備做了兩件事：

第一記錄binlog檔案的位置；

第二冷備非事務引擎引的表(MYISAM);

備份工具
1. mysqldump

2.1.1.特點

邏輯備份使用的工具，單線程。生産資料量比較大的時候備份可能很花時間。在大資料量備份的時候，由于需要把磁盤的資料讀到記憶體中，會把記憶體中熱點資料踢掉，影響mysql的性能。mysql自帶的工具，不需要額外安裝。

2.1.2.使用

#mysqldump --help

#mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 dbname> /tmp/dbname.sql

#mysqldump -h127.0.0.1 -P3306 -uroot -p --single-transaction --master-data=2 -A> /tmp/dbnameall.sql

-A表示導出所有的庫。

2.1.3.mysqldump備份流程

我們可以使用mysql的general_log來跟蹤mysqldump到底做了哪些操作。

mysql> show variables like '%gen%';

+----------------------------------------+-------------------------+

| Variable_name | Value |

+----------------------------------------+-------------------------+

| auto_generate_certs | ON |

| general_log | OFF |

| general_log_file | /mysql/data/dr-prov.log |

| sha256_password_auto_generate_rsa_keys | ON |

+----------------------------------------+-------------------------+

打開general日志

mysql> set global general_log=1;

驗證

mysql> show variables like '%gen%';

+----------------------------------------+-------------------------+

| Variable_name | Value |

+----------------------------------------+-------------------------+

| auto_generate_certs | ON |

| general_log | ON |

| general_log_file | /mysql/data/dr-prov.log |

| sha256_password_auto_generate_rsa_keys | ON |

+----------------------------------------+-------------------------+

檢視general日志

# tail -f /mysql/data/dr-prov.log

...

2020-04-29T01:55:56.176302+08:00 85757 Connect [email protected] on using SSL/TLS

2020-04-29T01:55:56.176597+08:00 85757 Query select @@version_comment limit 1

2020-04-29T01:55:56.176917+08:00 85757 Query SELECT [email protected]@hostname as isPrimary FROM performance_schema.global_status A,performance_schema.replication_group_members B WHERE A.VARIABLE_VALUE=B.MEMBER_ID and A.VARIABLE_NAME='group_replication_primary_member'

2020-04-29T01:55:56.178249+08:00 85757 Quit

這個是keepavlie每隔2秒就來連一次mysql查詢mysql是否挂了和目前節點是否是mysql的mgr的主節點，用以判斷是否需要切換浮動ip的日志。

我們使用mysqldump進行備份，然後去觀察general日志裡面做了哪些操作。

# mysqldump -P3306 -uroot -p --single-transaction --master-data=2 test> /tmp/test.sql

#view /mysql/data/dr-prov.log

...

2020-04-29T02:02:17.625183+08:00 85949 Connect [email protected] on using Socket

2020-04-29T02:02:17.625426+08:00 85949 Query

2020-04-29T02:02:17.625589+08:00 85949 Query

2020-04-29T02:02:17.625725+08:00 85949 Query FLUSH TABLES

2020-04-29T02:02:17.770962+08:00 85949 Query FLUSH TABLES WITH READ LOCK

2020-04-29T02:02:17.771185+08:00 85949 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2020-04-29T02:02:17.771269+08:00 85949 Query START TRANSACTION

2020-04-29T02:02:17.771381+08:00 85949 Query SHOW VARIABLES LIKE 'gtid\_mode'

2020-04-29T02:02:17.774001+08:00 85949 Query SELECT @@GLOBAL.GTID_EXECUTED

2020-04-29T02:02:17.774098+08:00 85949 Query SHOW MASTER STATUS

2020-04-29T02:02:17.774183+08:00 85949 Query UNLOCK TABLES

2020-04-29T02:02:17.774334+08:00 85949 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

2020-04-29T02:02:17.775759+08:00 85949 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('test')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

2020-04-29T02:02:17.776284+08:00 85949 Query SHOW VARIABLES LIKE 'ndbinfo\_version'

2020-04-29T02:02:17.777564+08:00 85949 Init DB test

2020-04-29T02:02:17.777631+08:00 85949 Query SAVEPOINT sp

2020-04-29T02:02:17.777735+08:00 85949 Query show tables

2020-04-29T02:02:17.777907+08:00 85949 Query show table status like 'tmp\_list\_001'

2020-04-29T02:02:17.778119+08:00 85949 Query SET SQL_QUOTE_SHOW_CREATE=1

2020-04-29T02:02:17.778206+08:00 85949 Query SET SESSION character_set_results = 'binary'

2020-04-29T02:02:17.778283+08:00 85949 Query show create table `tmp_list_001`

2020-04-29T02:02:17.778391+08:00 85949 Query SET SESSION character_set_results = 'utf8'

2020-04-29T02:02:17.778474+08:00 85949 Query show fields from `tmp_list_001`

2020-04-29T02:02:17.778765+08:00 85949 Query show fields from `tmp_list_001`

2020-04-29T02:02:17.779063+08:00 85949 Query SELECT * FROM `tmp_list_001`

2020-04-29T02:02:17.779287+08:00 85949 Query SET SESSION character_set_results = 'binary'

2020-04-29T02:02:17.779354+08:00 85949 Query use `test`

2020-04-29T02:02:17.779425+08:00 85949 Query select @@collation_database

2020-04-29T02:02:17.779510+08:00 85949 Query SHOW TRIGGERS LIKE 'tmp\_list\_001'

2020-04-29T02:02:17.779779+08:00 85949 Query SET SESSION character_set_results = 'utf8'

2020-04-29T02:02:17.779866+08:00 85949 Query ROLLBACK TO SAVEPOINT sp

2020-04-29T02:02:17.779933+08:00 85949 Query RELEASE SAVEPOINT sp

2020-04-29T02:02:17.815906+08:00 85949 Quit

...

這個就可以看到mysqldump在備份的時候資料庫都做了哪些具體操作。

1. mydumper

2.2.1.特點

邏輯備份使用的工具，多線程。跟mysqldump一樣，在大資料量備份的時候，由于需要把磁盤的資料讀到記憶體中，會把記憶體中熱點資料踢掉，影響mysql的性能。

2.2.2.使用

下載下傳：

https://launchpad.net/mydumper

mysql備份恢複專題一(mysqldump,mydumper)1.備份類型

編譯安裝：

#yum -y install cmake glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel

#tar -xzvf mydumper-0.9.1.tar.gz

#cd mydumper-0.9.1

#cmake .

#make

#make install

使用：

# mydumper --help

#mydumper \

--user=root \

--password='R00t_123' \

--socket=/var/lib/mysql/mysql.sock \

--regex '^(?!(mysql))' \

--outputdir=/mybackup/ \

--compress \

--verbose=3 \

--logfile=/mybackup/mydumper.log

備份出來的檔案如下

test-schema-create.sql.gz

test.tmp_list_001-schema.sql.gz

test.tmp_list_001.sql.gz

分别對應建庫、建表、表資料三部分。

mysql備份恢複專題一(mysqldump,mydumper)1.備份類型

1.備份類型

1.1.冷備

1.2.熱備

2.1.1.特點

2.1.2.使用

2.1.3.mysqldump備份流程

2.2.1.特點

2.2.2.使用

繼續閱讀

mysqldump got error:2002 及got error:1045錯誤解決方案

如何使用mysqldump備份資料庫一、背景二、解決方案三、寫在後面的話

歡迎光臨 https://github.com/zjcxc

linux和win下 MySQL資料庫的備份與還原

windows下Mysql批量備份多個表windows下Mysql批量備份多個表

Mysql資料庫結構導出以及資料導出

mysqldump詳細了解

mysql 導入導出資料庫

mysqldump與innobackupex備份過程知多少（二）

mysqldump的一些使用技巧

Mysql資料快速備份和恢複方法總結

MySQL之如何複制一張表的資料

資料庫備份表、視圖、存儲過程、觸發器、Event

mysqldump 搭建複制報錯，有哪些注意事項？

mysql資料庫備份工具mysqldump常見用法示例

備份資料庫腳本備份資料庫腳本