近期互联网的大事件无外乎微盟员工删库，造成了微盟巨大的损失。那么，同是程序员的你，是否想过删库怎么办呢？如何应对这种情况呢？

删库之后主要的还是看如何还原，之前是否有备份，今天我也分享一下2小时教你玩转企业级数据库备份与还原。

 

MySQL备份与还原（重要）

一、MySQL备份概述

1、关于备份（你要知道的）

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

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

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

 

2、备份什么

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

① 日志文件（二进制日志）

② 数据文件/usr/data/mysql/data文件夹，里面存放的就是数据文件

③ 配置文件my.cnf

 

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

必须制定详细的备份计划(策略)（备份频率、时间点、周期） 备份数据应该放在非数据库本地，并建议有多份副本

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

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

服务的可用性

 

4、备份类型

 

☆ 逻辑备份

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

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

最终结果：把MySQL中数据导出到一个.sql或.txt的文档中。

 

☆ 物理备份

直接复制数据库文件

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

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

最终结果：把/usr/local/mysql/data数据库数据目录进行备份

 

☆ 在线热备（冗余）

MySQL的replication架构，如M-S|M-S-S|M-M-S等实时在线备份

最终结果：主备模式、集群模式服务器

 

二、安装MySQL数据库

 

1、配置本地yum源

[Local] name=Local Yum

baseurl=file:///mnt/ gpgcheck=0 enabled=1

注意：要把光盘镜像挂载到/mnt目录，mount /dev/sr0 /mnt

2、软件包下载

mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz

说明：通用linux下的二进制包，已编译好，只需放到相应的安装目录里即可

3、默认选项

套接字文件，负责客户端与服务器

mysql安装目录

mysql数据目录

默认安装路径：/usr/local/mysql

默认数据目录：/usr/local/mysql/data 默认端口：3306

默认socket文件存放路径：/tmp/mysql.sock

端进行网络连接

4、安装步骤

参考官方文档：MySQL-glibc安装手册需求：

1. MySQL的安装目录为：/usr/local/mysql

2. MySQL的数据目录为: /usr/local/mysql/data

① 创建用户和安装目录

[root@db01 ~]# useradd mysql -r -s /sbin/nologin [root@db01 ~]# id mysql

uid=997(mysql) gid=995(mysql) 组 =995(mysql)

[root@db01 ~]# mkdir /usr/local/mysql

② 拷贝程序到安装目录

1）下载glibc的软件包

[root@db01 ~]# ls /soft/installer/mysql-5.6.35-linux-glibc2.5- x86_64.tar.gz

/soft/installer/mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz 2）解压glib的软件包

[root@db01 ~]# cd /soft/installer/

[root@db01 installer]# tar -xf mysql-5.6.35-linux-glibc2.5- x86_64.tar.gz

3）进入到解压目录里拷贝数据文件到安装目录

[root@db01 installer]# cd mysql-5.6.35-linux-glibc2.5-x86_64/ [root@db01 mysql-5.6.35-linux-glibc2.5-x86_64]# cp -a ./*

/usr/local/mysql/

说明：将数据文件拷贝到mysql的安装路径后，说明mysql数据库已经安装完成！！

③ 初始化数据库（重点）

ib_logfile1 mysql performance_schema

ib_logfile0

ibdata1

test

3）初始化数据库

注意：确保当前系统没有/etc/my.cnf文件

# rm -f /etc/my.cnf

[root@db01 mysql]# scripts/mysql_install_db --user=mysql [root@db01 mysql]# ls /usr/local/mysql/data/

20 18:54 /usr/local/mysql/

13 mysql mysql 191 1月

mysql]# chown -R mysql.mysql /usr/local/mysql/

mysql]# ll -d /usr/local/mysql/

