當(dāng)前位置：首頁(yè) > 运维知识 > 数据库 >内容正文

数据库

mariadb数据库备份与恢复

發(fā)布時(shí)間：2025/3/21 数据库 20 豆豆

生活随笔收集整理的這篇文章主要介紹了 mariadb数据库备份与恢复小編覺(jué)得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.

1.查詢?nèi)罩?

記錄每一條sql語(yǔ)句，建議不開(kāi)啟，因?yàn)槿绻L問(wèn)量較大，會(huì)占用相當(dāng)大的資源，影響性能;

vim /etc/my.cnf.d/server.cnf

general_log = ON| OFF#查詢?nèi)罩鹃_(kāi)關(guān)

general_log_file localhost.log #查詢?nèi)罩镜奈募?#xff08;/var/lib/mysql）

log_output TABLE | FILE | NONE #查詢?nèi)罩镜拇鎯?chǔ)形式

2.慢查詢?nèi)罩?/h3>
執(zhí)行時(shí)長(zhǎng)超出指定時(shí)間的查詢操作（測(cè)試命令:select sleep(4);）

slow_query_log = OFF|ON? #開(kāi)啟慢查詢?nèi)罩?/p>
slow_query_log_file = LOCALHOST-SLOW.log #慢查詢?nèi)罩镜奈募窂?/p>
long_query_time? #慢查詢時(shí)長(zhǎng)；默認(rèn)是10s

log_slow_rate_limit#如果要記錄的慢查詢?nèi)罩痉浅６嗟脑?#xff0c;會(huì)按照速率來(lái)記錄，默認(rèn)1秒記錄一個(gè)

log_slow_verbosity=full | query_plan #記錄的詳細(xì)級(jí)別

?

3.錯(cuò)誤日志

mysqld啟動(dòng)和關(guān)閉過(guò)程中輸出的事件信息

mysqld運(yùn)行中產(chǎn)生的錯(cuò)誤信息

event scheduler 運(yùn)行一個(gè)event時(shí)產(chǎn)生的日志信息

在主從復(fù)制架構(gòu)中的從服務(wù)器上啟動(dòng)從服務(wù)器線程時(shí)產(chǎn)生的信息

log_error = /var/log/mysql_error.log#指定錯(cuò)誤日志的輸出位置

log_warnings 為0，表示不記錄告警信息。

log_warnings 為1，表示告警信息寫(xiě)入錯(cuò)誤日志。

log_warnings 大于1，表示各類告警信息，例如有關(guān)網(wǎng)絡(luò)故障的信息和重新連接信息寫(xiě)入錯(cuò)誤日志。(默認(rèn)為2)

4.二進(jìn)制日志

針對(duì)時(shí)間點(diǎn)還原起著至關(guān)重要的作用

1）查看二進(jìn)制日志：

SHOW {BINARY | MASTER} LOGS

SHOW BINLOG EVENTS [IN 'log_name']

show master status;

2）記錄導(dǎo)致數(shù)據(jù)改變或者可能導(dǎo)致數(shù)據(jù)改變的SQL語(yǔ)句

log_bin = OFF | ON

log_bin_basename = /var/lib/mysql/mysql-bin

binlog_format=STATEMENT|ROW|MIXED:二進(jìn)制記錄格式

STATEMENT：基于“語(yǔ)句”記錄

ROW：基于“行”記錄

MIXED：讓系統(tǒng)自行判定該基于哪種方式進(jìn)行

sql_log_bin=1|0:是否啟用二進(jìn)制日志

log_bin_index=PATH:二進(jìn)制日志索引位置

sync_binlog=1|0:設(shè)定是否啟動(dòng)二進(jìn)制日志同步功能

max_binlog_size=SIZE:單個(gè)二進(jìn)制文件最大體積，默認(rèn)為1G

expire_logs_days=0#超過(guò)多少天就清除二進(jìn)制日志，默認(rèn)為0,代表不啟用此功能

3）二進(jìn)制日志滾動(dòng)：

1.flush logs;

2.文件超出指定大小

3.service mariadb restart

4）二進(jìn)制日志文件的構(gòu)成：

兩類文件：

日志文件：mysql-bin.xxxxx，二進(jìn)制格式

索引文件：mysql-bin.index,索引文件

5）二進(jìn)制日志格式:

#190613 14:17:32 server id 1? end_log_pos 666 CRC32 0xeb1cde6b? Query?? thread_id=9???? exec_time=

0?????? error_code=0

use `testdb`/*!*/;

事件發(fā)生的日期和時(shí)間：190613 14:17:32

