mysql定時備份任務

簡介

在生產環境上,為了避免數據的丟失,通常情況下都會定時的對數據庫進行備份。而Linux的crontab指令則可以幫助我們實現對數據庫定時進行備份。首先我們來簡單了解crontab指令,如果你會了請跳到下一個內容mysql備份
本文章的mysql數據庫是安裝在docker容器當中,以此為例進行講解。沒有安裝到docker容器當中也可以參照參照。

contab定時任務

使用crontab -e來編寫我們的定時任務。

0 5 * * 1 [command]

前面的5個数字分別代表分、時、日、月、周,後面的 command為你的執行命令。
假如你需要在每天晚上8點整執行定時任務,那麼可以這麼寫

0 8 * * * [command]

擴展:
crontab -l 可以查看自己的定時任務
crontab -r 刪除當前用戶的所有定時任務

mysql備份

快速上手

這裏我的mysql數據庫是docker容器。假如你需要在每天晚上8點整執行定時任務,那麼可以這麼寫。
首先執行命令crontab -e

0 8 * * * docker exec mysql_container mysqldump -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

mysql_container 為你的數據庫容器名
mysqldump 是mysql數據庫導出數據的指令
-u 填寫root賬號
-p 填寫root密碼
database_name 需要備份的數據庫名
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 備份文件,後面是文件名的格式

如果你沒什麼要求,單純的只是想要備份,那麼上面那個命令就可以幫你進行定時備份。

小坑: mysql備份的時候我使用了docker exec -it mysqldump ... 這樣的命令去做bash腳本,因為-i參數是有互動的意思,導致在crontab中執行定時任務的時候,沒有輸出數據到sql文件當中。所以使用crontab定時的對docker容器進行備份命令的時候不要添加-i參數。

crontab優化

我不建議直接在crontab -e裏面寫要執行的命令,任務多了就把這個文件寫的亂七八招了。
建議把數據庫備份的命令寫成一個bash腳本。在crontab這裏調用就好了
如:建立一個/var/backups/mysql/mysqldump.sh文件,內容如下

docker exec mysql_container mysqldump -uroot -pmypassword database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql

然後把文件改為當前用戶可執行的:

chmod 711 /var/backups/mysql/mysqldump.sh

執行crontab -e 命令修改成如下:

0 20 * * * /var/backups/mysql/mysqldump.sh

那麼這樣就比較規範了。

mysql備份優化

因為sql文件比較大,所以一般情況下都會對sql文件進行壓縮,不然的話磁盤佔用就太大了。
假設你做了上面這一步 crontab優化,我們可以把mysqldump.sh腳本改成下面這樣:

export mysqldump_date=$(date +%Y%m%d_%H%M%S) && \
docker exec mysql_container mysqldump -uroot -pmypassword database_name> /var/backups/mysql/$mysqldump_date.sql && \
gzip /var/backups/mysql/$mysqldump_date.sql
find /var/backups/mysql/ -name "*.sql" -mtime +15 -exec rm -f {} \;

export 在系統中自定義了個變量mysqldump_date,給備份和壓縮命令使用
gzip 為壓縮命令,默認壓縮了之後會把源文件刪除,壓縮成.gz文件
find ... 這行命令的意思為,查詢 /var/backups/mysql/目錄下,創建時間15天之前(-mtime +15),文件名後綴為.sql的所有文件 執行刪除命令-exec rm -f {} \;。總的意思就是:mysql的備份文件只保留15天之內的。15天之前的都刪除掉。

數據恢復

若一不小心你執行drop database,穩住,淡定。我們首先要創建數據庫被刪除的數據庫。

>mysql create database database_name;

然後恢復最近備份的數據。恢復備份的命令:

docker exec -i mysql_container mysql -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql

雖然恢復了備份文件的數據,但是備份時間點之後的數據我們卻沒有恢復回來。
如:晚上8點進行定時備份,但是卻在晚上9點drop database,那麼晚上8點到晚上9點這一個小時之內的數據卻沒有備份到。這時候就要使用binlog日誌了。

binlog日誌

binlog 是mysql的一個歸檔日誌,記錄的數據修改的邏輯,如:給 ID = 3 的這一行的 money 字段 + 1。
首先登錄mysql后查詢當前有多少個binlog文件:

> mysql show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       729 | No        |
| binlog.000002 |      1749 | No        |
| binlog.000003 |      1087 | No        |
+---------------+-----------+-----------+

查看當前正在寫入的binlog

mysql> show master status\G;

生成新的binlog文件,mysql的後續操作都會寫入到新的binlog文件當中,一般在恢複數據都時候都會先執行這個命令。

mysql> flush logs

查看binlog日誌

mysql> show binlog events in 'binlog.000003';

