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. +------------------+----------+--------------+------------------+
  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=2exec_time=0error_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=2exec_time=0error_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. +--------+------+--------+
  • 1
  • 2
  • 3
  • 下一页

相关内容