MySQL （mysqldump）

Linux系统环境

[root@mysql ~]# cat /etc/redhat-release                     #==》系统版本
CentOS release 6.7 (Final)
[root@mysql ~]# uname –r                                    #==》内核版本
2.6.32-573.el6.x86_64
[root@mysql ~]# uname -m                                #==》系统架构
x86_64
[root@mysql ~]# echo $LANG                              #==》系统字符集
en_US.UTF-8
[root@mysql ~]# mysql –V                                    #==》MySQL版本
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

MySQL配置文件
提示：/application/mysql是MySQL程序目录

#==》MySQL启动脚本，一般复制到/etc/init.d/mysqld
/application/mysql/support-files/mysql.server   
#==》MySQL主配置文件，一 般复制到/etc/my.cnf
/application/mysql/support-files/my-small.cnf /etc/my.cnf   
#==》MySQL所有二进制命令存放目录，可复制到/usr/local/sbin目录下或者添加环境变量
/application/mysql/bin/
#==》MySQL错误日志
/application/mysql/data/ MySQL01.err
#==》MySQL默认端口 3306
[root@mysql ~]# netstat -tlunp | grep 3306
tcp 0      0 0.0.0.0:3306        0.0.0.0:*        LISTEN      4780/mysqld
#==》MySQL套接字文件sock
/application/mysql-5.5.32/tmp/mysql.sock

mysqldump简介

mysqldump命令是MySQL数据库自带的一个备份命令，它支持数据库全备也可以指定库进行备份，它备份的文件以文件形式备份并且文件内容都是SQL语句。

mysqldump语法格式
mysqldump  [options] –u 用户名 -p 密码 数据库名  >   备份的文件

mysqldump [options]参数
1、-A , --all-databases      #==》备份所有数据库
2、  -B, --databases     #==》表示指定备份多个库，同时执行了use db和create databases db命令
3、--compact #==》去掉输出注释，适合调试输出使用，生产不可用
4、-F , --flush-logs     #==》刷新binlog日志
5、-d                    #==》只备份表结构
6、-t                    #==》只备份表数据
7、-l        #==》指定表上锁，让用户不能访问写入数据库指定表的数据
8、-x , --lock-all-tables        #==》所有表上锁
9、--master-data  #==》增加binlog日志文件名及对应的位置记录点
（1）、--master-data=1   #==》不注释位置记录点信息，从库会使用到
（2）、--master-data=2 #==》注释位置记录点信息
10、--single-transaction  #==》应答innodb事务数据库备份
11、--events     #==》转储事件，把events警告事件取消显示
12、--flush-privileges  #==》刷新数据库更新

MySQL全备数据命令（推荐）

#==》适合MyISAM引擎
mysqldump -uroot -p'123456' --all-databases --flush-privileges  –-lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > /opt/mysql_bak.sql
#==》适合InnoDB引擎
mysqldump -uroot -p'123456' --all-databases --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > /opt/mysql_bak.sql

一、mysqldump备份单个数据库（不加任何参数）
标注：如果出现乱码，需要确认字符集是否统一（包括ssh远程工具设置成utf8字符集）

mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client          | utf8                                      |
| character_set_connection      | utf8                                      |
| character_set_database        | utf8                                      |
| character_set_filesystem      | binary                                    |
| character_set_results         | utf8                                      |
| character_set_server          | utf8                                      |
| character_set_system          | utf8                                      |
| character_sets_dir            | /application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.01 sec)
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from testdb.student;"
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  2 | oldboy   |
|  3 | 张三     |
|  4 | 李四     |
+----+----------+
#==》MySQL备份操作
[root@mysql ~]# mysqldump -uroot -p'123456' testdb > /opt/mysql_bak.sql
[root@mysql ~]# ls -l /opt/mysql_bak.sql 
-rw-r--r-- 1 root root 1876 Aug  1 09:59 /opt/mysql_bak.sql
[root@mysql ~]# grep -Ev "#|\*|--|^$" /opt/mysql_bak.sql 
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(6) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'xiaoming'),(2,'oldboy'),(3,'张三'),(4,'李四');
UNLOCK TABLES;
[root@mysql ~]# mysql -uroot -p'123456' -e "drop table testdb.student;"
[root@mysql ~]# mysql -uroot -p'123456' -e "use testdb;show tables;"
#==》MySQL备份操作，必须指定testdb库名并且testdb库要在MySQL数据库存在
[root@mysql ~]# mysql -uroot -p'123456' testdb < /opt/mysql_bak.sql
[root@mysql ~]# mysql -uroot -p'123456' -e "use testdb;show tables;select * from student;"
+------------------+
| Tables_in_testdb |
+------------------+
| student          |
+------------------+
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  2 | oldboy   |
|  3 | 张三     |
|  4 | 李四     |
+----+----------+

二、mysqldump指定备份多个库（-B参数）

[root@mysql ~]# mysql -uroot -p'123456' -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb01           |
+--------------------+
#==》备份多个数据库
[root@mysql ~]# mysqldump -uroot -p'123456' -B testdb testdb01 > /opt/mysql_bak_B.bak
[root@mysql ~]# ls -l /opt/mysql_bak_B.bak 
-rw-r--r-- 1 root root 2168 Aug  1 11:00 /opt/mysql_bak_B.bak
#==》比较加-B参数与不加-B参数的区别
[root@mysql ~]# diff /opt/mysql_bak.sql /opt/mysql_bak_B.bak
18a19,26
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */;
> 
> USE `testdb`;
> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `testdb01` /*!40100 DEFAULT CHARACTER SET utf8 */;
> 
> USE `testdb01`;
[root@mysql ~]# mysql -uroot -p'123456' -e "drop database testdb;"
[root@mysql ~]# mysql -uroot -p'123456' -e "drop database testdb01;"
[root@mysql ~]# mysql -uroot -p'123456' -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@mysql ~]# mysql -uroot -p'123456' < /opt/mysql_bak_B.bak  #==》恢复数据库
[root@mysql ~]# mysql -uroot -p'123456' -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb01           |
+--------------------+
[root@mysql ~]# mysql -uroot -p'123456' -e "select * from testdb.student"
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  2 | oldboy   |
|  3 | 张三     |
|  4 | 李四     |
+----+----------+

三、mysqldump压缩备份

#==》未压缩备份
[root@mysql ~]# mysqldump -uroot -p'123456' -B testdb > /opt/mysql_bak.sql
#==》压缩备份
[root@mysql ~]# mysqldump -uroot -p'123456' -B testdb | gzip > /opt/mysql_bak_B.sql.gz
[root@mysql ~]# ls -lrth /opt/
-rw-r--r--  1 root root 2.0K Aug  1 14:01 mysql_bak.sql
-rw-r--r--  1 root root  776 Aug  1 14:01 mysql_bak_B.sql.gz
[root@mysql ~]# mysql -uroot -p'123456' -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb01           |
+--------------------+
#==》压缩备份恢复
[root@mysql ~]# gunzip < /opt/mysql_bak_B.sql.gz | mysql -uroot -p'123456'
[root@mysql ~]# mysql -uroot -p'123456' -e "show databases;select * from testdb.student;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb01           |
+--------------------+
+----+----------+
| id | name     |
+----+----------+
|  1 | xiaoming |
|  2 | oldboy   |
|  3 | 张三     |
|  4 | 李四     |
+----+----------+

四、mysqldump分库压缩备份与恢复(for语句实现)

1、分库压缩备份
[root@mysql ~]# mkdir -p /server/scripts
[root@mysql ~]# vim /server/scripts/MySQL_backup.sh
#!/bin/bash
#Backup MySQL Databases
BakName=$(mysql -uroot -p'123456' -e "show databases;" | grep -Evi "schema|Database")
[ -d /opt ] || mkdir -p /opt
for dbname in $BakName
do
        mysqldump -uroot -p'123456' --events -B $dbname | gzip > /opt/${dbname}_backup.sql.gz
