資料庫備份

hhhaaa12138發表於2024-08-18
資料庫

資料庫備份

通常資料庫備份 database 資料⽂件 binlog ⽇志⽂件 my.cnf 配置⽂件,所有的資料庫的備份資料都應放在⾮資料

庫本地, ⽽且建議備份多份。

僅備份是沒有任何意義, 需要在測試環境中做⽇常恢復演練, 測試備份的可⽤性, 恢復較⽐備份更加的重要。

備份: 能夠有效防⽌裝置故障以及⼈為誤操作帶來的資料丟失, 例如:將資料⽂件儲存在遠端。

冗餘: 資料有多分冗餘, 但不等於備份, 只能防⽌機械故障丟失的資料, 例如: 主備模式、資料庫叢集。

資料庫備份必須考慮因素

1.資料的⼀致性

2.服務的可⽤性

資料庫備份⽅式

1.邏輯備份: 備份 DDL DML DCL 語句, 適⽤於中⼩型資料庫, 效率相對低下。 mysqldump、mydumper

2.物理備份: 直接複製資料庫⽂件, 適⽤於⼤型資料庫環境, 效率相對較⾼。 xtrabackup、inbackup、cp、

tar、lvm snapshot

在選擇備份⽅式時，需要考慮資料庫的規模、備份和恢復的效率、備份資料的⼀致性等因素。同時，為了保證備份

資料的安全性，備份資料應該儲存在⾮本地的地⽅，並且建議備份多份。

資料庫備份模式

完全備份

增量備份

差異備份

冷備份與熱備份

冷備份

關閉資料庫服務，完整拷⻉資料⽂件

熱備份

在不影響資料庫讀寫服務的情況下備份資料庫

備份週期

考慮因素：

資料庫⼤⼩(決定備份時間)

恢復速度要求(快速or慢速)

備份⽅式(全量or增量)

企業備份機制

如果你的資料庫⼩於20G 每天公司0點進⾏全量備份 邏輯備份 物理備份可以的

如果資料庫⼤於100G，周⼀晚上0點進⾏物理全備份，周⼆ 增量備份,週三 以此類推，

邏輯備份與恢復

資料庫完整恢復流程

1.停⽌資料庫

2.刪除破損資料庫

3.重新初始化資料庫

4.重置密碼,否則⽆法恢復

5.恢復資料[新密碼]

