MySQL数据库之备份总结-职坐标

MySQL数据库之备份总结

小职 2018-06-28 来源：阅读 1284 评论 0

摘要：本文主要向大家介绍了MySQL数据库的备份总结，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

本文主要向大家介绍了MySQL数据库的备份总结，通过具体的内容向大家展现，希望对大家学习MySQL数据库有所帮助。

一个企业的正常运行，数据的完整性是最关键的；所以我们需要在工作中要很熟练的掌握数据的备份与恢复方法；下面是对Mysql数据库备份的三种方法总结，希望对大家会有所帮助

备份开始前的工作环境准备：

1、创建用于保存二进制日志文件的目录

1. # mkdir /mybinlog

2. # chown mysql.mysql /mybinlog

2、修改配置文件

1. # vim /etc/my.cnf

2. log-bin=/mybinlog/mysql-bin 二进制日志目录及文件前缀

3. innodb_file_per_table = 1 启用innoDB表

4. datadir = /mydata/data 指定数据库的目录

3、为备份数据库创建存放点

1. # mkdir /mybackup

2. # chown -R mysql.mysql /mybackup

4、启动mysql服务器

1. # service mysqld start

5、插入需要备份的数据库

1. # mysql < jiaowu.sql

一、使用mysqldump工具完成完全备份+增量备份基于mysqldump通常就是完整备份+二进制日志来进行恢复数据

1.1、mysqldump用来温备份，首先需要为所有库加读锁,并且滚动一下二进制日志，记录当前二进制文件位置

1. # mysqldump --all-databases --lock-all-tables --routines --triggers --master-data=2

2. --flush-logs > /mybackup/alldatabase.sql

3. 解释各个选项的意义：

4. --all-databases 备份所有数据库

5. --lock-all-tables 为所有表加锁

6. --routines 存储过程与存储函数

7. --triggers 触发器

8. --master-data=2 以change master to的方式记录位置，但默认为被注释

9. --flush-logs 执行日志滚动

1.2、备份二进制日志

1. # cp /mybinlog/mysql-bin.000001 /mybackup/alldatabase.000001

1.3、模拟数据库意外损坏，利用完全备份实现数据库的恢复

1. # rm -rf /mydata/data/*

2. # rm -rf /mybinlog/*

1.4、初始化mysql并启动服务器

1. # cd /usr/local/mysql/

2. # ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data

1.5、删除二进制日志，启动服务

1. # rm -rf /mybinlog/*

2. # netstat -tnlp 查看启动的mysql进程号

3. tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 2523/mysql

4. # kill 2523 （注意，如果在备份之前mysql服务是开启的，重启是不成功的，需要把这个进程先kill掉）

5. # service mysqld restart

1.6、恢复到备份状态,导入备份的数据库文件：

1. # mysql < /mybackup/alldatabase.sql

2、模拟往students表中添加数据，添加完成后不小心将表删除了，我们要恢复到删除之前的状态，并且新加的数据还要存在

2.1、往students表中添加数据

1. mysql> use jiaowu;

2. mysql> insert into students (Name,Age,Gender) values ('hadoop',22,'M');

2.2、模拟一下，不小心将表删除了

1. mysql> drop tables students;

2.3、查看一下二进制日志文件的位置

1. mysql> show master status;

2. +------------------+----------+--------------+------------------+

4. +------------------+----------+--------------+------------------+

5. | mysql-bin.000002 | 520351 | | |

6. +------------------+----------+--------------+------------------+

2.4、先恢复完整数据（恢复过程不要记录在日志中）

1. mysql> set global sql_log_bin=0;

2. # mysql < /mybackup/alldatabase.sql

2.5、查看删除表时的记录位置

1. # mysqlbinlog /mybinlog/mysql-bin.000002 内容如下：

2. # at 520084

3. #130501 16:44:08 server id 1 end_log_pos 520212 Query thread_id=2 exec_time=0 error_code=0

4. use jiaowu/*!*/;

5. SET TIMESTAMP=1367397848/*!*/;

6. insert into students (Name,Age,Gender) values ('hadoop',22,'M')

7. /*!*/;

8. # at 520212

9. #130501 16:44:08 server id 1 end_log_pos 520239 Xid = 308

10. COMMIT/*!*/;

11. # at 520239 删除命令在这个时刻开始执行的

12. #130501 16:45:37 server id 1 end_log_pos 520351 Query thread_id=2 exec_time=0 error_code=0

13. SET TIMESTAMP=1367397937/*!*/;

14. DROP TABLE `students` /* generated by server */

15. /*!*/;