小知識點:初始化mysql容器時,添加參數--binlog-rows-query-log-events=ON。或者到容器當中修改/etc/mysql/my.cnf文件,添加參數binlog_rows_query_log_events=ON,然後重啟mysql容器。這樣可以把原始的SQL添加到binlog文件當中。

恢複數據

拿回上面例子的這段話。

晚上8點進行定時備份,但是卻在晚上9點drop database,那麼晚上8點到晚上9點這一個小時之內的數據卻沒有備份到。。

首先進入到mysql容器后,切換到/var/lib/mysql目錄下,查看binlog文件的創建日期

cd /var/lib/mysql
ls -l
...
-rw-r----- 1 mysql mysql      729 Jun 19 15:54  binlog.000001
-rw-r----- 1 mysql mysql     1749 Jun 19 18:45  binlog.000002
-rw-r----- 1 mysql mysql     1087 Jun 19 20:58  binlog.000003
...

從文件日期可以看出:當天時間為2020-06-21,binlog.000002文件的最後更新時間是 18:45 分,那麼晚上8點的備份肯定包含了binlog.000002的數據;
binlog.000003的最後更新日期為 20:58 分,那麼我們需要恢復的數據 = 晚上8點的全量備份 + binlog.000003的 20:00 – 執行drop database命令時間前的數據。

恢復命令格式:

mysqlbinlog [options] file | mysql -uroot -proot_password database_name

mysqlbinlog常用參數:

–start-datetime 開始時間,格式 2020-06-19 18:00:00
–stop-datetime 結束時間,格式同上
–start-positon 開始位置,(需要查看binlog文件)
–stop-position 結束位置,同上

恢復備份數據和binlog數據前建議先登錄mysql后執行flush logs生成新的binlog日誌,這樣可以專註需要恢複數據的binlog文件。
首先我們需要查看binlog日誌,在哪個位置進行了drop database操作:

mysql> show binlog events in 'binlog.000003';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                        |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000003 |   4 | Format_desc    |         1 |         125 | Server ver: 8.0.20, Binlog ver: 4                                                                                                           |
| binlog.000003 | 125 | Previous_gtids |         1 |         156 |                                                                                                                                             |
| binlog.000003 | 156 | Anonymous_Gtid |         1 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                        |
| binlog.000003 | 235 | Query          |         1 |         318 | BEGIN                                                                                                                                       |
| binlog.000003 | 318 | Rows_query     |         1 |         479 | # INSERT INTO `product_category` SET `name` = '床上用品' , `create_time` = 1592707634 , `update_time` = 1592707634 , `lock_version` = 0      |
| binlog.000003 | 479 | Table_map      |         1 |         559 | table_id: 139 (hotel_server.product_category)                                                                                               |
| binlog.000003 | 559 | Write_rows     |         1 |         629 | table_id: 139 flags: STMT_END_F                                                                                                             |
| binlog.000003 | 629 | Xid            |         1 |         660 | COMMIT /* xid=2021 */                                                                                                                       |
| binlog.000004 | 660 | Anonymous_Gtid |         1 |         739 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                        |
| binlog.000004 | 739 | Query          |         1 |         822 | drop database hotel_server /* xid=26 */                                                                                                     |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+

根據上面的日誌,我們可以看到,在End_log_pos = 822 的位置執行了drop database操作,那麼使用binlog恢復的範圍就在2020-06-19 20:00:00 – 660 的位置。為什麼是660?因為drop database的上一個事務的提交是660的位置,命令如下:

mysqlbinlog --start-datetime=2020-06-19 20:00:00 --stop-position=660 /var/lib/mysql/binlog.000003 | mysql -uroot -proot_password datbase_name

如果你的範圍包括了822的位置,那麼就會幫你執行drop database命令了。不信你試試?
執行完上面的命令,你的數據就會恢復到drop database前啦!開不開心,激不激動!

總結

因為mysql定時備份是在生產環境上必須的任務。是很常用的。所以我就迫不及待的寫博客。當然也很感謝我同事的幫助。這篇文章已經寫了三天了,因為我也是在不斷地試錯,不斷的更新文章。避免把錯誤的知識點寫出來。如果幫到你了,關注我一波唄!謝謝。

個人博客網址: https://colablog.cn/

如果我的文章幫助到您,可以關注我的微信公眾號,第一時間分享文章給您

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】

※為什麼 USB CONNECTOR 是電子產業重要的元件?

網頁設計一頭霧水該從何著手呢? 台北網頁設計公司幫您輕鬆架站!

※台北網頁設計公司全省服務真心推薦

※想知道最厲害的網頁設計公司"嚨底家"!

新北清潔公司,居家、辦公、裝潢細清專業服務

※推薦評價好的iphone維修中心