数据库备份
通常数据库备份 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
建议在恢复备份时暂停 binlog ⽇志记录
//1.关闭binlog记录, 不然容易恢复增量失败
[root@sql mysql]# sed -i '23aSET sql_log_bin=0;' /backup/mysql/2023-05-2816-mysql
all.sql
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 |
+------+-------+
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/
# 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/ \
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
数据库差异备份与恢复
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;
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'
[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.
简单命令进⾏物理备份
[root@sql ~]#/etc/init.d/mysqld stop
[root@sql ~]# rm -rf /soft/mysql
[root@sql ~]# tar -xf /backup/2023-05-31-mysql-all.tar -C /soft/
[root@sql ~]# chown mysql.mysql -R /soft/mysql
[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
标签：--,备份,mysql,xtrabackup,sql,root,数据库 From： https://www.cnblogs.com/zgw03/p/18365498