MySQL 备份与还原

备份：防止由于机器故障或者人为误操作带来的数据丢失

冗余：数据存在多个服务中，如主备模式、数据库集群

还原：出现问题后，还原数据库为备份时状态

全量备份：对数据库进行完整备份

增量备份：基于全量备份进行增量备份（一次全备+多次增备）

一、备份时需要考虑的因素

①合适的备份工具

②详细的备份计划（备份频率、时间点）

③多份备份

④数据库恢复演练

⑤确保数据备份成功

二、逻辑备份

就是把数据库中数据及结构导出为一个SQL脚本

逻辑备份工具mysqldump，可以做到全库备份，库备份，表备份

优点：不需要停止服务，无论是什么存储引擎都可以使用

缺点：速度较慢，导入时可能会出现格式不兼容的问题，不能直接做增量备份

1.mysqldump

①表备份

mysqldump [options] 库名 表名 > 文件保存位置.sql -p

②表还原

# 方法1
mysql 库名 < 备份文件 -p
# 方法2，进入数据库
use 库名
source 备份文件.sql

③库备份

mysqldump  [options] --databases 库名 > 文件保存位置.sql -p

注意：当时GTIDs需要加上： --set-gtid-purged=off

实战：创建计划任务，每天对db_tiger进行一次备份，时间为凌晨2点，备份文件命名为：db_tiger_当天日期.sql

00 02 */1 * *  mysqldump --databases db_tiger > /tmp/mysql/backup/db_tiger_"$(date +'%y_%m_%d')".sql -p789 &> /dev/null

④库还原

# 方法1
mysql < 备份文件.sql -p
# 方法2，进入数据
source 备份文件.sql

⑤全库备份

mysqldump  [options] --all-databases > 文件保存位置.sql -p
# 一般使用
mysqldump --all-databases --master-data --single-transaction > 文件保存路径 -p

注意：使用前需要开启二进制日志，设置完成后重启

vim my.cnf
# 添加如下内容
log-bin = /usr/local/mysql/data/binlog
# 重启服务
service mysql restart

常用选项

⑥全库还原

# 方法1
mysql < 备份文件.sql -p
# 方法2，进入数据
source 备份文件.sql

2. mysqlbinlog

①实现增量备份

mysqldump + binlog ，实现增量备份，就是在全量备份mysqldump的基础上，只备份增加的那部分数据

第一步：全备份

mysqldump --all-databases --flush-logs  --master-data=2 --single-transaction > /tmp/mysql/backup/all.sql -p

第二步：对数据库进行操作

-- 删除或增加
delete from category where id =2;
insert into category values(null,'test02',1);

进行操作后，每个操作的日志都会写入到 binlog.000002中，可以通过 mysqlbinlog查看

查看正在使用的二进制日志文件 ：show master status;

第三步：备份增量数据

# 方法1.直接拷贝新产生的二进制日志文件
cp data/binlog.000002 /tmp/mysql/backup/
# 方法2.备份特定改变过的数据（SQL）
# mysqlbinlog --start-position=120  data/binlog.000002 > /tmp/mysql/backup/binlog.sql

第四步：模拟故障

-- 删除数据库
drop database db_tiger;

第五步：全量恢复

mysql < /tmp/mysql/backup/all.sql -p

注意：恢复后的数据为最开始的全量备份时的数据，并没有我们第二步操作后产生的数据

第六步：进行增量数据的恢复（从二进制文件中找到增加数据的SQL语句）

 # 1.恢复整个二进制日志文件内容
 mysqlbinlog /tmp/mysql/backup/binlog.000002 |mysql -p
 # 2.恢复从特定位置
 # mysqlbinlog --start-position=333 /tmp/mysql/backup/binlog2.000003 | mysql  -p
 # 3.恢复，根据第三步 方法2
 # mysql < /tmp/mysql/backup/binlog.sql -p

②恢复误删除数据

实现步骤：

（1）首先恢复全备份文件

（2）查找对应 --start-position 和 --stop-position编号(误删操作前的编号)

结束编号需要找commint之后那个的AT

（3）mysqlbinlog 恢复

mysqlbinlog --start-position=333 --stop-position=999 /tmp/mysql/backup/binlog.000002 | mysql  -p

3.逻辑导入导出

使用前需要修改配置文件设置导出目录

注意：需要手动创建目录并修改目录权限

修改配置文件my.cnf 添加导出目录： secure_file_priv=/tmp/myqlbak/

导出

