MySQL数据库入门之备份数据库操作详解

目录

• 1、单库备份
• 2、压缩备份
• 3、多库备份
• 4、单表备份

本文实例讲述了MySQL数据库入门之备份数据库操作。分享给大家供大家参考，具体如下：

接上一次：MySQL数据库入门多实例配置

一提到数据，大家神经都会很紧张，数据的类型有很多种，但是总归一点，数据很重要，非常重要，因此，日常的数据备份工作就成了运维工作的重点中的重点的重点.................

首先来看看数据库中的数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql> select * from test; + -----+------+ | id | name | + -----+------+ |  1 | 1   | | 11 | text | | 21 | abc | |  9 | bcd  | | 111 | 1   | | 441 | text | | 41 | abc | | 999 | bcd | + -----+------+ 8 rows in set (0.00 sec)

 

1、单库备份

?

1 2 3 4 5 6

[root@centos6 ~]# mysqldump -uroot -p test >/download/testbak_$( date +%F).sql Enter password :   [root@centos6 ~]# ll /download/ total 2 -rw-r --r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql

下面我们看看这个备份文件到底是什么内容

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

[root@centos6 ~]# egrep -v "^--|\*|^$"   /download/testbak_2016-12-12.sql   DROP TABLE IF EXISTS `test`;   CREATE TABLE `test` (     `id` int (4) NOT NULL ,     ` name ` char (20) NOT NULL   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;   LOCK TABLES `test` WRITE;   INSERT INTO `test` VALUES (1, '1' ),(11, 'text' ),(21, 'abc' ),(9, 'bcd' ),(111, '1' ),(441, 'text' ),(41, 'abc' ),(999, 'bcd' );   UNLOCK TABLES;

由上的文件内容，可以看出，这个备份实际的过程就是将创建数据库、建表、插入数据的sql语句备份出来，也可以说是将sql语句导出

-B参数

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71

[root@centos6 ~]# mysqldump -uroot -p -B test >/download/testbak_$( date +%F)_b.sql   Enter password :   [root@centos6 ~]# egrep -v "^--|^$" /download/testbak_2016-12-12_b.sql    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;   /*!40101 SET NAMES utf8 */;   /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;   /*!40103 SET TIME_ZONE= '+00:00' */;   /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;   /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;   /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE= 'NO_AUTO_VALUE_ON_ZERO' */;   /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;   CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;   USE `test`;   DROP TABLE IF EXISTS `test`;   /*!40101 SET @saved_cs_client   = @@character_set_client */;   /*!40101 SET character_set_client = utf8 */;   CREATE TABLE `test` (     `id` int (4) NOT NULL ,     ` name ` char (20) NOT NULL   ) ENGINE=MyISAM DEFAULT CHARSET=latin1;   /*!40101 SET character_set_client = @saved_cs_client */;   LOCK TABLES `test` WRITE;   /*!40000 ALTER TABLE `test` DISABLE KEYS */;   INSERT INTO `test` VALUES (1, '1' ),(11, 'text' ),(21, 'abc' ),(9, 'bcd' ),(111, '1' ),(441, 'text' ),(41, 'abc' ),(999, 'bcd' );   /*!40000 ALTER TABLE `test` ENABLE KEYS */;   UNLOCK TABLES;   /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;   /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;   /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;   /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;   /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;   /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;   /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-B参数的作用一目了然，就是当我们的数据库丢失时，可以直接用此备份文件进行恢复，无需再重新建库、建表，然后再进行数据恢复的操作

 

2、压缩备份

有时候，数据库的数据比较大，可能会用到压缩后进行备份，节省备份时间与磁盘空间的使用

?

1 2 3 4 5 6 7 8 9 10 11

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbak_$( date +%F).sql.gz Enter password :   [root@centos6 ~]# ll /download/testbak_2016-12-12.sql.gz -rw-r --r--. 1 root root 753 Dec 12 20:49 /download/testbak_2016-12-12.sql.gz   [root@centos6 ~]# ll /download/ total 14 -rw-r --r--. 1 root root 2027 Dec 12 20:41 testbak_2016-12-12_b.sql -rw-r --r--. 1 root root 1888 Dec 12 20:34 testbak_2016-12-12.sql -rw-r --r--. 1 root root 753 Dec 12 20:49 testbak_2016-12-12.sql.gz

同时也可以看的压缩后的效果

 

3、多库备份

?

1 2 3 4 5 6

[root@centos6 ~]# mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$( date +%F).sql01.gz Enter password : -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.   [root@centos6 ~]# ll /download/testbak_2016-12-12.sql01.gz -rw-r --r--. 1 root root 152696 Dec 12 20:52 /download/testbak_2016-12-12.sql01.gz

此处有个警告信息，可以忽略也可以备份时加上参数，备份语句如下

?

1 2 3 4

[root@centos6 ~]# mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz Enter password : [root@centos6 ~]# ll /download/testbak_2016-12-12.sql02.gz                 -rw-r --r--. 1 root root 152749 Dec 12 20:54 /download/testbak_2016-12-12.sql02.gz

这样就不会有这为警告信息了

