目录
1 备份单个数据库
mysql数据库自带列一个很好用的备份命令,就是mysqldump
基本使用语法:mysqldump -u 用户名 -p 数据库名 > 备份的文件名
1.1 备份oldboy数据库
mysql> select * from test;
+----+-----------+
| id | name |
+----+-----------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | oldman |
| 4 | oldwenmon |
+----+-----------+
4 rows in set (0.01 sec)
# 备份数据
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy >/opt/mysql_$(date +%F)_bak.sql
[root@db02 ~]# egrep -v "#|\*|--|^$" /opt/mysql_2018-06-05_bak.sql
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
LOCK TABLES `student` WRITE;
UNLOCK TABLES;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'oldman'),(4,'oldwenmon');
UNLOCK TABLES;
可以用vimdiff
来对比备份数据库文件加-B
和不加的区别,最后可以得出结论,加-B参数的作用是增加创建数据库和连接数据库的语句。所以最好以后备份用-B参数。加完会比不加多出来下面的语句:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100
USE `oldboy`;
1.2 优化备份文件大小减少输出注释(debug调试)
# 利用mysqldump的--compact参数优化备份结果(不建议用在生产环境,适合调试)
# --compact参数去掉注释信息,使得显示信息更少
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy --compact
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET gbk */;
USE `oldboy`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'oldman'),(4,'oldwenmon');
# 参数说明:该选项使得输出的内容更简洁,不包括默认选项中各种注释。下边选项没有(故不建议在生产环境中操作)
--skip-add-drop-table --no-set-names --skip-disable-keys --skip-add-locks
1.3压缩备份(推荐)
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy --compact|gzip >/opt/mysql_$(date +%F)_bak.sql.gz
[root@db02 ~]# cd /opt/
[root@db02 opt]# ll
-rw-r--r-- 1 root root 2713 Jun 5 16:14 mysql_2018-06-05_bak.sql
-rw-r--r-- 1 root root 370 Jun 5 16:32 mysql_2018-06-05_bak.sql.gz
# 压缩后的文件大小将近没压缩的3倍
1.4 备份单个数据库小结
1、备份数据使用-B参数,会在备份数据中增加建库及use库的语句。
2、备份数据使用-B参数,后面可以直接接多个库名
。
3、用gzip对备份的数据压缩,压缩后的文件大小几乎是没压缩之前文件大小的1/3
4、debug时可以用--compact
减少输出,但不用于生产环境中
5、指定字符集备份用–default-character-set=latin1(一般不用)
2 mysqldump备份
2.1 mysqldump工作原理
利用mysqldump命令备份数据的过程,实际上就是把数据从mysql库里以
逻辑的sql语句
的形式直接输出或者生成备份的文件的过程。
2.2 多库备份
多库备份用-B参数都将被作为数据库名。该参数用于导出若干个数据库,在备份结果中会加入CREATE DATABASE ‘db_name’和USE db_name;
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy oldboy2 >/opt/bak_$(date +%F)_test.sql
2.3 分库备份
分库备份实际上就是执行一个备份语句备份一个库,如果数据库中有多个库,就执行所条相同的备份单个库的备份语句就可以备份多个库了,注意每个库都可以用对应备份的库名作为库名,结尾加.sql
。命令如下:
# 每条备份命令大致如下
mysqldump -uroot -p123456 -B oldboy ...
mysqldump -uroot -p123456 -B oldboy2 ...
# 由于一条一条写如此之麻烦,故可以写脚本直接备份
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"| egrep -v "Database|_schema|mysql"|sed -r 's#^(.*)#mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B \1|gzip >/opt/\1_$(date +%F).sql.gz#g'
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy|gzip >/opt/oldboy_$(date +%F).sql.gz
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy2|gzip >/opt/oldboy2_$(date +%F).sql.gz
mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B rsq_utf8|gzip >/opt/rsq_utf8_$(date +%F).sql.gz
# 交给bash执行
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"| egrep -v "Database|_schema|mysql"|sed -r 's#^(.*)#mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B \1|gzip >/opt/\1_$(date +%F).sql.gz#g'|bash
[root@db02 ~]# ll -rt /opt/|tail -3
-rw-r--r-- 1 root root 871 Jun 5 17:21 oldboy_2018-06-05.sql.gz
-rw-r--r-- 1 root root 775 Jun 5 17:21 oldboy2_2018-06-05.sql.gz
-rw-r--r-- 1 root root 783 Jun 5 17:21 rsq_utf8_2018-06-05.sql.gz
分库备份的意义:
有时候一个企业的数据库里会有多个库,例如(www,bbs,blog),但是出问题时候的很可能是某一个库,如果在备份时把所有的库都备份成了一个数据文件的话,恢复某一个库的数据时就比较麻烦了。
2.4 单表备份
不加-B参数的时候,先写库名 再写表名即可
# 单表及双表备份
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "use oldboy;show tables;"
+------------------+
| Tables_in_oldboy |
+------------------+
| student |
| test |
+------------------+
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy student >/tmp/oldboy_student.$(date +%F).sql
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy student test >/tmp/oldboy_student_test.$(date +%F).sql
2.5 分表备份
只需要写个双重for循环语句,先把数据库遍历出来,再把某个表取出来
分库分表备份视频:
http://edu.51cto.com/course/course_id-808.html
3 mysqldump参数
3.1 表结构备份(-d)
mysqldump -d参数即可
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B oldboy -d --compact
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `oldboy` /*!40100 DEFAULT CHARACTER SET gbk */;
USE `oldboy`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
`age` tinyint(2) NOT NULL DEFAULT '0',
`dept` varchar(16) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
3.2 表数据备份(-t)
-t参数
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -t oldboy test --compact
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'oldman'),(4,'oldwenmon');
3.3 表结构和数据分离(-T)
–tab或-T
# 直接运行会报错,需要指定路径
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test --compact --tab=/opt/
mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
#
mysql> show variables like '%secure%';
+------------------+-------------+
| Variable_name | Value |
+------------------+-------------+
| secure_auth | OFF |
| secure_file_priv | /data/3306/ |
+------------------+-------------+
2 rows in set (0.00 sec)
# 备份到指定的目录下
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test --compact --tab=/data/3306/
[root@db02 ~]# ll /data/3306/ -rt |tail -2
-rw-r--r-- 1 root root 330 Jun 5 17:49 test.sql
-rw-rw-rw- 1 mysql mysql 40 Jun 5 17:49 test.txt
[root@db02 ~]# cat /data/3306/test.sql
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
[root@db02 ~]# cat /data/3306/test.txt
1 oldboy
2 oldgirl
3 oldman
4 oldwenmon
3.4 全库备份(-A)
-A参数
3.5 总结
1、-B备份多个库(并添加create和use库的语句)
2、-d只备份库表结构
3、-t只备份数据(sql语句形式)
4、-T(–tab)分离库表和数据成不同的文件,数据是文本,非SQL语句
5、-A全库备份
6、-x 锁表 –lock-all-tables
7、-l只读锁表
8、-q不做缓冲查询,直接导到标准输出,更快
4 刷新binlog的参数
binlog是什么:记录数据库更新的SQL语句,二进制文件
mysqldump用于定时对某一时刻
的数据的全备,例如:00点进行别分bak.sql.gz。
增量备份:当有数据写入到数据库时,还会同时把更新的SQL语句写入到对应的文件里,这个文件就叫做binlog文件。
10点丢失数据需要恢复数据:
1、00点时刻备份的bak.sql.gz数据还原到数据库,这个时候数据恢复到了00点。
2、00点-10:00数据,就要从binlog里回复
binlog文件生效需要一个参数:log-bin
[root@db02 ~]# grep log-bin /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
[root@db02 ~]# ll /data/3306/mysql-bin.00000*
-rw-rw---- 1 mysql mysql 1520 May 30 17:00 /data/3306/mysql-bin.000001
-rw-rw---- 1 mysql mysql 126 May 30 17:00 /data/3306/mysql-bin.000002
-rw-rw---- 1 mysql mysql 126 May 31 09:34 /data/3306/mysql-bin.000003
-rw-rw---- 1 mysql mysql 126 May 31 09:38 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql 564055 Jun 3 17:49 /data/3306/mysql-bin.000005
-rw-rw---- 1 mysql mysql 5273 Jun 5 15:52 /data/3306/mysql-bin.000006
[root@db02 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy test --compact -F
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=gbk;
/*!40101 SET character_set_client = @saved_cs_client */;
INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'oldman'),(4,'oldwenmon');
[root@db02 ~]# ll /data/3306/mysql-bin.00000*
-rw-rw---- 1 mysql mysql 1520 May 30 17:00 /data/3306/mysql-bin.000001
-rw-rw---- 1 mysql mysql 126 May 30 17:00 /data/3306/mysql-bin.000002
-rw-rw---- 1 mysql mysql 126 May 31 09:34 /data/3306/mysql-bin.000003
-rw-rw---- 1 mysql mysql 126 May 31 09:38 /data/3306/mysql-bin.000004
-rw-rw---- 1 mysql mysql 564055 Jun 3 17:49 /data/3306/mysql-bin.000005
-rw-rw---- 1 mysql mysql 5316 Jun 5 19:06 /data/3306/mysql-bin.000006
-rw-rw---- 1 mysql mysql 107 Jun 5 19:06 /data/3306/mysql-bin.000007
binlog日志切割:确定全备和增量的临界点
-F刷新binlog日志,生成新日志文件,将来增量恢复从这个新日志文件开始。
--master-data
在备份语句里添加CHANGE MASTER
语句及binlog文件及位置点信息
值=1,为可执行的CHANGE MASTER
语句
值=2,注释的--CHANGE MASTER
语句
[root@db02 3306]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy --master-data=1
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
[root@db02 3306]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock oldboy --master-data=2
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
5 生产场景不同引擎mysqldump备份命令
myisam引擎企业生产北非命令(适合所有引擎和混合引擎)
mysqldump -uroot -p123456 -A -B -F -R --master-data=2 -x -events|gzip >/opt/all.sql.gz
提示:-F也可以不用,与--master-data有些重复
innodb引擎企业生产备份命令:推荐使用的
mysqldump -uroot -p123456 -A -B -F -R --master-data=2 --events --single-transaction|gzip >/opt/all.sql.gz
数据库备份额外补充:
(一) mysqldump逻辑备份说明
缺点:效率不是特别高。
优点:简单、方便、可靠、迁移。
适用与数据量不是特别大的场景,打包前50G以内数据。
(二)超过50G可选方案。
1、xtrabackup 物理备份工具:全备和增量。
2、物理备份方案:从库停止SQL线程,打包,CP。
(三)什么时候会使用备份的数据?
1、恢复数据到测试库的时候。
2、人为通过SQL语句将数据删除的时候。
3、做主从复制的时候。
6 恢复MySQL数据库
提示:
1、数据恢复和字符集关联很大,如果字符集不正确会导致恢复的数据乱码
2、mysql命令以及source命令恢复数据库的原理就是把文件的SQL语句,在数据库里重新执行的过程。
6.1 利用source命令恢复数据库
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock
mysql> drop database oldboy;
Query OK, 2 rows affected (0.01 sec)
mysql> system ls /tmp/oldboy.2018-06-05.sql
/tmp/oldboy.2018-06-05.sql
mysql> source /tmp/oldboy.2018-06-05.sql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
提示
1、source数据恢复和字符集关联很大,如果字符集不正确会导致恢复的数据乱码。
2、UTF8数据库,那么恢复的文件格式需要为“UTF8没有签名”格式。
6.2 利用mysql恢复数据
mysql> drop database oldboy;
Query OK, 2 rows affected (0.01 sec)
mysql> exit;
Bye
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock </tmp/oldboy.2018-06-05.sql
[root@db02 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| performance_schema |
| rsq_utf8 |
+--------------------+
6.3 mysql恢复压缩数据库文件
1、首先先用gzip -d解压压缩包,但是这种做法会删除原文件
2、不想删除原文件要用gzip -cd mysql_bak_2018-06-05.sql.gz >mysql.sql