注意：建议导出的文件名字与数据表名字一致

 -- 1.导出
 select * into outfile 导出路径 from 表名;
 -- 2.导出，修改导出文件内容样式
 select * into outfile '/tmp/mysqlbak/test2.txt' 
 fields terminated by ':' 
 lines terminated by '\n'  
 from student;

导入

注意：导入的文件名字需要与数据表名字一致

  mysqlimport 库名字 文件路径 -p
  -- 1.导入
  mysqlimport db_tiger /tmp/mysqlbak/student.txt -p
  -- 2.导入，特定文件内容样式
  mysqlimport db_tiger /tmp/mysqlbak/student.txt
  fields terminated by ':' 
  lines terminated by '\n'  
  -p

实战：

将/etc/passwd 文件导入到 passwd表中

创建表

CREATE TABLE `passwd` (
  `uname` varchar(20) DEFAULT NULL,
  `pass` char(2) DEFAULT NULL,
  `uid` int(11) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `home` varchar(50) DEFAULT NULL,
  `shell` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

拷贝要导入数据文件

cp /etc/passwd /tmp/mysqlbak/

导入数据

mysqlimport db_tiger --fields-terminated-by=':' --lines-terminated='\n' /tmp/mysqlbak/passwd -p

三、物理备份

直接对数据库的数据文件/usr/local/mysql/data进行拷贝，一般在数据库彻底关闭的前提下进行备份

适合大型数据库架构

特点：速度快

常用工具：mysqlhotcopy、mysqlbackup、pxb(percona xtrabackup)(免费版和收费版)

备份内容：①二进制日志文件 ②数据文件mysql/data ③配置文件 my.cnf

1. pxb

pxb=percona xtrabackup

特点：

①innobackupex备份MyISAM表之前要对全库进行加READ LOCK，阻塞写操作；对InnoDB表备份不会阻塞读写

②可以实现数据库全备份，增量备份;只能对 innodb表进行增量备份，myisam表是全量备份

③备份过程不影响正在执行的事务

④备份还原速度快

备份原理：

① innobackupex 会先启动一个xtrabackup_log后台检测的进程，实时监测mysql的redo log的变化，把在备份期间产生的操作写入到xtrabackup_log中，用于 -- apply-log （整合备份期间操作）

②物理拷贝InnoDB数据文件和共享数据文件ibdata1到备份目录

③拷贝结束后，执行flush table with read lock操作进行全库锁表

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

⑤解锁表unlock tables

⑥备份日志及配置文件

⑦停止xtrabackup_log进程

2. xtrabackup 安装

下载地址： https://www.percona.com/downloads/

libev-4.15-3.el7.x86_64.rpm
percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm

注意：安装成功后 需要删除/etc/my.cnf（卸载 mariadb-libs ）

3. xtrabackup 全量备份

第一步：创建一个特定账号用于实现pxb备份

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

权限说明：
RELOAD和LOCK TABLES权限:为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程的信息（即有关会话执行的语句的信息），允许使用SHOW ENGINE

第二步:全量备份

在进行全量备份的同时，innobackupex会检测数据的更新，并写入xtrabackup_log，用于整合(--apply-log)

innobackupex -S /tmp/mysql.sock --user=admin --password=123 /full_xtrabackup

第三步：整合全量备份期间产生的更新

innobackupex -S /tmp/mysql.sock --user=admin --password=123 --apply-log /full_xtrabackup/???

第四步：模拟故障

rm -rf /usr/local/mysql/data/*
pkill mysqld

第五步：数据恢复

读取配置文件my.cnf获取datadir的信息，所以在配置文件中一定要设定datadir

innobackupex --defaults-file=/usr/local/mysql/my.cnf --copy-back /full_xtrabackup/???

第六步：最后

注意：需要修改还原后的文件权限，因为是物理拷贝

# 设置权限
chown -R mysql.mysql /usr/local/mysql
# 重新启动mysql
service mysql restart

5. xtrabackup 增量备份

步骤

1.全量备份
2.整合全量备份期间产生的日志， --apply-log(预备)
3.对数据库操作（产生新数据）
4.增量备份
5.整合增量备份备期间产生的日志,--apply-log(预备),并将怎量备份添加全量备份中
6.模拟数据故障
7.还原数据库

①全量备份

innobackupex -S /tmp/mysql31.sock  --user=admin --password=123 /full_xtrabackup/

②整合全量备份期间产生的日志， --apply-log(预备)

innobackupex -S /tmp/mysql31.sock  --user=admin --password=123 --apply-log --redo-only  /full_xtrabackup/???/
# --redo-only 表示只应用已经提交的事务

③对数据库操作（产生新数据）

delete from category where id=3;
insert into category values(null,'test03',1);

④增量备份

innobackupex -S /tmp/mysql31.sock --user=admin --password=123 --incremental /incre_backup --incremental-basedir=/full_xtrabackup/???

# --incremental ：代表增量备份的存储目录
# --incremental-basedir ：指定增量备份的上一次备份目录;当第二次增量备份时候，该值为上一次增量备份的目录

注意：如果是第二次增量备份，则--incremental-basedir指定的目录是上一次增量备份的存储目录--incremental

⑤整合增量备份备期间产生的日志， --apply-log(预备) ,并把增量数据整合到全量备份汇总

innobackupex  -S /tmp/mysql31.sock --user=admin --password=123  --redo-only --apply-log /full_xtrabackup/??? --incremental-dir=/incre_backup/???

#注：这个整合一共做了两件事，第一件事把增量备份期间产生的数据先整合到增量备份中，然后第二件事再把增量整合到全量中

⑥模拟数据故障

rm -rf /data/mysql31/data/*
pkill mysql

⑦还原数据库

innobackupex --defaults-file=/data/mysql31/my.cnf --user=admin --password=123 --copy-back /full_xtrabackup/???

注意：= 左右不能有空格

⑧最后

注意：需要修改还原后的文件权限，因为是物理拷贝

chown -R mysql.mysql /usr/local/mysql
service mysql start

四、在线热备

通过多台服务器组建一个MySQL集群，共同提供数据服务（当某台机器出现宕机或者故障时，系统可以实时切换到另一台，不会影响业务的正常使用）

特点：

适合大型或超大型项目架构

1. MySQL 集群

集群（Cluster），就是多台服务器共同提供某种服务。

两台服务器共同提供数据库服务，则两者就组成了数据库集群架构。

高可用集群（High Available Cluster，HA Cluster ）

高可用集群是指以减少服务中断时间为目的的服务器集群技术，通过特殊的软件把独立的服务器连接起来，组成一个能够提供故障切换（Fail Over）功能的集群。

2.主从复制

也叫做AB复制， M-S 架构（主从结构）

实现将数据从一台数据库服务器（master）复制到一台到多台数据库服务器(slave)

默认情况下，属于异步复制，所以无需维持长连接

工作原理：

第一步：当我们对Master数据库进行DML(增删改)操作时，系统会自动把DML语句写入到binlog二进制日志

第二步：Slave数据库会自动通过网络的IO线程去主服务器拉取binlog日志，然后把二进制中的SQL语句复制到Slave服务器的中继日志relaylog中。从服务器还会开启一个SQL线程，专门负责把中继日志中的SQL语句自动执行一次。

MySQL主从复制搭建：

https://www.tigeru.cn/archives/61/

3.半同步复制

基于主从复制的基础上，添加ack响应信息，以此保证主从数据高度一致

半同步复制就是master每commit一个事务，要确保slave接受完主服务器发送的binlog日志文件并写入到自己的中继日志relay log里，然后会给master信号，告诉对方已经接收完毕，这样master才能把事物成功commit。这样就保证了master-slave的数据绝对的一致（但是以牺牲master的性能为代价)。

①安装插件

插件保存位置 $basedir/lib/plugin/

安装插件

-- Master
install plugin rpl_semi_sync_master soname 'semisync_master.so';
-- Slave
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
-- 查看是否安装成功
show global variables like 'rpl_semi_sync%';

卸载插件

-- 查看所有插件
show plugins;
-- 卸载插件
uninstall plugin 插件名; 

②激活

--Master
set global rpl_semi_sync_master_enabled = on;
--Slave
set global rpl_semi_sync_slave_enabled = on;
-- 当开启不了时，尝试重启 slave
-- 查看状态
show global status like 'rpl_semi_sync%';

③重启Slave的IO线程

stop slave IO_THREAD;
start slave IO_THREAD;

④测试验证

当slave从库的IO_Thread 线程将binlog日志接受完毕后，要给master一个确认，如果超过10s未收到slave的接收确认信号，那么就会自动转换为传统的异步复制模式。

关闭Slave的IO线程

stop slave IO_THREAD;

Master 插入新数据，这时Master会进行等待（默认10秒），然后自动转成了原来的异步模式

再次启动slave，查看同步模式

start slave IO_THREAD;
show global status like 'rpl_semi_sync%';

等待时间可以在master上动态调整

set global rpl_semi_sync_master_timeout=3600000;