一、MySQL常用日志

1.1 概述

日志文件在數(shù)據(jù)庫進(jìn)行備份和恢復(fù)時(shí)起到了很重要的作用

常用的日志文件默認(rèn)保存在 /usr/local/mysql/data 目錄下

可在 /etc/my.cnf 配置文件中的 [mysqld] 中進(jìn)行日志的路徑修改、開啟、關(guān)閉等操作

1.2 錯(cuò)誤日志

用于記錄 mysql 啟動(dòng)、停止或運(yùn)行時(shí)產(chǎn)生的錯(cuò)誤信息

可通過一下字段進(jìn)行更新

log-error=/usr/local/mysql/data/mysql_error.log （指定日志的保存位置和文件名）

1.3 二進(jìn)制文件

二進(jìn)制日志，用來記錄所有更新的數(shù)據(jù)或者已經(jīng)潛在更新了數(shù)據(jù)的語句，記錄了數(shù)據(jù)的更改，可用于數(shù)據(jù)恢復(fù)

開啟方式：

log-bin=mysql-bin 或者 log_bin=mysql-bin

1.4 中繼日志

一般情況下，它在 mysql 主從同步（復(fù)制）、讀寫分離集群的從節(jié)點(diǎn)上才開啟。

主節(jié)點(diǎn)一般不需要這個(gè)日志。

1.5 慢查詢?nèi)罩?/h3>

慢查詢?nèi)罩?，用來記錄所有?zhí)行時(shí)間超過long_query_time秒的語句，可以找到哪些查詢語句執(zhí)行時(shí)間長(zhǎng)，以便于優(yōu)化

開啟方式：

slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log （指定文件路徑和名稱）
long_query_time=5 （設(shè)置執(zhí)行超過5秒的語句會(huì)被記錄，缺省時(shí)默認(rèn)為10秒）

1.6 數(shù)據(jù)庫中的查詢?nèi)罩緺顟B(tài)

1.6.1 查看二進(jìn)制日志狀態(tài)開啟

show variables like '%log_bin%';

1.6.2 查看慢查詢?nèi)罩竟δ苁欠耖_啟

show variables like '%slow%';

1.6.3 查詢慢時(shí)間設(shè)置

show variables like 'long_query_time';

1.6.4 在數(shù)據(jù)庫中設(shè)置開啟慢查詢的辦法（臨時(shí)）

set global slow_query_log=ON;

查看
show variables like ‘long_query_time';

日志超時(shí)時(shí)間

二、備份

2.1 概述

• 備份的主要目的是災(zāi)難恢復(fù)
• 還可以用來測(cè)試應(yīng)用、回滾數(shù)據(jù)修改、查詢歷史數(shù)據(jù)、審計(jì)等
• 在生產(chǎn)環(huán)境中，數(shù)據(jù)的安全性至關(guān)重要
• 任何數(shù)據(jù)的丟失都可能產(chǎn)生嚴(yán)重的后果

2.2 備份的重要性

在企業(yè)中，數(shù)據(jù)的價(jià)值至關(guān)重要，數(shù)據(jù)保障了企業(yè)業(yè)務(wù)的正常運(yùn)行。

因此，數(shù)據(jù)的安全性及數(shù)據(jù)的可靠性是運(yùn)維的重中之重，任何數(shù)據(jù)的吊事都可能對(duì)企業(yè)產(chǎn)生嚴(yán)重的后果。

== 通常情況下，造成數(shù)據(jù)丟失的原因有一下幾種：==

• 程序錯(cuò)誤
• 人為操作錯(cuò)誤
• 運(yùn)算錯(cuò)誤
• 磁盤故障
• 災(zāi)難（火災(zāi)、地震、盜竊等）

2.3 備份類型

從物理與邏輯的角度分類可分為：邏輯備份、物理備份

從數(shù)據(jù)庫的備份策略角度分類可分為：完全備份、差異備份、增量備份

• ==完全備份：==每次對(duì)數(shù)據(jù)進(jìn)行完整的備份，即對(duì)整個(gè)數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份，保存的是備份完成時(shí)刻的數(shù)據(jù)庫，是差異備份與增量備份的基礎(chǔ)。完全備份的備份與恢復(fù)操作都非常簡(jiǎn)單方便，但是數(shù)據(jù)存在大量的重復(fù)，并且會(huì)占用大量的磁盤空間，備份的時(shí)間也很長(zhǎng)。
• ==差異備份：==備份那些自從上次完全備份之后被修改過的所有文件，備份的時(shí)間節(jié)點(diǎn)是從上次完整備份起，備份數(shù)據(jù)量會(huì)越來越大?；謴?fù)數(shù)據(jù)時(shí)，只需恢復(fù)上次的完全備份與最近的一次差異備份。
• ==增量備份：==只有那些在上次完全備份或者增量備份后被修改的文件才會(huì)被備份。以上次完整備份或上次增量備份的時(shí)間為時(shí)間點(diǎn)，僅備份這之間的數(shù)據(jù)變化，因而備份的數(shù)據(jù)量小，占用空間小，備份速度快。但恢復(fù)時(shí)，需要從上一次的完整備份開始到最后一次增量備份之的所有增量依次恢復(fù)，如中間某次的備份數(shù)據(jù)損壞，將導(dǎo)致數(shù)據(jù)的丟失。

2.4 備份的辦法

數(shù)據(jù)庫的備份可以采用很多種方式，如直接打包數(shù)據(jù)庫文件（物理冷備份）、專用備份工具（mysqldump）、二進(jìn)制日志增量備份、第三方工具備份等

2.4.1 冷備份

• 冷備份時(shí)需要在數(shù)據(jù)庫處于關(guān)閉狀態(tài)下，能夠較好地保證數(shù)據(jù)庫的完整性
• 冷備份的特點(diǎn)就是速度快，恢復(fù)時(shí)也是最為簡(jiǎn)單的。
• 通過直接打包數(shù)據(jù)庫文件夾(/usr/loc.al/mysql/data)來實(shí)現(xiàn)備份

2.4.2 通過啟用二進(jìn)制日志進(jìn)行增量備份

• 支持增量備份，進(jìn)行增量備份時(shí)必須啟用二進(jìn)制日志。
• 二進(jìn)制日志文件為用戶提供復(fù)制，對(duì)執(zhí)行備份點(diǎn)后進(jìn)行的數(shù)據(jù)庫更改所需的信息進(jìn)行恢復(fù)。
• 如果進(jìn)行增量備份(包含自上次完全備份或增量備份以來發(fā)生的數(shù)據(jù)修改) ，需要刷新二進(jìn)制日志

2.4.3 通過第三方工具備份

第三方工具Percona xtraBackup是一個(gè)免費(fèi)的MysQL熱備份軟件，支持在線熱備份Innodb和xtraDB，也可以支持MySQL表備份，不過MyISAM表的備份要在表鎖的情況下進(jìn)行。

2.5 備份命令

• 完全備份

InnoDB存儲(chǔ)引擎的數(shù)據(jù)庫在磁盤上存儲(chǔ)成三個(gè)文件：db.opt(表屬性文件)、表名.frm(表結(jié)構(gòu)文件)、表名.ibd(表數(shù)據(jù)文件)。

物理冷備份與恢復(fù)

systemctl stop mysqld
yum -y install xz
 
#壓縮備份
cd /usr/local/mysql/data
tar jcvf mysql_all_$(date +%F).tar.xz /usr/local/mysql/data
systemctl start mysqld
 
#模擬故障，刪除數(shù)據(jù)庫
drop database HUISUO;
 
#解壓恢復(fù)
tar jxvf /opt/mysql_all_2022-06-21.tar.xz -C /usr/local/mysql/data
cd /usr/local/mysql/data
mv usr/local/mysql/data/* ./

完全備份一個(gè)或多個(gè)完整的庫（包括其中所有的表）

mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] ... > /備份路徑/備份文件名.sql   
#導(dǎo)出的就是數(shù)據(jù)庫腳本文件
 
例:
 
mysqldump -u root -p --databases liu > /opt/kgc.sql       #備份一個(gè)kgc庫
mysqldump -u root -p --databases mysql li > /opt/mysql-kgc.sql    #備份mysql與 kgc兩個(gè)庫

備份所有的庫

mysqldump -uroot -p[密碼] --all-databases > /備份路徑/備份文件名.sql
 

完全備份指定庫中的部分表

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] … > /備份路徑/備份文件名.sql
 
如：
mysqldump -uroot -p[密碼] [-d] HUISUO member1 > /opt/member1.sql
#使用“-d”選項(xiàng)，說明只保存數(shù)據(jù)庫的表結(jié)構(gòu)
#不使用“-d”選項(xiàng)，說明表數(shù)據(jù)也進(jìn)行備份

查看備份文件

grep -v "^--" /opt/member1.sql | grep -v "^/" | grep -v "^$"

2.6 增量備份與恢復(fù)

2.6.1 增量備份需要開啟二進(jìn)制日志功能

vim /etc/my.cnf
#錯(cuò)誤日志
log-error=/usr/local/mysql/data/mysql_error.log	 
#通用查詢?nèi)罩?
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#二進(jìn)制日志
log-bin=mysql-bin	
#慢查詢?nèi)罩?
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
#配置文件添加完后需要重啟MySQL
systemctl restart mysql

2.6.2 可每天進(jìn)行增量備份操作，生成新的二進(jìn)制文件

先完成完全備份（在創(chuàng)建好表和庫的基礎(chǔ)上）

systemctl restart mysqld.service
mysqldump -uroot -p meeting working > /mnt/meeting_working_$(date +%F).sql
mysqldump -uroot -p meeting > /mnt/meeting_$(date +%F).sql
生成新的二進(jìn)制文件(可每天進(jìn)行增量備份操作)
mysqladmin -uroot -p flush-logs

2.6.3 查看新生成的日志內(nèi)容

mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000002

2.7 恢復(fù)的方法

2.7.1 按位置恢復(fù)

先刪除表
drop table working;
 
清空表內(nèi)容
truncate table meeting.working;
 
恢復(fù)結(jié)束點(diǎn)為刪除命令前和插入命令后
mysqlbinlog --no-defaults --stop-position='902' usr/local/mysql/data/mysql-bin.000003 | mysql -uroot -p

2.7.2 按時(shí)間恢復(fù)

先清空表CLASS1,方便實(shí)驗(yàn)
mysql -uroot -p -e "truncate table meeting.working;"
mysql -uroot -p -e "select * from meeting.woring;"
 
mysqlbinlog --no-defaults --stop-datetime='2021-04-15 15:39:23' /opt/mysql-bin.000003 |mysql -uroot -p
mysql -uroot -p -e "select * from meeting.woring;"

總結(jié)

mysql沒有直接提供增量備份的工具，需要借助二進(jìn)制日志文件進(jìn)行操作

使用日志分隔日志的方式進(jìn)行增量備份

增量恢復(fù)需要根據(jù)日志文件的時(shí)間先后逐個(gè)執(zhí)行

使用基于時(shí)間和位置的方式進(jìn)行恢復(fù)，可以更精準(zhǔn)的恢復(fù)數(shù)據(jù)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論