一、MySQL日志管理

1、MySQL日志文件

• 常用的日志文件（在/etc/my.cnf中[mysqld]客戶端配置中修改）
• MySQL的默認(rèn)日志保存位置為/usr/local/mysql/data

錯(cuò)誤日志

用于記錄MySQL啟動(dòng)、停止或運(yùn)行時(shí)發(fā)生的錯(cuò)誤信息，默認(rèn)已開啟

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

二進(jìn)制日志

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

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

中繼日志

一般情況下它在MySQL主從同步、讀寫分離集群的從節(jié)點(diǎn)才開啟，主節(jié)點(diǎn)一般不需要這個(gè)日志。

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

用來記錄所有執(zhí)行時(shí)間超過long_query_time秒的語句，可以找到哪些查詢語句執(zhí)行時(shí)間長，以便于優(yōu)化，默認(rèn)是關(guān)閉的

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í)間為10秒systemctl restart mysqld

二、查看日志狀態(tài)命令

1、查看通用查詢?nèi)罩臼欠耖_啟

mysql -u root -p show variables like 'general%';

2、查看二進(jìn)制日志是否開啟

show variables like 'log_bin%';

3、查看慢查詢?nèi)展δ苁欠耖_啟

show variables like '%slow%';

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

show variables like 'long_query_time';

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

set global slow_query_log=ON; 該方法重啟服務(wù)失效

三、備份的重要性

在企業(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)重的后果。

1、造成數(shù)據(jù)丟失的原因有如下幾種

程序錯(cuò)誤

人為操作錯(cuò)誤

運(yùn)算錯(cuò)誤

磁盤故障

災(zāi)難（如火災(zāi)、地震)和盜竊

四、備份類型

1、從物理與邏輯的角度分類

數(shù)據(jù)庫備份可以分為物理備份和邏輯備份。物理備份是對(duì)數(shù)據(jù)庫操作系統(tǒng)的物理文件 (如數(shù)據(jù)文件、日志文件等）的備份。這種類型的備份適用于在出現(xiàn)問題時(shí)需要快速恢復(fù)的大型重要數(shù)據(jù)庫。物理備份又可以分為 冷備份（脫機(jī)備份）、熱備份（聯(lián)機(jī)備份）和溫備份。

冷備份:在數(shù)據(jù)庫關(guān)閉狀態(tài)下進(jìn)行備份操作。(tar)

熱備份:在數(shù)據(jù)庫處于運(yùn)行狀態(tài)時(shí)進(jìn)行備份操作，該備份方法依賴數(shù)據(jù)庫的日志文件。(mysqldump)

溫備份:數(shù)據(jù)庫鎖定表格（不可寫入但可讀）的狀態(tài)下進(jìn)行備份操作。

邏輯備份是對(duì)數(shù)據(jù)庫邏輯組件（如表等數(shù)據(jù)庫對(duì)象）的備份，表示為邏輯數(shù)據(jù)庫結(jié)構(gòu)（CREATE DATABASB，CREATBTABLE語句)和內(nèi)容(INSERT語句或分隔文本文件）的信息。這種類型的備份適用于可以編輯數(shù)據(jù)值或表結(jié)構(gòu)較小的數(shù)據(jù)量，或者在不同的機(jī)器體系結(jié)構(gòu)上重新創(chuàng)建數(shù)據(jù)

2、從數(shù)據(jù)庫的備份策略角度分類

從數(shù)據(jù)庫的備份策略角度，數(shù)據(jù)庫的備份可分為完全備份、差異備份和增量備份（面試點(diǎn))

完全備份:每次對(duì)數(shù)據(jù)進(jìn)行完整的備份，即對(duì)整個(gè)數(shù)據(jù)庫、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份，保存的是備份完成時(shí)刻的數(shù)據(jù)庫，是差異備份與增量備份的基礎(chǔ)。完全備份的備份與恢復(fù)操作都非常簡單方便，但是數(shù)據(jù)存在大量的重復(fù)，并且會(huì)占用大量的磁盤空間，備份的時(shí)間也很長。

差異備份:備份那些自從上次完全備份之后被修改過的所有文件，備份的時(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ù)的丟失。

3、備份方法

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

物理冷備份

物理冷備份時(shí)需要在數(shù)據(jù)庫處于關(guān)閉狀態(tài)下，能夠較好地保證數(shù)據(jù)庫的完整性。物理冷備份一般用于非核心業(yè)務(wù)，這類業(yè)務(wù)一般都允許中斷，物理冷備份的特點(diǎn)就是速度快，恢復(fù)時(shí)也是最為簡單的。通常通過直接打包數(shù)據(jù)庫文件夾（/usr/local/mysql/data)來實(shí)現(xiàn)備份

專用備份工具mysqldump或mysqlhotcopy

mysqldump程序和mysqlhotcopy都可以做備份。 mysqldump是客戶端常用邏輯備份程序，能夠產(chǎn)生一組被執(zhí)行以后再現(xiàn)原始數(shù)據(jù)庫對(duì)象定義和表數(shù)據(jù)的sgr語句。它可以轉(zhuǎn)儲(chǔ)一個(gè)到多個(gè)MysQI數(shù)據(jù)庫，對(duì)其進(jìn)行備份或傳輸?shù)竭h(yuǎn)程sQI服務(wù)器。mysqldump更為通用， 因?yàn)樗梢詡浞莞鞣N表。 mysqlhotcopy 僅適用于某些存儲(chǔ)引擎。

通過啟用二進(jìn)制日志進(jìn)行增量備份MySQL 支持增量備份，進(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)制日志。 通過第三方工具備份 Percona XtraBackup是一個(gè)免費(fèi)的 MySgL熱備份軟件，支持在線熱備份Innodb 和xtraDB,也可以支持MySQL表備份，不過 MyISAM表的備份要在表鎖的情況下進(jìn)行。

五、數(shù)據(jù)庫冷備份與恢復(fù)及完全備份與恢復(fù)的基本命令

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

systemctl stop mysqld yum -y install xz （一種壓縮工具，詳單與gzip，bz2）壓縮備份 tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/解壓恢復(fù) tar Jxvf /opt/mysql_all_2021-04-14.tar.xz -C /usr/local/mysql/datasystemctl restart mysql

2、mysqldump 備份與恢復(fù)

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

mysqldump -u root -p[密碼] --databases 庫名1 [庫名2] … > /備份路徑/備份文件名.sql #導(dǎo)出的就是數(shù)據(jù)庫腳本文件

例：

mysqldump -uroot -p35123512 --databases school > /opt/school.sql

完全備份 MySQL 服務(wù)器中所有的庫

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

例：

mysqldump -u root -p35123512 --all-databases > /opt/all_database.sql

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

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] … > /備份路徑/備份文件名.sql

例：

mysqldump -uroot -p35123512 school test > /opt/school_test.sql #使用“-d”選項(xiàng)，說明只保存數(shù)據(jù)庫的表結(jié)構(gòu) #不使用“-d”選項(xiàng)，說明表數(shù)據(jù)也進(jìn)行備份

3、查看備份文件

cat /opt/備份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"grep -v "^--" /opt/school_test.sql | grep -v "^/" | grep -v "^$"

4、MySQL 完全恢復(fù)

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

#“-e”選項(xiàng)，用于指定連接 MySQL 后執(zhí)行的命令，命令執(zhí)行完后自動(dòng)退出 mysql -u root -p -e 'drop database school;' mysql -u root -p -e 'show databases;'mysql -u root -p < /opt/school.sql mysql -u root -p -e 'show databases;'

六、MySQL增量備份與恢復(fù)

1、二進(jìn)制日志（binlog）有三種不同的記錄格式

STATEMENT（基于SQL語句）
binlog format=STATEMENT默認(rèn) 每一條涉及到被修改的sql都會(huì)記錄在binlog中。 缺點(diǎn):日志量過大,如sleep()函數(shù)， last_insert_id()>，以及user-definedfunctions(udf)、主從復(fù)制等架構(gòu)記錄日志時(shí)會(huì)出問題會(huì)出現(xiàn)問題

ROW（基于行）
shell binlog format=ROw 只記錄變動(dòng)的記錄，不記錄sql的上下文環(huán)境。 缺點(diǎn):如果遇到updata … set … where true那么就binlog的數(shù)據(jù)量就變大

MIXED（混合模式）
binlog format=M工XED推薦使用 一般的語句使用statement,函數(shù)使用ROW方式存取。

2、MySQL增量備份
開啟二進(jìn)制日志文件

vim /etc/my.cnf [mysqld] log-bin=mysql-bin server-id = 1 binlog_format = MIXED #指定二進(jìn)制日志(binlog)的記錄格式為 MIXED#二進(jìn)制日志(binlog)有3種不同的記錄格式：STATEMENT(基于SQL語句)、ROW(基于行)、MIXED(混合模式)，默認(rèn)格式是STATEMENTsystemctl restart mysqld.service ls -l /usr/local/mysql/data/mysql-bin.*

可每周對(duì)數(shù)據(jù)庫或表進(jìn)行完全備份（增量備份是基于完全備份，所以這里我們直接備份數(shù)據(jù)庫）

#完全備份

mysqldump -u root -p school test > /opt/school_test_$(date +%F).sql mysqldump -u root -p school > /opt/school_$(date +%F).sql#使用crontab -e 計(jì)劃性任務(wù)來執(zhí)行；每周1凌晨2點(diǎn)對(duì)表test和school庫進(jìn)行完全備份 0 2 * * 1 mysqldump -u root -p school test > /opt/school_test_$(date +%F).sql 0 2 * * 1 mysqldump -u root -p school > /opt/school_$(date +%F).sql

可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

插入新數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更

mysql -u root -p use school； insert into test values (3,'李四','廣州'); insert into test values (4,'五一','南京');

再次生成新的二進(jìn)制日志文件(例如 mysql-bin.000003)

mysqladmin -u root -p flush-logs #上面我們往test表中插入數(shù)據(jù)的操作會(huì)保存到mysql-bin.000002文件中，之后數(shù)據(jù)庫數(shù)據(jù)再發(fā)生變化則保存在mysql-bin.000003文件中

查看二進(jìn)制日志文件的內(nèi)容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/ mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 #--base64-output=decode-rows：使用64位編碼機(jī)制去解碼并按行讀取 #-v：顯示詳細(xì)內(nèi)容

3、MySQL 增量恢復(fù)

一般恢復(fù)

模擬丟失更改的數(shù)據(jù)的恢復(fù)步驟

mysql -u root -p use school; delete from test where id=3; delete from test where id=4; select * from test; quitmysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p mysql -u root -p -e "select * from school.test;"

模擬丟失所有數(shù)據(jù)的恢復(fù)步驟

mysql -u root -p use school; drop table test; show tables; quitmysql -uroot -p school < /opt/school_test_2021-04-15.sql mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p mysql -u root -p -e "select * from school.test;"

斷點(diǎn)恢復(fù)

