MySQL資料庫備份與恢複

可以看出當我們不加--compact這個選項時備份出來的sql檔案有很多注釋和一些沒有用的垃圾資訊。但是加上--compact這個選項後，有些選項的内容也是被過濾掉不顯示的比如：-skip-add-drop-table --no-set-names

--skip-disable-keys --skip-add-locks這些選項的資訊就不會給出。是以--compact這個選項我們用于debug調試。正式環境還是不要用這個選項

1.mysqldump 邏輯備份mysql資料庫。

mysqldump的原理：mysqldump指令備份資料的過程，實際上就是把資料從mysql庫裡以邏輯的sql語句的形式直接輸出到标準輸出，要想儲存到檔案，我們需要用到輸出重定向到檔案來儲存。

文法：mysqldump -u使用者名 -p密碼 [-hmysql資料庫位址] 要備份的資料庫名 [要備份的表名] > 備份檔案名

--compact 優化備份檔案大小減少輸出注釋，主要用于debug調試

[root@qiuhom bak]# mysqldump -uroot -padmin  qiuhom test >/work/bak/qiuhom.sql                          
[root@qiuhom bak]# cat qiuhom.sql 
-- MySQL dump 10.13  Distrib 5.5.32, for Linux (x86_64)
--
-- Host: localhost    Database: qiuhom
-- ------------------------------------------------------
-- Server version       5.5.32-log

/*!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 */;

