MySQL数据库备份总结

sky

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

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

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

• # mkdir /mybinlog
• # chown mysql.mysql /mybinlog
  

2、修改配置文件

• # vim /etc/my.cnf
• log-bin=/mybinlog/mysql-bin   二进制日志目录及文件前缀
• innodb_file_per_table = 1   启用innoDB表
• datadir = /mydata/data    指定数据库的目录
  

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

• # mkdir /mybackup
• # chown -R mysql.mysql /mybackup
  

4、启动mysql服务器

• # service mysqld start
  

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

• # mysql < jiaowu.sql
  

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

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

• # mysqldump --all-databases --lock-all-tables --routines --triggers --master-data=2
• --flush-logs > /mybackup/alldatabase.sql
• 解释各个选项的意义：
• --all-databases 备份所有数据库
• --lock-all-tables 为所有表加锁
• --routines 存储过程与存储函数
• --triggers 触发器
• --master-data=2 以change master to的方式记录位置，但默认为被注释
• --flush-logs  执行日志滚动
  

1.2、备份二进制日志

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

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

• # rm -rf /mydata/data/*
• # rm -rf /mybinlog/*
  

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

• # cd /usr/local/mysql/
• # ./scripts/mysql_install_db --user=mysql --datadir=/mydata/data
  

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

• # rm -rf /mybinlog/*
• # netstat -tnlp 查看启动的mysql进程号
• tcp        0      0 0.0.0.0:3306           0.0.0.0:*           LISTEN      2523/mysql
• # kill 2523  （注意，如果在备份之前mysql服务是开启的，重启是不成功的，需要把这个进程先kill掉）
• # service mysqld restart
  

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

• # mysql < /mybackup/alldatabase.sql  
  

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

2.1、往students表中添加数据

• mysql> use jiaowu;
• mysql> insert into students (Name,Age,Gender) values ('hadoop',22,'M');
  

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

• mysql> drop tables students;
  

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

• mysql> show master status;
• +------------------+----------+--------------+------------------+
• | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
• +------------------+----------+--------------+------------------+
• | mysql-bin.000002 |   520351 |              |                  |
• +------------------+----------+--------------+------------------+
  

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

• mysql> set global sql_log_bin=0;
• # mysql < /mybackup/alldatabase.sql
  

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

• # mysqlbinlog /mybinlog/mysql-bin.000002  内容如下：
• # at 520084
• #130501 16:44:08 server id 1 end_log_pos 520212 Query thread_id=2 exec_time=0 error_code=0
• use jiaowu/*!*/;
• SET TIMESTAMP=1367397848/*!*/;
• insert into students (Name,Age,Gender) values ('hadoop',22,'M')
• /*!*/;
• # at 520212
• #130501 16:44:08 server id 1  end_log_pos 520239    Xid = 308
• COMMIT/*!*/;
• # at 520239     删除命令在这个时刻开始执行的
• #130501 16:45:37 server id 1 end_log_pos 520351 Query thread_id=2 exec_time=0 error_code=0
• SET TIMESTAMP=1367397937/*!*/;
• DROP TABLE `students` /* generated by server */
• /*!*/;
• DELIMITER ;
• # End of log file
  

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

• # mysqlbinlog --stop-position=520239 /mybinlog/mysql-bin.000002 > /root/change.sql
• 解释：
• --start-position 指定从哪开始导出二进制日志  
• --stop-position 指定到哪结束  
• --start-datetime 从哪个时间开始
• --stop-datetime 到哪个时间结束  
  

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

• # mysql < /root/change.sql
  

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

• mysql> select Name,Age,Gender from students where Name='hadoop';
• +--------+------+--------+
• | Name   | Age  | Gender |
• +--------+------+--------+
• | hadoop |   22 | M      |
• +--------+------+--------+
  

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

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

