mysql备份

备份的类型

1、根据是否需要数据库离线

（1）冷备： 需要关mysql服务，请写请求均不允许

（2）温备： 服务在线，但仅支持读请求。

（3）热备：备份的同时，业务不受影响。

mysql中进行不同方式的备份还需要考虑存储引擎是否支持

	MyISAM	InnoDB
热备	不支持	支持
温备	支持	支持
冷备	支持	支持

查看存储引擎方法：

show variables like '%storage_engine%';

直接备份数据库数据

1、查看数据库的数据保存在哪个目录下

SHOW VARIABLES LIKE 'datadir%'

这种方法就是将mysql中的数据库文件直接复制出来，不过需要先将服器停止，以防止期间数据被修改，同时还原时MySQL的版本最最好相同，这种方法是最快。

2、停止mysql

systemctl stop mysqld.service

3、数据打包压缩

tar -czvf mysql.tar.gz datadir // datadir换成1中的路径

4、把数据copy出来

scp

5、还原

（0）删除原来的数据

rm -rf /var/lib/mysql

（1）解压缩

tar -zxvf mysql.tar.gz -C /

（2）修改目录属主

chown -R mysql:mysql  /var/lib/mysql

（3）启动mysql

 

 

mysqldump+binlog

适用数据量比较小的应用，不需要停掉mysql服务，但需要锁表。mysqldump是MySQL的一个命令行工具。

1、施加读锁的方式：

（1）直接在备份的时候添加选项

--lock-all-tables 是对要备份的数据库的所有表施加读锁

--lock-table 仅对单张表施加读锁，即使是备份整个数据库，它也是在我们备份某张表的时候才对该表施加读锁，因此适用于备份单张表

 

（2）在服务器端书写命令

mysql> flush tables with read lock; 施加锁，表示把位于内存上的表统统都同步到磁盘上去，然后施加读锁
mysql> unlock tables;释放读锁

但这对于InnoDB存储引擎来讲，虽然你也能够请求道读锁，但是不代表它的所有数据都已经同步到磁盘上，
因此当面对InnoDB的时候，我们要使用
mysql> show engine innodb status; 
看看InnoDB所有的数据都已经同步到磁盘上去了，才进行备份操作。

2、日常用法

（1）备份所有的数据库

mysqldump --all-databases > dump.sql (不包含INFORMATION_SCHEMA,performance_schema,sys，如果想要导出的话还要结合--skip-lock-tables和--database一起用)

（2）备份指定的数据库

shell> mysqldump --databases db1 db2 db3 > dump.sql
当我们只备份一个数据的时候可以省去 --databases 直接写成：mysqldump test > dump.sql 不过有一些细微的差别，如果不加的话，数据库转储输出不包含创建数据库和use语句，所以可以不加这个参数直接导入到其它名字的数据库里
当然我们也可以只备份某个表 ：
mysqldump --user [username] --password=[password] [database name] [table name] table_name.sql

3、常用参数

（1）--master-data

获取备份数据的Binlog位置和Binlog文件名，用于通过备份恢复的实例之间建立复制关系时使用，该参数会默认开启。

（2）--dump-slave

用于在slave上dump数据，建立新的slave。大多数据情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File和Exec_Master_Log_Pos，需要用到这个参数。

（3）--no-data／-d

不导出任何数据，只导出数据库表结构。

4、恢复操作

（1）找到恢复前操作的log position

dbadmin@test 11:20:57>show master logs;
dbadmin@(none) 11:21:37>show binlog events in 'mysql-bin.000004';

（2）恢复到备份

source test.sql

（3）因为在备份的时候使用了master-data的参数，所以可以直接看到备份的最后位置，然后应用中间的log。

mysqlbinlog --start-position=187 --stop-position=444 mysql-bin.000004 > increment.sql
dbadmin@test 11:44:37>source /u01/my3307/log/increment.sql

5、sql_login_bin

是一个动态变量，修改该变量时，可以只对当前会话生效，也可以是全局的，当全局的修改这个变量时，只会对新的会话生效。

当还原数据库的时候，如果不关闭二进制日志，那么你还原的过程仍然会记录在二进制日志里面，不仅浪费资源，还会增加磁盘的容量，所以一般还原的时候会选择关闭二制制的日志。

mysqlbackup

是oracle公司提供的针对企业的备份软件，全名叫做MySQL Enterprise Backup， 是一个收费软件。这里不细述。

xtrabackup/innobackupex

Percona Xtrabacup是基于MySQL的热备份的开源实用程序，有两人个主要的工具：

（1）xtrabackup：只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。

（2）innobackupex则封装了xtrabackup，是一个脚本封装(perl)，能处理innodb和myisam，但在对myisam备份需要加一个全局的读锁。

（3）xbcrypt 加密解密备份工具

（4） xbstream 打包工具

1、xtrabackup

xtrabackup基于innodb的crash-recovery功能，先copy innodb的物理文件（这个时候数据的一致性无法满足），然后进行基于redo log进行恢复，达到数据一致性。

xtrabackup支持压缩（--compress）加密（--encrypt）并行（--parallel）.

（1）全备

xtrabackup --backup --target-dir=/data/backup/base

（2）增量备份

当我们做过全量备份以后，会在目录下产生xtrabackup_checkpoints的文件，这里记录了lsn和备份方式，我们可以基于这次的全量做增量的备份。

$cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1676085
last_lsn = 1676085
compact = 0
recover_binlog_info = 0

xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

同样，可以在增量备份的基础上继续做增量的备份。

（3）恢复

