MySQL笔记系列七备份

当运行Mysql的时候，难免会发生各种意外，这个意外有可能是设备硬件的问题，也有可能是软件方面的操作问题而引起的数据的丢失，因此，对于数据的备份也是至关重要的。

MySQL数据备份根据备份的方法可分为：热备份；冷备份；温备份。

热备份：MySQL官方称为在线备份，即在数据库进行运行时进行备份，并且不会对运行的数据库造成影响；

冷备份：MySQL官方称为离线备份，即备份的时候需要将数据库停止运行，对相应的数据物理文件(.frm文件，共享表空间文件，独立表空间文件(*.ibd),重做日志文件，建议将配置文件my.cnf也备份)进行拷贝即可；

温备份：在数据库运行时对数据进行备份，但是对数据库会有所影响。

MySQL根据备份的文件的内容可以分为：逻辑备份；裸文件备份。

逻辑备份：备份的文件是可读的文本文件，内容为SQL语句，或表的实际数据；

裸文件备份：即数据库的物理文件。

按照备份的内容又可以分为：完全备份；增量备份；日志备份。

注意：如果进行冷备份，不光在本地机器进行备份，还要做一个远程备份，防止本地机器因为自然灾害受损导致本地备份丢失而没有多余的备份。

mysqldump备份

mysqldump语法：

C:\Users\liumin>mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

eg: 备份所有数据库

C:\Users\liumin>mysqldump -u root -p --databases mysql > C:\Users\liumin\Desktop\dbmysql2.sql

dbmysql2.sql的部分内容为：

-- MySQL dump 10.13  Distrib 8.0.11, for Win64 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version	8.0.11

/*!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 */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
...

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */;

USE `mysql`;


DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 SET character_set_client = utf8mb4 ;
CREATE TABLE `columns_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0 COMMENT='Column privileges';
/*!40101 SET character_set_client = @saved_cs_client */;
...
LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` DISABLE KEYS */;
INSERT INTO `db` VALUES ('localhost','performance_schema','mysql.session','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'),('localhost','sys','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
UNLOCK TABLES;
...

dbmysql2.sql文件的包含的内容有表结构和数据，开始和结束的注释是有助于还原工作的执行，接下来首先是创建数据库，数据表，以及各种数据操作语句。

mysqldump参数详解：

--single-transaction:该参数在备份前会执行START TRANSACTION命令，可以保证数据的一致性，该参数只对InnoDB存储引擎有效；

--lock-tables:在备份中，会锁住每个架构下的所有表，备份时只能对数据进行读取操作，一般用于MyISAM存储引擎，备份依然可以保证一致性。注意，在Mysql，因为它支持多个存储引擎，所以会有不同的存储引擎的表，如果既有MyISAM,又有InnoDB,则只能使用--lock-tables,在innoDB存储引擎中，不需要使用该参数，使用-single-tranaction参数即可；

--lock-all-tables:在备份中，锁住所有的表；

--add-drop-databases:在CREATE DATABASE前先运行DROP DATABASE，该参数需要和-all-databases或-databases选项一起使用，默认情况下，导出文件是不会有CREATE DATABASE，除非指定该参数。

--master-data[=value]:该备份转存文件主要用来建立一个slave replication。当value=1，转存文件中有CHANGE MASTER语句，当value=2,该语句被注释。默认value为空。

--events:备份事件调度器；

--routines:备份存储过程和函数；

--triggers:备份触发器；

--hex-blob:将BINARY,VARBINARY,BLOG,BIT列备份为十六进制的格式。

--where='condition'：用来导出特定条件的数据，可以用来导出一张表，SELECT..INTO OUTFILE也可以

eg 备份liud数据库中名为user100数据表中成绩大于90的记录

C:\Users\liumin>mysqldump -u root -p --single-transaction --where=score>90 liud user100 > C:\Users\liumin\Desktop\user90.sql

user90.sql文件中的操作部分为：

/*!40000 ALTER TABLE `user100` DISABLE KEYS */;
INSERT INTO `user100` VALUES (1,'李','敏励','0',67,1),(2,'赵','励诚','0',67,2),(3,'赵','田甲','0',49,3),(4,'侯','六乙','1',84,4),(5,'孙','八','1',85,5),(6,'陈','丙晶','0',68,6),(7,'李','立','0',57,7),(8,'杨','立','0',40,8),(9,'柳','景甲','0',96,9),(10,'吴','名景','0',65,10),(11,'彭','名景','1',44,11),(12,'柳','名景','1',61,12),(13,'侯','三黎','1',41,13),(14,'陈','九五','1',48,14),(15,'王','六名','1',79,15),(16,'阮','俊依','0',52,16),(17,'彭','八','1',78,17),(18,'高','敏兲','0',84,18),(19,'王','敏兲','0',50,19),(20,'周','敏兲','0',60,20),(21,'阮','敏兲','0',85,21),(22,'钱','敏兲

 SELECT...INTO OUTFILE

该语句也是备份的方法，用来导出一张表中的数据，语法为：

SELECT [COLUMN1],[COLUMN2]...
INTO
OUTFILE 'FILE_NAME'
[{FIELDS | COLUMNS}[TERMINATED BY 'string'][[OPTIONALLY] ENCLOSED BY 'char'][ESCAPED BY 'char']]
[LINES [STARTING BY 'string'] [ TERNINATED BY 'string']]
FROM TABLE WHERE ...

参数解释：

COLUMN1:表示需要备份的字段；

TERMINATED BY 'string':表示每列的分隔符；

ESCAPED BY 'char' :表示转义字符；

[OPTIONALLY] ENCLOSED BY 'char’：表示对于字符串的包含符；

FILE_NAME:表示文件的名字；

eg:

mysql> SELECT * INTO OUTFILE 'C:\User\liumin\Desktop\a.txt' from liud.user100;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

注意：这里执行该语句出现错误提示，主要是Mysql默认对导出的目录有权限设置，即在导出的时候，需要指定目录；

查看secure-file-priv变量的值为：

mysql> show global variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_file_priv         | NULL  |
+--------------------------+-------+
2 rows in set, 1 warning (0.03 sec)

使用into outfile导出时，需要导出到secure_file_priv指定的目录， 所以需要先设值secure_file_priv变量的值。

默认导出是以TAB进行列分隔的，想要设置为其他分隔符，可以使用FIELDS TERMINATED BY 'string'选项来设置。

mysqldump 恢复

恢复备份，只需要执行备份文件即可，即：

mysql -u root -p < dump.sql

也可以使用SOURCE命令来执行导出的逻辑备份文件：

source /xxx/xxx/dump.sql;

LOAD DATA INFILE：

LOAD DATA INFILE命令可以进行导入，它的语法和SELECT INTO OUTFILE完全一样，对服务器文件使用LOAD DATA INFILE，必须拥有FILE权。

二进制日志备份

二进制日志中包含对数据库的操作，在使用二进制日志备份的时候，首先需要启动它，即在配置文件中进行设置：

[mysqld]
log_bin = mysql-bin

对于InnoDB数据库，我们还需要配置一些参数来保证安全和正确的记录二进制日志，推荐配置：

[mysqld]
log_bin = mysql-bin
sync_binlog=1
innodb_support_xa=1

可以使用SHOW MASTER LOGS查看二进制日志：

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000012 |       155 |
| binlog.000013 |       155 |
| binlog.000014 |       155 |
| binlog.000015 |       178 |
| binlog.000016 |       178 |
| binlog.000017 |       199 |
| binlog.000018 |       199 |
| binlog.000019 |       155 |
+---------------+-----------

使用FLUSH LOGS来刷新mysql,生成新的二进制日志文件，我们可以备份之前的二进制日志。然后再查看二进制日志：

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.20 sec)

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000012 |       155 |
| binlog.000013 |       155 |
| binlog.000014 |       155 |
| binlog.000015 |       178 |
| binlog.000016 |       178 |
| binlog.000017 |       199 |
| binlog.000018 |       199 |
| binlog.000019 |       199 |
| binlog.000020 |       155 |
+---------------+-----------+
9 rows in set (0.00 sec)

二进制文件的恢复

使用mysqlbinlog命令即可：

C:\Users\liumin>mysqlbinlog E:\mysqlData\binlog.000019 | mysql -u root -p
Enter password: ******

恢复多个二进制日志文件：

C:\Users\liumin>mysqlbinlog E:\mysqlData\binlog.[0-20]* | mysql -u root -p

热备份

热备份即在数据库运行时，对数据进行备份，且该备份不会影响数据库的运行。热备份的软件有收费的ibbackup和开源的XtraBackup。

XtraBackup支持MySQL数据库5.0和5.1，xtrabackup命令的使用方式：

xtrabackup --backup | -- prepare [OPTIONS]

eg:完全备份：

./xtrabackup --backup

 

 

参考《MySQL技术内幕InnoDB存储引擎》 如果有时间，建议亲自阅读此书，整理如有错误，敬请指出，谢谢。