服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL数据库入门之备份数据库操作详解

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

2021-01-16 16:38民工哥 Mysql

这篇文章主要介绍了MySQL数据库入门之备份数据库操作,结合实例形式详细分析了MySQL备份数据库基本操作命令与相关注意事项,需要的朋友可以参考下

本文实例讲述了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

  • mysql
  • 数据库
  • 备份数据库

    • 延伸 · 阅读

    精彩推荐
    • Mysqlmysql 不能插入中文问题

      mysql 不能插入中文问题

      当向mysql5.5插入中文时，会出现类似错误 ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column ...

      MYSQL教程网5722019-11-25
    • MysqlMySQL锁的知识点总结

      MySQL锁的知识点总结

      在本篇文章里小编给大家整理了关于MySQL锁的知识点总结以及实例内容，需要的朋友们学习下。...

      别人放弃我坚持吖4362020-12-14
    • Mysql浅谈mysql 树形结构表设计与优化

      浅谈mysql 树形结构表设计与优化

      在诸多的管理类，办公类等系统中，树形结构展示随处可见，本文主要介绍了mysql 树形结构表设计与优化，具有一定的参考价值，感兴趣的小伙伴们可以参...

      小码农叔叔5242021-11-16
    • Mysql解决MySQl查询不区分大小写的方法讲解

      解决MySQl查询不区分大小写的方法讲解

      今天小编就为大家分享一篇关于解决MySQl查询不区分大小写的方法讲解，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起...

      Veir_dev5592019-06-25
    • MysqlMySQL 数据备份与还原的示例代码

      MySQL 数据备份与还原的示例代码

      这篇文章主要介绍了MySQL 数据备份与还原的相关知识,本文通过示例代码给大家介绍的非常详细，具有一定的参考借鉴价值，需要的朋友可以参考下...

      逆心2962019-06-23
    • Mysql详解MySQL中的分组查询与连接查询语句

      详解MySQL中的分组查询与连接查询语句

      这篇文章主要介绍了MySQL中的分组查询与连接查询语句,同时还介绍了一些统计函数的用法,需要的朋友可以参考下 ...

      GALAXY_ZMY5432020-06-03
    • MysqlMySQL数据库varchar的限制规则说明

      MySQL数据库varchar的限制规则说明

      本文我们主要介绍了MySQL数据库中varchar的限制规则，并以一个实际的例子对限制规则进行了说明，希望能够对您有所帮助。 ...

      mysql技术网4192019-11-23
    • MysqlERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event()

      ERROR: Error in Log_event::read_log_event(): read error, data_len: 438, event_type: 2 ...

      MYSQL教程网6402020-03-13
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1449 　 1053 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1862 　 1864 　 1146 　 2003 　 1175 　 1215 　 1615 　 1054 　 2006 　 1130 　 1366 　 10048 　 28000 　 1786 　 1135 　 1577 　 1665 　 1067 　 1093 　
    © 2019-2024 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图