mysql 5.7 备份_MySQL-5.7 备份与恢复

一、备份分类

按介质分类：

物理备份

指通过拷贝数据库文件方式完成备份，适用于数据库很大，数据重要且需要快速恢复的数据库。

逻辑备份

指通过备份数据库的逻辑结构和数据内容的方式完成备份，适用于数据库不是很大，或需要对导出文件做一定修改，或重建此库的情况。

优缺点：

物理备份速度快于逻辑备份，因为逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式

物理备份的备份恢复粒度范围是整个数据库或单个文件，对单表是否有恢复能力取决于存储引擎(MyISAM下每个表对应独立文件，可以单独恢复；InnoDB可能使用共享数据文件)

物理备份要求在数据库关闭情况下执行，如果在运行情况下执行，要求备份期间数据库不能修改，逻辑备份需要在数据库运行状态下执行

通常逻辑备份的文件大小比物理备份大

逻辑备份不包含数据库的配置文件和日志文件内容

按状态分类：

在线备份

离线备份

按距离分类：

本地备份

远程备份

按类型分类：

全量备份

指备份中包含所有数据

增量备份

指备份中仅包含在某个指定时间段内的变化情况，需要借助二进制日志完成

二、MySQL备份方式

(1)mysqldump

mysqldump -u 用户名 -p 数据库名 数据库表 > 导出的文件名

(2)拷贝物理表生成备份

当前存储引擎下每个表都有自己独立的数据文件时可以使用这种方式。如果当前数据库是运行状态，则需要对此表加上一个只读锁，防止备份期间的修改操作。

对InnoDB存储引擎的表不太支持。

(3)select...into outfile

通过select * into outfile ‘file_name’ from tbl_name生成在服务器上的文件

通过mysqldump命令加--tab参数生成文件

只会生成表数据，不会生成表结构

(4)增量备份

将MySQL实例设置开启log-bin参数，备份增量生成的二进制日志到指定的备份地

(5)Xtrabackup

支持全量和增量备份

三、MySQL备份演示

(1)物理备份

CREATE TABLE `students_myisam` (

`sid` int(11) NOT NULL,

`sname` varchar(64) DEFAULT NULL,

`gender` int(11) DEFAULT NULL,

`dept_id` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`),

KEY `idx_sname` (`sname`),

KEY `idx_gender` (`gender`),

KEY `dept_id` (`dept_id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

mysql> insert into students_myisam values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

开始备份

[root@localhost course]# pwd

/data1/mysql/data/course

[root@localhost course]# ll students_my*

-rw-r----- 1 mysql mysql 8660 Mar 5 11:11 students_myisam.frm

-rw-r----- 1 mysql mysql 60 Mar 5 11:12 students_myisam.MYD

-rw-r----- 1 mysql mysql 5120 Mar 5 11:12 students_myisam.MYI

把这个表相关的三个文件拷贝到另外的数据库实例对应的数据库目录下(记得需要修改文件权限)

[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam.* ./

[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam.*

mysql> select * from students_myisam;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

| 1 | a | 1 | 1 |

| 2 | b | 2 | 2 |

| 3 | c | 3 | 3 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

对于InnoDB表来说，即使设置了innodb_file_per_table=on时，直接拷贝也不行

CREATE TABLE `students_myisam2` (

`sid` int(11) NOT NULL,

`sname` varchar(64) DEFAULT NULL,

`gender` int(11) DEFAULT NULL,

`dept_id` int(11) DEFAULT NULL,

PRIMARY KEY (`sid`),

KEY `idx_sname` (`sname`),

KEY `idx_gender` (`gender`),

KEY `dept_id` (`dept_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> insert into students_myisam2 values(1,'a',1,1),(2,'b',2,2),(3,'c',3,3);

Query OK, 3 rows affected (0.03 sec)

Records: 3 Duplicates: 0 Warnings: 0

开始备份

[root@codis-178 cmdb_v2]# cp /home/xiaoda/students_myisam2.* ./

[root@codis-178 cmdb_v2]# chown mysql:mysql students_myisam2.*

mysql> select * from students_myisam2;

ERROR 1146 (42S02): Table 'cmdb_v2.students_myisam2' doesn't exist

日志报错：

180305 11:23:53 [ERROR] Cannot find or open table cmdb_v2/students_myisam2 from

the internal data dictionary of InnoDB though the .frm file for the

table exists. Maybe you have deleted and recreated InnoDB data

files but have forgotten to delete the corresponding .frm files

of InnoDB tables, or you have moved .frm files to another database?

or, the table contains indexes that this version of the engine

doesn't support.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

how you can resolve the problem.

所以对于InnoDB来说可以通过拷贝整个data目录方式来完成备份和恢复。

(2)Mysqldump

用来生成MySQL的逻辑备份文件，其文件内容就是构成数据库对象和数据内容的可重复执行的SQL语句。

mysqldump [OPTIONS] database [tables]

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump [OPTIONS] --all-databases [OPTIONS]

options的关键参数：

-h, --host=name 要导出的目标数据库所在主机，默认是localhost

-u, --user=name 链接目标数据库的数据库用户名

-p, --password[=name] 链接目标数据库的数据库密码

-P, --port=# 链接目标数据库的端口

--add-drop-database 在使用--databases或--all-databases参数时在每个create database命令前都加上drop database命令

--add-drop-table 在每个create table命令前加上drop table命令

--default-character-set=name 指定默认的字符集，默认是UTF8

--replace 使用该