16. DELIMITER ;

17. # End of log file

2.5、将二进制文件中完整备份到删除表之前的记录导出

1. # mysqlbinlog --stop-position=520239 /mybinlog/mysql-bin.000002 > /root/change.sql

2. 解释：

3. --start-position 指定从哪开始导出二进制日志

4. --stop-position 指定到哪结束

5. --start-datetime 从哪个时间开始

6. --stop-datetime 到哪个时间结束

2.6、将改变的数据库日志导入到mysql库中

1. # mysql < /root/change.sql

2.7、见证奇迹的时刻数据库恢复成功，并且插入的数据也还原回来了

1. mysql> select Name,Age,Gender from students where Name='hadoop';

2. +--------+------+--------+

3. | Name | Age | Gender |

4. +--------+------+--------+

5. | hadoop | 22 | M |

6. +--------+------+--------+

二、select命令也能完成逻辑备份比mysqldump更节约空间，速度更快，但比mysqldump用起来要麻烦，并且备份出来的数据都是纯文本信息，没有额外的开销空间，适合备份某张表模拟备份一张表

1、备份出来，保存在某个目录下，但需要注意的是，这个目录下的文件的具有权限，当登录到mysql时需要具有执行的权限

1. mysql> select * into outfile '/tmp/tutor.txt' from tutors;

2. 保存为.txt格式：因为它是纯文本格式的，保存的是表格式的信息，如下所示：

3. # cat /tmp/tutor.txt

4. 1 HongQigong M 93

5. 2 HuangYaoshi M 63

6. 3 Miejueshitai F 72

7. 4 OuYangfeng M 76

8. 5 YiDeng M 90

9. 6 YuCanghai M 56

10. 7 Jinlunfawang M 67

11. 8 HuYidao M 42

12. 9 NingZhongze F 49

2、恢复数据库需要创建一个空表，模仿原来的表创建

1. mysql> create table tutor like tutors;

把原来的表删除了

1. mysql> drop tutors;

2. mysql> load data infile '/tmp/tutor.txt' into table tutor;

验证：

1. mysql> select * from tutor;

2. +-----+--------------+--------+------+

3. | TID | Tname | Gender | Age |

4. +-----+--------------+--------+------+

5. | 1 | HongQigong | M | 93 |

6. | 2 | HuangYaoshi | M | 63 |

7. | 3 | Miejueshitai | F | 72 |

8. | 4 | OuYangfeng | M | 76 |

9. | 5 | YiDeng | M | 90 |

10. | 6 | YuCanghai | M | 56 |

11. | 7 | Jinlunfawang | M | 67 |

12. | 8 | HuYidao | M | 42 |

13. | 9 | NingZhongze | F | 49 |

14. +-----+--------------+--------+------+

当然用select也可以把表中符合条件的语句备份出来，这里不再做演示了，很简单。

这种方法适合于某长表的备份，但不会记录到二进制日志中

三、利用LVM快照从物理角度实现几乎热备的完全备份，配合二进制日志备份实现增量备份，进而实现数据库的备份。用lvm的快照来备份速度是非常快的，而且几乎热备，恢复也很快速，操作也简单，完整恢复后再将相应二进制恢复即可。前提：

1、数据文件要在逻辑卷上

2、此逻辑卷所在卷组必须有足够空间使用快照卷

3、数据文件和事务日志要在同一个逻辑卷上

步骤：

1、启动事务

1. mysql> start transaction;

2. 模拟启动事务时能否施加锁

3. mysql> insert into tutor (Tname) values ('stu001');

2、打开会话，施加读锁，锁定所有表，此时别人是不能执行命令的（不能往数据库中插入数据）

1. mysql> flush tables with read lock;

2. mysql> flush logs;

3. 查看一下二进制日志的位置：

4. mysql> show master status;

5. +------------------+----------+--------------+------------------+

7. +------------------+----------+--------------+------------------+

8. | mysql-bin.000003 | 107 | | |

9. +------------------+----------+--------------+------------------+

注意：执行表锁定时，一定不要退出

3、通过另一个终端，保存二进制日志文件及相关信息位置

1. 创建备份路径

2. # mkdir /backup/

3. # mysql -e 'show master status\G' > /backup/master-`date +%F`.info

4. 如数据库有用户名和密码要加 -u -p

4、创建快照卷

1. # lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata

5、释放锁

1. mysql> unlock tables;

2. 插入两行数据：

3. mysql> set sql_log_bin=1;

4. mysql> insert into tutor (Tname) values ('stu002');

5. mysql> insert into tutor (Tname) values ('stu003');

