mysql数据库的备份与还原

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

 
  
  1. 错误演示： 
  2.  
  3. 下面整个过程我们都已jiaowu库为模板，进行演示
  4. [root@localhost ~]# ls  
  5. anaconda-ks.cfg         cmake-2.8.8               install.log         linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz 
  6. busybox-1.20.2          cmake-2.8.8.tar.gz        install.log.syslog  lvs-nat 
  7. busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql          mysql-5.5.28 
  8. [root@localhost ~]# mysql < jiaowu.sql    #读取sql脚本，并创建jiaowu库 
  9. [root@localhost ~]# rm jiaowu.sql  
  10. rm: remove regular file `jiaowu.sql'? y   #删除jiaowu.sql 
  11. [root@localhost ~]# mysqldump -uroot -p jiaowu > /root/jiaowu.sql  #将jiaowu库备份为jiaowu.sql  
  12. Enter password:  
  13. [root@localhost ~]# ls 
  14. anaconda-ks.cfg         cmake-2.8.8               install.log         linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz 
  15. busybox-1.20.2          cmake-2.8.8.tar.gz        install.log.syslog  lvs-nat 
  16. busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql          mysql-5.5.28 
  17. [root@localhost ~]# mysql 
  18. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  19. Your MySQL connection id is 5 
  20. Server version: 5.5.28-log Source distribution 
  21.  
  22. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  23.  
  24. Oracle is a registered trademark of Oracle Corporation and/or its 
  25. affiliates. Other names may be trademarks of their respective 
  26. owners. 
  27.  
  28. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  29.  
  30. mysql> show databases; 
  31. +--------------------+ 
  32. | Database           | 
  33. +--------------------+ 
  34. | information_schema | 
  35. | jiaowu             | 
  36. | mysql              | 
  37. | performance_schema | 
  38. | test               | 
  39. +--------------------+ 
  40. 5 rows in set (0.00 sec) 
  41.  
  42. mysql> drop database jiaowu;    #删除jiaowu库 
  43. Query OK, 4 rows affected (1.11 sec) 
  44.  
  45. mysql> create database studb;   #创建一个库，用于jiaowu库的还原 
  46. Query OK, 1 row affected (0.03 sec) 
  47.  
  48. mysql> \q 
  49. Bye 
  50. [root@localhost ~]# mysql studb < jiaowu.sql  
  51. [root@localhost ~]# mysql 
  52. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  53. Your MySQL connection id is 7 
  54. Server version: 5.5.28-log Source distribution 
  55.  
  56. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  57.  
  58. Oracle is a registered trademark of Oracle Corporation and/or its 
  59. affiliates. Other names may be trademarks of their respective 
  60. owners. 
  61.  
  62. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  63.  
  64. mysql> show databases; 
  65. +--------------------+ 
  66. | Database           | 
  67. +--------------------+ 
  68. | information_schema | 
  69. | mysql              | 
  70. | performance_schema | 
  71. | studb              | 
  72. | test               | 
  73. +--------------------+ 
  74. 5 rows in set (0.00 sec) 
 
  

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

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

      
  
 
  

    备份策略： 
  
 
  

      
  
 
  
 
  
  1. 备份策略：每周完全备份+每日增量备份 
  2.     完全备份：mysqldump 
  3.     增量备份：备份二进制日志文件（每次备份前：flush logs） 
 
  
1、完全备份
 
  
 
  
  1. [root@localhost ~]# mysqldump -uroot -p --lock-all-tables --flush-logs --all-databases --master-data=2 > /root/alldatabases.sql 
  2. Enter password:  
  3. [root@localhost ~]# ls 
  4. alldatabases.sql  busybox-1.20.2.tar.bz2  config-2.6.38.5-i686.cfg  jiaowu.sql              mysql-5.5.28 
  5. anaconda-ks.cfg   cmake-2.8.8             install.log               linux-2.6.38.5.tar.bz2  mysql-5.5.28.tar.gz 
  6. busybox-1.20.2    cmake-2.8.8.tar.gz      install.log.syslog        lvs-nat 
  7. [root@localhost ~]# vim alldatabases.sql  
  8. 这只是一部分，说明一下： 
  9.  
  10. -- Position to start replication or point-in-time recovery from 
  11. -- 
  12.  
  13. -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;     #这就是记录当前二进制日志文件的位置 
  14.  
  15. -- 
  16. -- Current Database: `mysql` 
  17. -- 
  18.  
  19. CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;   #做完全备份时，还能够自动创建数据库 
  20.   
  21. USE `mysql`; 
  22.  
  23. -- 
  24. -- Table structure for table `general_log` 
 
  
 
  
 
  
  1. 选项说明： 
  2.  
  3. --master-data={0|1|2} 
  4.     0: 表示不记录二进制日志文件及其位置 
  5.     1：以CHANGER MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器 
  6.     2：以CHANGER MASTER TO 的方式记录位置，但默认为被注释 
  7. --lock-all-tables: 锁定所有表 
  8. --flush-logs: 滚动日志 
  9.  
  10. 如果指定库中的表类型均为InnoDB，可使用--single-transaction启动热备，这个选项不要和--lock-all-tables一起使用 
  11.  
  12. mysqldump是将整个表中的数据备份为了批量插入的insert语句，但是还原时，他不能创建数据库，必须手动创建数据库，然后再将数据还原至此数据库 
 
  

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

      
  
 
  
 
  
  1. [root@localhost ~]# mysql 
  2. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  3. Your MySQL connection id is 3 
  4. Server version: 5.5.28-log Source distribution 
  5.  
  6. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  7.  
  8. Oracle is a registered trademark of Oracle Corporation and/or its 
  9. affiliates. Other names may be trademarks of their respective 
  10. owners. 
  11.  
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  13.  
  14. mysql> purge binary logs to 'mysql-bin.000005';    #刚才完全备份时，二进制日志也备份了，我们把之前的删除，但是在生产环境中，不建议就这样删除。先复制一份，说不定有救急作用 
  15. Query OK, 0 rows affected (1.40 sec) 
  16.  
  17. mysql> show binary logs;    #查看当前数据库上的二进制日志文件 
  18. +------------------+-----------+ 
  19. | Log_name         | File_size | 
  20. +------------------+-----------+ 
  21. | mysql-bin.000005 |       107 | 
  22. +------------------+-----------+ 
  23. 1 row in set (0.00 sec) 
  24.  
  25. mysql> use studb; 
  26. Database changed 
  27. mysql> select * from tutors; 
  28. +-----+--------------+--------+------+ 
  29. | TID | Tname        | Gender | Age  | 
  30. +-----+--------------+--------+------+ 
  31. |   1 | HongQigong   | M      |   93 | 
  32. |   2 | HuangYaoshi  | M      |   63 | 
  33. |   3 | Miejueshitai | F      |   72 | 
  34. |   4 | OuYangfeng   | M      |   76 | 
  35. |   5 | YiDeng       | M      |   90 | 
  36. |   6 | YuCanghai    | M      |   56 | 
  37. |   7 | Jinlunfawang | M      |   67 | 
  38. |   8 | HuYidao      | M      |   42 | 
  39. |   9 | NingZhongze  | F      |   49 | 
  40. +-----+--------------+--------+------+ 
  41. 9 rows in set (0.01 sec) 
  42.  
  43. mysql> delete from tutors where age>80;  #删除几行 
  44. Query OK, 2 rows affected (0.03 sec) 
  45.  
  46. mysql> select * from tutors; 
  47. +-----+--------------+--------+------+ 
  48. | TID | Tname        | Gender | Age  | 
  49. +-----+--------------+--------+------+ 
  50. |   2 | HuangYaoshi  | M      |   63 | 
  51. |   3 | Miejueshitai | F      |   72 | 
  52. |   4 | OuYangfeng   | M      |   76 | 
  53. |   6 | YuCanghai    | M      |   56 | 
  54. |   7 | Jinlunfawang | M      |   67 | 
  55. |   8 | HuYidao      | M      |   42 | 
  56. |   9 | NingZhongze  | F      |   49 | 
  57. +-----+--------------+--------+------+ 
  58. 7 rows in set (0.00 sec) 
  59.  
  60. mysql> \q 
  61. Bye 
 
  

      
  
 
  

    3、增量备份 
  
 
  

      
  
 
  
 
  
  1. [root@localhost ~]# mysql 
  2. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  3. Your MySQL connection id is 4 
  4. Server version: 5.5.28-log Source distribution 
  5.  
  6. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  7.  
  8. Oracle is a registered trademark of Oracle Corporation and/or its 
  9. affiliates. Other names may be trademarks of their respective 
  10. owners. 
  11.  
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  13.  
  14. mysql> flush logs;  #必须执行日志滚动
  15. Query OK, 0 rows affected (0.01 sec) 
  16.  
  17. mysql> \q 
  18. Bye 
  19. [root@localhost ~]# cd /mydata/data      #mysql-bin.000005就是我们第一天的数据增量 
  20. [root@localhost data]# ls 
  21. ibdata1      ib_logfile1                localhost.localdomain.pid  mysql-bin.000005  mysql-bin.index     studb 
  22. ib_logfile0  localhost.localdomain.err  mysql                      mysql-bin.000006  performance_schema  test  
  23. [root@localhost data]# mysqlbinlog mysql-bin.000005 > /root/mon-indremental.sql    #将数据读取出来，并保存 
 
  
4、模拟生产环境，mysql数据库坏了的场景
 
  
 
  
  1. 连上数据库，插入一条新数据 
  2. [root@localhost data]# mysql 
  3. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  4. Your MySQL connection id is 5 
  5. Server version: 5.5.28-log Source distribution 
  6.  
  7. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  8.  
  9. Oracle is a registered trademark of Oracle Corporation and/or its 
  10. affiliates. Other names may be trademarks of their respective 
  11. owners. 
  12.  
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  14.  
  15. mysql> use studb; 
  16. Database changed 
  17. mysql> insert into tutors (tname) values ('stu123'); 
  18. Query OK, 1 row affected (0.01 sec) 
  19.  
  20. mysql> \q 
  21. Bye 
  22. [root@localhost data]# ls 
  23. ibdata1      ib_logfile1                localhost.localdomain.pid  mysql-bin.000005  mysql-bin.index     studb 
  24. ib_logfile0  localhost.localdomain.err  mysql                      mysql-bin.000006  performance_schema  test 
  25. [root@localhost data]# cp mysql-bin.000006 /root   #把二进制日志复制出来，因为我们要用它做及时点还原 
  26.  
  27. 这是我们把数据库的所有数据都删除，数据库已坏 
  28. [root@localhost data]# rm -rf ./*      
  29.  
  30. 服务停止不了，我们杀死进程 
  31. [root@localhost data]# service mysqld stop 
  32. MySQL server PID file could not be found!                  [FAILED] 
  33. [root@localhost data]# killall mysqld 
  34.  
  35. 这是我们千万不要试图启动mysql，先初始化数据库 
  36. [root@localhost data]# cd /usr/local/mysql/ 
  37. [root@localhost mysql]# ls 
  38. bin  COPYING  data  docs  include  INSTALL-BINARY  lib  man  mysql-test  README  scripts  share  sql-bench  support-files 
  39. [root@localhost mysql]# scripts 
  40. scripts 
  41. [root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data 
  42.  
  43. 初始化完成以后再启动mysql 
  44. [root@localhost mysql]# service mysqld start 
  45. Starting MySQL.....                                        [  OK  ] 
  46. [root@localhost mysql]# cd 
 
  
5、数据还原
 
  
 
  
  1. 完全备份还原
  2.  
  3. [root@localhost ~]# mysql < alldatabases.sql   
  4. [root@localhost ~]# mysql  
  5. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  6. Your MySQL connection id is 2 
  7. Server version: 5.5.28-log Source distribution 
  8.  
  9. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  10.  
  11. Oracle is a registered trademark of Oracle Corporation and/or its 
  12. affiliates. Other names may be trademarks of their respective 
  13. owners. 
  14.  
  15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  16.  
  17. mysql> use studb; 
  18. Database changed 
  19. mysql> select * from tutors;      #此时大于80的连个用户都在 
  20. +-----+--------------+--------+------+ 
  21. | TID | Tname        | Gender | Age  | 
  22. +-----+--------------+--------+------+ 
  23. |   1 | HongQigong   | M      |   93 | 
  24. |   2 | HuangYaoshi  | M      |   63 | 
  25. |   3 | Miejueshitai | F      |   72 | 
  26. |   4 | OuYangfeng   | M      |   76 | 
  27. |   5 | YiDeng       | M      |   90 | 
  28. |   6 | YuCanghai    | M      |   56 | 
  29. |   7 | Jinlunfawang | M      |   67 | 
  30. |   8 | HuYidao      | M      |   42 | 
  31. |   9 | NingZhongze  | F      |   49 | 
  32. +-----+--------------+--------+------+ 
  33. 9 rows in set (0.00 sec) 
  34.  
  35. 增量备份还原 
  36.  
  37. [root@localhost ~]# mysql < mon-indremental.sql  
  38. [root@localhost ~]# mysql 
  39. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  40. Your MySQL connection id is 4 
  41. Server version: 5.5.28-log Source distribution 
  42.  
  43. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  44.  
  45. Oracle is a registered trademark of Oracle Corporation and/or its 
  46. affiliates. Other names may be trademarks of their respective 
  47. owners. 
  48.  
  49. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  50.  
  51. mysql> use studb; 
  52. Database changed 
  53. mysql> select * from tutors;    #此时大于80的两个用户已没有了 
  54. +-----+--------------+--------+------+ 
  55. | TID | Tname        | Gender | Age  | 
  56. +-----+--------------+--------+------+ 
  57. |   2 | HuangYaoshi  | M      |   63 | 
  58. |   3 | Miejueshitai | F      |   72 | 
  59. |   4 | OuYangfeng   | M      |   76 | 
  60. |   6 | YuCanghai    | M      |   56 | 
  61. |   7 | Jinlunfawang | M      |   67 | 
  62. |   8 | HuYidao      | M      |   42 | 
  63. |   9 | NingZhongze  | F      |   49 | 
  64. +-----+--------------+--------+------+ 
  65. 7 rows in set (0.00 sec) 
  66.  
  67.  
  68. 及时点还原 
  69.  
  70. [root@localhost ~]# mysqlbinlog mysql-bin.000006 > temp.sql    
  71. [root@localhost ~]# mysql < temp.sql  
  72. [root@localhost ~]# mysql 
  73. Welcome to the MySQL monitor.  Commands end with ; or \g. 
  74. Your MySQL connection id is 6 
  75. Server version: 5.5.28-log Source distribution 
  76.  
  77. Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. 
  78.  
  79. Oracle is a registered trademark of Oracle Corporation and/or its 
  80. affiliates. Other names may be trademarks of their respective 
  81. owners. 
  82.  
  83. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
  84.  
  85. mysql> use studb; 
  86. Database changed 
  87. mysql> select * from tutors;   #插入的数据也存在了 
  88. +-----+--------------+--------+------+ 
  89. | TID | Tname        | Gender | Age  | 
  90. +-----+--------------+--------+------+ 
  91. |   2 | HuangYaoshi  | M      |   63 | 
  92. |   3 | Miejueshitai | F      |   72 | 
  93. |   4 | OuYangfeng   | M      |   76 | 
  94. |   6 | YuCanghai    | M      |   56 | 
  95. |   7 | Jinlunfawang | M      |   67 | 
  96. |   8 | HuYidao      | M      |   42 | 
  97. |   9 | NingZhongze  | F      |   49 | 
  98. |  10 | stu123       | M      | NULL | 
  99. +-----+--------------+--------+------+ 
  100. 8 rows in set (0.00 sec) 
 
  

        
  
 
  

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