linux 定時執行指令_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 statusG;           

生成新的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/

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