6. 查看二进制日志文件的位置

7. mysql> show master status;

8. +------------------+----------+--------------+------------------+

10. +------------------+----------+--------------+------------------+

11. | mysql-bin.000003 | 573 | | |

12. +------------------+----------+--------------+------------------+

6、挂载快照卷，备份

1. # mount /dev/myvg/mydata-snap /mnt/ -o ro

2. 查看：

3. # ls /mnt/

4. data lost+found

5. 创建备份目录

6. # mkdir /backup/full-backup-`date +%F`

7. # cp /mnt/data/* /backup/full-backup-2013-05-06/ -a

7、卸载/mnt,删除快照卷

1. # umount /mnt/

2. # lvremove --force /dev/myvg/mydata-snap

8、增量备份二进制日志

首先删除二进制日志文件，对我们没有太大用处

1. # rm -rf /backup/full-backup-2013-05-06/mysql-bin.* -f

现在模拟数据库被格式化

格式化之前先把二进制日志备份出来

1. # mysqlbinlog /mydata/data/mysql-bin.000003 > /backup/`date +%F`.sql

2. 关闭mysql服务器

3. # service mysqld stop

4. # rm -rf /mydata/data/*

5. # cp /backup/full-backup-2013-05-06/* /mydata/data/ -a

6. mysql> use jiaowu;

7. Database changed

8. mysql> select * from tutor;

9. +-----+--------------+--------+------+

10. | TID | Tname | Gender | Age |

11. +-----+--------------+--------+------+

12. | 1 | HongQigong | M | 93 |

13. | 2 | HuangYaoshi | M | 63 |

14. | 3 | Miejueshitai | F | 72 |

15. | 4 | OuYangfeng | M | 76 |

16. | 5 | YiDeng | M | 90 |

17. | 6 | YuCanghai | M | 56 |

18. | 7 | Jinlunfawang | M | 67 |

19. | 8 | HuYidao | M | 42 |

20. | 9 | NingZhongze | F | 49 |

21. +-----+--------------+--------+------+

22. 将二进制日志文件导入

23. mysql> source /backup/2013-05-05.sql

24. mysql> select * from tutor;

25. +-----+--------------+--------+------+

26. | TID | Tname | Gender | Age |

27. +-----+--------------+--------+------+

28. | 1 | HongQigong | M | 93 |

29. | 2 | HuangYaoshi | M | 63 |

30. | 3 | Miejueshitai | F | 72 |

31. | 4 | OuYangfeng | M | 76 |

32. | 5 | YiDeng | M | 90 |

33. | 6 | YuCanghai | M | 56 |

34. | 7 | Jinlunfawang | M | 67 |

35. | 8 | HuYidao | M | 42 |

36. | 9 | NingZhongze | F | 49 |

37. | 10 | stu001 | M | NULL |

38. | 11 | stu002 | M | NULL |

39. | 12 | stu003 | M | NULL |

40. +-----+--------------+--------+------+

41. mysql> set sql_log_bin=1;

42. mysql> show master status;

43. +------------------+----------+--------------+------------------+

45. +------------------+----------+--------------+------------------+

46. | mysql-bin.000003 | 107 | | |

47. +------------------+----------+--------------+------------------+

恢复到了还原前的状态

这就是逻辑卷实现的一次完全备份

如果在完整备份后MySQL出现故障，与mysqldump一样，先恢复上次的完整备份，再利用二进制日志恢复，找到完整备份时的二进制位置，把从那时到故障前的日志用mysqlbinlog导出来，然后导入到MySQL中就可以了。

以上就介绍了MySQL的相关知识，希望对MySQL有兴趣的朋友有所帮助。了解更多内容，请关注职坐标数据库MySQL频道！

数据库知识点数据库编程入门数据库是什么

本文由 @小职发布于职坐标。未经许可，禁止转载。

喜欢 | 0

不喜欢 | 0

看完这篇文章有何感觉？已经有0人表态，0%的人喜欢快给朋友分享吧~

后参与评论

加入IT交流圈

JAVA工程师交流群 +加入

大数据架构师交流群 +加入

人工智能Python交流群 +加入

WEB/H5前端交流群 +加入

数据库 直通车

索取资料
答疑解惑
技术交流
职业测评
面试技巧
高薪秘笈

海同师资推荐更多>>

黄骈联系TA

认证System Analyst，System Architect

程光淼联系TA

精通C、C++等语言、智能芯片开发

热门就业培训班更多>>

【零基础】人工智能+Python全栈开发课程

MySQL30天热搜词更多>>