mysql 数据备份的几种常用方法
2019-03-07 本文已影响25人
代码汇
数据备份是容灾的基础，是指为防止系统出现操作失误或系统故障导致数据丢失，而将全部或部分数据集合从应用主机的硬盘或阵列复制到其它的存储介质的过程。备份数据库非常重要，这样您就可以恢复数据，并在发生问题时重新启动并运行，例如系统崩溃，硬件故障或用户错误地删除数据。本文主要介绍mysqldump+binlog、lvm、abackup几种常用方法的使用。
原文地址：代码汇个人博客 http://www.codehui.net/info/67.html
为什么需要备份数据
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.
- 硬件故障
- 软件故障
- 自然灾害
- 黑客攻击
- 误操作 (占比最大)
所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
- 能够容忍丢失多少数据
- 恢复数据需要多长时间
- 需要恢复哪一些数据
数据的备份类型
数据的备份类型根据其自身的特性主要分为以下几组
- 完全备份
- 部分备份
完全备份指的是备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)
而部分备份又分为以下两种
- 增量备份
- 差异备份
增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦
差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单
代码汇
MySQL备份数据的方式
在MySQl中我们备份数据一般有几种方式
- 热备份
- 温备份
- 冷备份
热备份指的是当数据库进行备份时, 数据库的读写操作均不是受影响
温备份指的是当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作
冷备份指的是当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
- MyISAM
- 热备 ×
- 温备 √
- 冷备 √
- InnoDB
- 热备 √
- 温备 √
- 冷备 √
我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式
物理备份一般就是**通过tar,cp等命令直接打包复制数据库的数据文件**达到备份的效果
逻辑备份一般就是**通过特定工具从数据库中导出数据并另存备份**(逻辑备份会丢失数据精度)
- 物理备份
- 逻辑备份
备份需要考虑的问题
定制备份策略前, 我们还需要考虑一些问题
我们要备份什么?
一般情况下, 我们需要备份的数据分为以下几种
- 数据
- 二进制日志, InnoDB事务日志
- 代码(存储过程、存储函数、触发器、事件调度器)
- 服务器配置文件
备份方法
这里我们列举出常用的几种备份方法，详细可参见 官网数据备份介绍
mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份
xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供
设计合适的备份策略
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下ji种
- 直接cp,tar复制数据库文件
- mysqldump+复制BIN LOGS
- lvm2快照+复制BIN LOGS
- xtrabackup
- 以上的几种解决方案分别针对于不同的场景
- 如果数据量较小, 可以使用第一种方式, 直接复制数据库文件
- 如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
- 如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果
- 如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
实战测试
使用cp进行备份
cp备份这里不详讲，大概意思就是：
- 数据库所有表添加读锁
flush tables with read lock; - cp命令拷贝mysql源数据文件
- rm命令删除数据库所有文件
- cp命令再把拷贝的源文件拷贝回去
使用mysqldump+复制BINARY LOG备份
binlog二进制日志的详细使用请参见：mysql binlog介绍以及通过binlog实现数据恢复
mysqldump命令介绍
mysqldump是一个客户端的逻辑备份工具, 可以生成一个重现创建原始数据库和表的SQL语句, 可以支持所有的存储引擎, 对于InnoDB支持热备 官方文档介绍
#基本语法格式，详细参数太多，详细请查看官方文档介绍
shell> mysqldump [options] db_name [tbl_name ...] 恢复需要手动CRATE DATABASES
shell> mysqldump [options] --databases db_name ... 恢复不需要手动创建数据库
shell> mysqldump [options] --all-databases 恢复不需要手动创建数据库
其他选项:
-B：指定数据库
-F：刷新日志
-x：锁表
-E, --events: 备份事件调度器
-R, --routines: 备份存储过程和存储函数
--triggers: 备份表的触发器; --skip-triggers
--master-date[=value]
1: 记录为CHANGE MASTER TO 语句、语句不被注释
2: 记录为注释的CHANGE MASTER TO语句
基于二进制还原只能全库还原
--flush-logs: 日志滚动
锁定表完成后执行日志滚动
查看数据库的信息
# 查看当前的数据库，测试用的是codehui库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| codehui |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.03 sec)
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 查看codehui库中所有的表的记录数
mysql> select table_name,table_rows from tables where TABLE_SCHEMA = 'codehui' order by table_rows desc;
+------------+------------+
| table_name | table_rows |
+------------+------------+
| test | 5 |
| proxy | 2 |
| demo | 1 |
+------------+------------+
3 rows in set (0.00 sec)
使用mysqldump备份数据库
# 查看当前二进制文件的状态, 并记录下position的数字
root@ba586179fe4b:/# mysql -u root -p123456 -e 'show master status'
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 107 | | |
+------------------+----------+--------------+------------------+
# 备份数据库到/opt/mysql/backup.sql文件中
root@ba586179fe4b:/opt/mysql# mysqldump -uroot -p123456 -E --all-databases --lock-all-tables > /opt/mysql/backup.sql
# 查看数据表备份成功
root@ba586179fe4b:/opt/mysql# ls /opt/mysql
backup.sql
# 创建一个数据库，一会导入备份的数据
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
# 记下现在的position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 332 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
# 备份二进制文件
root@ba586179fe4b:/# cp /var/lib/mysql/mysql-bin.000005 /opt/mysql
# 停止mysql
# 删除所有的数据文件
root@ba586179fe4b:/# rm -rf /var/lib/mysql/*
# 启动mysql
# 查看数据库 没了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
# 暂时先将二进制日志关闭
mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.01 sec)
# 恢复数据，所需时间根据数据库时间大小而定
mysql> source /opt/mysql/backup.sql
# 开启二进制日志
mysql> set sql_log_bin=ON;
# 查看数据库其他恢复了 备份之后创建的test需要binlog日志进行恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| codehui |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
# 通过mysqlbinlog查看mysql-bin.000005日志中的记录，可以看到创建test表的sql日志
root@ba586179fe4b:/# mysqlbinlog /opt/mysql/mysql-bin.000005
===========================================
# at 249
#190222 2:37:17 server id 1 end_log_pos 332 Query thread_id=20 exec_time=0 error_code=0
SET TIMESTAMP=1550803037/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
create database test
/*!*/;
===========================================
# 恢复mysql-bin.000005日志中的数据
root@ba586179fe4b:/# mysqlbinlog /opt/mysql/mysql-bin.000005 > /opt/mysql/000005.sql
root@ba586179fe4b:/# mysql -uroot -p123456 -v < /opt/mysql/000005.sql
# 查看数据库，test表回来了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| codehui |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
使用lvm2快照备份数据
后面继续更新，敬请期待
使用Xtrabackup备份
后面继续更新，敬请期待
