mysql定時(shí)備份任務(wù)
簡(jiǎn)介#
在生產(chǎn)環(huán)境上，為了避免數(shù)據(jù)的丟失，通常情況下都會(huì)定時(shí)的對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份。而Linux的crontab指令則可以幫助我們實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)定時(shí)進(jìn)行備份。首先我們來簡(jiǎn)單了解crontab指令，如果你會(huì)了請(qǐng)?zhí)较乱粋€(gè)內(nèi)容mysql備份。
本文章的mysql數(shù)據(jù)庫(kù)是安裝在docker容器當(dāng)中，以此為例進(jìn)行講解。沒有安裝到docker容器當(dāng)中也可以參照參照。
contab定時(shí)任務(wù)#
使用crontab -e來編寫我們的定時(shí)任務(wù)。
0 5 * * 1 [command]
前面的5個(gè)數(shù)字分別代表分、時(shí)、日、月、周，后面的command為你的執(zhí)行命令。 假如你需要在每天晚上8點(diǎn)整執(zhí)行定時(shí)任務(wù)，那么可以這么寫
0 8 * * * [command]
擴(kuò)展： crontab -l可以查看自己的定時(shí)任務(wù) crontab -r刪除當(dāng)前用戶的所有定時(shí)任務(wù)
mysql備份#
快速上手#
這里我的mysql數(shù)據(jù)庫(kù)是docker容器。假如你需要在每天晚上8點(diǎn)整執(zhí)行定時(shí)任務(wù)，那么可以這么寫。 首先創(chuàng)建數(shù)據(jù)庫(kù)備份目錄
mkdir -p /var/backups/mysql
然后先嘗試執(zhí)行數(shù)據(jù)庫(kù)備份命令
docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/test.sql
如果你看到了/var/backups/mysql/test.sql文件，那么應(yīng)該就沒啥問題了（最好還是看看文件大小，文件太小可能說明會(huì)有問題）。 mysql_container為你的數(shù)據(jù)庫(kù)容器名 --single-transaction不會(huì)鎖表，也確保數(shù)據(jù)的一致性 mysqldump是mysql數(shù)據(jù)庫(kù)導(dǎo)出數(shù)據(jù)的指令 -u填寫mysql的root賬號(hào) -p填寫mysql的root密碼 database_name需要備份的數(shù)據(jù)庫(kù)名
最后執(zhí)行命令crontab -e，把mysql的備份命令寫到定時(shí)任務(wù)中。
0 8 * * * docker exec mysql_container mysqldump --single-transaction -uroot -proot_password database_name > /var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql
/var/backups/mysql/$(date +%Y%m%d_%H%M%S).sql備份文件，后面是文件名的格式
如果你沒什么要求，單純的只是想要備份，那么上面那個(gè)命令就可以幫你進(jìn)行定時(shí)備份。
小坑：mysql備份的時(shí)候我使用了docker exec -it mysqldump ...這樣的命令去做bash腳本，因?yàn)?code style="margin-right:3px;margin-left:3px;line-height:1;vertical-align:middle;padding:.2em .3em;font-family:consolas;font-size:14px;background:rgb(251,251,251);border-width:1px;border-style:solid;border-color:rgb(238,238,238);">-i參數(shù)是有互動(dòng)的意思，導(dǎo)致在crontab中執(zhí)行定時(shí)任務(wù)的時(shí)候，沒有輸出數(shù)據(jù)到sql文件當(dāng)中。所以使用crontab定時(shí)的對(duì)docker容器進(jìn)行備份命令的時(shí)候不要添加-i參數(shù)。
crontab優(yōu)化#
我不建議直接在crontab -e里面寫要執(zhí)行的命令，任務(wù)多了就把這個(gè)文件寫的亂七八招了。 建議把數(shù)據(jù)庫(kù)備份的命令寫成一個(gè)bash腳本。在crontab這里調(diào)用就好了 如：建立一個(gè)/var/backups/mysql/mysqldump.sh文件，內(nèi)容如下
docker exec mysql_container mysqldump -uroot --single-transaction -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)化#
因?yàn)?code style="margin-right:3px;margin-left:3px;line-height:1;vertical-align:middle;padding:.2em .3em;font-family:consolas;font-size:14px;background:rgb(251,251,251);border-width:1px;border-style:solid;border-color:rgb(238,238,238);">sql文件比較大，所以一般情況下都會(huì)對(duì)sql文件進(jìn)行壓縮，不然的話磁盤占用就太大了。 假設(shè)你做了上面這一步crontab優(yōu)化，我們可以把mysqldump.sh腳本改成下面這樣：
export mysqldump_date=$(date +%Y%m%d_%H%M%S) && docker exec mysql_container mysqldump --single-transaction -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)中自定義了個(gè)變量mysqldump_date，給備份和壓縮命令使用 gzip為壓縮命令，默認(rèn)壓縮了之后會(huì)把源文件刪除，壓縮成.gz文件 find ...這行命令的意思為，查詢/var/backups/mysql/目錄下，創(chuàng)建時(shí)間15天之前(-mtime +15)，文件名后綴為.sql的所有文件 執(zhí)行刪除命令-exec rm -f {} ;。總的意思就是：mysql的備份文件只保留15天之內(nèi)的。15天之前的都刪除掉。
數(shù)據(jù)恢復(fù)#
若一不小心你執(zhí)行drop database，穩(wěn)住，淡定。我們首先要?jiǎng)?chuàng)建數(shù)據(jù)庫(kù)被刪除的數(shù)據(jù)庫(kù)。
>mysql create database database_name;
然后恢復(fù)最近備份的數(shù)據(jù)?；謴?fù)備份的命令：
docker exec -i mysql_container mysql --single-transaction -uroot -proot_password database_name < /var/backups/mysql/20200619_120012.sql
雖然恢復(fù)了備份文件的數(shù)據(jù)，但是備份時(shí)間點(diǎn)之后的數(shù)據(jù)我們卻沒有恢復(fù)回來。 如：晚上8點(diǎn)進(jìn)行定時(shí)備份，但是卻在晚上9點(diǎn)drop database，那么晚上8點(diǎn)到晚上9點(diǎn)這一個(gè)小時(shí)之內(nèi)的數(shù)據(jù)卻沒有備份到。這時(shí)候就要使用binlog日志了。
binlog日志#
binlog 是mysql的一個(gè)歸檔日志，記錄的數(shù)據(jù)修改的邏輯，如：給 ID = 3 的這一行的 money 字段 + 1。 首先登錄mysql后查詢當(dāng)前有多少個(gè)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 statusG;
生成新的binlog文件，mysql的后續(xù)操作都會(huì)寫入到新的binlog文件當(dāng)中，一般在恢復(fù)數(shù)據(jù)都時(shí)候都會(huì)先執(zhí)行這個(gè)命令。
mysql> flush logs
查看binlog日志
mysql> show binlog events in 'binlog.000003';
小知識(shí)點(diǎn)：初始化mysql容器時(shí)，添加參數(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點(diǎn)進(jìn)行定時(shí)備份，但是卻在晚上9點(diǎn)
drop database，那么晚上8點(diǎn)到晚上9點(diǎn)這一個(gè)小時(shí)之內(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)天時(shí)間為2020-06-21，binlog.000002文件的最后更新時(shí)間是 18:45 分，那么晚上8點(diǎn)的備份肯定包含了binlog.000002的數(shù)據(jù)； binlog.000003的最后更新日期為 20:58 分，那么我們需要恢復(fù)的數(shù)據(jù) = 晚上8點(diǎn)的全量備份 +binlog.000003的 20:00 - 執(zhí)行drop database命令時(shí)間前的數(shù)據(jù)。
恢復(fù)命令格式：
mysqlbinlog [options] file | mysql -uroot -proot_password database_name
mysqlbinlog常用參數(shù)：
--start-datetime 開始時(shí)間，格式 2020-06-19 1800 --stop-datetime 結(jié)束時(shí)間，格式同上 --start-positon 開始位置，（需要查看binlog文件） --stop-position 結(jié)束位置，同上 ...
恢復(fù)備份數(shù)據(jù)和binlog數(shù)據(jù)前建議先登錄mysql后執(zhí)行flush logs生成新的binlog日志，這樣可以專注需要恢復(fù)數(shù)據(jù)的binlog文件。 首先我們需要查看binlog日志，在哪個(gè)位置進(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 2000- 660 的位置。為什么是660？因?yàn)?code style="margin-right:3px;margin-left:3px;line-height:1;vertical-align:middle;padding:.2em .3em;font-family:consolas;font-size:14px;background:rgb(251,251,251);border-width:1px;border-style:solid;border-color:rgb(238,238,238);">drop database的上一個(gè)事務(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的位置，那么就會(huì)幫你執(zhí)行drop database命令了。不信你試試？ 執(zhí)行完上面的命令，你的數(shù)據(jù)就會(huì)恢復(fù)到drop database前啦！開不開心，激不激動(dòng)！
鏈接：https://www.cnblogs.com/Johnson-lin/p/13178028.html
-
Linux
+關(guān)注
關(guān)注
87文章
11292瀏覽量
209322 -
指令
+關(guān)注
關(guān)注
1文章
607瀏覽量
35692 -
容器
+關(guān)注
關(guān)注
0文章
495瀏覽量
22060 -
日志
+關(guān)注
關(guān)注
0文章
138瀏覽量
10639
原文標(biāo)題：binlog日志#
文章出處：【微信號(hào)：magedu-Linux，微信公眾號(hào)：馬哥Linux運(yùn)維】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
MySQL零基礎(chǔ)入門教程！
0基礎(chǔ)學(xué)Mysql:mysql入門視頻教程！
基于linux的mysql數(shù)據(jù)庫(kù)每天自動(dòng)備份定時(shí)備份的實(shí)現(xiàn)
如何用labview對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份/如何在MySql中使用命令的方式進(jìn)行數(shù)據(jù)庫(kù)備份（非cmd窗口非手動(dòng)保存）
MySQL基礎(chǔ)架構(gòu)自動(dòng)化測(cè)試分析
Linux教程之linux下如何備份還原mysql數(shù)據(jù)庫(kù)
如何完成Mysql主從復(fù)制的在線配置
工商網(wǎng)監(jiān)
評(píng)論