MySQL数据库入门——备份数据库

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 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

MySQL数据库入门——备份数据库 **长按关注公众号——友侃有笑**
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> MySQL数据库入门——备份数据库


 上一篇
MySQL数据库入门——常用基础命令 MySQL数据库入门——常用基础命令
**MySQL数据库入门———常用基础命令** **    数据——公司的生命线,因此在大多数的互联网公司,都在使用开源的数据库产品,MySQL也因此关注度与使用率非常的高,所以做为运维的屌丝们,掌握它的一些基本操作还是
下一篇 
Oracle服务端和客户端版本补丁不一致造成Tuxedo应用程序出core案例 Oracle服务端和客户端版本补丁不一致造成Tuxedo应用程序出core案例
这是一个CU*系统调用的查询天气预报的Tuxedo服务,Oracle ProC程序,连接的是Oracle 10.2.0.4库,出现问题的现象是不定时的产生core,服务down,触发Tuxedo服务的自动重启机制,但一般MAXGEN配置是1