linux学习之Mysql数据备份与恢复

本文详细介绍了Linux中MySQL的数据备份方法,包括物理备份(如冷备、mysqlump、mysql)和逻辑备份,以及不同的备份策略:完全备份、增量备份和差异备份。重点讨论了binlog日志在数据恢复和主从同步中的作用,同时提到了物理备份的缺点,如跨平台性差、备份时间长等。此外,文章还提到了Percona的XtraBackup工具作为在线热备份解决方案,能够避免锁定表。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

数据备份方式
1、物理备份
–冷备:cp、tar、
2、逻辑备份
–mysqlump
–mysql

数据备份策略
1、完全备份
–备份所有数据
2、增量备份
–备份上次备份以后所产生的新数据
3、差异备份
–在完全备份后,只备份所有新产生的数据

物理备份和恢复
备份:

cp -rp /var/lib/mysql/数据库  备份目录
tar -zcvf xxx.tar.gz /var/lib/mysql/数据库/*

恢复:

cp -rp 备份目录/文件名 /var/lib/mysql/
tar -zxvf xxx.tar.gz -C /var/lib/mysql/数据库名/
逻辑备份和恢复

备份:

mysqldump -uroot -p 库名  > /路径/xxx.sql

恢复

mysql -uroot -p 库名 < 路径/xxx.sql
库名表示方式:

–all-databases 或 -A 指所有库

mysqldump  -uroot -p --all-databases >  alldb.sql

数据库名指单个库

mysqldump -uroot -p userdb > userdb.sql

数据库名 表名 指某库某表

mysqldump -uroot -p 库名 表名

-B 数据库1 数据库2 指多个库

mysqldump -uroot -p -B 数据库1 数据库2 > double.sql
binlog日志

binlog日志概述:
binlog日志是二进制日志,记录所有更改数据的操作
【配置】
log_bin[=dir/name]
server_id=数字
max_binlog_size=数字m

用处:记录查询之外的所有SQL命令,可用于数据恢复,是配置主从同步的必要条件

启用binlog日志

vim /etc/my.cnf
[mysqld]
log_bin     //启用binlog日志
server_id=100   //指定id值

systemctl restart mysqld  //重启服务

binlog相关文件:
主机名-bin.index 记录已有日志文件名
主机名-bin.000001 第一个二进制日志
主机名-bin.000002 第二个二进制日志

手动生成新的日志文件:
1、重启mysql
2、在mysql中使用命令flush logs;
3、使用mysqldump --flush-logs命令
4、生成新日志文件mysql -uroot -p密码 -e ‘flush logs’

清理binlog日志
删除早于制定版本的日志文件

mysql> puurge master logs to 'binlog文件名';
mtsql>reset master;

binlog日志的三种记录格式:
1、statement:每一条修改数据的sql命令都会记录在binlog日志中
2、row:不记录sql语句上下文相关信息,仅保存哪条记录被修改
3、mixed:是以上两种格式的混合使用

查看目前日志记录格式:

mysql>show variables like "binlog_format";

修改日志记录格式

vim /etc/my.cnf
[mysql]
binlog_format=mixed

systemctl restart mysqld

分析binlog日志
使用mysqlbinlog工具
格式:mysqlbinlog [选项] binlog日志文件名
常用选项

--start-datetime="yyyy-mm-dd hh:mm:ss"
--stop-datetime="yyyy-mm-dd hh:mm:ss"
--start-position=数字
--stop-position=数字

使用binlog恢复数据
使用mysqlbinlog提取历史SQL操作,在管道给mysql执行命令
(相当于把执行过的命令再执行,达到恢复数据的效果)

cd /var/lib/mysql
mysqlbinlog mysql-bin.000001 | mysql -uroot -p密码

mysql备份工具特点
物理备份缺点:
跨平台性差,备份时间长、冗余备份、浪费存储空间
mysqldump备份缺点:
效率过低,备份还原速度慢、备份过程会导致数据插入更新被挂起

XtraBackup工具:
在线热备份工具,备份过程中不锁表。由Percona提供
包含两个组件:
xtrabackup:C程序,支持InnoDb/XtraDB
innobackupex:以Perl脚本封装xtrabackup,还支持MyISAM

安装percona
1、下载安装rpm包
percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

yum -y install perl-Digest-MD5.x86_64 rsync perl-DBD-MySQL   #依赖包
rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm   
rpm -ivh percona-xtrabackup-24-2.4.7-1.el7.x86_64.rpm

/usr/bin/innobackupex :备份innodb、xtrdb、myisam引擎的表
/usr/bin/xtrabackup :备份innodb、xtrdb引擎的表

innobackupex基本选项

常用选项含义
–host主机名
–user用户名
–password密码
–databases数据库名
–no-timestamp不用日期命名备份文件存储的子目录名
–redo-only日志合并
–apply-log准备还原
–copy-back恢复数据
–incremental目录名增量备份
–incremental-basedir=目录名增量备份时,指定上一次备份数据存储的目录名
–export导出表信息
import导入表空间

ps:
–databases=“库名” #单个库
–databases=“库1 库2” #多个库
–databases=“库.表” #单个表

innobk应用案例

完全备份与恢复

1、完全备份

innobackupex --user root --password 密码   /backup  --no-timestamp   #完全备份

2、恢复数据

innobackupex --user root -password 密码 --apply-log /back  #准备恢复数据
rm -rf /var/lib/mysql/*   #恢复时要求库目录为空
innobackupex --user root --password 密码 --copy-back /backup #恢复数据

systemctl restart mysqld #重启服务,完成恢复
增量备份与恢复

在增量备份前,必须有一次备份,通常是完全备份
增量备份:

innobackupex --user root --password 密码 --databases =“库名列表” /fullbak  --no-timestamp  #完全备份

innobackupex --user root --password 密码 --databases="库名列表" --incremental /new1 --incremental-basedir="/fullbak" --no-timestamp   #第一次增量备份

innobackupex --user root --password 密码 --databases=“库名列表” --incremental /new2 --incremental-basedir="/new1" --no-timestamp #第二次增量

恢复:(数据丢失后)

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullbak  #完全恢复

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullback --incremental-dir="/new1"  #第一次增量恢复

innobackupex --user root --password 密码 --databases="库名列表" --apply-log --redo-only /fullbak --incremental-dir="/new2"  #第二次增量恢复

innobackupex --user root --password 密码 --databases="库名列表" --copy-back /fullbak  #拷贝文件

chown -R mysql:mysql /var/lib/mysql/    #数据库目录权限
重启服务

在完全备份中恢复单个表

innobackupex --user root --password 密码 --databases="test" /allbak --no-timestramp   #备份test库
mysql> drop table test.a;   #删掉test库中的a表
innobackupex --user root --password 密码 --databases="test" --apply-log --export /allbak  #导出表信息
mysql>create table test.a(id int……);    #创建表
mysql>alter table test.a discard tablespace;  #删除表空间

恢复单个表

mysql>system cp /allbak/test/a.{ibd,cfg,exp} /var/lib/mysql/test  #拷贝表信息文件
mysql>system chown mysql:mysql /var/lib/mysql/bbsdb/a.*  #修改所有者
mysql> alter table test.a import tablespace; #导入表空间
恢复完成
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值