事件發(fā)生的服務(wù)器標(biāo)識(shí)：server id 1

事件的結(jié)束位置：end_log_pos 666

事件的類型：Query

事件發(fā)生時(shí)所在服務(wù)器執(zhí)行此事件的線程ID：thread_id=9

語(yǔ)句的時(shí)間戳與將其寫(xiě)入二進(jìn)制文件中的時(shí)間差：exec_time=0

錯(cuò)誤代碼：error_code=0

事件內(nèi)容：

GTID:Global Transaction ID;

專屬屬性：GTID

6）mysqlbinlog：客戶端命令工具

mysqlbinlog [options] log_file ...

--start-datetime=

--stop-datetime=

--start-position=

--stop-position=

5.中繼日志:

復(fù)制架構(gòu)中，備服務(wù)器用于保存主服務(wù)器的二進(jìn)制日志中讀取到的事件

6.事務(wù)日志:transaction log（ib_logfile0，ib_logfile1）

innodb_buffer_pool_size??????? ????????????????????????一般設(shè)置成為物理內(nèi)存的3/4,或者4/5

innodb_log_files_in_group = 2? ????????????????????????事務(wù)日志文件的個(gè)數(shù)，默認(rèn)為2個(gè)事務(wù)日志文件

innodb_log_file_size = 50331648（48m）????????事務(wù)日志文件的單個(gè)大小48m

innodb_log_group_home_dir = ./ ????????????????????????事務(wù)日志文件的所在路徑，默認(rèn)就在mariadb的數(shù)據(jù)目錄/var/lib/mysql

事務(wù)型存儲(chǔ)引擎自行管理和使用(Innodb,myisam引擎是不支持事務(wù)，外鍵，行級(jí)鎖)

redo log : 重做日志

undo log ：撤銷(xiāo)日志

7.備份和恢復(fù)：

1）為什么要備份？

災(zāi)難恢復(fù)：硬件故障，軟件故障，自然災(zāi)害，黑客攻擊，誤操作

測(cè)試

2）要注意的要點(diǎn)：

備份需要多少時(shí)間

能容忍最多丟失多少數(shù)據(jù)

恢復(fù)數(shù)據(jù)需要在多場(chǎng)時(shí)間內(nèi)完成

需要恢復(fù)哪些數(shù)據(jù)

（1）做還原測(cè)試，用于測(cè)試備份的可用性

（2）還原演練

3）備份類型:

完全備份，部分備份

完全備份:整個(gè)數(shù)據(jù)集

部分備份:只備份數(shù)據(jù)子集

完全備份、增量備份、差異備份

增量備份:僅備份最近一次完全備份或增量備份（如果有增量備份）以來(lái)變化的數(shù)據(jù)

差異備份:僅備份最近一次完全備份以來(lái)變化的數(shù)據(jù);

熱備、溫備、冷備

熱備:讀寫(xiě)操作均可執(zhí)行

溫備:讀操作可以、寫(xiě)不行

冷備:讀寫(xiě)操作均不能執(zhí)行

MyISAM:溫備，不能熱備；

Innodb：熱備

物理備份、邏輯備份

物理備份：直接復(fù)制數(shù)據(jù)文件進(jìn)行備份，與存儲(chǔ)引擎無(wú)關(guān)

邏輯備份：從數(shù)據(jù)庫(kù)中“導(dǎo)出”數(shù)據(jù)庫(kù)另存而進(jìn)行備份

備份時(shí)需要考慮的因素：

持續(xù)多久

備份過(guò)程的時(shí)長(zhǎng)

備份負(fù)載

恢復(fù)過(guò)程的時(shí)長(zhǎng)

備份什么？

要備份的數(shù)據(jù)

二進(jìn)制日志，innodb的事務(wù)日志

代碼（存儲(chǔ)過(guò)程，存儲(chǔ)函數(shù)，觸發(fā)器，時(shí)間調(diào)度器）

服務(wù)器的配置文件

設(shè)計(jì)備份方案：

數(shù)據(jù)集：完全+增量

備份手段：物理，邏輯

對(duì)于備份較大的數(shù)據(jù)建議物理備份，對(duì)于較小的數(shù)據(jù)建議用邏輯備份;

備份工具的選擇：

mysqldump+復(fù)制binlog：

mysqldump：完全備份

復(fù)制binlog中指定時(shí)間范圍內(nèi)的event：增量備份

lvm2快照+復(fù)制binlog：

lvm2快照：適用cp或者tar等做物理備份：完全備份

復(fù)制binlog中指定時(shí)間范圍內(nèi)的event：增量備份

xtrabackup|mariabackup：

由Percona提供的支持對(duì)InnoDB做熱備（物理備份）工具

完全備份，增量工具

4）基于二進(jìn)制文件的恢復(fù)

1.算好要恢復(fù)數(shù)據(jù)的時(shí)間段，重定向輸入到bin.sql文件中

mysqlbinlog --start-datetime="2019-06-08 22:55:13" --stop-datetime="2019-06-08 22:55:13" binlog.0000011 > bin.sql

mysqlbinlog /var/lib/mysql/mysqlbin.000001 --stop-pos=1093 | mysql -uroot -proot

2.執(zhí)行bin.sql文件還原

source bin.sql

5）基于lvm2的備份

(1)請(qǐng)求鎖定所有表

flush tables with read lock;

(2)記錄二進(jìn)制日志文件及事件位置或時(shí)間點(diǎn)

flush logs;

mysql -uroot -proot -e 'show? master status;' > /path/to/somefile

(3)創(chuàng)建快照

lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

(4)釋放鎖

unlock tables;

(5)掛在快照卷，執(zhí)行數(shù)據(jù)備份

(6)備份完成后，刪除快照卷

(7)指定好策略，通過(guò)原卷備份二進(jìn)制日志

(8)還原的時(shí)候確保屬組和屬主是mysql:mysql

如果是yum安裝默認(rèn)數(shù)據(jù)目錄在/var/lib/mysql,如果需要更改

[mysqld]

datadir=/mysql_data/

pid_file=/mysql_data/localhost.pid

socket=/mysql_data/mysql.sock

wsrep_data_home_dir=/mysql_data/

log-bin=mysql-bin

[client]

socket=/mysql_data/mysql.sock

6）備份工具mariabackup

Mariabackup

簡(jiǎn)介

Mariabackup是MariaDB提供的一個(gè)開(kāi)源工具，用于對(duì)InnoDB，Aria和MyISAM表進(jìn)行物理在線備份。這個(gè)工具是基于Percona的XtraBackup（版本2.3.8）的解決方案。

這里有一點(diǎn)需要注意，在MariaDB10.3.x及以上的版本用Percona XtraBackup工具會(huì)有問(wèn)題。原因可能是MariaDB10.3以上版本的redo日志格式和之前不同了。

Percona的官方文檔：https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

Mariabackup的官方文檔：https://mariadb.com/kb/en/library/mariabackup-overview/

安裝方法

yum install MariaDB-backup

備份與恢復(fù)

全備+恢復(fù)

1.全量備份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

2.準(zhǔn)備全備數(shù)據(jù)

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

3.還原數(shù)據(jù)(請(qǐng)確保數(shù)據(jù)目錄下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

4.修改屬組和屬主

chown -R mysql.mysql /var/lib/mysql

全備+增量+恢復(fù)

1.全量備份

mariabackup --backup --target-dir=/root/fullbackup --user=root --password=root

2.增量備份

mariabackup --backup --target-dir=/root/inc1 --incremental-basedir=/root/fullbackup/ --user=root --password=root

3.準(zhǔn)備全備數(shù)據(jù)

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root

4.增量和全備數(shù)據(jù)合并

mariabackup --prepare --target-dir=/root/fullbackup/ --user=root --password=root --incremental-dir=/root/inc1/ --apply-log-only

5.恢復(fù)數(shù)據(jù)(請(qǐng)確保數(shù)據(jù)目錄下是空的)

mariabackup --copy-back --target-dir=/root/fullbackup/ --user=root --password=root

6.修改屬組和屬主

chown -R mysql.mysql /var/lib/mysql

7）基于mysqldump工具進(jìn)行備份（邏輯備份工具）

備份testdb數(shù)據(jù)庫(kù)(-l代表備份單個(gè)數(shù)據(jù)庫(kù)時(shí)鎖定該庫(kù)的所有表；-x代表對(duì)所有表進(jìn)行備份時(shí)鎖定所有數(shù)據(jù)庫(kù)的所有表)

mysqldump -uroot -proot -l --databases testdb > testdb.sql

備份所有數(shù)據(jù)庫(kù)

mysqldump -uroot -proot --all-databases > all-databases.sql

備份testdb數(shù)據(jù)庫(kù)下的students表

mysqldump -uroot -proot testdb students > students.sql

還原數(shù)據(jù)

source testdb.sql

轉(zhuǎn)載于:https://www.cnblogs.com/Agnostida-Trilobita/p/11150718.html

總結(jié)

以上是生活随笔為你收集整理的mariadb数据库备份与恢复的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

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