mysql的备份与还原
2018-04-18 本文已影响20人
光小月
网上有很多备份与还原的文章，这里我只是针对自己实战过的做一下记录。
备份的目的这里不说了，就说一下备份的类型与实践。
1、数据备份的类型
1.1 根据是否需要数据库离线
- 冷备（cold backup）：需要关mysql服务，读写请求均不允许状态 下进行；
- 温备（warm backup）-快照备份： 服务在线，但仅支持读请求，不允许写请求；LVM
- 热备（hot backup）：备份的同时，业务不受影响。（mysqldump、xtrabackup）
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
| -- | 热备 | 温备 | 冷备 |
|---|---|---|---|
| MyISAM | × | √ | √ |
| InnoDB | √ | √ | √ |
1.2 根据要备份的数据集合的范围
- 完全备份：full backup，备份全部字符集。
- 增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，
要借助完全备份，备份的频率取决于数据的更新频率。
- 差异备份：differential backup 上次完全备份以来改变了的数据。
建议的恢复策略:
完全+增量+二进制日志
完全+差异+二进制日志
image.png
2、备份需要考虑的问题
一般情况下, 我们需要备份的数据分为以下几种
• 数据
• 二进制日志, InnoDB事务日志
• 代码(存储过程、存储函数、触发器、事件调度器)
• 服务器配置文件
3、 备份工具
1. 命令行 into outfile与load data infile *.txt into table tableName
2. mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于存储引擎支持热备
3. cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
4. lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份
5. mysqlhotcopy: 名不副实的的一个工具, 几乎冷备, 仅支持存储引擎
6. xtrabackup: 一款非常强大的热备工具, 支持完全备份、增量备份, 由percona提供
4、 导入测试用的库:
环境： mysql version : mysql5.7，
操作步骤：主要看下载的readme文件，里面有详细的步骤
下载地址： https://launchpad.net/test-db/employees-db-1/1.0.6
下载文件： employees_db-full-1.0.6.tar.bz2
1. 导入命令：
Mysql -u root -p < E:\\employees_db\\employees.sql
******************************
可能出现问题
问题1：ERROR 1193 (HY000) at line 44: Unknown system variable 'storage_engine'
解决方法： http://blog.chinaunix.net/uid-25847668-id-5752234.html
问题2： ERROR at line 108: Failed to open file 'load_departments.dump', error: 2
解决： 找到employees.sql，找到字符load_departments.dump, 加入绝对路径。
验证， mysql -u root -p -t < E:\\employees_db\\test_employees_md5.sql
5、备份实例
5.1 使用命令行：
导出数据：select * from tableName into outfile '' ;
导入数据： load data infile '**' into table ;
1. SELECT * INTO OUTFILE '/tmp/fi.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test.fi;
总结：
FIELDS TERMINATED BY ',' 字段间分割符
OPTIONALLY ENCLOSED BY '"' 将字段包围 对数值型无效
LINES TERMINATED BY '\n' 换行符
2. use test2;
load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\20170418-back.txt'
into table test2.employ fields TERMINATED by ','
OPTIONALLY ENCLOSED by '"'
LINES TERMINATED by '\n';
注意
问题1. MYSQL导出数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this
解决方法： 使用show variables like '%secure%'; 查看导出文件secure_file_priv的地址，这是mysql权限设置的地址， 该地址的权限范围是可读的。
修改mysql的安装文件my.ini
添加一行， secure_file_priv=****
然后重启服务,才可以导出。
参考地址： https://blog.csdn.net/man_to_home/article/details/54947518
问题2. 导出的文件, 是一个文本文件，一些排列整齐的数据，并没有什么表接口，表索引等信息。
5.2 利用mysqldump工具对数据进行备份和还原
逻辑备份，热备
单线程，适合数据量小的库
全库
1)mysqldump -uroot -p123456 --socket=XXX --all-databases > XXX.sql
指定库
2)mysqldump -uroot -p123456 --socket=XXX --databases db2 > XXX.sql
指定表
3)mysqldump -uroot -p123456 --socket=XXX db2 t1 > XXX.sql
恢复
4)create database db3;
source XXX.sql;
使用事务备份全库
5)mysqldump --single-transaction -uroot -p123456 --all-databases > XXX.sql
远程备份
6)mysqldump -utest -ptest -hXXX.com -P3306 --all-databases > XXX.sql
备份.sql,和.csv格式；
7)mysqldump --single-transaction -uroot -p123456 db1 --databases db1 > XXX.csv
指定分隔符为","默认分隔符为制表符
8)mysqldump -uroot -p000000 --single-transaction --fields-terminated-by=, shuxue --databases db1 > XXX.csv
5.4 使用LVM备份
参考：https://blog.csdn.net/only_jing1314/article/details/51064989
5.3 使用xtrabackup工具进行备份
参考： https://www.cnblogs.com/waynechou/p/xtrabackup_backup.html
参考：
欢迎关注，以后会不定时更新！
