MySQL备份方案
–
为什么于超老师要给大家讲mysql备份?
先看上面两张图。。
为什么要备份
运维是干什么的?
- 保护服务器数据安全
- 维护公司运维资产7*24小时运转
企业真实案件:
https://www.leiphone.com/category/sponsor/Isb7Smi17CHBTxVF.html
企业丢了数据,就等于失去了商机、客户、产品、甚至倒闭。
在各式各样的数据中,数据库的数据更是核心之核心,当然其他各式各样的如静态文件数据,也很重要,也会通过其他的备份方式来保证安全。
MySQL备份
mysqldump备份语法
Mysqldump -u用户名 -p密码 参数 数据库名 > 数据备份文件
备份kings数据库
mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B kings > /opt/kings.sql
查看备份文件信息
过滤无用信息
[root@mysql-server56 ~]# grep -Ev '#|\*|--|^$' /opt/kings.sql
USE `kings`;
DROP TABLE IF EXISTS `Heros`;
CREATE TABLE `Heros` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
LOCK TABLES `Heros` WRITE;
INSERT INTO `Heros` VALUES (1,'大虫子'),(2,'巨魔');
UNLOCK TABLES;
DROP TABLE IF EXISTS `tanks`;
CREATE TABLE `tanks` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`skills` varchar(255) NOT NULL,
`summoner_skills` enum('flush','ghost') NOT NULL DEFAULT 'flush',
`price` int(11) NOT NULL,
`introduction` varchar(255) NOT NULL,
`camp` varchar(50) DEFAULT NULL,
`pic` char(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
LOCK TABLES `tanks` WRITE;
INSERT INTO `tanks` VALUES (1,'亚瑟','圣剑裁决','flush',5888,'能抗能打,技能沉默','近战','https://img.18183.com/uploads/allimg/190924/266-1Z9241Q224.jpg'),(2,'凯','','flush',0,'',NULL,NULL),(3,'东皇太一','堕神契约','flush',0,'',NULL,NULL),(4,'吕布','魔神降临','flush',18888,'',NULL,NULL),(9,'庄周','','flush',7777,'',NULL,NULL),(10,'关羽','','flush',8888,'',NULL,NULL),(11,'钟馗','轮回吞噬','flush',9888,'',NULL,NULL);
UNLOCK TABLES;
看来备份的命令,导出的SQL语句,是相当于以后创建一个新的数据库,以及插入数据
为了恢复效率,mysqldump是把数据导出为数据插入语句
以及在插入数据的时候,lock tables锁表,数据插入结束后,解锁表
备份且压缩gzip
对于数据库有大量数据表,以及信息,导出的备份文件,最好是压缩后的,节省磁盘。
[root@mysql-server56 ~]# mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B kings|gzip > /opt/kings.sql.gz
Warning: Using a password on the command line interface can be insecure.
[root@mysql-server56 ~]# ls -lh /opt/kings*
-rw-r--r-- 1 root root 3.3K Apr 20 14:25 /opt/kings.sql
-rw-r--r-- 1 root root 1.2K Apr 20 16:30 /opt/kings.sql.gz
这里是一个没有多少数据的table,也被gzip压缩了很大空间。
mysqldump备份原理
mysqldump命令备份过程,实际上是把数据库、表,以SQL语句的形式,输出为文件的备份过程,这种方式称之为逻辑备份。
但是这种方式效率并不高,以SQL导出,在海量数据下,例如几十G的场景,备份、恢复的时间都会过长。
因此还会有其他备份方案。
备份多个库
备份多个库,如下
[root@mysql-server56 ~]# mysql -uroot -pyuchao7777 -P3307 -h127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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 |
| K8S |
| blog |
| kings |
| luffy |
| mysql |
| performance_schema |
+--------------------+
7 rows in set (0.01 sec)
# 备份
[root@mysql-server56 ~]# mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B kings K8S luffy mysql|gzip > /opt/more_db.sql.gz
Warning: Using a password on the command line interface can be insecure.
小结
-
备份命令,尽量携带
-B
参数,会让sql更加完整
-B
可以跟上多个数据库名,同时备份多个库
-
尽量结合gzip命令压缩
分库、分表备份
直接看于超老师之前的脚本开发,分库分表备份
# 其实是等于执行多次mysqldump命令
mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B kings > /opt/kings.sql
mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 -B K8S > /opt/K8S.gz
备份单个table
这里不能加上-B
参数了,这是指定数据库的作用
单独指定备份某个table
[root@mysql-server56 opt]# mysqldump -uroot -pyuchao7777 -P3307 -h127.0.0.1 kings tanks > /opt/tanks.sql
Warning