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

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

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

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

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

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、单库备份

[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

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

[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参数

[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、压缩备份

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

[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、多库备份

[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

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

[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

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

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

[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、单表备份

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

[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

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

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

[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

执行的结果如下

[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