快捷導(dǎo)航

淺析mysql 定時備份任務(wù)

更新時間：2020年07月06日 15:23:42 作者：返回主頁Johnson木木

這篇文章主要介紹了mysql 定時備份任務(wù)的相關(guān)資料，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下

簡介

在生產(chǎn)環(huán)境上，為了避免數(shù)據(jù)的丟失，通常情況下都會定時的對數(shù)據(jù)庫進(jìn)行備份。而Linux的crontab指令則可以幫助我們實現(xiàn)對數(shù)據(jù)庫定時進(jìn)行備份。首先我們來簡單了解crontab指令，如果你會了請?zhí)较乱粋€內(nèi)容mysql備份。
本文章的mysql數(shù)據(jù)庫是安裝在docker容器當(dāng)中，以此為例進(jìn)行講解。沒有安裝到docker容器當(dāng)中也可以參照參照。

contab定時任務(wù)

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

0 5 * * 1 [command]

前面的5個數(shù)字分別代表分、時、日、月、周，后面的 command為你的執(zhí)行命令。
假如你需要在每天晚上8點整執(zhí)行定時任務(wù)，那么可以這么寫

0 8 * * * [command]

擴(kuò)展：

crontab -l 可以查看自己的定時任務(wù)
crontab -r 刪除當(dāng)前用戶的所有定時任務(wù)

mysql備份

快速上手

這里我的mysql數(shù)據(jù)庫是docker容器。假如你需要在每天晚上8點整執(zhí)行定時任務(wù)，那么可以這么寫。
首先執(zhí)行命令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 為你的數(shù)據(jù)庫容器名
mysqldump 是mysql數(shù)據(jù)庫導(dǎo)出數(shù)據(jù)的指令
-u 填寫root賬號
-p 填寫root密碼
database_name 需要備份的數(shù)據(jù)庫名
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql 備份文件，后面是文件名的格式

如果你沒什么要求，單純的只是想要備份，那么上面那個命令就可以幫你進(jìn)行定時備份。

小坑： mysql備份的時候我使用了docker exec -it mysqldump ... 這樣的命令去做bash腳本，因為-i參數(shù)是有互動的意思，導(dǎo)致在crontab中執(zhí)行定時任務(wù)的時候，沒有輸出數(shù)據(jù)到sql文件當(dāng)中。所以使用crontab定時的對docker容器進(jìn)行備份命令的時候不要添加-i參數(shù)。

crontab優(yōu)化

我不建議直接在crontab -e里面寫要執(zhí)行的命令，任務(wù)多了就把這個文件寫的亂七八招了。
建議把數(shù)據(jù)庫備份的命令寫成一個bash腳本。在crontab這里調(diào)用就好了
如：建立一個/var/backups/mysql/mysqldump.sh文件，內(nèi)容如下

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

然后把文件改為當(dāng)前用戶可執(zhí)行的：

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

執(zhí)行crontab -e命令修改成如下：

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

那么這樣就比較規(guī)范了。

mysql備份優(yōu)化

因為sql文件比較大，所以一般情況下都會對sql文件進(jìn)行壓縮，不然的話磁盤占用就太大了。
假設(shè)你做了上面這一步 crontab優(yōu)化，我們可以把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 在系統(tǒng)中自定義了個變量mysqldump_date，給備份和壓縮命令使用
gzip 為壓縮命令，默認(rèn)壓縮了之后會把源文件刪除，壓縮成.gz文件
find ... 這行命令的意思為，查詢/var/backups/mysql/目錄下，創(chuàng)建時間15天之前(-mtime +15)，文件名后綴為.sql的所有文件執(zhí)行刪除命令-exec rm -f {} \;?？偟囊馑季褪牵簃ysql的備份文件只保留15天之內(nèi)的。15天之前的都刪除掉。

數(shù)據(jù)恢復(fù)

若一不小心你執(zhí)行drop database，穩(wěn)住，淡定。我們首先要創(chuàng)建數(shù)據(jù)庫被刪除的數(shù)據(jù)庫。

>mysql create database database_name;

然后恢復(fù)最近備份的數(shù)據(jù)?；謴?fù)備份的命令：

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

雖然恢復(fù)了備份文件的數(shù)據(jù)，但是備份時間點之后的數(shù)據(jù)我們卻沒有恢復(fù)回來。
如：晚上8點進(jìn)行定時備份，但是卻在晚上9點drop database，那么晚上8點到晚上9點這一個小時之內(nèi)的數(shù)據(jù)卻沒有備份到。這時候就要使用binlog日志了。

binlog日志

binlog 是mysql的一個歸檔日志，記錄的數(shù)據(jù)修改的邏輯，如：給 ID = 3 的這一行的 money 字段 + 1。
首先登錄mysql后查詢當(dāng)前有多少個binlog文件:

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

查看當(dāng)前正在寫入的binlog

mysql> show master status\G;

生成新的binlog文件，mysql的后續(xù)操作都會寫入到新的binlog文件當(dāng)中，一般在恢復(fù)數(shù)據(jù)都時候都會先執(zhí)行這個命令。

mysql> flush logs

查看binlog日志

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

小知識點：初始化mysql容器時，添加參數(shù)--binlog-rows-query-log-events=ON?；蛘叩饺萜鳟?dāng)中修改/etc/mysql/my.cnf文件，添加參數(shù)binlog_rows_query_log_events=ON，然后重啟mysql容器。這樣可以把原始的SQL添加到binlog文件當(dāng)中。

恢復(fù)數(shù)據(jù)

拿回上面例子的這段話。

晚上8點進(jìn)行定時備份，但是卻在晚上9點drop database，那么晚上8點到晚上9點這一個小時之內(nèi)的數(shù)據(jù)卻沒有備份到。。

首先進(jìn)入到mysql容器后，切換到/var/lib/mysql目錄下，查看binlog文件的創(chuàng)建日期

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
...

從文件日期可以看出：當(dāng)天時間為2020-06-21，binlog.000002文件的最后更新時間是 18:45 分，那么晚上8點的備份肯定包含了binlog.000002的數(shù)據(jù)；
binlog.000003的最后更新日期為 20:58 分，那么我們需要恢復(fù)的數(shù)據(jù) = 晚上8點的全量備份 + binlog.000003的 20:00 - 執(zhí)行drop database命令時間前的數(shù)據(jù)。

恢復(fù)命令格式：

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

mysqlbinlog常用參數(shù)：

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

恢復(fù)備份數(shù)據(jù)和binlog數(shù)據(jù)前建議先登錄mysql后執(zhí)行flush logs生成新的binlog日志，這樣可以專注需要恢復(fù)數(shù)據(jù)的binlog文件。
首先我們需要查看binlog日志，在哪個位置進(jìn)行了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 */                                                   |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------

根據(jù)上面的日志，我們可以看到，在End_log_pos= 822 的位置執(zhí)行了drop database操作，那么使用binlog恢復(fù)的范圍就在2020-06-19 20:00:00 - 660 的位置。為什么是660？因為drop database的上一個事務(wù)的提交是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的位置，那么就會幫你執(zhí)行drop database命令了。不信你試試？
執(zhí)行完上面的命令，你的數(shù)據(jù)就會恢復(fù)到drop database前啦！開不開心，激不激動！

總結(jié)

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

以上就是淺析mysql 定時備份任務(wù)的詳細(xì)內(nèi)容，更多關(guān)于mysql 定時備份任務(wù)的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: