一.Mysql日志分類

MySQL的默認日志保存位置為/usr/local/mysql/data

vim /etc/my.cnf

1.錯誤日志

說明：

在對應的數據目錄中，以主機名+.err命名的文件，錯誤日志記錄的信息類型：

記錄了服務器運行中產生的錯誤信息

記錄了服務在啟動和停止是所產生的信息

在從服務器上如果啟動了復制進程的時候，復制進程的信息也會被記錄

記錄event錯誤日志

#指定日志的保存位置和文件名

log-error=/usr/local/mysql/data/mysql_error.log

2.通用查詢日志

說明：

用來記錄MySQL的所有連接和語句，默認是關閉的。

對除了慢查日志中記錄的查詢信息都將記錄下來,這將對服務器主機產生大量的壓力，所以對于繁忙的服務器應該關閉這個日志與查詢日志相關的變量。

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

3.二進制日志(bin log)

說明：

用來記錄所有當MySQL啟動、停止或運行時發送的錯誤信息。

默認開啟，精確的記錄了用戶對數據庫中的數據進行操作的命令和操作的數據對象。

二進制日志文件的作用：

提供了增量備份的功能。

提供了數據基于時間點的恢復，這個恢復的時間點可以由用戶控制。

為mysql的復制架構提供基礎，將這主服務器的二進制日志復制到從服務器上并執行同樣的操作，就可將數據進行同步。

log-bin=mysql-bin #也可以 log_bin=mysql-bin

4.慢日志查詢

說明：

用來記錄所有執行時間超過long_query_time秒的語句，可以找到哪些查詢語句執行時間長，以便于優化。

默認為關閉狀態，記錄下來查詢時間超過設定時長的查詢，這些查詢日志將被慢查日志記錄下來。

slow_query_log=ON

slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log

long_query_time=5 #設置超過5秒執行的語句被記錄，缺省時為10秒。

二.查看日志是否開啟

1.查看通用日志是否開啟

show variables like 'general%';

2.查看二進制值日志是否開啟

show variables like 'log_bin%';

3.查看慢查詢日志功能是否開啟

show variables like '%slow%';

(1)查看慢查詢時間

show variables like 'long_query_time';

三.日志備份

1.日志備份的重要性

備份的主要目的是災難恢復

在生產環境中，數據的安全性至關重要

任何數據的丟失都可能產生嚴重的后果

造成數據丟失的原由

1.程序錯誤

2.認為操作錯誤

3.運算錯誤

4.磁盤故障

5.災難(如火災、地震等不可抗力元素)和盜竊

2.備份分類

(1.)從物理與邏輯的角度，備份可分為

物理備份

對數據庫操作系統的物理文件(如數據文件、日志文件等)的備份

物理備份的方法：

1.冷備份(脫機備份)：是在關閉數據庫的時候進行的

2.熱備份(聯機備份)：數據庫處于運行狀態，依賴于數據庫的日志文件

3.溫備份：數據庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作

邏輯備份

對數據庫邏輯組件(如：表等數據庫對象)的備份

(2.)從數據庫備份策略角度，備份可分為

1.完全備份：每次對數據庫進行完整的備份

2.差異備份：備份自從上次完全備份之后被修改過的文件

3.增量備份：只要在上次完全備份或者增量備份后被修改的文件才會被備份

(3.)常用備份方法

物理冷備份

1.備份時數據庫處于關閉狀態，直接打包數據庫文件

2.備份速度快，恢復時也是最簡單的

3.關閉MySQL數據庫

4.使用tar命令直接打包數據庫文件夾

5.直接替換現有的MySQL目錄即可

專用備份工具

1.mysqldump常用的邏輯備份工具

- MySQL自帶的備份工具，可實現對MySQL的備份

- 可以將指定的庫、表導出為SQL腳本

- 使用命令mysql導入備份的數據

2.mysqlhotcopy僅擁有備份myisam和archive表

啟用二進制日志進行增量備份

進行增量備份，需要刷新二進制日志

第三工具備份

免費的MySQL熱備份軟件Percona XtraBackup

完全備份

1.是對整個數據庫、數據庫結構和文件結構的備份

2.保存的是備份完成時刻的數據庫

3.是差異備份與增量備份的基礎

優點：備份與恢復操作簡單方便

缺點：1.數據存在大量的重復

2.占用大量的備份空間

3.備份恢復時間長

四.Mysql完全備份與恢復

1.實驗

create database LJ; #創建LJ庫

use LJ; #進入LJ庫

create table if not exists LJR1 ( #如果沒有LJR1的表則創建，有則不創建

id int(4) not null auto_increment, #id最大顯示長度為4位，不為空，自動遞增

name varchar(10) not null, #name可變長度字段最多不能超過10個字符，不為空

sex char(10) not null, #sex固定字段為10個字符，不為空

hobby varchar(50), #hobby可變長度字段最多不能超過20個字符

primary key (id)); #主鍵為id

insert into LJR1 values(1,'liwei','male','running'); #在LJR1表中插入新的數據，id=1，name=liwei，sex=male，hobby=running

insert into LJR1 values(2,'xiaohao','female','singing'); #在LJR1表中插入新的數據，id=2，name=xiaohao，sex=male，hobby=singing

2.完全備份

InnoDB存儲引擎的數據庫在磁盤上存儲成三個文件：db.opt(表屬性文件)、表名.frm(表結構文件)、表名.ibd(表數據文件)。

