Mysql的备份

发布于 2023-05-11  181 次阅读


目标:什么是数据库备份,怎么去备份

什么是MySQL备份

备份数据库时的状态

  1. Hot Backup(热备份):正常运行中直接备份
  2. Cold Backup(冷备份):数据库完全停止后再进行备份
  3. Warm Backup(温备份):介于热备和冷备之间, 数据库只能读

备份文件的格式

  1. 逻辑备份:输出文本或SQL语句 (导出成人可读的逻辑性的文本)
  2. 物理备份(裸文件):备份数据库底层文件

备份的内容

  1. 完全备份:备份完整数据
  2. 增量备份:备份数据的差异
  3. 日志备份:备份Binlog(严格讲也属于增量备份的一种,增量备份一般指逻辑的备份。)

备份工具

  • mysqldump:备份的是逻辑备份、热备份、全量备份
  • xtrabackup:物理备份、热备份、全量备份 + 增量备份

备份实践

OUTFILE的使用方式

OUTFILE是Mysql的原生SQL指令,它是最原始的逻辑备份方式。

它的备份的功能和效果完全取决于如何写SQL语句

1.导出路径

首先查出Mysql导出路径


show variables like '%secure%';

2.修改导出路径

如果secure_file_priv为Null,则需要修改my.cnf


[mysqld]
secure_file_priv=/home

3.使用INTO导出

注意事项:

  • 在INNODB事务下面,可以做到一致性视图
  • 修改分隔符:fields terminated by
  • 修改换行符:lines terminated by

-- 直接导出
select * into outfile '/home/t3-out' from t3;

-- 修改分隔符,用逗号分隔
select * into outfile '/home/t3-out' fields terminated by ',' from t3;

-- 修改换行符,用分号
select * into outfile '/home/t3-out' fields terminated by ',' lines terminated by ';' from t3;

4. OUTFILE的缺陷

  • 输出的文本比较简单
  • 很难进行还原,现在往往用来导出简单的数据

mysqldump的使用方式

1.全量备份

全量备份


mysqldump -uroot -p123456 --databases sakila --single-transaction > test.sql

还原


source test.sql

2.全量备份+Binlog增量备份

增量备份思路

  • Binlog忠实的记录了Mysql的数据变化
  • mysqldump全量备份之后,可用Binlog作为增量
  • mysqldump全量备份后,切换新的Binlog文件
  • 从零还原时,采用全量备份还原+Binlog还原

全量备份+增量备份的准备工作

  • --flush-logs: 备份后切换Binlog文件
  • --master-data=2:记录切换后的Binlog文件名,并且注释方式写入到test.sql文件里

mysqldump -uroot -p123456 --databases sakila --single-transaction --flush-logs --master-data=2 > test.sql

Binlog增量备份

  • 需要增量备份时,使用mysqladmin切换Binlog文件
  • 将所有新增的Binlog文件备份

mysqladmin -uroot -p123456 flush-logs

还原


# 如果全量恢复
source test.sql

# 然后将增量的Binlog还原到数据库
mysqlbinlog 「文件名」| mysql -uroot -p123456

3.mysqldump注意事项

  • --single-tranction:在RR(可重复读)级别下进行(INNODB)
  • --lock-all-tables:使用FTWRL锁所有表(MyISAM)
  • --lock-tables:使用READ LOCAL锁当前库的表(MyISAM)
  • --all-databases:备份所有的库

4.mysqldump缺点

  • 导出逻辑数据,备份慢,会影响性能
  • 还原需要执行SQL,解析速度也比较慢

5.mysqldump总结

  1. mysqldump使用简单,可以热备份
  2. SQL文件可以直接执行、占用空间少、可阅读性高
  3. 备份、还原的性能不如物理备份

XtraBackup备份

物理备份的优点

  • 直接备份INNODB底层文件
  • 导出不需要转换,速度快
  • 工作时对数据库的压力比较小
  • 更容易实现增量备份

XtraBackup:下载地址

mysql8.0选择XtraBackup8.0、mysql5.2~5.6选择XtraBackup2.4版本


间歇性凌云壮志,持续性混吃等死