使用mysqldump模拟生产环境实现mysql数据库的备份与还原

cto67

一、mysqldump备份单个数据库及还原过程

• 错误演示：
• 下面整个过程我们都已jiaowu库为模板，进行演示
• [iyunv@localhost ~]# ls  
• anaconda-ks.cfg         cmake-2.8.8               install.log         linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz
• busybox-1.20.2          cmake-2.8.8.tar.gz        install.log.syslog  lvs-nat
• busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql          mysql-5.5.28
• [iyunv@localhost ~]# mysql < jiaowu.sql    #读取sql脚本，并创建jiaowu库
• [iyunv@localhost ~]# rm jiaowu.sql  
• rm: remove regular file `jiaowu.sql'? y   #删除jiaowu.sql
• [iyunv@localhost ~]# mysqldump -uroot -p jiaowu > /root/jiaowu.sql  #将jiaowu库备份为jiaowu.sql  
• Enter password:  
• [iyunv@localhost ~]# ls
• anaconda-ks.cfg         cmake-2.8.8               install.log         linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz
• busybox-1.20.2          cmake-2.8.8.tar.gz        install.log.syslog  lvs-nat
• busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql          mysql-5.5.28
• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 5
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> show databases;
• +--------------------+
• | Database           |
• +--------------------+
• | information_schema |
• | jiaowu             |
• | mysql              |
• | performance_schema |
• | test               |
• +--------------------+
• 5 rows in set (0.00 sec)
• mysql> drop database jiaowu;    #删除jiaowu库
• Query OK, 4 rows affected (1.11 sec)
• mysql> create database studb;   #创建一个库，用于jiaowu库的还原
• Query OK, 1 row affected (0.03 sec)
• mysql> \q
• Bye
• [iyunv@localhost ~]# mysql studb < jiaowu.sql  
• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 7
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> show databases;
• +--------------------+
• | Database           |
• +--------------------+
• | information_schema |
• | mysql              |
• | performance_schema |
• | studb              |
• | test               |
• +--------------------+
• 5 rows in set (0.00 sec)
  

注意：上述过程看似正确，实则不然，真正的生产环境中，会面临读写操作的同时进行，所以用上述过程备份的数据库还原时，会造成时间点上的不一致，并且用户的在备份过程中的写操作并没还原至数据库中，又造成了数据的不完整性，下面我们来模拟生产环境正确演示一遍完整过程！

二、mysqldump+二进制日志备份整个mysql数据库以及及时点还原的过程（温备）


备份策略：

• 备份策略：每周完全备份+每日增量备份
•     完全备份：mysqldump
•     增量备份：备份二进制日志文件（每次备份前：flush logs）
  

1、完全备份

• [iyunv@localhost ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/alldatabases.sql
• Enter password:  
• [iyunv@localhost ~]# ls
• alldatabases.sql  busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql              mysql-5.5.28
• anaconda-ks.cfg   cmake-2.8.8             install.log               linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz
• busybox-1.20.2    cmake-2.8.8.tar.gz      install.log.syslog        lvs-nat
• [iyunv@localhost ~]# vim alldatabases.sql  
• 这只是一部分，说明一下：
• -- Position to start replication or point-in-time recovery from
• --
• -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;     #这就是记录当前二进制日志文件的位置
• --
• -- Current Database: `mysql`
• --
• CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;   #做完全备份时，还能够自动创建数据库
•   
• USE `mysql`;
• --
• -- Table structure for table `general_log`
  

• 选项说明：
• --master-data={0|1|2}
•     0: 表示不记录二进制日志文件及其位置
•     1：以CHANGER MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器
•     2：以CHANGER MASTER TO 的方式记录位置，但默认为被注释
• --lock-all-tables: 锁定所有表
• --flush-logs: 滚动日志
• 如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备，这个选项不要和--lock-all-tables一起使用
• mysqldump是将整个表中的数据备份为了批量插入的insert语句，但是还原时，他不能创建数据库，必须手动创建数据库，然后再将数据还原至此数据库
  

2、模拟生产环境，执行写操作（第一天的增量）

• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 3
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> purge binary logs to 'mysql-bin.000005';    #刚才完全备份时，二进制日志也备份了，我们把之前的删除，但是在生产环境中，不建议就这样删除。先复制一份，说不定有救急作用
• Query OK, 0 rows affected (1.40 sec)
• mysql> show binary logs;    #查看当前数据库上的二进制日志文件
• +------------------+-----------+
• | Log_name         | File_size |
• +------------------+-----------+
• | mysql-bin.000005 |       107 |
• +------------------+-----------+
• 1 row in set (0.00 sec)
• mysql> use studb;
• Database changed
• mysql> select * from tutors;
• +-----+--------------+--------+------+
• | 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 |
• +-----+--------------+--------+------+
• 9 rows in set (0.01 sec)
• mysql> delete from tutors where age>80;  #删除几行
• Query OK, 2 rows affected (0.03 sec)
• mysql> select * from tutors;
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• +-----+--------------+--------+------+
• 7 rows in set (0.00 sec)
• mysql> \q
• Bye
  

3、增量备份

• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 4
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> flush logs;  #必须执行日志滚动
• Query OK, 0 rows affected (0.01 sec)
• mysql> \q
• Bye
• [iyunv@localhost ~]# cd /mydata/data      #mysql-bin.000005就是我们第一天的数据增量
• [iyunv@localhost data]# ls
• ibdata1      ib_logfile1                localhost.localdomain.pid  mysql-bin.000005  mysql-bin.index     studb
• ib_logfile0  localhost.localdomain.err  mysql                      mysql-bin.000006  performance_schema  test  
• [iyunv@localhost data]# mysqlbinlog mysql-bin.000005 > /root/mon-indremental.sql    #将数据读取出来，并保存
  

4、模拟生产环境，mysql数据库坏了的场景

• 连上数据库，插入一条新数据
• [iyunv@localhost data]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 5
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> use studb;
• Database changed
• mysql> insert into tutors (tname) values ('stu123');
• Query OK, 1 row affected (0.01 sec)
• mysql> \q
• Bye
• [iyunv@localhost data]# ls
• ibdata1      ib_logfile1                localhost.localdomain.pid  mysql-bin.000005  mysql-bin.index     studb
• ib_logfile0  localhost.localdomain.err  mysql                      mysql-bin.000006  performance_schema  test
• [iyunv@localhost data]# cp mysql-bin.000006 /root   #把二进制日志复制出来，因为我们要用它做及时点还原
• 这是我们把数据库的所有数据都删除，数据库已坏
• [iyunv@localhost data]# rm -rf ./*      
• 服务停止不了，我们杀死进程
• [iyunv@localhost data]# service mysqld stop
• MySQL server PID file could not be found!                  [FAILED]
• [iyunv@localhost data]# killall mysqld
• 这是我们千万不要试图启动mysql，先初始化数据库
• [iyunv@localhost data]# cd /usr/local/mysql/
• [iyunv@localhost mysql]# ls
• bin  COPYING  data  docs  include  INSTALL-BINARY  lib  man  mysql-test  README  scripts  share  sql-bench  support-files
• [iyunv@localhost mysql]# scripts
• scripts
• [iyunv@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data
• 初始化完成以后再启动mysql
• [iyunv@localhost mysql]# service mysqld start
• Starting MySQL.....                                        [  OK  ]
• [iyunv@localhost mysql]# cd
  

5、数据还原

• 完全备份还原
• [iyunv@localhost ~]# mysql < alldatabases.sql   
• [iyunv@localhost ~]# mysql  
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 2
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> use studb;
• Database changed
• mysql> select * from tutors;      #此时大于80的连个用户都在
• +-----+--------------+--------+------+
• | 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 |
• +-----+--------------+--------+------+
• 9 rows in set (0.00 sec)
• 增量备份还原
• [iyunv@localhost ~]# mysql < mon-indremental.sql  
• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 4
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> use studb;
• Database changed
• mysql> select * from tutors;    #此时大于80的两个用户已没有了
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• +-----+--------------+--------+------+
• 7 rows in set (0.00 sec)
• 及时点还原
• [iyunv@localhost ~]# mysqlbinlog mysql-bin.000006 > temp.sql   
• [iyunv@localhost ~]# mysql < temp.sql  
• [iyunv@localhost ~]# mysql
• Welcome to the MySQL monitor.  Commands end with ; or \g.
• Your MySQL connection id is 6
• Server version: 5.5.28-log Source distribution
• Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
• Oracle is a registered trademark of Oracle Corporation and/or its
• affiliates. Other names may be trademarks of their respective
• owners.
• Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
• mysql> use studb;
• Database changed
• mysql> select * from tutors;   #插入的数据也存在了
• +-----+--------------+--------+------+
• | TID | Tname        | Gender | Age  |
• +-----+--------------+--------+------+
• |   2 | HuangYaoshi  | M      |   63 |
• |   3 | Miejueshitai | F      |   72 |
• |   4 | OuYangfeng   | M      |   76 |
• |   6 | YuCanghai    | M      |   56 |
• |   7 | Jinlunfawang | M      |   67 |
• |   8 | HuYidao      | M      |   42 |
• |   9 | NingZhongze  | F      |   49 |
• |  10 | stu123       | M      | NULL |
• +-----+--------------+--------+------+
• 8 rows in set (0.00 sec)
  

    这时我们的mysql数据库已还原至我们损坏时的那一刻了，大家都来试试吧，但是使mysqldump来实现mysql的备份与还原，只能应用于那些读写量不大的小企业，因为备份过程太慢了，会造成精度的丢失！

dew

不在课堂上沉睡，就在酒桌上埋醉。

FXMAR

读书读到抽筋处，文思方能如尿崩!

榕叶

沙发！沙发!

liuming794

走自己的路，让别人打车去吧。

beebe_3

听君一席话，省我十本书!

-★出爺;3

做爱做的事，交配交的人。