--
-- Table structure for table `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(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
/*!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 */;

-- Dump completed on 2018-10-06 13:14:03
[root@qiuhom bak]# mysqldump -uroot -padmin  --compact qiuhom test >/work/bak/qiuhom.sql 
[root@qiuhom bak]# cat qiuhom.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

　　說明：可以看出當我們不加--compact這個選項時備份出來的sql檔案有很多注釋和一些沒有用的垃圾資訊。但是加上--compact這個選項後，有些選項的内容也是被過濾掉不顯示的比如：-skip-add-drop-table --no-set-names

--skip-disable-keys --skip-add-locks這些選項的資訊就不會給出。是以--compact這個選項我們用于debug調試。正式環境還是不要用這個選項

-B 指定資料庫，可以多個資料庫，注意-B參數後面全部是接的庫名，不能跟表名。

1.01備份多個庫

mysqldump -uroot -padmin -B 庫1 庫2 庫3 > /work/bak/xxx.sql

[root@qiuhom ~]# mysqldump -uroot -padmin -B qiuhom qiuhom_gbk > /work/bak/qiuhom_qiuhom_gbk.sql
[root@qiuhom ~]# egrep -v "#|\*|--|^$" /work/bak/qiuhom_qiuhom_gbk.sql                          
USE `qiuhom`;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
UNLOCK TABLES;
USE `qiuhom_gbk`;
DROP TABLE IF EXISTS `test_gbk`;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
LOCK TABLES `test_gbk` WRITE;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');
UNLOCK TABLES;

1.02備份單個庫

mysqldump -uroot -padmin 庫名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom_gbk   
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');

mysqldump -uroot -padmin -B 庫名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -B qiuhom_gbk 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `qiuhom_gbk` /*!40100 DEFAULT CHARACTER SET gbk */;

USE `qiuhom_gbk`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test_gbk` (
  `id` int(11) NOT NULL,
  `name` char(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test_gbk` VALUES (1,'hh'),(2,'ff'),(3,'xx');

　　說明：從以上兩條指令傳回的結果來看我可以得出一點 mysql備份加上-B的選項時，備份的sql檔案裡就會多一條create 備份資料庫名和use 備份資料庫名，這兩條條語句，所有加-B備份的庫在還原時可以不指定庫名，也可以不用顧慮還原的庫是否存在，直接還原即可。沒有加-B選項備份的還原就需要考慮庫是否存在，還要指定庫名。這就是加-B的好處。

1.03備份多個表

mysqldump -uroot -padmin 庫名表1名表2名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom test test1 test2 >/work/bak/qiuhom_tables.sql
[root@qiuhom bak]# cat qiuhom_tables.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

1.04備份單個表

mysqldump -uroot -padmin 庫名表名 > /work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact  qiuhom test >/work/bak/qiuhom__test.sql             
[root@qiuhom bak]# cat qiuhom__test.sql           
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

　　說明：備份表的文法是一個庫名後面可以跟一個表或者多個表名，但都是同一個庫裡的表。

-d 備份表結構

1.05備份單表結構

mysqldump -uroot -padmin -d 庫名表名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom test >/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

　　說明：可以看出加上-d選項後，備份到sql檔案裡沒有insert into 語句插入資料，隻有建表的語句。

1.06備份多表結構

mysqldump -uroot -padmin -d 庫名表名1 表明2 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom test test1>/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

1.07備份某庫裡的所有表結構

mysqldump -uroot -padmin -d 庫名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -d qiuhom >/work/bak/qiuhom__test.sql          
[root@qiuhom bak]# cat qiuhom__test.sql 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

　　說明：備份表結構和備份表都是一樣的文法隻是多-d的選項，也是在同一個庫裡面的，不能跨庫的去備份。

-t 隻備份資料

1.08單表備份資料

mysqldump -uroot -padmin -t 庫名表名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom test>/work/bak/qiuhom__test.sql 
[root@qiuhom bak]# cat qiuhom__test.sql 
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

1.09多表備份資料

mysqldump -uroot -padmin -t 庫名表名1 表名2>/work/bak/xxx.sql　

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom test test1>/work/bak/qiuhom__test.sql
[root@qiuhom bak]# cat qiuhom__test.sql 
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO `test1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(1,'a'),(2,'b'),(3,'c'),(4,'d');

1.10備份整個庫的表資料

mysqldump -uroot -padmin -t 庫名 >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -t  qiuhom>/work/bak/qiuhom__test.sql                
[root@qiuhom bak]# cat qiuhom__test.sql 
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
INSERT INTO `test1` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(1,'a'),(2,'b'),(3,'c'),(4,'d');
INSERT INTO `test2` VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

說明：備份資料和備份表、備份表結構都是一樣，必須跟庫名然後是表名，一個或多個，不能跨庫備份。　

-A 備份資料庫裡的所有庫的内容

mysqldump -uroot -padmin -A >/work/bak/xxx.sql

[root@qiuhom bak]# mysqldump -uroot -padmin --compact -A  >/work/bak/qiuhom__test.sql       
[root@qiuhom bak]# cat qiuhom__test.sql 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `mysql`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `slow_log` (
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `query_time` time NOT NULL,
  `lock_time` time NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log';

由于内容過多。。隻截取了部分作為展示

　　說明：加了-A選項就表示備份整個資料庫的内容，包括mysql系統的庫都給一起備份下來了

-F 重新整理bin-log

這個參數的主要作用是重新整理binlog檔案，就是你每備份一次binlog檔案就會自動重新整理為一個新的檔案

[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*
ls: cannot access /application/mysql/data/mysql-bin*: No such file or directory
[root@qiuhom bak]# mysqldump -uroot -padmin -B qiuhom >/work/bak/qiuhom.sql 
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                    
ls: cannot access /application/mysql/data/mysql-bin*: No such file or directory
[root@qiuhom bak]# mysqldump -uroot -padmin -F -B qiuhom >/work/bak/qiuhom.sql 
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                       
-rw-rw---- 1 mysql mysql 107 Oct  6 12:50 /application/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql  19 Oct  6 12:50 /application/mysql/data/mysql-bin.index
[root@qiuhom bak]# mysqldump -uroot -padmin -F -B qiuhom >/work/bak/qiuhom.sql 
[root@qiuhom bak]# ll /application/mysql/data/mysql-bin*                       
-rw-rw---- 1 mysql mysql 150 Oct  6 12:50 /application/mysql/data/mysql-bin.000001
-rw-rw---- 1 mysql mysql 107 Oct  6 12:50 /application/mysql/data/mysql-bin.000002
-rw-rw---- 1 mysql mysql  38 Oct  6 12:50 /application/mysql/data/mysql-bin.index

　這裡順便說下binlog怎麼開啟吧，binlog是mysql的日志檔案，記錄着mysql内部增删改查等對mysql資料庫有更新的内容記錄，這個檔案相當重要，對恢複資料有很大的幫助，所有一般情況我們都是把他給開啟的。開啟binlog很簡單在mysql的配置檔案裡找到log-bin=mysql-bin,預設是關閉的狀态，就是注釋掉的，我們把注釋去掉，儲存退出後重新開機mysql 我們就可以在mysql安裝的目錄的data目錄看到mysql-bin.000001等名字的檔案這個就是binlog檔案。這個檔案是mysql的日志檔案，一般我們用mysqlbinlog 這個指令去檢視裡面的内容，用cat和vim都是亂碼。

--master-data 增加bin-log日志檔案名及對應的位置點

mysqldump導出資料時，當這個選項的值為1的時候，mysqldump出來的檔案就會包括CHANGE MASTER TO這個語句，CHANGE MASTER TO後面緊接着就是binlog檔案和position的記錄資訊，在slave上導入資料時就會執行這個語句，salve就會根據指定這個檔案位置從master端複制binlog。預設情況下這個選項的值是1;當這個選項的值是2的時候，chang master to也是會寫到dump檔案裡面去的，但是這個語句是被注釋的狀态。

[root@qiuhom bak]# mysqldump -uroot -padmin --compact --master-data=1 qiuhom test >/work/bak/qiuhom.sql    
[root@qiuhom bak]# cat qiuhom.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');
[root@qiuhom bak]# mysqldump -uroot -padmin --compact --master-data=2 qiuhom test >/work/bak/qiuhom.sql  
[root@qiuhom bak]# cat qiuhom.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=107;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'a'),(2,'b'),(3,'c');

2.分庫備份和恢複

2.1指令拼接法

思想：我們用mysql -e的方式去查詢資料裡的所有庫，然後過濾掉不需要備份的庫，然後用sed拼接指令來完成，最後把拼接好的字元指令交給bash執行

mysql -uroot -padmin -e "show databases;"|grep -Evi "database|info|perfo"|sed -r 's#^([a-z].*$)#mysqldump -uroot -padmin -B --events \1|gzip >/work/bak/\1.sql.gz#g'|bash

　　說明：我們已經知道備份庫的方法，所謂分庫備份就是把一個庫一個庫單獨備份成單獨的檔案，友善日後某個庫出問題好恢複，這也分庫備份的意義所在，以上指令的核心思想就是通過指令把要執行備份的資料庫名和備份資料庫名的指令字元作組合成指令，然後再通過管道傳給bash去執行。

2.2shell腳本，for循環

思想：利用mysql -e在Linux指令行裡執行sql語句取出所有需要備份的資料庫名，然後通過for循環去循環執行備份的指令。

#!/bin/bash
dbname=`mysql -uroot -padmin -e "show databases;"|grep -Evi "database|info|perfo"`
for db in ${dbname}
    do
        mysqldump -uroot -padmin -B $db |gzip >/work/bak/${db}.sql.gz
    done

2.3批量恢複分庫備份的資料庫

思想：從上面的腳本看，我們要注意一下恢複的原理，恢複的原理就是把sql檔案裡的sql語句給在mysql裡執行一遍，因為上面的備份我們做過壓縮，是以第一件事就是把壓縮的備份檔案給解壓。其次用for循環去循環執行檔案裡的内容，還有一點需要注意的是你要看你備份時是否用-B去指定庫名，如果沒有，恢複的時候就要指定庫名，是以還要考慮庫是否存在的問題，這是不用-B需要考慮的。

#!/bin/bash
if [ `ls /work/bak/*.gz|wc -l` -ne 0 ];then
    gzip -d /work/bak/*
    for dbname in `ls /work/bak/*.sql`
	do
	mysql -uroot -padmin </work/bak/${dbname}
	done
fi

　說明：以上因為備份用到-B 是以恢複時不需要判斷是否存在資料庫，以及指定資料庫。　

3.分庫分表備份和恢複

3.1批量分庫分表備份

思想：第一層for循環負責循環庫,第二層for循環負責循環庫裡的表，最終是一個庫循環備份完庫裡的所有表後，在進行循環第二個庫，以此類推。

#!/bin/bash
dbname=`mysql -uroot -padmin -e "show databases;"|grep -Evi "database|info|perfo"`
for db in ${dbname }
    do
        for tb in `mysql -uroot -padmin -e "use ${db};show tables;"|grep -vi "Tables_in_${db}"`
            do
                mysqldump -uroot -padmin ${db} ${tb} |gzip >/work/bak/${dbname}__${tb}.sql.gz         
            done    
    done

3.2批量恢複分庫分表備份

思想：和分庫分表備份思想一樣，隻是還原的時候要指定庫是以要把庫名給切出來，其次就是表名，表名要分出那個庫裡有哪些表，然後一個庫裡的所有表還原完後，在循環還原下一個庫，同理我們分離表名的時候也是，一個庫裡的所有表名全部分離出來後，就還原一個庫裡的所有表，還原完了還原下一個庫。

#!/bin/bash
dbname=`ls -l /work/bak/|awk '{print $9}'|awk -F "__" '{print $1}'|uniq|grep -Ev "^$"`
for db in ${dbname}
    do
        if [ `mysql -uroot -padmin -e "show databases;"|grep -E "^($db)$"|wc -l` -eq 0 ];then
            mysql -uroot -padmin -e "create database $db;"
        fi
        #db_tb=`ls -l /work/bak/|grep ${db}|awk -F "__" '{print $2}'|sed 's#.sql.gz##g'`
        #db_tb=`ls -l /work/bak/|grep ${db}|awk -F "__" '{print $2}'|cut -d. -f1`
        db_tb=`ls -l /work/bak/|grep ${db}|awk -F "__" '{print $2}'|awk -F "." '{print $1}'` ###分離相同庫裡的各個表
        for tb in ${db_tb}
            do
                gzip -d /work/bak/${db}__${tb}.sql.gz
                mysql -uroot -padmin $db </work/bak/${db}__${tb}.sql
            done
    done

3.3分庫分表恢複指令拼接

ls -l /work/bak/|awk '{print $9}'|sed -r 's#(^.*)__(.*).sql.gz#gzip -d /work/bak/\1__\2.sql.gz; mysql -uroot -padmin \1 </work/bak/\1__\2.sql#g'|bash

　　提示：以上指令需庫存中的情況使用，若庫不存在還需要考慮庫的問題。

[root@qiuhom ~]# mysql -uroot -padmin -e "use qiuhom;show tables;"
[root@qiuhom ~]# ls -l /work/bak/|awk '{print $9}'|sed -r 's#(^.*)__(.*).sql.gz#gzip -d /work/bak/\1__\2.sql.gz; mysql -uroot -padmin \1 </work/bak/\1__\2.sql#g'
gzip -d /work/bak/mysql__columns_priv.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__columns_priv.sql
gzip -d /work/bak/mysql__db.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__db.sql
gzip -d /work/bak/mysql__event.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__event.sql
gzip -d /work/bak/mysql__func.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__func.sql
gzip -d /work/bak/mysql__general_log.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__general_log.sql
gzip -d /work/bak/mysql__help_category.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__help_category.sql
gzip -d /work/bak/mysql__help_keyword.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__help_keyword.sql
gzip -d /work/bak/mysql__help_relation.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__help_relation.sql
gzip -d /work/bak/mysql__help_topic.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__help_topic.sql
gzip -d /work/bak/mysql__host.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__host.sql
gzip -d /work/bak/mysql__ndb_binlog_index.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__ndb_binlog_index.sql
gzip -d /work/bak/mysql__plugin.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__plugin.sql
gzip -d /work/bak/mysql__procs_priv.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__procs_priv.sql
gzip -d /work/bak/mysql__proc.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__proc.sql
gzip -d /work/bak/mysql__proxies_priv.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__proxies_priv.sql
gzip -d /work/bak/mysql__servers.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__servers.sql
gzip -d /work/bak/mysql__slow_log.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__slow_log.sql
gzip -d /work/bak/mysql__tables_priv.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__tables_priv.sql
gzip -d /work/bak/mysql__time_zone_leap_second.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__time_zone_leap_second.sql
gzip -d /work/bak/mysql__time_zone_name.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__time_zone_name.sql
gzip -d /work/bak/mysql__time_zone.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__time_zone.sql
gzip -d /work/bak/mysql__time_zone_transition.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__time_zone_transition.sql
gzip -d /work/bak/mysql__time_zone_transition_type.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__time_zone_transition_type.sql
gzip -d /work/bak/mysql__user.sql.gz; mysql -uroot -padmin mysql </work/bak/mysql__user.sql
gzip -d /work/bak/qiuhom_gbk__test_gbk.sql.gz; mysql -uroot -padmin qiuhom_gbk </work/bak/qiuhom_gbk__test_gbk.sql
gzip -d /work/bak/qiuhom__test1.sql.gz; mysql -uroot -padmin qiuhom </work/bak/qiuhom__test1.sql
gzip -d /work/bak/qiuhom__test2.sql.gz; mysql -uroot -padmin qiuhom </work/bak/qiuhom__test2.sql
gzip -d /work/bak/qiuhom__test.sql.gz; mysql -uroot -padmin qiuhom </work/bak/qiuhom__test.sql
[root@qiuhom ~]# ls -l /work/bak/|awk '{print $9}'|sed -r 's#(^.*)__(.*).sql.gz#gzip -d /work/bak/\1__\2.sql.gz; mysql -uroot -padmin \1 </work/bak/\1__\2.sql#g'|bash
[root@qiuhom ~]# mysql -uroot -padmin -e "use qiuhom;show tables;"
+------------------+
| Tables_in_qiuhom |
+------------------+
| test             |
| test1            |
| test2            |
+------------------+

mysql裡面恢複資料庫

在mysql裡面我們用source +備份的sql檔案還原資料庫。source+備份檔案是必須要登入mysql後執行的語句，當然也可以用mysql -e 去執行。

[root@qiuhom ~]# mysql -uroot -padmin -e "show databases;use qiuhom_gbk;select *from test_gbk;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
| qiuhom_gbk         |
+--------------------+
+----+------+
| id | name |
+----+------+
|  1 | hh   |
|  2 | ff   |
|  3 | xx   |
+----+------+
[root@qiuhom ~]# mysqldump -uroot -padmin -B qiuhom_gbk --compact > qiuhom_gbk.sql
[root@qiuhom ~]# mysql -uroot -padmin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 534
Server version: 5.5.32-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
| qiuhom_gbk         |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database qiuhom_gbk;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
+--------------------+
4 rows in set (0.00 sec)

mysql> source qiuhom_gbk.sql
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qiuhom             |
| qiuhom_gbk         |
+--------------------+
5 rows in set (0.00 sec)

mysql> use qiuhom_gbk;
Database changed
mysql> show tables;
+----------------------+
| Tables_in_qiuhom_gbk |
+----------------------+
| test_gbk             |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from test_gbk;
+----+------+
| id | name |
+----+------+
|  1 | hh   |
|  2 | ff   |
|  3 | xx   |
+----+------+
3 rows in set (0.00 sec)

　當然mysql -e “source +sql檔案”也是可以的。我們可以自己寫sql檔案用source 去執行，比如插入多條資料以不同的字元集去插入。　

[root@qiuhom ~]# cat test.sql 
use qiuhom;
set names latin1;
insert into test(id,name) values(10,'張三'),(11,'李四');
set names gbk;
insert into test(id,name) values(14,'王五'),(15,'cbdf');
set names utf8;
insert into test(id,name) values(16,'小明'),(17,'小紅');
[root@qiuhom ~]# mysql -uroot -padmin -e "use qiuhom;select * from test;"        
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
[root@qiuhom ~]# mysql -uroot -padmin -e "source test.sql"                       
[root@qiuhom ~]# mysql -uroot -padmin -e "use qiuhom;select * from test;"
+----+----------------+
| id | name           |
+----+----------------+
|  1 | a              |
|  2 | b              |
|  3 | c              |
| 10 | å¼ ä¸‰         |
| 11 | æŽå››         |
| 14 | 鐜嬩簲         |
| 15 | cbdf           |
| 16 | 小明           |
| 17 | 小紅           |
+----+----------------+

作者：Linux-1874

出處：https://www.cnblogs.com/qiuhom-1874/

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利.

MySQL資料庫備份與恢複

1.mysqldump 邏輯備份mysql資料庫。

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述