當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL（5）日志管理、备份与恢复，断点

發布時間：2024/9/3 数据库 32 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL（5）日志管理、备份与恢复，断点小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

一、MySQL日志管理
- 1.1 日志的分類
- - 1）錯誤日志
  - 2）通用查詢日志
  - 3）二進制日志(binlog)
  - 4）慢查詢日志
  - 5）配置日志
- 1.2 日志的查詢
二、MySQL 完全備份與恢復
- 2.1 數據備份的重要性
- 2.2 造成數據丟失的原因
- 2.3 數據庫備份的分類
- - 2.3.1 從物理和邏輯角度分
  - - 1）物理備份
    - 2）邏輯備份
- 2.4 數據庫的備份策略
- 2.5 MySQL 完全備份
- - 2.5.1 物理冷備份與恢復
  - 2.1.2 專用備份工具mydump 和mysqlhotocopy
  - - （1）完全備份一個或多個完整的庫（包括其中所有的表）
    - （2）完全備份 MySQL 服務器中所有的庫
    - （3）完全備份指定庫中的部分表
    - （4）查看備份文件
  - 2.1.3 MySQL 完全備份（使用免交互）
  - - （1）恢復數據庫
    - （2）恢復數據表
  - 2.1.4 MySQL 增量備份與恢復
  - - 1．開啟二進制日志功能
    - 2．可每周對數據庫或表進行完全備份
    - 3．可每天進行增量備份操作，生成新的二進制日志文件(例如 mysql-bin.000002)
    - 4．插入新數據，以模擬數據的增加或變更
    - 5．再次生成新的二進制日志文件(例如 mysql-bin.000005)
    - 6．查看二進制日志文件的內容
  - 2.1.5 MySQL 增量恢復
  - - 1．一般恢復
    - 2．斷點恢復

一、MySQL日志管理

MySQL 的日志默認保存位置為/usr/local/mysql/data
MySQL 的日志配置文件為/etc/my.cnf ，里面有個[mysqld]項。

這是我們當時安裝mysql時指定的目錄，如下圖所示：

1.1 日志的分類

1）錯誤日志

用來記錄當MySQL啟動、停止或運行時發生的錯誤信息，默認已開啟

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）二進制日志(binlog)

用來記錄所有更新了數據或者已經潛在更新了數據的語句，記錄了數據的更改，可用于數據恢復，默認已開啟

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秒

5）配置日志

1）修改my,cof配置文件

2）重新mysql服務

1.2 日志的查詢

mysql -u root -p[密碼]
show variables like ‘general%’; #查看通用查詢日志是否開啟

show variables like ‘log_bin%’; #查看二進制日志是否開啟

show variables like ‘%slow%’; #查看慢查詢日功能是否開啟
show variables like ‘long_query_time’; #查看慢查詢時間設置

set global show_query_log=ON; #在數據庫中設置開啟慢查詢的方法

二、MySQL 完全備份與恢復

2.1 數據備份的重要性

備份的主要目的是災難恢復
在生產環境中，數據的安全性至關重要
任何數據的丟失都可能產生嚴重的后果

2.2 造成數據丟失的原因

程序錯誤
人為操作錯誤.
運算錯誤
磁盤故障
災難(如火災、地震)和盜竊

2.3 數據庫備份的分類

2.3.1 從物理和邏輯角度分

1）物理備份

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

物理備份的方法：

冷備份(脫機備份) :是在關閉數據庫的時候進行的；
熱備份(聯機備份) :數據庫處于運行狀態，依賴于數據庫的日志文件；
溫備份:數據庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作。

2）邏輯備份

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

2.4 數據庫的備份策略

完全備份:每次對數據庫進行完整的備份

是對整個數據庫、數據庫結構和文件結構的備份
保存的是備份完成時刻的數據庫
是差異備份與增量備份的基礎

優點：備份與恢復操作簡單方便
缺點：數據存在大量的重復、占用大量的備份空間及備份與恢復時間長

差異備份:備份自從上次完全備份之后被修改過的文件
增量備份:只有在上次完全備份或者增量備份后被修改的文件才會被備份

use school;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
sex char(10) not null,
hobby varchar(50),
primary key (id));

insert into info1 values(1,‘user1’,‘male’,‘running’);
insert into info1 values(2,‘user2’,‘female’,‘singing’);

2.5 MySQL 完全備份

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

實驗環境如下：在school庫中有info1、2張表

2.5.1 物理冷備份與恢復

備份時數據庫處于關閉狀態，直接打包數據庫文件
備份速度快，恢復時也是最簡單的
操作步驟（理論）：

systemctl stop mysqld
yum -y install xz
cd /usr/local/mysql

#壓縮備份data目錄
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz data/
#解壓恢復data目錄
tar Jxvf /opt/mysql_all_2021-06-26.tar.xz -C ./