• mysql> select * into outfile '/tmp/tutor.txt' from tutors;
• 保存为.txt格式：因为它是纯文本格式的，保存的是表格式的信息，如下所示：
• # cat /tmp/tutor.txt  
• 1   HongQigong  M   93
• 2   HuangYaoshi M   63
• 3   Miejueshitai    F   72
• 4   OuYangfeng  M   76
• 5   YiDeng  M   90
• 6   YuCanghai   M   56
• 7   Jinlunfawang    M   67
• 8   HuYidao M   42
• 9   NingZhongze F   49
  

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

• mysql> create table tutor like tutors;
  

把原来的表删除了

• mysql> drop tutors;
• mysql> load data infile '/tmp/tutor.txt' into table tutor;
  

验证：

• mysql> select * from tutor;
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   1 | HongQigong   | M      |   93 |
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   5 | YiDeng       | M      |   90 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• +-----+--------------+--------+------+
  

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

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

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

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

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

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

步骤：

1、启动事务

• mysql> start transaction;
• 模拟启动事务时能否施加锁
• mysql> insert into tutor (Tname) values ('stu001');
  

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

• mysql> flush tables with read lock;
• mysql> flush logs;
• 查看一下二进制日志的位置：
• mysql> show master status;
• +------------------+----------+--------------+------------------+
• | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
• +------------------+----------+--------------+------------------+
• | mysql-bin.000003 |      107 |              |                  |
• +------------------+----------+--------------+------------------+
  

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

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

• 创建备份路径
• # mkdir /backup/   
• # mysql -e 'show master status\G' > /backup/master-`date +%F`.info
• 如数据库有用户名和密码要加 -u -p
  

4、创建快照卷

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

5、释放锁

• mysql> unlock tables;
• 插入两行数据：
• mysql> set sql_log_bin=1;
• mysql> insert into tutor (Tname) values ('stu002');
• mysql> insert into tutor (Tname) values ('stu003');
• 查看二进制日志文件的位置
• mysql> show master status;
• +------------------+----------+--------------+------------------+
• | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
• +------------------+----------+--------------+------------------+
• | mysql-bin.000003 |      573 |              |                  |
• +------------------+----------+--------------+------------------+
  

6、挂载快照卷，备份

• # mount /dev/myvg/mydata-snap /mnt/ -o ro  
• 查看：
• # ls /mnt/
• data  lost+found
• 创建备份目录
• # mkdir /backup/full-backup-`date +%F`
• # cp /mnt/data/* /backup/full-backup-2013-05-06/ -a
  

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

• # umount /mnt/
• # lvremove --force /dev/myvg/mydata-snap  
  

8、增量备份二进制日志

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

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

现在模拟数据库被格式化

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

• # mysqlbinlog   /mydata/data/mysql-bin.000003  > /backup/`date +%F`.sql
• 关闭mysql服务器
• # service mysqld stop
• # rm -rf /mydata/data/*
• # cp /backup/full-backup-2013-05-06/* /mydata/data/ -a
• mysql> use jiaowu;
• Database changed
• mysql> select * from tutor;
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   1 | HongQigong   | M      |   93 |
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   5 | YiDeng       | M      |   90 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• +-----+--------------+--------+------+
• 将二进制日志文件导入
• mysql> source /backup/2013-05-05.sql
• mysql> select * from tutor;
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   1 | HongQigong   | M      |   93 |
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   5 | YiDeng       | M      |   90 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• |  10 | stu001       | M      | NULL |
• |  11 | stu002       | M      | NULL |
• |  12 | stu003       | M      | NULL |
• +-----+--------------+--------+------+
• mysql> set sql_log_bin=1;
• mysql> show master status;
• +------------------+----------+--------------+------------------+
• | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
• +------------------+----------+--------------+------------------+
• | mysql-bin.000003 |      107 |              |                  |
• +------------------+----------+--------------+------------------+
  

恢复到了还原前的状态

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

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

13432878738

商女不知亡国恨、妓女不懂婚外情。

prollerweg

男人在结婚前觉得适合自己的女人很少，结婚后觉得适合自己的女人很多。

k668

所有的男人生来平等，结婚的除外。

civilvar

帮帮顶顶！！

窝窝插件

学海无涯，回头是岸!

浪人

只要不下流，我们就是主流!