MySQL數(shù)據(jù)庫(kù)入門之備份數(shù)據(jù)庫(kù)操作詳解

更新時(shí)間：2020年05月11日 11:08:20 作者：民工哥

這篇文章主要介紹了MySQL數(shù)據(jù)庫(kù)入門之備份數(shù)據(jù)庫(kù)操作,結(jié)合實(shí)例形式詳細(xì)分析了MySQL備份數(shù)據(jù)庫(kù)基本操作命令與相關(guān)注意事項(xiàng),需要的朋友可以參考下

一提到數(shù)據(jù)，大家神經(jīng)都會(huì)很緊張，數(shù)據(jù)的類型有很多種，但是總歸一點(diǎn)，數(shù)據(jù)很重要，非常重要，因此，日常的數(shù)據(jù)備份工作就成了運(yùn)維工作的重點(diǎn)中的重點(diǎn)的重點(diǎn).................

首先來(lái)看看數(shù)據(jù)庫(kù)中的數(shù)據(jù)

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、單庫(kù)備份

[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

下面我們看看這個(gè)備份文件到底是什么內(nèi)容

[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;

由上的文件內(nèi)容，可以看出，這個(gè)備份實(shí)際的過(guò)程就是將創(chuàng)建數(shù)據(jù)庫(kù)、建表、插入數(shù)據(jù)的sql語(yǔ)句備份出來(lái)，也可以說(shuō)是將sql語(yǔ)句導(dǎo)出

-B參數(shù)

[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參數(shù)的作用一目了然，就是當(dāng)我們的數(shù)據(jù)庫(kù)丟失時(shí)，可以直接用此備份文件進(jìn)行恢復(fù)，無(wú)需再重新建庫(kù)、建表，然后再進(jìn)行數(shù)據(jù)恢復(fù)的操作

2、壓縮備份

有時(shí)候，數(shù)據(jù)庫(kù)的數(shù)據(jù)比較大，可能會(huì)用到壓縮后進(jìn)行備份，節(jié)省備份時(shí)間與磁盤空間的使用

[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

同時(shí)也可以看的壓縮后的效果

3、多庫(kù)備份

[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

此處有個(gè)警告信息，可以忽略也可以備份時(shí)加上參數(shù)，備份語(yǔ)句如下

[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

這樣就不會(huì)有這為警告信息了

但是這種多庫(kù)一起備份，就會(huì)產(chǎn)生一個(gè)問(wèn)題，如果只是其中一個(gè)數(shù)據(jù)庫(kù)有問(wèn)題了，就不好進(jìn)行單庫(kù)恢復(fù)了，故此備份方法不常用，也不符合實(shí)際需求，因此多庫(kù)備份時(shí)就需要進(jìn)行多次單庫(kù)備份的操作

[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、單表備份

分庫(kù)備份是為了恢復(fù)數(shù)據(jù)庫(kù)時(shí)方便操作，但是同樣面臨問(wèn)題，如果是某個(gè)庫(kù)中的某一個(gè)表有損壞，但又不有全庫(kù)進(jìn)行恢復(fù)，所以實(shí)際生產(chǎn)中常用的是分庫(kù)、分表進(jìn)行備份，這樣數(shù)據(jù)也備份了，恢復(fù)時(shí)也好操作

[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

因此分表備份同分庫(kù)備份一樣，只需要進(jìn)行多次單表備份的操作，但是有的小伙伴肯定會(huì)提出問(wèn)題了，如果一個(gè)庫(kù)里幾千張表，幾萬(wàn)張表，這種備份要備到猴年馬月吧？？？？，數(shù)據(jù)量比較大的備份可以使用專業(yè)的備份工具，數(shù)據(jù)量不大或者表不是很多的情況，可以將備份操作寫成腳本納入定時(shí)任務(wù)，定時(shí)執(zhí)行，只需要檢查備份是否成功即可

分享一下民工哥，實(shí)際生產(chǎn)環(huán)境中一個(gè)簡(jiǎn)單的備份腳本，僅供參考

[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}'`
#定義服務(wù)器IP變量
BAKDIR=/backup 
#定義備份路徑

[ ! -d $BAKDIR/${ip} ] && mkdir -p $BAKDIR/${ip}
 #判斷如果不存在這個(gè)路徑就創(chuàng)建一個(gè)，為了服務(wù)器多的時(shí)候方便看

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'`
 #定義數(shù)據(jù)庫(kù)變量

for name in $DB_NAME
#for循環(huán)語(yǔ)句取庫(kù)名
do
 $MYSQL_DUMP -u$DB_USER -p$DB_PWD -B ${name} |gzip >$BAKDIR/${ip}/${name}_$DATA.sql.gz 
 #全庫(kù)備份

 [ ! -d $BAKDIR/${ip}/${name} ] && mkdir -p $BAKDIR/${ip}/${name}
#判斷這個(gè)路徑，為了區(qū)別哪個(gè)庫(kù)的備份文件

 for tablename in `$MYSQL -u$DB_USER -p$DB_PWD -e "show tables from ${name};"|sed '1d'`
#for循環(huán)語(yǔ)句取表名
 do
  $MYSQL_DUMP -u$DB_USER -p$DB_PWD ${name} ${tablename} |gzip >$BAKDIR/${ip}/${name}/${tablename}_$DATA.sql.gz
#分表備份
 done
done

執(zhí)行的結(jié)果如下

[root@ranzhioa ~]# tree /backup/
/backup/
10.1xx.1xx.1xx  #服務(wù)器IP
 xxxxxxx    #其實(shí)是庫(kù)名
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

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

您可能感興趣的文章: