1、关于数据保存你要知道

思考：备份和冗余有什么区别？

备份： 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

冗余： 数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

2、到底要备份什么

☆ 备份什么

数据库：一堆物理文件的集合；日志文件(二进制日志)+数据文件+配置文件

① 数据文件

② 配置文件 => my.cnf

③ 日志文件（主要是二进制日志文件）

☆ 存储引擎层（MyISAM与InnoDB引擎）

存储引擎层：简单来说，就是数据的存储方式。在MySQL中，我们可以使用show engines查看当前数据库版本支持哪些引擎，常见的数据存储引擎：InnoDB、MyISAM、NDB...

常见面试题：请简述MySQL的MyISAM引擎与InnoDB引擎的区别

① MyISAM引擎：擅长数据的查询，支持全文索引

② InnoDB引擎：Supports transactions, row-level locking, and foreign keys，支持事务处理、行级锁、支持外键。

☆ 存储层（数据文件是如何进行存储的）

问题：存储引擎到底是如何保存数据文件的？

mysql> create database db_yun default charset=utf8;

当数据库创建完毕后，查看/mysql_3306/data文件夹：

db_yun文件夹中还有一个文件db.opt，存放内容为数据库的编码格式。

MyISAM引擎

mysql> use db_yun;
mysql> create table tb_user(id int, name char(1)) engine=myisam default charset=utf8;

创建完成后，查看db_yun目录信息，发现产生了3个文件

*.frm ：框架文件，定义数据表结构 *.MYI ：INDEX索引，主要用于存放索引文件 *.MYD：数据文件

InnoDB引擎

mysql> use db_yun;
mysql> create table tb_user1(id int, name char(1)) engine=innodb default charset=utf8;

创建完成后，查看db_yun目录信息，发现产生了2个文件

*.frm ：框架文件，定义数据表结构 *.ibd：索引文件+数据文件

其实InnoDB引擎不仅仅会产生以上两个文件，其在外部data目录中也会产生一个文件（确切来说不能叫做产生文件，而应该叫做共享文件）

所以由此可知，InnoDB引擎的数据备份不能简简单单的通过拷贝方式实现，必须使用专业的备份工具。

☆ 日志文件（MySQL中我们需要了解哪些日志）

error错误日志的命令规则与存放的目录：/data目录下 + 主机名称.err

更改错误日志的存放位置：

# vim /mysql_3306/my.cnf
[mysqld]
...
log_error=data数据目录/主机名称.err或mysql.err

binlog二进制日志应用场景：

用于主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同。

用于数据的恢复操作

binlog二进制日志如何开启？

默认binlog日志是关闭的，可以通过修改配置文件完成开启，如下：

# vim /mysql_3306/my.cnf
[mysqld]
...
server-id=10
log-bin=data数据目录/binlog

当我们更改了my.cnf配置文件，一定要记得重启MySQL服务器。service命令

3、备份过程须考虑的因素

• 必须制定详细的备份计划(策略)（备份频率、时间点、周期）

• 备份数据应该放在非数据库本地，并建议有多份副本

• 必须做好数据恢复的演练（每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据。）

• 根据数据应用的场合、特点选择正确的备份工具。

• 数据的一致性

• 服务的可用性

4、备份的类型

☆ 逻辑备份

• 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL）。

• 适用于中小型数据库，效率相对较低。 一般在数据库正常提供服务的前提下进行，如：mysqldump、into outfile（表的导出导入）等。

• 备份实质：就是把要备份的数据导出成.sql或.txt文件

☆ 物理备份

• 直接复制数据库文件

• 适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

• 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份）；如：tar、cp、xtrabackup（数据库可以正常提供服务）、lvm snapshot、rsync等

• 备份的实质：对数据文件 + 配置文件 + 日志文件进行拷贝操作

☆ 在线热备（数据冗余、AB复制、主从复制）

• MySQL的replication架构

• 实时在线备份

5、备份工具

㈠ 社区版安装包中的备份工具