但是这种多库一起备份，就会产生一个问题，如果只是其中一个数据库有问题了，就不好进行单库恢复了，故此备份方法不常用，也不符合实际需求，因此多库备份时就需要进行多次单库备份的操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[root@centos6 ~]# mysqldump -uroot -p -B test|gzip >/download/testbackup_$( date +%F).sql.gz         Enter password :   [root@centos6 ~]# mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz      Enter password :   [root@centos6 ~]# ll /download/ total 80 -rw-r --r--. 1 root root 152608 Dec 12 20:58 mysqlbak_2016-12-12.sql.gz -rw-r --r--. 1 root root  754 Dec 12 20:58 testbackup_2016-12-12.sql.gz -rw-r --r--. 1 root root  2027 Dec 12 20:41 testbak_2016-12-12_b.sql -rw-r --r--. 1 root root  1888 Dec 12 20:34 testbak_2016-12-12.sql -rw-r --r--. 1 root root 152696 Dec 12 20:52 testbak_2016-12-12.sql01.gz -rw-r --r--. 1 root root 152749 Dec 12 20:54 testbak_2016-12-12.sql02.gz -rw-r --r--. 1 root root  753 Dec 12 20:49 testbak_2016-12-12.sql.gz

 

4、单表备份

分库备份是为了恢复数据库时方便操作，但是同样面临问题，如果是某个库中的某一个表有损坏，但又不有全库进行恢复，所以实际生产中常用的是分库、分表进行备份，这样数据也备份了，恢复时也好操作

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

[root@centos6 ~]# mysqldump -uroot -p -B test test >/download/test_testbak_$( date +%F).sql     Enter password :   [root@centos6 ~]# egrep -v "#|^$|\*" /download/test_testbak_2016-12-12.sql   -- MySQL dump 10.13 Distrib 5.5.52, for linux2.6 (x86_64) -- -- Host: localhost  Database: test -- ------------------------------------------------------ -- Server version    5.5.53-log -- -- Current Database: `test` -- USE `test`; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (   `id` int (4) NOT NULL ,   ` name ` char (20) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES (1, '1' ),(11, 'text' ),(21, 'abc' ),(9, 'bcd' ),(111, '1' ),(441, 'text' ),(41, 'abc' ),(999, 'bcd' ); UNLOCK TABLES; -- -- Current Database: `test` -- USE `test`; -- -- Table structure for table `test` -- DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (   `id` int (4) NOT NULL ,   ` name ` char (20) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -- Dumping data for table `test` -- LOCK TABLES `test` WRITE; INSERT INTO `test` VALUES (1, '1' ),(11, 'text' ),(21, 'abc' ),(9, 'bcd' ),(111, '1' ),(441, 'text' ),(41, 'abc' ),(999, 'bcd' ); UNLOCK TABLES; -- Dump completed on 2016-12-12 21:13:16

因此分表备份同分库备份一样，只需要进行多次单表备份的操作，但是有的小伙伴肯定会提出问题了，如果一个库里几千张表，几万张表，这种备份要备到猴年马月吧？？？？，数据量比较大的备份可以使用专业的备份工具，数据量不大或者表不是很多的情况，可以将备份操作写成脚本 纳入定时任务，定时执行，只需要检查备份是否成功即可

分享一下民工哥，实际生产环境中一个简单的备份脚本，仅供参考

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

[root@centos6 scripts]# vi bak.sh #!/bin/sh ########################################## #this scripts create by root of mingongge # create at 2016-11-11 #######################################   ip=`grep 'IPADDR' /etc/sysconfig/network-scripts/ifcfg-eth0|awk -F "=" '{print $2}' ` #定义服务器IP变量 BAKDIR=/backup #定义备份路径   [ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}   #判断如果不存在这个路径就创建一个，为了服务器多的时候方便看   DB_PWD= "mingongge" DB_USER= "root" MYSQL= "/application/mysql/bin/mysql" MYSQL_DUMP= "/application/mysql/bin/mysqldump" DATA=` date +%F`   ####bak data of test 's databses####   DB_NAME=`$MYSQL -u$DB_USER -p$DB_PWD -e "show databases;"|sed ' 1,5d '`   #定义数据库变量   for name in $DB_NAME #for循环语句取库名 do   $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz   #全库备份     [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name} #判断这个路径，为了区别哪个库的备份文件     for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed ' 1d'` # for 循环语句取表名   do    $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${ name } ${tablename} |gzip >$BAKDIR/${ip}/${ name }/${tablename}_$DATA.sql.gz #分表备份   done done

执行的结果如下

?

1 2 3 4 5 6 7 8 9 10 11 12 13

[root@ranzhioa ~]# tree /backup/ /backup/ 10.1xx.1xx.1xx  #服务器IP   xxxxxxx    #其实是库名 cash_balance_2016-12-15.sql.gz cash_depositor_2016-12-15.sql.gz cash_trade_2016-12-15.sql.gz crm_customer_2016-12-15.sql.gz crm_delivery_2016-12-15.sql.gz crm_order_2016-12-15.sql.gz crm_orderAction_2016-12-15.sql.gz crm_orderField_2016-12-15.sql.gz crm_plan_2016-12-15.sql.gz

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://segmentfault.com/a/1190000020675546