MySQL数据库入门之备份数据库操作详解
作者：民工哥 发布时间：2024-01-24 07:10:05
标签：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
希望本文所述对大家MySQL数据库计有所帮助。
来源：https://segmentfault.com/a/1190000020675546
0
投稿猜你喜欢
在本节中，您将开始修改为电影控制器所新加的操作方法和视图。然后，您将添加一个自定义的搜索页。在浏览器地址栏里追加/Movies， 浏览到Mo- 1. # 可以使用LaTeX表示数学公式# 可以使用LaTeX表示数学公式from IPython.display import Latex
- 举例如下： 数据表为DemoTable，字段有id, condition1,condition2,condition3,condition4
- 今晚开放ecmall商城的QQ登陆功能，在回调时产生错误，file_get_contents函数执行时，没有抓取到正确的信息，于是改用cur
- 正则表达式是一个特殊的字符序列，它能帮助你方便的检查一个字符串是否与某种模式匹配。re 模块使 Python 语言拥有全部的正则表达式功能。
- 需要安装pywin32模块，pip install pywin32##pip install pywin32import win32api,
- 详解python中 os._exit() 和 sys.exit(), exit(0)和exit(1) 的用法和区别os._exit() 和
- 1、使用函数shutil.make_archive()创建归档文件，并返回归档后的名称。import shutilpath_1 = r
- 我就废话不多说了，大家还是直接看代码吧~lt=client.fangjia.district_stat_all_0416dl = dt.fi
- 本教程旨在介绍如何使用七牛的Python SDK来快速地进行文件上传，下载，处理，管理等工作。安装首先，要使用Python的SDK必须要先安
- Python os 模块os是“operating system”的缩写，os模块提供各种 Pyth
- 本文实例讲述了python使用socket连接远程服务器的方法。分享给大家供大家参考。具体如下：import socketprint &qu
- MySQL的数据库管理工具非常多，有哪些优秀的GUI工具可以帮助提高工作效率?不妨看一看这5个MySQL GUI工具。1、Navicat f
- 检测是否注册成功<％ Set Jpeg =Server.CreateObject("Persi
- OpenCV介绍OpenCV是一个基于BSD许可（开源）发行的跨平台计算机视觉库，可以运行在Linux、Windows、Android和Ma
- 目录1.列表的介绍2.打印出列表的数据1.我们可以根据下标取值进行打印2.使用for循环遍历3.使用while循环遍历3.列表的添加操作1.
- 学生信息系统提示：python编写的学生成绩管理系统，包括8个功能和打包教程一、功能界面 def menum():
- 一、用户相关SQL语句/*新建用户*/create user SA identified by 2013;说明：SA用户名，2013密码/*
- 本文实例讲述了Python minidom模块用法。分享给大家供大家参考，具体如下：一、DOM写XML文件# -*- coding:utf-
- pycharm确实要比Spyder功能全面，但是anaconda的环境配置实在太方便了，所以想take both，其实很简单，利用pycha