需要分别对全量、增量备份各做一次prepare操作。

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能包含尚未提交的事务或者已经提交的事务但尚未同步至数据文件中的事务，因此数据文件处于不一致的状态，prepare的主要作用正是勇冠回滚未提交的事务及同步已经提交的事务至数据文件，使得数据文件处于一致性状态。

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

增量
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base \
--incremental-dir=/data/backup/inc1

如果我们使用它自带的还原命令的时候就要先把data目录给清空。

$innobackupex --copy-back /data/backup/base/

（4）被恢复的目录里会多出来两人个文件，一个是xtrabackup_binlog_pos_innodb，一个是xtrabackup_info。在这两个文件中都可以看到最后的log.pos，在info里还可以看到lsn，我们基于这个pos再进行binlog的重演，恢复到binlog没有备份的数据。

 $mysqlbinlog mysql-bin.000001 --start-position=1076 --stop-position=1333 -vv >increment.sql

dbadmin@test 03:51:25>source /u01/my3307/log/increment.sql

Xtrabackup实战( 2.4，对应mysql8.0之前)

1、在两台机器（原mysql主机，新mysql主机）上分别安装Xtrabackup

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.9/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm -y

#Xtrabackup包依赖于epel源一些工具包，所以使用yum安装。

2、完全备份数据库

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /backup/

3、数据变动后，执行第一次增量备份

mkdir /backup/up1
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/backup/2018-02-27_14-44-54 /backup/up1

// 2018-02-27...是全备份文件

4、数据变动后，执行第二次增量备份

innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental  --incremental-basedir=/backup/up1/2018-02-27_14-48-24 /backup/up2/

//14-48-24是第一次增量备份的文件

5、恢复前准备

（1）将备份数据和/etc/my.cnf文件拷贝到另一台主机

scp -r /backup/  192.168.1.4:/root
scp /etc/my.cnf 192.168.1.4:/etc/

（2）不要启动数据库，开始恢复前的准备工作

#回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态:
innobackupex --apply-log --redo-only /root/2018-02-27_14-44-54/

（3）将增量备份整合到全备份中

innobackupex --apply-log --redo-only /root/2018-02-27_14-44-54/ --incremental-dir=/root/up1/2018-02-27_14-48-24
innobackupex --apply-log --redo-only /root/2018-02-27_14-44-54/ --incremental-dir=/root/up2/2018-02-27_14-50-03

（4）开始恢复

确保数据目录为空

rm -fr /var/lib/mysql

拷贝文件到数据目录

innobackupex --copy-back --datadir=/var/lib/mysql  /root/2018-02-27_14-44-54/

 

修改数据库目录权限：

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

启动数据库

systemctl start mariadb

xtrabackup 8.0

8.0是针对mysql 8的版本，而xtrabackup 2.4继续支持mysql 5.6，它移除了innobackupex命令。

1、下载

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

2、安装

yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm

3、全量备份

xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --target-dir=/data/backup/
 或者：使用参数--datadir替换掉参数--defaults-file.
 # xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm/ --backup --target-dir=/data/backup/

4、数据恢复

（1）停止掉运行的数据库实例

systemctl stop mysqld.service

（2）准备

  xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --prepare --target-dir=/data/backup/

（3）拷贝数据

  xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm --copy-back --target-dir=/data/backup/

（4）修改目录属性启动数据库

  chown -R mysql.mysql /data/crm
  chmod -R 755 /data/crm

（5）启动数据库实例

systemctl start mysqld.service

5、增量备份

全量备份的目录为：mkdir  -p /data/backup
增量备份的目录为： mkdir -p /data/backupIncr

（1）、备份

全量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --parallel=3 --target-dir=/data/backup/
增量备份：
xtrabackup --defaults-file=/etc/my.cnf --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --backup --parallel=3 --target-dir=/data/backupIncr  --incremental-basedir=/data/backup

（2）、恢复操作

 准备全备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup
准备增量备份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup  --incremental-dir=/data/backupIncr
全备份准备：
# xtrabackup --prepare --target-dir=/data/backup
拷回数据：
xtrabackup --host=172.16.1.52 --user=root --password=xxxyyy --port=3310 --datadir=/data/crm --copy-back --target-dir=/data/backup/
 

（3）、修改数据目录的权限和属性

chown -R mysql:mysql /data/crm
chmod -R 755 /data/crm

xtrabackup进行备份所需的权限

通常进行数据库备份，无论是数据库的还是操作系统的，都使用root用户来备份，由于这两者都是在各自系统里拥有最高权限。但是在一些用户和权限严格控制的生产环境下，通常都无法使用自各系统的root用户。

1、Linux

对于备份的的datadir，即数据目录，需要rw权限，对于存储备份的目录，即xtrabackup的输出目录，需要rwx。

2、数据库层面

（1）reload, lock tables

这些权限用于实现备份锁。

（2）backup_admin(针对mysql 8.0)

此权限用于查询performance_schema.log_status，和执行lock instance for bakcup, lock binlog for backup lock tables for backup

（3）replication client

用于查看binlog位点，拥有该权限可以执行show master status, shwo slave status ,show binary logs

（4）create tablespace

此权限用于导入表，在恢复数据时使用

（5）process

用于执行shwo engine innodb status和show processlist

（6）super

此权限用于控制复制线程

（7）create

用于创建percona_schema.xtrabackup_history

（8）insert

用于插入percona_schema.xtrabackup_history

（9）select

用于使用--incremental-hisotyr-name或-incremental-history-uuid时在percona_schema.xtrabackup_history查询innodb_to_lsn的值。

3、一个授权例子

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cr%T';
mysql> GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.log_status TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;