[root@db01 [root@db01

drwxr-xr-x

2）更改mysql安装目录的权限

1）进入到mysql的安装目录里

[root@db01 mysql-5.6.35-linux-glibc2.5-x86_64]# cd

/usr/local/mysql/ [root@db01 mysql]#

④ 配置my.cnf文件

注意：要手工在/usr/local/mysql目录下创建mysql.err文件并设置权限

(mysql.mysql)

log-error = /usr/local/mysql/mysql.err 错误日志

character_set_server = utf8 编码格式，默认lati1，更改为

utf8

安装目录

数据目录=安装目录/data 端口号

mysql服务器编号，必须唯一

socket套接字文件

[mysqld]

basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306

server_id = 10

socket = /tmp/mysql.sock

⑤ 启动数据库

users:(("mysqld",pid=15921,fd=10))

:::*

[root@db01 mysql]# cp support-files/mysql.server

/etc/init.d/mysql35

[root@db01 mysql]# service mysql35 start Starting MySQL.Logging to '/usr/local/mysql/data/db01.itcast.cn.err'.

SUCCESS!

[root@db01 mysql]# ss -naltp|grep mysqld LISTEN 0 80 :::3306

⑤ 后续配置(任选其一)

Disallow root login remotely? [Y/n] n 是否禁止root从远程登录;生产禁止，测试允许

...

Remove test database and access to it? [Y/n] y 是否移除test库

...

Reload privilege tables now? [Y/n] y 是否刷新权限表

... Success!

n 是否更改管理员root密码

是否移除匿名用户

(enter for none): 输入当前密码

moving on...

Enter current password for root OK, successfully used password,

...

Change the root password? [Y/n]

...

Remove anonymous users? [Y/n] y

... Success!

...

2）安全初始化数据库

[root@db01 mysql]# ./bin/mysql_secure_installation

...

1）更改数据库管理员root密码

[root@db01 mysql]# ./bin/mysqladmin -u root password '123'

Warning: Using a password on the command line interface can be insecure.

⑥ 测试登录

[root@db01 mysql]# mysql -u root -p

-bash: mysql: 未找到命令说明：

-u 指定连接用户

-p 指定用户密码

原因：环境变量找不到

解决：修改/etc/profile文件追加以下内容

export PATH=$PATH:/usr/local/mysql/bin

[root@db01 mysql]# source /etc/profile

[root@db01 mysql]# mysql -u root -p Enter password:

...

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

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

3 rows in set (0.00 sec)

 

 

三、MySQL的基本操作

 

1、MySQL结构图

 

 

 

2、创建数据库

创建db1库

create database db1;

创建db1库并指定默认字符集

create database db1 default charset gbk;

如果存在不报错(if not exists)

create database if not exists db1 default character set utf8;

说明：不能创建相同名字的数据库！

3、查看数据库

查看所有数据库：

mysql> show databases;

4、创建数据表

第一步：选择数据库

选择数据库

mysql> use db1;

第二步：创建数据表

create table 表名 (字段1,字段2，...)

create table 表名 (字段1 数据类型(字符长度)，字段2，...)

create table 表名 (字段1 数据类型(字符长度) 约束条件，字段2，...)

案例：创建数据包

create table t1(

id int not null auto_increment primary key, name varchar(40) not null,

age tinyint unsigned default 0, mobile char(11),

remark varchar(255)

);

备注：

not null，当前字段插入或更新数据时不能为空auto_increment，自动增长，插入时设置为null即可primary key，主键，要求此字段的每个值必须唯一unsigned，无符号型，说白了就是必须为0或正整数default，默认值，不写默认就是这个值

5、查看数据表

查看所有表

mysql> show tables;

 

5、添加数据

以下是往表里插入数据的几种不同方法

insert into 表名 values(值1,值2),(值1,值2)

insert into t1 values(1,'zhang'),(2,'wang');

insert into 表名 (指定字段1,指定字段2) values(字段1值，字段2值)； insert into t2 (id,name) values(3,'li');

6、查询数据

查询表里所有记录

select * from t1;

7、更新数据

update 表名 set 字段1=新值,字段2=新值,... where条件;

案例：

update t1 set name='harry' where id=1; update t1 set name='jack',id=33 where id=3;

update t1 set id=222,name='wanger' where id=2;

8、删除数据

根据条件删除

delete from t1 where id>3;

四、MySQL的物理备份

1、xtrabackup备份介绍

 

① xtrabackup优缺点

 

优点：

1. 备份过程快速、可靠(因为是物理备份)；

2. 支持增量备份，更为灵活

3. 备份过程不会打断正在执行的事务；

4. 能够基于压缩等功能节约磁盘空间和流量；

5. 自动实现备份检验；

6. 还原速度快；

 

缺点：

1. 只能对innodb表增量备份，myisam表增量备份时是全备

2. innobackupex备份MyISAM表之前要对全库进行加READ LOCK，阻塞写操作， 若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写。

 

② xtrabackup备份原理

1. innobackupex首先会启动一个xtrabackup_log后台检测的进程，实时检测mysql的redo log的变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中。

2. 物理拷贝innodb的数据文件和系统表空间文件ibdata1到对应的以默认时间戳为备份目录的地方

3. 复制结束后，执行flush table with read lock操作进行全库锁表准备备份非InnoDB文件

4. 物理复制.frm .myd .myi等非InnoDB引擎文件到备份目录

5. 查看二进制日志的位置

6. 解锁表unlock tables

7. 停止xtrabackup_log进程

 

 

注：redo log又称重做日志文件，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

在实例和介质失败（media failure）时，redo log文件就能派上用场，如数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性。

每个InnoDB存储引擎至少有1个重做日志文件组（group），每个文件组至少有

2个重做日志文件，如默认的ib_logfile0 和ib_logfile1

 

③ xtrabackup备份恢复原理

 

2、xtrabackup全备及恢复

 

思路：

1. innobackupex工具安装

2. innobackupex进行全备，备份集不能直接用于恢复

3. 预备阶段，备份过程中产生的xtrabackup_log应用到全量备份集

4. 模拟故障（删除数据）

5. 进行全库恢复

6. 测试验证

① 安装工具

[root@db01 ~]# yum [root@db01 ~]# yum 2.el7.x86_64.rpm

[root@db01 ~]# rpm

-y install libev-4.15-3.el7.x86_64.rpm

-y install percona-xtrabackup-24-2.4.7-

-ql percona-xtrabackup-24

/usr/bin/innobackupex

/usr/bin/xbcloud

/usr/bin/xbcloud_osenv

/usr/bin/xbcrypt

/usr/bin/xbstream

/usr/bin/xtrabackup

/usr/share/doc/percona-xtrabackup-24-2.4.7

/usr/share/doc/percona-xtrabackup-24-2.4.7/COPYING

/usr/share/man/man1/innobackupex.1.gz

/usr/share/man/man1/xbcrypt.1.gz

/usr/share/man/man1/xbstream.1.gz

/usr/share/man/man1/xtrabackup.1.gz

备注：安装DBD默认会安装mariadb-libs，而且在/etc下也会创建一个my.cnf文件.

② 全库备份(数据文件+配置文件)

㈠ 创建测试表

create database db02;

use db02

create insert create insert

mysql>

table t1(id int,name varchar(10)) into t1 values(1,'mona');

table t2(id int,name varchar(10)) into t2 values(2,'tom');

select * from db02.t1;

engine=myisam;

engine=innodb;

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

| id | name |

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

| 1 | mona |

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

1 row in set (0.00 sec)

mysql> select * from db02.t2;

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

| id | name |

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

| 2 | tom |

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

1 row in set (0.00 sec)

㈡ 创建备份用户并授权

说明：管理员root用户拥有所有权限，可以省略

创建备份用户admin，并授予相应权限

mysql> grant reload,process,lock tables,replication client on *.* to 'admin'@'localhost' identified by '123';

mysql> flush privileges;

说明：

在数据库中需要以下权限：

RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK

REPLICATION CLIENT权限：为了获取binary log位置

PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息）， 允许使用SHOW ENGINE

㈢ 全库备份

[root@db01 ~]# innobackupex --user=admin --password=123

/full_xtrabckup

说明：备份目录默认会自动创建，也可以手动创建

[root@db01 ~]# ll /full_xtrabckup/2019-01-23_22-57-28/

了解相关文件：

xtrabackup_checkpoints：

备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。

LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。

xtrabackup_binlog_info：

mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。xtrabackup_info：

xtrabackup工具在备份时记录的使用工具及数据库信息backup-my.cnf：备份命令用到的配置选项信息

xtrabackup_logfile：xtrabackup记录innodb事物日志的信息

 

③ 应用日志到备份集(预备=>日志文件)

--apply-log 表示应用日志到备份集

[root@db01 ~]# innobackupex --user=admin --password=123 --apply-log

/full_xtrabckup/2019-01-23_22-57-28/

说明：

在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

④ 故障模拟

删除数据目录里的所有文件

[root@db01 ~]# rm -rf /usr/local/mysql/data/*

停止mysql服务

[root@db01 ~]# ps -ef|grep mysqld

⑤ 数据恢复

--copy-back 表示从备份集拷贝文件到数据目录

# innobackupex --copy-back /full_xtrabckup/2019-01-23_22-57-28/

说明：

1. innobackupex工具是物理拷贝文件，所以在恢复时不需要连接数据库，这样就不需要初始化数据库并启动服务。

2. 数据目录必须是为空的，innobackupex --copy-back不会覆盖已存在的文件，除非指定--force-non-empty-directories参数；

3. 还要注意，还原时需要先关闭服务

如遇以下错误：

innobackupex version 2.4.7 based on MySQL server 5.7.13 Linux (x86_64) (revision id: 05f1fcf)

Error: datadir must be specified.

原因：innobackupex工具不知道数据目录在哪里

解决：指定配置文件位置--defaults-file=/path/my.cnf,默认为/etc/my.cnf

说明：恢复完毕务必要更改数据文件权限！

[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data/

启动数据库：

[root@db01 ~]# service mysql start Starting MySQL. SUCCESS!

 

⑥ 测试验证

mysql> select * from db02.t1;

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

| id | name |

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

| 1 | mona |

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

1 row in set (0.00 sec)

 

mysql> select * from db02.t2;

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

| id | name |

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

| 2 | tom |

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

1 row in set (0.00 sec)