详解mysql之数据备份与恢复

迷茫

发布时间：2017-03-26 13:31:42

1313人浏览过

来源于php中文网

原创

该文使用mysql5.5 centos6.5 64位（本人使用rpm安装mysql，数据库的安装目录默认）

一、数据备份注意事项

读锁问题：数据库（或者某个表）一旦进行读锁操作则影响数据库的写操作所以此时不能对数据库进行写操作。之所以在数据备份的时候进行读锁操作是为了保证备份数据的完整性一致性，在数据备份完成后会自动进行解锁。

更新日志问题：每次备份数据的时候新生成一个日志文件，这样数据库在备份一段时间后突然崩溃可以通过该bin-log日志还原该时间段的数据。数据恢复的步骤:恢复备份的数据+还原最新bin-log日志中的数据

二、使用mysqldump进行书籍备份

mysqldump：命令将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。

mysqldump命令的工作原理：它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换成一条INSERT语句。然后通过这些语句，就能够创建表并插入数据。

将test数据库备份到/tmp/mysql_back/目录下

 [root@localhost tmp]# mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

参数解读：

1、mysqldump mysql数据备份的命令

2、-uroot -p111111 用户名密码

3、test：要备份的数据库名

4、-l：进行读锁控制

5、-F:生成新日志文件

执行该语句后会发现在 /var/lib/mysql 目录下会生成一个新的bin-log日志 mysql-bin.000002

6、/tmp/mysql_back/test.sql ：生成备份文件的位置及名称

三、数据恢复

1、备份数据的恢复：

kgogoprime

KGOGOMall 是一套采用 Php + MySql 开发的基于 WEB 应用的 B/S 架构的B2C网上商店系统。具有完善的商品管理、订单管理、销售统计、新闻管理、结算系统、税率系统、模板系统、搜索引擎优化，数据备份恢复，会员积分折扣功能，不同的会员有不同的折扣，支持多语言，模板和代码分离等，轻松创建属于自己的个性化用户界面。主要面向企业和大中型网商提供最佳保障，最大化满足客户目前及今后的独立

下载

　[root@localhost tmp]# mysql -uroot -p111111 test -v -f　　-v：查看导入的详细信息
　　-f：导入过程中遇到错误时可以skip过，继续执行下面的语句。
2、bin-log日志中数据的还原：
　　该日志在/var/lib/mysql下mysql-bin.000002日志中所有的记录
　　[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 |mysql -uroot -p111111 test;
3、还原bin-log日志中指定位置的数据
通过 position位置还原数据
BEGIN
/*!*/;
# at 175
#170206 22:55:48 server id 1  end_log_pos 263     Query    thread_id=17    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1486392948/*!*/;
insert into t1 values(10)
/*!*/;
# at 263
#170206 22:55:48 server id 1  end_log_pos 290     Xid = 178
COMMIT/*!*/;
# at 290
#170206 22:55:54 server id 1  end_log_pos 358     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
BEGIN
/*!*/;
# at 358
#170206 22:55:54 server id 1  end_log_pos 446     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
insert into t1 values(12)
/*!*/;
# at 446
#170206 22:55:54 server id 1  end_log_pos 473     Xid = 179
COMMIT/*!*/;
# at 473
#170206 22:56:42 server id 1  end_log_pos 547     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486393002/*!*/;
truncate t1
还原该bin-log日志中position 位置在 172，473 中的数据(end_log_pos)
[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 --start-position="175" --stop-position="473"|mysql -uroot -p111111 test;
4、还原bin-log日志中指定时间段的数据
格式：将3中即可
--start-position="175"  替换为 --start-date="2016-12-30 21:30:34"
--stop-position="473" 替换为 --stop-date="2016-12-30 23:30:34"