實驗操作步驟（實操）：
1）備份data命令

2）登錄mysql，刪除school庫

3）解壓之前備份的數據庫data目錄，不用刪除原目錄，會自動替換

4）重啟服務查看被刪除的庫

2.1.2 專用備份工具mydump 和mysqlhotocopy

mysqldump常用的邏輯備份工具
mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表

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

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

例：備份單個和多個庫

mysqldump -uroot -p --databases school > /opt/backup/school.sql
mysqldump -uroot -p --databases school yy > /opt/backup/school_yy.sql

（2）完全備份 MySQL 服務器中所有的庫

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

例：備份所有庫

mysqldump -uroot -p --all-databases > /opt/backup/all.sql

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

mysqldump -u root -p[密碼] 庫名 [表名1] [表名2] … > /備份路徑/備份文件名.sql
例：備份school庫中的兩個表

mysqldump -uroot -p school [-d] info1 info2 > /opt/backup/school_info1_info2.sql

#使用“-d”選項，說明只保存數據庫的表結構
#不使用“-d”選項，說明表數據也進行備份

（4）查看備份文件

grep -v “^–” /opt/backup/school_info1_info2.sql | grep -v “^/” | grep -v “^$”

2.1.3 MySQL 完全備份（使用免交互）

systemctl start mysqld

（1）恢復數據庫

mysql -u root -p -e ‘drop database school;’
#“-e”選項，用于指定連接 MySQL 后執行的命令，命令執行完后自動退出
mysql -u root -p -e ‘show databases;’

#恢復school數據庫
mysql -u root -p < /opt/backup/school.sql
mysql -u root -p -e ‘show databases;’
具體操作：

（2）恢復數據表

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

mysqldump -u root -p kgc info1 > /opt/kgc_info1.sql

mysql -u root -p -e ‘drop table kgc.info1;’
mysql -u root -p -e ‘show tables from kgc;’

mysql -u root -p kgc < /opt/kgc_info1.sql
mysql -u root -p -e ‘show tables from kgc;’

具體操作：
1)刪除school中的class1表

2）恢復school庫中的表

2.1.4 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 start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

測試：

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

mysqldump -u root -p school class1 > /opt/school_class1_ $KaTeX parse error: Expected group after '_' at position 83: \dotsl > /opt/school_?$ (date +%F).sql #對所有的庫做備份

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

mysqladmin -u root -p flush-logs

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

use school;
insert into info1 values(6,‘qq’,‘女’,‘上天’);
insert into info1 values(7,‘la’,‘男’,‘整容’);

5．再次生成新的二進制日志文件(例如 mysql-bin.000005)

mysqladmin -u root -p flush-logs

#之前的步驟4的數據庫操作會保存到mysql-bin.000004文件中，之后數據庫數據再發生變化則保存在mysql-bin.000005文件中

6．查看二進制日志文件的內容

cp /usr/local/mysql/data/mysql-bin.000004 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt//backup/mysql-bin.000004
#–base64-output=decode-rows：使用64位編碼機制去解碼并按行讀取
#-v：顯示詳細內容

2.1.5 MySQL 增量恢復

1．一般恢復

（1）模擬丟失更改的數據的恢復步驟

use school;
delete from info1 where id=6;
delete from info1 where id=7;

mysqlbinlog --no-defaults mysql-bin.000007 | mysql -u root -p123123
具體操作：
1）刪除info1表中的末2行數據

2）通過二進制日志進行恢復

（2）模擬丟失所有數據的恢復步驟
use school;
drop table class1;

mysql -u root -p school < /opt/backup/school_info1_info2.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000005 | mysql -u root -p

具體操作：

1）刪除school庫中的class1表

2）先回復整表，然后在恢復二進制日志文件

2．斷點恢復

實驗：表和數據如下

mysqladmin -u root -p flush-logs # 刷新生成新的二進制日志文件
cd /usr/local/mysql/data # 進入到data目錄
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.0000009 #查看二進制日志文件

例：

# at 497 #斷點
#210831 21:24:46 #時間
insert into football values(1,‘梅西’,34,800000) #插入的數據

# at 2246
#210831 21:28:02 #時間
insert into football values(7,‘楊鵬’,23,100) #插入的數據

（1）基于位置恢復

#僅恢復到操作 ID 為“2246之前的數據，即不恢復“后面一條”的數據
mysqlbinlog --no-defaults --stop-position=‘2246’ mysql-bin.000009 | mysql -uroot -p

1）刪除football表中所有數據

2）對數據進行恢復

恢復最后一條數據
mysqlbinlog --no-defaults --start-position=‘2246’ mysql-bin.000006 | mysql -uroot -p

1）重新刪除數據