MySQL数据库备份与恢复

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 | 小红           |
+----+----------------+

posted @ 2018-10-06 13:50 Linux-1874 阅读(598) 评论(0) 编辑收藏举报

刷新页面返回顶部

1874

MySQL数据库备份与恢复

1.mysqldump 逻辑备份mysql数据库。

公告