6.重新整理授權[備份時密碼

mysqldump -uroot -p'zgw123' -h 192.168.107.133 \

--all-databases \

c



--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

image-20240817184713348

建議在恢復備份時暫停 binlog ⽇志記錄

//1.關閉binlog記錄, 不然容易恢復增量失敗

[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql

all.sql

//2.在mysql控制檯臨時修改不記錄binlog⽇志

mysql> set sql_log_bin=0;

mysql> source /backup/mysql/2023-05-2816-mysql-all.sql

//3. binlog開啟

mysql> set sql_log_bin=1;

1.2****資料庫增量備份與恢復

實戰案例****2: 資料庫完整備份+資料庫增量備份

//備份之前

mysql> set sql_log_bin=0;

mysql> drop database wing;

mysql> set sql_log_bin=1;

mysql> create database wing;

mysql> create table wing.t1 (id int, name varchar(20));

mysql> insert into wing.t1 values (1,"wing1");

mysql> insert into wing.t1 values (2,"wing2");

mysql> select * from wing.t1;

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

+------+------+

2 rows in set (0.00 sec)

//基於當前狀態備份

[root@sql ~]# mysqldump -uroot -p'Wing@123' \

--all-databases \

--single-transaction \

--master-data=1 \

--flush-logs > /backup/mysql/`date +%F%H`-mysql-all.sql

//模擬資料插⼊操作

mysql> insert into wing.t1 values (3,"wing3");

mysql> insert into wing.t1 values (5,"tt");

mysql> select * from wing.t1;

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

| 3 | wing3 |

| 5 | tt |

+------+------+

4 rows in set (0.00 sec)

//模擬資料庫故障

[root@sql ~]# /etc/init.d/mysqld stop

[root@sql ~]# rm -rf /sort/mysql

恢復全備資料

//關閉binlog記錄, 不然容易恢復增量失敗

[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2716-mysql

all.sql

[root@sql ~]# mysql -uroot -p'Wing@123' < /backup/mysql/2023-05-2716-mysql-all.sql

[root@sql mysql]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"

+------+------+

| id | name |

+------+------+

| 1 | wing1 |

| 2 | wing2 |

+------+------+

恢復增量資料

//檢視備份後binlog起始位置點

[root@sql ~]# sed -n '22p' /backup/mysql/2023-05-2816-mysql-all.sql

CHANGE MASTER TO MASTER_LOG_FILE='sql_106_70.000014', MASTER_LOG_POS=154;

//查詢結束位置點, 進⾏恢復

[root@sql bin]# mysqlbinlog --start-position=154 sql_106_70.000014|mysql -uroot -

p'Wing@123'

//驗證增量恢復資料

[root@sql bin]# mysql -uroot -p'Wing@123' -e "select * from wing.t1;"

+------+-------+

| id | name |

+------+-------+

| 1 | wing1 |

| 2 | wing2 |

| 3 | wing3 |

| 5 | tt |

+------+-------+

image-20240817201717153

image-20240817201728619

2.MySQL****物理備份與恢復

2.1 xtrabackup 物理備份

特點：

開源，線上備份InnoDB表

⽀持限速備份，避免對業務造成影響

⽀持流備

⽀持增量備份

⽀持備份⽂件壓縮與加密

⽀持並⾏備份與恢復，速度快

基於InnoDB的crash-recovery功能

備份期間允許⽤戶讀寫，寫請求產⽣redo⽇志

從磁碟上拷⻉資料⽂件

從InnoDB redo log file實時拷⻉⾛備份期間產⽣的所有redo⽇志

恢復的時候 資料⽂件 + redo⽇志 = ⼀致性資料

innobackupex --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root --password=zgw /xtrabackup/

image-20240817222039883

# innobackupex --username=root --password=zgw123 -S /tmp/mysql.sock /xtrabackup/

*/5 * * * * /usr/sbin/ntpdate -u ntp1.aliyun.com

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock \--incremental /xtrabackup/ \

image-20240817224526967

innobackupex --username=root --password=zgw123 -S /tmp/mysql.sock /xtrabackup/

innobackupex --apply-log --redo-only /xtrabackup/2024-08-18_08-20-55 --incremental-dir=/xtrabackup/2023-05-29_00-25-52

image-20240818091709778

資料庫差異備份與恢復

3.差異備份恢復流程

//1.停⽌資料庫

[root@sql ~]# /etc/init.d/mysqld stop

//2.清理環境

[root@sql ~]# rm -rf /soft/mysql/data/*

//3.重演回滾, 回滾全備

[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/

//4.重演回滾, 將差異備份應⽤⾄完整備份上

[root@sql ~]# innobackupex --apply-log --redo-only /xtrabackup/2023-05-28_16-52-14/ \

--incremental-dir=/xtrabackup/2023-05-31_00-00-30/

//5.應⽤資料

[root@sql ~]# innobackupex --copy-back /xtrabackup/2023-05-28_16-52-14/

//6.修改許可權

[root@sql ~]# chown -R mysql.mysql /soft/mysql/

//7.啟動資料庫

[root@sql ~]# /etc/init.d/mysqld start

1.完整備份

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock/xtrabackup/

[root@zgw 2024-08-18_10-25-07]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 23607770
last_lsn = 23607779
compact = 0
recover_binlog_info = 0
flushed_lsn = 23607779
[root@zgw 2024-08-18_10-25-07]# backup_type = full-backuped

innobackupex --user=root --password='zgw123' -S /tmp/mysql.sock --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2024-08-18_10-25-07

2、差異備份:周⼀ -> 週三



//修改時間為周⼀, 新增資料
[root@sql ~]# date -s '2023-05-29'
mysql> insert into wingdb.t2 values(1);
//差異備份(以完整備份為準2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password='Wing@123' -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
//修改時間為周⼆, 新增資料
[root@sql ~]# date -s '2023-05-30'
mysql> insert into wingdb.t2 values(2);
//差異備份(以完整備份為準2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/
//修改時間為週三, 新增資料
[root@sql ~]# date -s '2023-05-31'
mysql> insert into wingdb.t2 values(3);
//差異備份, (以完整備份為準2023-05-28_16-52-1)
[root@sql ~]# innobackupex --user=root --password=Wing@123 -S /tmp/mysql.sock \
--incremental /xtrabackup/ \
--incremental-basedir=/xtrabackup/2023-05-28_16-52-14/

4.模擬備份後⼀次錯誤操作

mysql> create database WingSred;

mysql> drop database WingSred;

image-20240818104343831

6.binlog恢復刪除掉的WingSred;庫


[root@sql ~]# cat /xtrabackup/2023-05-31_00-00-25/xtrabackup_binlog_info
sql_106_70.000018 1676
//匯出對應的sql, 找到drop語句位置
[root@sql ~]# mysqlbinlog --start-position=1676 \
--base64-output="decode-rows" \
-v sql_106_70.000019 sql_106_70.000018 >db.sql
//針對起始點與結束點位置恢復
[root@sql bin]# mysqlbinlog --start-position=1676 \
--stop-position=1847 sql_106_70.000018 |mysql -uroot -p'Wing@123'

image-20240818110117152


[root@zgw bin]# mysqlbinlog --start-position=1676 --base64-output=DECODE-ROWS /log/mysql/bin/sql_107_133.000003 > db.sql
[root@zgw bin]# grep 'DROP' db.sql
[root@zgw bin]# vim db.sql
[root@zgw bin]# mysqlbinlog --start-position=1676 \

> --stop-position=1847 sql_107_133.000003  |mysql -uroot -p'zgw123'
> mysql: [Warning] Using a password on the command line interface can be insecure.

image-20240818110254633

簡單命令進⾏物理備份

//1.停⽌資料庫

[root@sql ~]#/etc/init.d/mysqld stop

//2.清理環境

[root@sql ~]# rm -rf /soft/mysql

//3.導⼊備份資料

[root@sql ~]# tar -xf /backup/2023-05-31-mysql-all.tar -C /soft/

//4.許可權

[root@sql ~]# chown mysql.mysql -R /soft/mysql

//4.啟動資料庫

[root@sql ~]#/etc/init.d/mysqld start

//5.binlog 恢復

⽣產備份思路與實戰

邏輯備份

保證⼀致性，服務可⽤性

適合少量的資料1G-10G

只能全備->指定某⼀個資料庫備份

效率不是很⾼, 將所有的資料轉成sql語句

ddl dml dcl

物理備份xtrabackup

保證⼀致性，服務可⽤性

適合⼤量的資料20G 100G 500G

⽀持增量、差異、全備

備份怎麼做

物理備份 周天全備 周⼀到週六增量

/xtrabackup/1⽉/1周/xxx

/xtrabackup/1⽉/2周/xx

/xtrabackup/1⽉/3周

/xtrabackup/1⽉/4周

/xtrabackup/2⽉/1周

注意：

binlog⽇志⾮常的重要

備份時，不要記錄binlog⽇志

備份不單單只是資料⽂件，還有binlog、my.cnf

相關文章