(1.)物理冷備份與恢復

1.systemctl stop mysqld #關閉數據庫

2.yum -y install xz

3.tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/ #壓縮備份

4.tar Jxvf /opt/mysql_all_2020-2-10.tar.xz -C /usr/local/mysql/data #解壓恢復

5.systemctl restart mysql #重啟數據庫

(2.)數據庫備份

1.完全備份一個或多個完整的庫(包括其中所有的表)

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

示列：

mysqldump -uroot -p123456 --databases LJ > /opt/LJ.sql #完全備份完整的LJ庫到/opt目錄下文件名為LJ.sql

mysqldump -uroot -p123456 --databases mysql LJ > /opt/mysql-LJ.sql #備份多個完整的LJ庫到/opt目錄下文件名為mysql-LJ.sql

2.完全備份服務器中的所有庫

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

示列：

mysqldump -u root -p123456 --all-databases > /opt/all.sql #備份所有的庫到/opt目錄，文件名為all.sql

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

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

示列：

mysqldump -uroot -p123456 LJ LJR1 > /opt/LJ_LJR1.sql #備份LJ庫里的LJR1表到/opt目錄，文件名為LJ_LJR1.sql

#使用“-d”選項，說明只保存數據庫的表結構

#不使用“-d”選項，說明表數據也進行備份

4.查看備份文件

grep -v "^--" /opt/[備份文件名] | grep -v "^/" | grep -v "^$" #過濾--開頭、/開頭和空格

(3.)數據庫恢復

1.恢復數據庫

mysql -uroot -p123456 -e 'drop database LJ;' #刪除LJ庫

mysql -uroot -p123456 -e 'SHOW DATABASES;' #查看所有的庫

mysql -uroot -p123456 < /opt/LJ.sql #恢復數據庫，將備份文件注入數據庫中

2.恢復數據表

當備份文件中只包含表的備份，而不包含創建的庫的語句時，執行導入操作時必須指定庫名，且目標庫必須存在。

mysql -uroot -p123456 -e 'drop table LJ.LJR1;'#刪除LJ庫中的LJR1表

mysql -uroot -p123456 -e 'show tables from LJ;' #查看LJ庫中的表

mysql -uroot -p123456 LJ < /opt/LJ_LJR1.sql #恢復數據表，將備份文件注入數據庫中

五.Mysql增量備份

1.開啟二進制日志功能

vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin

binlog_format = MIXED#可選，指定二進制日志(binlog)的記錄格式為MIXED

server-id = 1

#二進制日志(binlog)有3種不同的記錄格式：STATEMENT(基于SQL語句)、ROW(基于行)、MIXED(混合模式)，默認格式是STATEMENT

systemctl restart mysqld

ls -l /usr/local/mysql/data/mysql-bin.*

2.可每周對數據庫或表進行完全備份

mysqldump -uroot -p123456 LJ LJR1 > /opt/LJ_LJR1_$(date +%F).sql

mysqldump -uroot -p123456 --all-databases LJ > /opt/LJ_$(date +%F).sql

3.可每天進行增量備份操作

可以生成新的二進制日志文件(例如 mysql-bin.000002)

mysqladmin -uroot -p123456 flush-logs #可生成新的二進制文件

4.插入新數據，以模擬數據的增加或變更

mysql -uroot -p123456 #進入數據庫

use LJ; #進入LJ庫

insert into LJR1 values(3,'yuanyuan','female','game'); #插入新的數據記錄id=3，name=yuanyuan，sex=femalehobby=game

insert into LJR1 values(4,'wenlin','male','reading'); #插入新的數據記錄id=4，name=wenlin，sex=male，hobby=reading

select * from LJ; #查看LJR1標的數據

六.Mysql恢復

1.一般恢復

(1.)模擬丟失更改的數據的恢復步驟

mysql -uroot -p123456 #進入數據庫

use LJ; #進入LJ庫

delete from LJR1 where id=3; #刪除id=3的數據記錄

delete from LJR1 where id=4; #刪除id=4的數據記錄

select * from LJR1; #查看LJR1表的所有數據記錄

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123456 #日志恢復，將二進制日志文件注入數據庫

select * from LJ.LJR1; #再次查看LJR1表中的所有數據記錄

(2.)模擬丟失所有數據的恢復步驟

2.斷點恢復

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

(1.)基于位置恢復

1.mysqlbinlog --no-defaults --stop-position='611' /opt/mysql-bin.000002 | mysql -uroot -p123456 #僅恢復到操作 ID 為“611”之前的數據，即不恢復“user4”的數據

2.mysqlbinlog --no-defaults --start-position='611' /opt/mysql-bin.0.00002 | mysql -uroot -p123456 #僅恢復‘user4’的數據，跳過‘user3’的數據恢復

(2.)基于時間恢復

mysqlbinlog --no-defaults --stop-datetime='2021-2-10 12:19:15' /opt/mysql-bin.000002 |mysql -uroot -p123456 #僅恢復到 12:19:15 之前的數據，即不恢復“user4”的數據

mysqlbinlog --no-defaults --start-datetime='2021-2-10 12:19:15' /opt/mysql-bin.000002 |mysql -uroot -p123456 #僅恢復‘user4’的數據，跳過‘user3’的數據恢復

總結

以上是生活随笔為你收集整理的mysql 日志记录 archive_完美起航-Mysql日志管理、备份与恢复的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。