done 
[root@mysql ~]# chmod 600 /server/scripts/MySQL_backup.sh
[root@mysql ~]# ls -l /server/scripts/MySQL_bak.sh 
-rw------- 1 root root 301 Aug  1 16:46 /server/scripts/MySQL_backup.sh
[[root@mysql ~]# sh /server/scripts/MySQL_recover.sh 
[root@mysql ~]# ls -l /opt/
total 152
-rw-r--r-- 1 root root 144311 Aug  1 17:18 mysql_backup.sql.gz
-rw-r--r-- 1 root root    533 Aug  1 17:18 testdb01_backup.sql.gz
-rw-r--r-- 1 root root    794 Aug  1 17:18 testdb_backup.sql.gz

2、压缩分库恢复（for语句实现）
[root@mysql ~]# vim /server/scripts/MySQL_recover.sh 
#!/bin/bash
#Recover MySQL Databases
BakName=$(ls /opt/*.gz | xargs -n 1 | awk -F "[/_]+" '{print $3}')

for dbname in $BakName
do
        gunzip /opt/${dbname}_backup.sql.gz
        mysql -uroot -p'123456' < /opt/${dbname}_backup.sql
done
[root@mysql ~]# chmod 600 /server/scripts/MySQL_recover.sh 
[root@mysql ~]# ls -l /server/scripts/MySQL_recover.sh 
-rw------- 1 root root 235 Aug  1 17:54 /server/scripts/MySQL_recover.sh
[root@mysql ~]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb01           |
+--------------------+
[root@mysql ~]# mysql -uroot -p123456 -e "drop database testdb;"
[root@mysql ~]# mysql -uroot -p123456 -e "drop database testdb01;"
[root@mysql ~]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@mysql ~]# sh /server/scripts/MySQL_recover.sh 
[root@mysql ~]# mysql -uroot -p123456 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb01           |
+--------------------+

五、mysqldump备份表结构

[root@mysql ~]# mysqldump -uroot -p'123456' -d testdb > /opt/testdb_bak.sql
[root@mysql ~]# grep -Ev "#|\*|--|^$" /opt/testdb_bak.sql 
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(6) NOT NULL,
  `name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

六、mysqldump备份表数据

[root@mysql ~]# mysqldump -uroot -p'123456' -t testdb > /opt/testdb_bak_data.sql
[root@mysql ~]# grep -Ev "#|\*|--|^$" /opt/testdb_bak_data.sql 
LOCK TABLES `student` WRITE;
INSERT INTO `student` VALUES (1,'xiaoming'),(2,'oldboy'),(3,'张三'),(4,'李四');
UNLOCK TABLES;

七、刷新binlog日志（增量备份日志）

[root@mysql ~]# vim /etc/my.cnf
log-bin=mysqlbin_log        #==》开启增量日志
[root@mysql ~]# /etc/init.d/mysqld restart
[root@mysql ~]# ls -l /application/mysql/data/
total 28756
-rw-rw---- 1 mysql mysql 18874368 Aug  1 19:01 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Aug  1 19:01 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jul 22 20:16 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug  1 17:57 mysql
-rw-rw---- 1 mysql mysql      107 Aug  1 19:01 mysqlbin_log.000001
-rw-rw---- 1 mysql mysql       22 Aug  1 19:01 mysqlbin_log.index
-rw-r----- 1 mysql mysql    51191 Aug  1 19:01 mysql.err
-rw-rw---- 1 mysql mysql        6 Aug  1 19:01 mysql.pid
drwx------ 2 mysql mysql     4096 Jul 24 15:35 performance_schema
drwx------ 2 mysql mysql     4096 Aug  1 18:05 testdb
drwx------ 2 mysql mysql     4096 Aug  1 17:57 testdb01
[root@mysql ~]# mysqldump -uroot -p'123456' -A -B -F --events | gzip > /opt/mysql_backup.sql
[root@mysql ~]# ls -l /application/mysql/data/
total 28768
-rw-rw---- 1 mysql mysql 18874368 Aug  1 19:03 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Aug  1 19:03 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jul 22 20:16 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug  1 17:57 mysql
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000001
#==》每执行一次刷新binlog，会产生新的binlog日志，以000002开始记录
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000002
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000003
-rw-rw---- 1 mysql mysql      107 Aug  1 19:03 mysqlbin_log.000004
-rw-rw---- 1 mysql mysql       88 Aug  1 19:03 mysqlbin_log.index
-rw-r----- 1 mysql mysql    51191 Aug  1 19:01 mysql.err
-rw-rw---- 1 mysql mysql        6 Aug  1 19:01 mysql.pid
drwx------ 2 mysql mysql     4096 Jul 24 15:35 performance_schema
drwx------ 2 mysql mysql     4096 Aug  1 18:05 testdb
drwx------ 2 mysql mysql     4096 Aug  1 17:57 testdb01
[root@mysql ~]# mysqldump -uroot -p'123456' -A -B --master-data=1 --events | gzip > /opt/mysql_backup_master.sql
[root@mysql ~]# ls -l /application/mysql/data/
total 28768
-rw-rw---- 1 mysql mysql 18874368 Aug  1 19:03 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Aug  1 19:03 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Jul 22 20:16 ib_logfile1
drwx------ 2 mysql mysql     4096 Aug  1 17:57 mysql
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000001
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000002
-rw-rw---- 1 mysql mysql      153 Aug  1 19:03 mysqlbin_log.000003
#==》master-data=1会以mysqlbin_log.000004最后一个增量日志开始记录
-rw-rw---- 1 mysql mysql      107 Aug  1 19:03 mysqlbin_log.000004
-rw-rw---- 1 mysql mysql       88 Aug  1 19:03 mysqlbin_log.index
-rw-r----- 1 mysql mysql    51191 Aug  1 19:01 mysql.err
-rw-rw---- 1 mysql mysql        6 Aug  1 19:01 mysql.pid
drwx------ 2 mysql mysql     4096 Jul 24 15:35 performance_schema
drwx------ 2 mysql mysql     4096 Aug  1 18:05 testdb
drwx------ 2 mysql mysql     4096 Aug  1 17:57 testdb01

八、使用mysql命令或source命令恢复数据库

1、使用source 命令恢复数据库
mysql> system ls -l /opt/mysql_bak.sql
-rw-r--r-- 1 root root 529794 Aug  1 19:29 /opt/mysql_bak.sql
mysql> system ls -l /opt/mysql_bak.sql
2、使用mysql命令恢复数据库
[root@mysql ~]# mysql -uroot -p'123456' < /opt/mysql_bak.sql

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 158,560评论 4赞 361
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,104评论 1赞 291
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,297评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 43,869评论 0赞 204
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,275评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,563评论 1赞 216
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,833评论 2赞 312
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,543评论 0赞 197
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,245评论 1赞 241
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,512评论 2赞 244
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,011评论 1赞 258
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,359评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,006评论 3赞 235
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,062评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,825评论 0赞 194
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,590评论 2赞 273
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,501评论 2赞 268

MySQL （mysqldump）

推荐阅读更多精彩内容