Mysql DBA 進階營運學習筆記-MySQL備份與恢複實戰案例及生產方案

標籤：鎖表   暫停   更改   好的   0 rows   火牆   Database Backup   第一步   頻率   

1.全量備份與增量備份1.1 全量備份

全量資料就是資料庫中所有的資料，全量備份就是把資料庫中所有的資料進行備份。

備份所有庫：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B –A gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

備份一個庫：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B linzhongniao|gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

1.2 增量備份

增量備份是從上次全量備份之後更新的資料，對mysql來說binlog就是增量備份資料。增量備份可以按天備份。第二天做全備了，理論上前一天的備份就沒用了。我們一般主伺服器的增量備份一般保持七天，可以通過設定my.cnf設定檔中的expire_logs_days參數讓它的值等於7.備份伺服器上增量備份一般保持180天。按天備份就是一天做一次全量備份，每天的零點全量備份，零點之前做增量備份；除了按天全備也可以按周全備，一周做一次全備每天做增量備份。

按天備份的優點：恢復：短 維護成本：低

缺點：佔用空間多，佔用系統資源多，經常鎖表影響使用者體驗。

按周全備的優點：佔用空間少，佔用系統資源少使用者體驗好一些。

缺點：恢複麻煩維護正本高，時間長。

企業情境全備和增量的頻率是怎麼做的呢？

（1）中小公司，全量一般是每天一次，業務流量低穀執行全備，執行前需要鎖表。

（2）單台資料庫沒有做主從同步，如何增量。用rsync(配合定時任務頻率大點，或者inotify,主從複製)把所有binlog備份到遠程伺服器,盡量做主從複製。

增量備份的例子：

rsync –avz /data/3306/mysql-bin.000* [email protected]::backup –-password-file=/etc/rsync.password

（3）大公司周備，每周六00點一次全量，下周日-下周六00點前都是增量。

有點：節省備份時間，減小備份壓力，缺點：增量的binlog檔案副本太多，還原會很麻煩。

（4）一主多從，會有一個從庫做備份，延遲同步。

Mysql的mysqldump備份什麼時候派上用場？

a.遷移或者升級資料庫時。

b.增加從庫的時候。

c.因為硬體或特殊異常情況，主庫或從庫宕機，主從可以切換，無需備份。把其中一個同步最快的切換為主庫即可。

d.人為的DDL、DML語句，主從庫沒辦法了，所有庫都會執行，此時需要備份。

e.跨機房災備，需備份拷貝走資料。

2.MySQL增量恢複必備條件2.1 開啟MySQL log-bin 日誌功能

[[email protected] ~]# egrep "\[mysqld]|log-bin" /data/3306/my.cnf [mysqld]log-bin = /data/3306/mysql-bin

提示：主庫和備份的從庫都需要開啟binlog記錄功能。

小結：增量恢複的條件：

存在一份全備加上全備之後的時刻到出問題時刻的所有增量binlog檔案備份。

2.2 存在MySQL資料庫全備2.2.1 生產環境mysqldump備份命令

在淩晨某一時刻進行資料庫全備（生產情境一般通過定時任務每日淩晨執行），備份命令如下：

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B linzhongniao|gzip >/server/backup/mysq_backup_$(date +%F).sql.gz

innode引擎備份

mysqldump -uroot -p123456 -S /data/3306/mysql.sock --single-transaction -A -B|gzip >/server/backup/mysql_$(date +%F).sql.gz

myisam引擎備份

mysqldump -uroot -p123456 -S /data/3306/mysql.sock --lock-all-tables  -A -B|gzip >/server/backup/mysql_$(date +%F).sql.gz

2.3 資料恢複思想

恢複思想就是先找到發生故障的原因，，誰在什麼時候做了什麼操作，檢查是不是這個操作導致資料庫故障。之後盡量停止資料庫對外訪問，先備份0:00到十點更新的資料binlog記錄檔，重新整理binlog。第一步先恢複全備，第二步增量恢複0：00到10:00這個時間段的資料。

3.實戰示範資料恢複過程3.1 執行語句並檢查環境3.1.1 執行建庫建表語句及插入資料庫語句

[[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 6Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show variables like ‘%chaeacter_set%‘;Empty set (0.01 sec)mysql> create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> primary key(id)-> );Query OK, 0 rows affected (0.01 sec)mysql> insert into student(name) values(‘nishishei‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘zhangsan‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘lisi‘);Query OK, 1 row affected (0.00 sec)

3.1.2 檢查資料庫及資料

檢查是否有linzhongniao庫

mysql> show databases like ‘linzhongniao‘;+-------------------------+| Database (linzhongniao) |+-------------------------+| linzhongniao|+-------------------------+1 row in set (0.00 sec)

4.淩晨0點全量備份資料庫4.1 定時任務Database Backup指令碼

(1) 這裡我們使用MySQL主從複製課程內容裡的備份指令碼

#/bin/sh#Date: 2018-02-11 #Author: Create by linzhongniao  #Mail: [email protected] #Function:This scripts function is More complex backup scripts, which need to find binlog log files and location points #Version: 1.1 USER=rootPASS=123456MYSOCK=/data/3306/mysql.sockDATA_PATH=/server/backupDATA_FILE=${DATA_PATH}/mysql_backup_`date +%F`.sql.gzLOG_FILE=${DATA_PATH}/mysql_backup_`date +%F`.logMYSQL_PATH=/usr/local/mysql/binmysqldb=linzhongniao#--single-transaction Specifically for the InnoDB engine, when the data is updated when the data is updated, it can‘t see the whole isolation.MYSQL_DUMP="${MYSQL_PATH}/mysqldump -u$USER -p$PASS -S $MYSOCK --events -B -F --master-data=2 --single-transaction $mysqldb"MYSQL_CMD="${MYSQL_PATH}/mysql -u$USER -p$PASS -S $MYSOCK"cat |${MYSQL_CMD}<<EOFflush table with read lock;system echo "-----show master status result-----" >>$LOG_FILE;system ${MYSQL_CMD} -e "show master status"|tail -1 >>$LOG_FILE;system ${MYSQL_DUMP}|gzip > $DATA_FILE;unlock tables;quitEOF  

(2) 執行指令碼全量備份linzhongniao庫原理

[[email protected] ~]# date -s ‘2018/02/11‘ 更改系統時間類比零點時刻2018年 02月 11日 星期日 00:00:00 CST[[email protected] ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -F -B --master-data=2 linzhongniao|gzip >/server/backup/bak_$(date +%F).sql.gz

查看備份的資料

[[email protected] ~]# ll /server/backup/總用量 684 -rw-r--r--. 1 root root203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root 529266 2月   8 19:11 mysql_backup_2018-02-08.sql -rw-r--r--. 1 root root805 2月  10 23:18 mysql_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root306 2月  11 00:25 mysql_backup_2018-02-11.log -rw-r--r--. 1 root root915 2月  11 00:25 mysql_backup_2018-02-11.sql.gz -rw-r--r--. 1 root root805 2月  10 23:18 mysql_backup_.sql

(3) 實際工作中是通過定時任務來備份的

[[email protected] ~]# crontab –l#mysql backup by linzhongniao on 2018021100 00 * * * /bin/sh beifen1.sh >/dev/null 2>&1

4.2 備份後查看binlog日誌情況

[[email protected] ~]# ll /data/3306/總用量 172drwxr-xr-x. 8 mysql mysql  4096 2月  12 2018 data-rw-r--r--. 1 mysql mysql  2099 2月  10 06:48 my.cnf-rwx------. 1 root  root   1126 2月   8 10:10 mysql-rw-rw----. 1 mysql mysql  1227 2月   8 19:17 mysql-bin.000001-rw-rw----. 1 mysql mysql   126 2月   9 05:20 mysql-bin.000002-rw-rw----. 1 mysql mysql   239 2月   9 05:25 mysql-bin.000003-rw-rw----. 1 mysql mysql   458 2月   9 05:30 mysql-bin.000004-rw-rw----. 1 mysql mysql   233 2月   9 05:34 mysql-bin.000005-rw-rw----. 1 mysql mysql   415 2月   9 05:57 mysql-bin.000006-rw-rw----. 1 mysql mysql   126 2月   9 05:59 mysql-bin.000007-rw-rw----. 1 mysql mysql   126 2月   9 05:59 mysql-bin.000008-rw-rw----. 1 mysql mysql   388 2月   9 08:22 mysql-bin.000009-rw-rw----. 1 mysql mysql   652 2月  10 04:51 mysql-bin.000010-rw-rw----. 1 mysql mysql   107 2月  10 04:51 mysql-bin.000011-rw-rw----. 1 mysql mysql  3355 2月  10 06:17 mysql-bin.000012-rw-rw----. 1 mysql mysql   126 2月  10 06:17 mysql-bin.000013-rw-rw----. 1 mysql mysql   126 2月  10 06:17 mysql-bin.000014-rw-rw----. 1 mysql mysql  1914 2月  10 08:34 mysql-bin.000015-rw-rw----. 1 mysql mysql   150 2月  10 23:18 mysql-bin.000016-rw-rw----. 1 mysql mysql   150 2月  10 23:18 mysql-bin.000017-rw-rw----. 1 mysql mysql   126 2月  11 2018 mysql-bin.000018-rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000019-rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000020-rw-rw----. 1 mysql mysql   150 2月  11 00:25 mysql-bin.000021-rw-rw----. 1 mysql mysql   893 2月  11 01:41 mysql-bin.000022-rw-rw----. 1 mysql mysql   616 2月  11 00:25 mysql-bin.index-rw-rw----. 1 mysql mysql 5 2月  12 2018 mysqld.pidsrwxrwxrwx. 1 mysql mysql 0 2月  12 2018 mysql.sock-rw-r-----. 1 mysql root  42156 2月  12 2018 mysql_zbf3306.err-rw-rw----. 1 mysql mysql56 2月  11 00:34 relay-bin.index-rw-rw----. 1 mysql mysql55 2月  11 01:41 relay-log.info

提示：我們可以看用-F參數備份產生的新的檔案mysql-bin.000022，增量恢複就是從mysql-bin.000022開始的。

5.備份後類比使用者繼續更新資料5.1 類比網站使用者更新資料

因為在0點備份後到發生故障這個時間段內，使用者還會隨時的更新資料

[[email protected] ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 18Server version: 5.5.32-log Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> use linzhongniaoDatabase changedmysql> insert into student(name) values(‘linzhongniao10‘);Query OK, 1 row affected (0.35 sec)mysql> insert into student(name) values(‘linzhongniao11‘);Query OK, 1 row affected (0.00 sec)mysql> insert into student(name) values(‘linzhongniao12‘);Query OK, 1 row affected (0.00 sec)mysql> select * from student;+----+----------------+| id | name   |+----+----------------+|  1 | nishishei  ||  3 | zhangsan   ||  5 | lisi   ||  6 | burenshi   ||  8 | liushishi  || 10 | luhan  || 11 | linzhongniao10 || 13 | linzhongniao11 || 15 | linzhongniao12 |+----+----------------+9 rows in set (0.00 sec)mysql>

5.2 查看備份後再次更新後的資料庫內容

一會兒我們類比故障恢複後還應是這些資料，如果不是這些資料就是丟失了資料。

mysql> select * from linzhongniao.student;+----+----------------+| id | name   |+----+----------------+|  1 | nishishei  ||  3 | zhangsan   ||  5 | lisi   ||  6 | burenshi   ||  8 | liushishi  || 10 | luhan  || 11 | linzhongniao10 || 13 | linzhongniao11 || 15 | linzhongniao12 |+----+----------------+9 rows in set (0.00 sec)

6.類比使用者破壞資料庫6.1 刪除資料庫linzhongniao

公司的老大於早晨10點，執行了刪除資料庫語句。然後跟沒事兒人一樣，本來想刪除一個沒有用的庫。

mysql> drop database linzhongniao;Query OK, 1 row affected (0.11 sec)

6.2 檢查破壞結果

Linzhongniao庫不見了

mysql> show databases;+--------------------+| Database   |+--------------------+| information_schema || dfhjdhf|| mysql  || performance_schema || school || test   |+--------------------+

6 rows in set (0.00 sec)

6.3 發現故障排查並檢查原因

資料庫出問題10分鐘後，公司的網站運營人員報網站故障，聯絡dba營運人員解決。此時，dba人員或開發人員查看網站報錯（或者查看後台日誌）。可以看到連不上linzhongniao資料庫的顯示。然後登入資料庫排查，可以探索資料庫linzhongniao庫確實不在。經過多方詢問得知老大早晨要處理一下資料庫，於是問老大都幹啥了。答曰，10點左右剛剛清除了一個“沒有用的”資料庫。致此問題原因找到，開始準備恢複，原因還有可能是開發人員通過程式日誌判斷。
提示：資料庫的許可權管理思想就在這裡。不讓別人有delete許可權。

7.增量備份過程

通過防火牆禁止web等應用向主庫寫資料，讓主庫暫停更新，然後進行恢複。

7.1 檢查全備及binlog日誌7.1.1 檢查淩晨備份

[[email protected] ~]# ll /server/backup/mysql_backup_2018-02-11.*-rw-r--r--. 1 root root 306 2月  11 00:25 /server/backup/mysql_backup_2018-02-11.log-rw-r--r--. 1 root root 915 2月  11 00:25 /server/backup/mysql_backup_2018-02-11.sql.gz

7.1.2 檢查全備後的所有binlog

[[email protected] backup]# ls -lrt /data/3306/ >/opt/mysql-bin.txt[[email protected] backup]# sed -n "21,30p" /opt/mysql-bin.txt  -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000019 -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000020 -rw-rw----. 1 mysql mysql   150 2月  11 00:25 mysql-bin.000021 -rw-rw----. 1 mysql mysql   616 2月  11 00:25 mysql-bin.index -rw-rw----. 1 mysql mysql   253 2月  11 00:34 relay-bin.000023 -rw-rw----. 1 mysql mysql   253 2月  11 00:34 relay-bin.000024 -rw-rw----. 1 mysql mysql56 2月  11 00:34 relay-bin.index -rw-rw----. 1 mysql mysql   126 2月  11 07:51 mysql-bin.000018drwxr-xr-x. 7 mysql mysql  4096 2月  11 23:52 data -rw-rw----. 1 mysql mysql   995 2月  11 23:52 mysql-bin.000022

提示：我們在0點執行的全備，所以binlog記錄檔是從0點往後更新的。我們可以用mysqlbinlog參數查看一下零點之後更新的binlog檔案，看看裡面有沒有更新的資料。當然我們也可以通過查看0點全備的資料檔案裡面的change master的位置來查看最新更新的binlog檔案。可以看到執行全備後的所有binlog記錄檔，它裡面記錄了從0點全備之後到第二天十點所有有更新的資料包括執行的誤操作，所以我們在增量恢複的時候必須把執行誤操作的那條語句刪掉。例如本次示範應該把drop那條語句刪掉。

[[email protected] ~]# grep -i "change" /server/backup/mysql_backup_2018-02-11.sql  -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000022‘, MASTER_LOG_POS=107;

7.1.3 立即重新整理並備份出binlog

一般資料庫故障我們要停止資料庫，如果領導說不能停,這種情況下我們要重新整理一下binlog。重新整理一下binlog就會產生一個新的mysql-bin記錄檔mysql-bin.000023，這時候再更新資料就會往這個新的裡面寫。現在增量恢複的目標就是mysql-bin.000022。

[[email protected] ~]# mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs [[email protected] ~]# ls -lrt /data/3306/ >/opt/mysql-bin.txt[[email protected] ~]# vim /opt/mysql-bin.txt [[email protected] ~]# sed -n "21,27p" /opt/mysql-bin.txt  -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000019 -rw-rw----. 1 mysql mysql   150 2月  11 00:24 mysql-bin.000020 -rw-rw----. 1 mysql mysql   150 2月  11 00:25 mysql-bin.000021 -rw-rw----. 1 mysql mysql   126 2月  11 07:51 mysql-bin.000018drwxr-xr-x. 7 mysql mysql  4096 2月  11 23:52 data -rw-rw----. 1 mysql mysql  1038 2月  12 01:36 mysql-bin.000022 -rw-rw----. 1 mysql mysql   107 2月  12 01:36 mysql-bin.000023

7.1.4 增量備份binlog

（1）要把mysql-bin.000022拷貝出來防止二次破壞資料

[[email protected] 3306]# cp mysql-bin.000022 /server/backup/

（2）將mysql-bin.000022解析成bin.sql資料檔案

[[email protected] backup]# mysqlbinlog -d linzhongniao mysql-bin.000022 >bin.sql [[email protected] backup]# ll總用量 24 -rw-r--r--. 1 root root 2868 2月  12 13:45 bin.sql -rw-r--r--. 1 root root  203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz -rw-r--r--. 1 root root  306 2月  11 00:25 mysql_backup_2018-02-11.log -rw-r--r--. 1 root root 2331 2月  11 00:25 mysql_backup_2018-02-11.sql -rw-r--r--. 1 root root  805 2月  10 23:18 mysql_backup_.sql -rw-r-----. 1 root root 1038 2月  12 13:41 mysql-bin.000022

8.恢複資料

如果不挺庫禁止對外訪問就會有一個問題，什麼問題呢？
第一個問題就是，在恢複的時候還有使用者往資料庫寫資料，mysql-bin.000023還會記錄更新的內容；全備恢複和增量恢複後還要恢複mysql-bin.000023更新的資料，備份mysql-bin.000023還得重新整理binlog，增量恢複完mysql-bin.000023還得增量恢複更新的binlog，這樣就死結了老得恢複。
第二個問題就是，我們在增量恢複的時候，mysql-bin.000023也會記錄全量和增量恢複的更新。在全量和增量恢複之後再恢複mysql-bin.000023更新的記錄的時候就會導致主鍵衝突，可以編輯mysql-bin.000023解析成的mysql資料檔案將衝突的資料刪掉，如果資料多呢？還可以關閉sql_log_bin。關閉sql_log_bin就不會更新binlog記錄檔了，這樣會導致資料缺失。所以這個時候，最好的方法就是挺庫，禁止對外訪問，再做全量和增量備份。最根本的就是資料庫的許可權管理，不給刪除修改許可權，只有營運的有刪除修改許可權，防止故障的發生。誰有許可權都要有記錄，誰導致的故障要負責任。

mysql> show variables like ‘%log_bin%‘;+---------------------------------+-------+| Variable_name   | Value |+---------------------------------+-------+| log_bin | ON|| log_bin_trust_function_creators | OFF   || sql_log_bin | ON|3 rows in set (0.00 sec)

8.1 全量恢複

[[email protected] backup]# ll總用量 24-rw-r--r--. 1 root root 2868 2月  12 13:45 bin.sql-rw-r--r--. 1 root root  203 2月  10 23:26 mysq_backup_2018-02-10.sql.gz-rw-r--r--. 1 root root  306 2月  11 00:25 mysql_backup_2018-02-11.log-rw-r--r--. 1 root root 2331 2月  11 00:25 mysql_backup_2018-02-11.sql-rw-r--r--. 1 root root  805 2月  10 23:18 mysql_backup_.sql-rw-r-----. 1 root root 1038 2月  12 13:41 mysql-bin.000022[[email protected] backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock <mysql_backup_2018-02-11.sql

8.2 增量恢複

[[email protected] backup]# mysql -uroot -p123456 -S /data/3306/mysql.sock linzhongniao <bin.sql

8.3 檢查恢複後的資料

mysql> select * from linzhongniao.student;+----+----------------+| id | name   |+----+----------------+|  1 | nishishei  ||  3 | zhangsan   ||  5 | lisi   ||  6 | burenshi   ||  8 | liushishi  || 10 | luhan  || 11 | linzhongniao10 || 13 | linzhongniao11 || 15 | linzhongniao12 |+----+----------------+9 rows in set (0.00 sec)

9.增量恢複小結

1.人為SQL造成的誤操作

2.全量和增量

3.恢複時建議對外停止更新

4.恢複全量，然後把增量日誌中有問題的SQL語句刪除，恢複到資料庫。

增量恢複的核心思想：

1.流程制度的控制，如果不做，面臨服務和資料，魚和熊掌不可兼得。

2.可以通過延遲備份來解決或者通過監控，黑名單（不加where的語句是不讓執行），白名單機制。

3.業務需求容忍度，選擇停庫，根據業務需求選擇停庫或鎖表或者容忍丟失部分資料。

Mysql DBA 進階營運學習筆記-MySQL備份與恢複實戰案例及生產方案