① mysqldump(逻辑备份，只能全量备份）

1）企业版和社区版都包含

2）本质上使用SQL语句描述数据库及数据并导出

3）在MYISAM引擎上锁表，Innodb引擎上锁行

4）数据量很大时不推荐使用

② mysqlhotcopy(物理备份工具）

1）企业版和社区版都包含

2）perl写的一个脚本，本质上是使用锁表语句后再拷贝数据

3）只支持MYISAM数据引擎

㈡ 企业版安装包中的备份工具

mysqlbackup

1）在线备份

2）增量备份

3）部分备份

4）在某个特定时间的一致性状态的备份

㈢ 第三方备份工具

① XtraBackup和innobackupex(物理备份）

1）Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），是商业备份工具InnoDB Hotbackup的一个很好的替代品。

2）Xtrabackup有两个主要的工具：xtrabackup、innobackupex

a、xtrabackup只能备份InnoDB和XtraDB两种数据表，不能备份myisam类型的表。

b、innobackupex是将Xtrabackup进行封装的perl脚本，所以能同时备份处理innodb和myisam的存储引擎，但在处理myisam时需要加一个读锁。

② mydumper(逻辑备份，备份SQL语句）

6、备份方法

• 完全备份（全备）

• 增量备份（增量备份基于全量备份）

MySQL逻辑备份

1、mysqldump基本备份

本质：导出的是sql语句文件

优点：无论是什么存储引擎，都可以用mysqldump备成sql语句

缺点：速度较慢,导入时可能会出现格式不兼容的突发状况.无法直接做增量备份.

提供三种级别的备份，表级，库级和全库级

2、mysqldump基本语法

表级别备份
mysqldump [OPTIONS] database [tables]
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

准备一些要备份的数据：

mysql> create database db_yun default charset=utf8;
mysql> use db_yun;
mysql> create table tb_student(
    id int not null auto_increment,
    name varchar(20),
    age tinyint unsigned default 0,
    gender enum('male','female'),
    subject enum('ui','java','yunwei','python'),
    primary key(id)
) engine=innodb default charset=utf8;
​
mysql> insert into tb_student values (null,'刘备',33,'male','java');
mysql> insert into tb_student values (null,'关羽',32,'male','yunwei');
mysql> insert into tb_student values (null,'张飞',30,'male','python');
mysql> insert into tb_student values (null,'貂蝉',18,'female','ui');
mysql> insert into tb_student values (null,'大乔',18,'female','ui');

3、mysqldump表级备份与还原

☆ 备份

案例：把db_yun数据库中的tb_student数据表进行备份

# mkdir /tmp/sqlbak
# mysqldump db_yun tb_student > /tmp/sqlbak/tb_student.sql -p
Enter password:数据库密码

☆ 还原

# mysql 数据库名称 < /tmp/sqlbak/tb_student.sql -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> use db_yun
mysql> source .sql文件的位置

4、mysqldump库级备份与还原

☆ 备份

案例：把db_yun数据库进行备份

# mysqldump --databases db_yun > /tmp/sqlbak/db_yun.sql -p
Enter password:123

☆ 还原

# mysql < .sql文件位置 -p
Enter password:123
或
# mysql -uroot -p
Enter password:123
mysql> source .sql文件的位置

5、mysqldump全库级备份

在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志！！！

开启二进制日志

[root@bogon ~]# vim /mysql_3306/my.cnf
​
[mysqld]
...
#为MySQL实例指定一个唯一的ID号。对于部署MySQL集群环境非常重要，每个MySQL服务器实例必须有一个唯一的server-id。
server-id=10
log-bin=/mysql_3306/data/binlog
​
[root@bogon ~]# service mysql_3306 restart

mysqldump高级选项说明：

--master-data参数其他说明：

1）恢复时会执行，默认是1 2）需要RELOAD privilege并必须打开二进制文件 3）这个选项会自动打开--lock-all-tables，关闭--lock-tables

☆ 备份

前提：一定要开启二进制日志

# mysqldump --all-databases --master-data --single-transaction > /tmp/sqlbak/all.sql -p
Enter password:root

☆ 还原

# mysql < all.sql -p
Enter password:123

总结

1. mysqldump工具备份的是SQL语句，故备份不需要停服务

2. 使用备份文件恢复时，要保证数据库处于运行状态

3. 只能实现全库，指定库，表级别的某一时刻的备份，本身不能增量备份

4. 适用于中小型数据库