mysqladmin -uroot -p35123512 flush-logs 進(jìn)行日志刷新 查看我們剛才操作保存的日志000007二進(jìn)制日志文件 mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000007 # at 219 #210415 17:09:24 server id 1 end_log_pos 354 CRC32 0x00c7f80d Query thread_id=38 exec_time=0 error_code=0 use `school`/*!*/; SET TIMESTAMP=1618477764/*!*/; SET @@session.pseudo_thread_id=38/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1437073414/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; create table test (id int(5),name varchar(10),age int(5)) /*!*/; # at 354 #210415 17:11:10 server id 1 end_log_pos 419 CRC32 0x6ed07c34 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 419 #210415 17:11:10 server id 1 end_log_pos 502 CRC32 0xba0129d4 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477870/*!*/; BEGIN /*!*/; # at 502 #210415 17:11:10 server id 1 end_log_pos 639 CRC32 0x463df0b3 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477870/*!*/; insert into test values (1,'張三','10'),(2,'李四','20') /*!*/; # at 639 #210415 17:11:10 server id 1 end_log_pos 670 CRC32 0xda19363b Xid = 499 COMMIT/*!*/; # at 670 #210415 17:11:32 server id 1 end_log_pos 735 CRC32 0x99150b17 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 735 #210415 17:11:32 server id 1 end_log_pos 818 CRC32 0x34f81d52 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477892/*!*/; BEGIN /*!*/; # at 818 #210415 17:11:32 server id 1 end_log_pos 937 CRC32 0x80433f9d Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477892/*!*/; insert into test values (3,'王五','25') /*!*/; # at 937 #210415 17:11:32 server id 1 end_log_pos 968 CRC32 0xd3537b3b Xid = 500 COMMIT/*!*/; # at 968 #210415 17:12:40 server id 1 end_log_pos 1033 CRC32 0x91cc3262 Anonymous_GTID last_committed=3sequence_number=4 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1033 #210415 17:12:40 server id 1 end_log_pos 1116 CRC32 0x17bd5f4a Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477960/*!*/; BEGIN /*!*/; # at 1116 1116位置是模擬我們誤刪除文件的操作 #210415 17:12:40 server id 1 end_log_pos 1221 CRC32 0x35e5f3c9 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477960/*!*/; delete from test where id=1 /*!*/; # at 1221 #210415 17:12:40 server id 1 end_log_pos 1252 CRC32 0x5868d5c4 Xid = 503 COMMIT/*!*/; # at 1252 #210415 17:13:06 server id 1 end_log_pos 1317 CRC32 0xc864fc14 Anonymous_GTID last_committed=4sequence_number=5 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1317 #210415 17:13:06 server id 1 end_log_pos 1400 CRC32 0x8c31a8b8 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477986/*!*/; BEGIN /*!*/; # at 1400 #210415 17:13:06 server id 1 end_log_pos 1519 CRC32 0xaef20ea3 Query thread_id=38 exec_time=0 error_code=0 SET TIMESTAMP=1618477986/*!*/; insert into test values (4,'田一','30') /*!*/;

基于位置恢復(fù)

這邊我們模擬刪除id=1的操作為誤刪除 我們進(jìn)行恢復(fù)

恢復(fù)到操作ID為‘1116’之前的數(shù)據(jù)，就是不執(zhí)行刪除id=1的操作 mysqlbinlog --no-defaults --stop-position='1116' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p35123512 意思就是跳過我們刪除的操作其余的都恢復(fù) mysqlbinlog --no-defaults --start-position='1400' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p35123512

操作前首先要?jiǎng)h除需要備份的表

基于時(shí)間點(diǎn)恢復(fù)
刪除school庫中的test表，先基于完整備份的恢復(fù)，在基于時(shí)間點(diǎn)恢復(fù)

#僅恢復(fù)到17:12:40之前的數(shù)據(jù)，就是不執(zhí)行刪除id=1的操作直接跳過 mysqlbinlog --no-defaults --stop-datetime='2021-04-15 17:12:40' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p35123512 #跳過17:12:40的數(shù)據(jù)恢復(fù)之后的數(shù)據(jù) mysqlbinlog --no-defaults --start-datetime='2021-04-15 17:13:06' /usr/local/mysql/data/mysql-bin.000007 | mysql -uroot -p35123512

總結(jié)：斷點(diǎn)恢復(fù)

如果恢復(fù)某條SQL語句之前的所有數(shù)據(jù)，就stop在這個(gè)語句的位置節(jié)點(diǎn)或者時(shí)間點(diǎn)
G9nLmNzZG4ubmV0L0lIQk9T,size_16,color_FFFFFF,t_70)

總結(jié)：斷點(diǎn)恢復(fù)

如果恢復(fù)某條SQL語句之前的所有數(shù)據(jù)，就stop在這個(gè)語句的位置節(jié)點(diǎn)或者時(shí)間點(diǎn)
如果恢復(fù)某條SQ語句以及之后的所有數(shù)據(jù)，就從這個(gè)語句的位置節(jié)點(diǎn)或者時(shí)間點(diǎn)start

總結(jié)

以上是生活随笔為你收集整理的一、MySQL日志与备份的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。