mysql备份方案

mysql备份主要分为2种,

1. 物理备份
2. 逻辑备份

备份方案:逻辑备份+物理备份(双重保险)

---

物理备份:字面理解就是拷贝mysql的数据文件,这里我使用常用的xtrabackup

• 1.安装xtrabackup
  https://www.percona.com/downloads/XtraBackup/LATEST/
  详细参数中文翻译
  https://www.cnblogs.com/zhoujinyi/p/5893333.html
  官方文档
  https://www.percona.com/doc/percona-xtrabackup/2.4/index.html

我使用的mysql版本为5.7需要去官方下载rpm包手动安装.

#yum安装会自动安装依赖源
yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

• 2.全量备份数据库

# 指定mysql配置文件绝对路径, 指定用户,密码 备份路径
innobackupex --defaults-file=/etc/my.cnf --user=root --password=你的密码 /data/backup_db

备份完成以后会生成一个带日期时间的目录,里面就是我们的备份文件.

root@docker [03:12:14 PM] [/data/backup_db] 
-> # ls
2018-11-16_14-21-09  all.sql  mysql

• 3.修改数据库后增量备份
  在原有的基础上进行增量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=你的密码 --incremental  --incremental-basedir=/data/backup_db/2018-11-16_14-21-09 /data/backup_db/incremental_data

–incremental 进行增量备份
–incremental-basedir 指定一个全量备份的路径(全量备份的内容)

• 4.进行恢复数据库
  自己删掉一个表…

# 停止掉mysql
systemctl stop mysqld 

# 删除mysql的data文件夹
cd /var/lib/mysql
rm -rf *

开始恢复

#回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态:
innobackupex --apply-log --redo-only /data/backup_db/2018-11-16_14-21-09

# 增量恢复操作
innobackupex --apply-log --redo-only /data/backup_db/2018-11-16_14-21-09 --incremental-dir=/data/backup_db/incremental_data/2018-11-16_15-35

# 恢复文件
innobackupex --copy-back --datadir=/var/lib/mysql /data/backup_db/2018-11-16_14-21-09

–redo-only 对增量备份进行合并
–apply-log 处理未提交事物，回滚日志
**对于全备份和增量备份（除最后一个增备外） 都需要加 --redo-only **
也就是说恢复增量备份的最后一次不需要添加–redo-only

修改权限

chown -R mysql.mysql /var/lib/mysql

启动mysql完成恢复

systemctl start mysqld

注意:
使用 xtrabackup 自带的move,copy命令必须清空/var/lib/mysql(mysql的data目录) ， 不然会报错

#查看MySql数据库data物理文件存放位置
show global variables like "%datadir%";

---

逻辑备份:通过mysqldump等导出生成sql语句

查看是否开启2进制日志模式

show variables like 'log_%';

开启2进制日志后会在你mysql的data文件夹下生成mysql-bin.index , mysql-bin.000001 2进制日志文件,每次重新启动一次mysql就会依次生成

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin   
server-id=55  #唯一ID随便填写
binlog_format=row #binlog模式 （行、混合、sql语句）
max_binlog_size = 512M #单个文件大小
expire_logs_days = 30 #保留最近30天
#修改完成后重启mysql

全表结构以及数据的逻辑备份

mysqldump -uroot -p --all-databases > all.sql

options的关键参数：

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [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  使用该命令插入数据而不是使用insert命令
--set-charset  将set names default_character_set命令写入到导出备份文件中，默认是开启状态
--dump-slave[=#]  表示从复制的slave从库导出备份，且其中包含了change master 通语句。value参数如果不写或=-1的情况下，则change master to语句写入dump文件中，设置为2则表示也写入dump文件中，只是会注释掉
--master-data[=#]  表示从复制的主库上导出备份。value参数与--dump-slave相同。使用该参数会自动打开lock-all-table参数，除非同时使用--single-transaction参数
-T, --tab=name  表示将备份文件以文本文件的方式生成，并指定存放文件路径，每个表会生成两个文件，一个是.sql文件保存表结构，一个是.txt文件保存表数据信息
-A, --all-databases  导出所有数据库里的所有表
-B, --databases  导出指定的一个或多个数据库
--ignore-table=name  代表导出过程中忽略某个指定表的导出，如果要忽略多个表则这个参数使用多次
-d, --no-data  代表只导出表结构
-R, --routines  代表导出时也要把存储过程和函数也导出来
--triggers  代表导出时也将触发器导出来
-w, --where=name  代表导出符合条件的数据
-x, --lock-all-tables  代表在导出过程中对每个数据库的每个表加上一个只读锁
--no-autocommit  代表对每个表的数据导出内容用set autocommit=0和commit两个语句包裹
--single-transaction  代表将事务隔离级别设置为可重复读并在导出开始执行start transaction开启一个新事务，在dump执行过程中也不会阻止任何读写操作

恢复sql有2种方法,因为我的是全表备份,如果是单个数据库的话,需要先建立一个空数据库,再指定数据库进行恢复.

# 方法1
mysql -uroot -p123 <  full.sql
# 方法2
mysql>source full.sql

二进制日志恢复数据库(前提是日志记录下增删改查)

# flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件,免得搞会混淆
 flush logs;

通过日志定位时间点可以通过position和datetime选择恢复点

 # 查看日志定位你要恢复的点
 # 因为二进制数据库是base64加密的所以需要解密,并且指定--no-defaults 不然会报错
 mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003 
 mysql> show binlog events in 'mysql-bin.000003';

我这里用的是position节点恢复

 mysqlbinlog --no-defaults --stop-position='1194' /var/lib/mysql/mysql-bin.000003 | mysql -uroot -p