linux教程分享之MySQL数据库备份总结

mage_linux03 发布于 2014/09/02 19:56
阅读 383
收藏 9


linux教程  一个企业的正常运行,数据的完整性是最关键的;所以我们需要在工作中要很熟练的掌握数据的备份与恢复方法;下面是对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. +------------------+----------+--------------+------------------+ 

3. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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. +------------------+----------+--------------+------------------+ 

6. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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. +------------------+----------+--------------+------------------+ 

9. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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. +------------------+----------+--------------+------------------+ 

44. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 

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

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

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

恢复到了还原前的状态

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

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

加载中
返回顶部
顶部