MySQL备份方案

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命令压缩

分库、分表备份

直接看于超老师之前的脚本开发，分库分表备份

http://book.luffycity.com/linux-book/%E8%B6%85%E5%93%A5%E5%B8%A6%E4%BD%A0%E5%AD%A6Shell/06_for%E5%BE%AA%E7%8E%AF%E5%BC%80%E5%8F%91.html#%E5%BC%80%E5%8F%91mysql%E5%88%86%E5%BA%93%E5%A4%87%E4%BB%BD%E8%84%9A%E6%9C%AC

# 其实是等于执行多次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