mariadb恢复mysql的数据库_MariaDB数据库的备份和还原

MariaDB数据库备份和还原

-------------------------------------------------------------------------------------------------------------------------------------------

1、备份和恢复

(1)为什么要备份？

灾难恢复：硬件故障、软件故障、自然灾害、******、误操作

测试，做还原测试，用于测试备份的可用性

(2)要注意的要点

能容忍最多丢失多少数据，99.999%比99.99%成功率是多了10倍

恢复数据需要在多长时间内完成，尽可能尽量短的时间内恢复完成

需要恢复哪些数据

2、备份类型：

(1)完全备份，部分备份

(2)完全备份：整个数据集

(3)部分备份：只备份数据子集

3、还原要点

(1)做还原测试，用于测试备份的可用性

(2)还原演练，便于在发生万一时可以从容面对快速恢复数据

4、完全备份、增量备分、差异备份

(1)增量备份：以某个固定时间为周期，这个时间内到上一次变化的数据就是增量，仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据，还原时候完全备份+增量备份1+增量备份2+...增量备份n，从完全备份后有几个增量备份就要加几个增量备份，这种方式还原麻烦，但节省空间

(2)差异备份：仅备份最近一次完全备份以来变化的数据，还原时候是完全备份+差异备份，相对增量备份比较占用空间

(3)一般使用策略是完全备份+增量备份或者完全备份+差异备份，不能增量备份和差异备份混用

5、热备份、温备份、冷备份

(1)热备份：线上系统读写操作均可操作，mysql引擎不支持

(2)温备份：线上系统读操作可执行，但写操作不行

(3)冷备份：线上系统读写操作不可执行

注意：MyISAM引擎：温备，不能热备；InnoDB，引擎：可以做热备、温备

6、物理备份、逻辑备份

(1)物理备份：直接复制数据文件进行备份，只需要直接复制即可

(2)逻辑备份：从数据可中“导出”数据另存而进行的备份，需要专业的协议客户端，与存储引擎无关

7、备份时需要考虑的因素：

温备持锁多久、备份过程的时长、备份负载、恢复过程的时长

8、备份内容

数据、二进制日志和InnoDB的事务日志、代码(存储过程、存储函数、触发器、时间调度器)、服务器的配置文件

9、设计备份方案：

(1)数据集：完全+增量

(2)备份手段：物理，逻辑

10、备份工具

(1)mysqldump：逻辑备份工具，适用所有存储引擎，支持温备，不支持热备，但对于InnoDB存储引擎支持热备，该工具同时可做完全备份、部分备份

(2)cp,tar等复制归档工具，物理备份工具，使用所有存储引擎，只能做冷备，可用来做完全备份和部分备份

(3)lvm2的快照：几乎热备，借助于文件系统管理工具

(4)mysqlhotcopy：几乎冷备，仅适用于MyISAM存储引擎

(5)备份工具的选择

<1>mysqldump+复制binlog(二进制日志)，时间较慢，支持远程操作

mysqldump：完全备份

复制binlog中指定时间范围内的event完成做增量备份

<2>lvm2快照+复制binlog

lvm2快照：使用cp或tar等做物理备份，完全备份

复制binlog中指定时间范围内的event完成做增量备份

<3>xtrabackup

由Percona提供的支持对InnoDB做热备(物理备份)的工具

完全备份、增量备份

11、逻辑备份工具

mysqldump、mydumper、phpMyAdmin

mysqldump：客户端命令，通过mysql协议连接至mysqld服务器

mysqldump [OPTION] DBNAME [TBLNAME]，语句中带有[]内内容代表不是必须有的

-A,--all-databases

-B DBNAME,--databases DBNAME [TBLNAME]

区别： mysqldump DBNAME TBLNAME，不会自动创建数据库，而-A,--all-databases和-B DBNAME,--databases DBNAME [TBLNAME]会自动创建数据库

12、实现冷备

(1)停止数据库，进入数据库存放目录，tar Jcvf /DIR/SQL.tar /数据库存放目录/，过程中如果日志在其它目录，也需要一并拷贝

(2)还原：停止服务，解压备份数据，拷贝至需要还原的数据库，解压缩，检查用户权限组权限是否都为mysql，之后配置文件放到对应的位置后，重启数据库服务

注意：拷贝单个数据库时候最好打包后拷贝至需要还原的数据库，同时拷贝事务日志，同时注意文件的用户和组权限

13、逻辑卷快照实现数据库几乎热备

(1)创建两个逻辑卷(一个给数据库，一个给二进制日志)，新建文件系统，并挂载新建的备份存放目录

(2)vim /etc/my.cnf，在[mysqld_safe]下写入信息datadir=/数据库备份存放目录/

(3)请求锁定所有表：进入数据库 FLUSH TABLES with READ LOCK;

(4)记录二进制日志文件及事件位置

<1>FLUSH LOGS;

<2>SHOW MASTER STATUS;，或者mysql -e 'SHOW MASTER STATUS' > /DIR/pos.`date + F%`，记下binlog的时间点

(5)创建快照lvcreate -L SIZE -n LVNAME-snap -p r -s /dev/LVMDIR/LVNAME，创建逻辑卷/dev/LVMDIR/LVNAME的快照，SIZE大小，名称叫LVNAME-snap，只读r属性

(6)释放锁，进入数据库UNLOCK TABLES;

(7)挂载逻辑卷快照临时到某个目录

(8)转到快照目录下，cp -a mysql/ /tmp

(9)备份完成后，删除快照卷，lvremove

(10)制定好策略后，通过原卷备份二进制日志，mysqlbinlog --start-position=7697 binlog.00001 > /root/binlog.sql，mysqlbinlog  binlog.00002 >>binlog.sql，7697是刚才记录的binlog的时间点

(11)还原，将/tmp/mysql/拷贝至需要还原的数据库存放目录，将binlog放置对应目录中，mysql < 导入后重启mysql服务

14、mysqldump的使用

(1)备份

mysqldump -uUSER --databases DBNAME > /DIR/*.sql，用重定向方式备份一个数据库

mysqldump -uUSER --databases DB1 DB2 > /DIR/DB12.sql，备份多个数据库

(2)锁表

MyISAM、InnoDB实现温备

mysqldump --lock-all-tables：锁定所有库的所有表

--lock-tables：对于每个单独的数据库，在启动备份之前锁定该数据库所有表

InnoDB实现热备

mysqldump --single-transaction，启动一个事务，当完成时才会被提交，使用时配合-q一起使用，不适用MyISAM引擎，使用时候需要确保其他人不用增删改和truncate命令

(3)其它选项

mysqldump -E,--events：备份指定数据库相关的所有event scheduler，事件调度器

-R,--routines：备份指定数据库可相关的所有存储过程和存储函数

--triggers：备份表相关的触发器

--master-data=NUM，此选项须启用二进制日志，NUM为1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，记录二进制日志POS点，从此点之后没做备份；NUM为2：记录为注释的CHANGE MASTER TO语句，此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能(除非开启--single-transaction)

-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志

--compact 去掉注释，适合调试，生产不使用

-d, --no-data 只备份表结构

-t, --no-create-info 只备份数据,不备份create table

-n,--no-create-db 不备份create database，可被-A或-B覆盖

--flush-privileges 备份mysql或相关时需要使用

-f, --force 忽略SQL错误，继续执行

--hex-blob 使用十六进制符号转储二进制列(例如，“abc”变为0x616263)，受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT的列时使用，避免乱码，生产中带有二进制日志数据建议加

-q, --quick 不缓存查询，直接输出，加快备份速度

15、完全备份，并还原至最新状态

(1)启用二进制日志，并分离存放vim /etc/my.cnf，[mysqld]下log-bin=/DIR/

(2)mysqldump -A -F --single-transaction --master-data=2 | gzip > /data/`date +%F`.sql.gz

(3)模拟破坏，停止数据库并删库

(4)还原到备份时状态，直到还原结束一直不允许其他用户连接数据库，解压数据备份，gzip -d *.sql.gz，systemctl start mariadb(数据库无内容)，关闭二进制，进入数据库，mysql >set sql_log_bin=off，mysql > source / *.sql.gz

(5)还原到最新状态grep "CHANGE MASTER" /data/*.sql，查看position，比如说245

mysqlbin --start-potion=245 mysql-bin.0002 > /data/incr.sql

mysqlbin mysql-bin.0003 >>  /data/incr.sql

mysqlbin mysql-bin.0004 >> /data/incr.sql

mysql

>set sql_log_bin=off

>source /data/incr.sql

>set sql_log_bin=on

附：备份时候压缩：mysaldump -B DB | gzip > /DIR/*.sql.gz，解压时候gzip -d

注意：二进制日志文件不应该与数据文件放在同一磁盘

16、恢复误删除

(1)开启二进制日志功能

(2)完全备份：mysqldump -A -F --single-transaction --master-data=2 | gzip > /data/`date +%F`.sql.gz

(3)修改数据库：产生变化二进制文件

删除表：产生模拟事件

修改数据库

(4)加读锁，flush tables with read lock，并拒绝用户访问数据库

(5)还原：<1>停止服务，清空数据库，启动数据库，解压备份的数据

<2>mysql

> set sql_log_bin=off

>source /DIR/完全备份的数据库.sql

(6)分析二进制日志，找到丢失的表的drop table指令

grep -i "change master" /data/all.sql 找到带有drop table的二进制文件和pos点

mysqlbinlog --start-position=POSNUM binlog.0000* > /data/incr.sql

vim /data/incr.sql，注释drop那一行

(7)还原

mysql>set sql_log_bin=off

mysql> source /data/incr.sql;

mysql>set sql_log_bin=on

(8)复查数据完整

(9)开放用户

17、分库备份脚本

(1)mysql -e 'show databases' | grep -Ev  '^Database|info|performanc'|while read dbname;do mysqldump -B $dbname | gzip > /data/${dbname}_bak`date +%F`.gz;done

(2)mysql -e 'show databases' | grep -Ev  '^Database|info|performanc' |sed -r 's@(.*)@mysqldump -B \1| gzip > /data/\1__bak`date +%F`.gz@' |bash

(3)for db in `mysql -e 'show databases' | grep -Ev  '^Database|info|performanc'`; do mysqldump -B $db |gzip > /data/${db}_bak`date +%F`.gz;done

18、生产环境实战备份策略

(1)InnoDB建议备份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

(2)MyISAM建议备份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

注：以上实验在另一篇文章中实现数据库备份和还原实验

19、Xtrabackup

(1)特点：

<1>备份还原过程快速、可靠

<2>备份过程不会打断正在执行的事务

<3>能够基于压缩等功能节约磁盘空间和流量

<4>自动实现备份检验

<5>开源，免费

(2)xtrabackup是用来备份InnoDB表的，不能备份非InnoDB表，和MySQL没有交互

(3)innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和 MySQL Server 发送命令进行交互，如加全局读锁(FTWRL)、获取位点(SHOW SLAVE STATUS)等。即innobackupex是在 xtrabackup 之上做了一层封装实现的

(4)虽然目前一般不用 MyISAM 表，只是 MySQL 库下的系统表是 MyISAM 的，因此备份基本都通过 innobackupex 命令进行

(5)选项

--user：该选项表示备份账号

--password：该选项表示备份的密码

--host：该选项表示备份数据库的地址

--databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表

--defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置

--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir

--incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用

--incremental-dir：该选项表示还原时增量备份的目录

--include=name：指定表名，格式：databasename.tablename

(6)Prepare：innobackupex --apply-log [option] BACKUP-DIR

选项说明：

--apply-log：一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态

--use-memory：和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G

--export：表示开启可导出单独的表之后再导入其他Mysql中

--redo-only：此选项在prepare base full backup，往其中合并增量备份时候使用，不适用于最后一个增量备份

(7)还原：innobackupex --copy-back [选项] BACKUP-DIR，还原的时候需要停止服务

innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR

选项说明：

--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir

--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本

(8)还原注意事项：

<1>datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖

<2>在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中

<3>由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户

chown -R mysql:mysql /data/mysql

以上需要在用户调用innobackupex之前完成

--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败

(9)备份生成的相关文件

使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：

<1>xtrabackup_info：innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置

<2>xtrabackup_checkpoints：备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号，即LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的

<3>xtrabackup_binlog_info：MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复

<4>backup-my.cnf：备份命令用到的配置选项信息

<5>xtrabackup_logfile：备份生成的日志文件

20、xtrabackup的备份和还原

旧版：

(1)xtrabackup使用进行热备份，需要epel下载或者官网下载

<1>确认数据库表格的引擎是InnoDB或者XtraDB

<2>vim /etc/my.cnf，在[mysqld]和[mysqld_safe]之间加入innodb_file_per_table=ON

<3>重启数据库服务

<4>创建临时目录 /backups

<5>innobackupex --user=root /backups，该指令会自动备份二进制日志文件，是整库备份，生成的文件夹以日期显示

<6>scp -r /backups/2018-10-13_16-45-57/ 目标主机:/data/

(2)还原：

<1>注意vim /etc/my.cnf下[mysqld]内容中datadir指向路径和预存放的数据库路径要一致，如果自己想在/data/mysql存放还原的数据库，需要建立目录后更改权限为mysql.mysql，并在文件下添加datadir=/data/mysql

<2>vim /etc/my.cnf，在[mysqld]和[mysqld_safe]之间加入innodb_file_per_table=ON

<3>重启数据库服务

<4>将备份的数据库传至本机，移动至本机的备份目录/backups

<5>innobackupex --apply-log /backups/日期显示的文件夹

<6>停止数据库服务，删除原来所有数据库

<7>完全恢复：innobackupex --copy-back /backups/2018-10-13_16-45-57/

<8>更改/data/mysql目录权限为mysql.mysql

<9>重启服务

新版：两边的配置文件需要同步

<1>在原主机做完全备份到/data/backups

xtrabackup --backup --target-dir=/backups/

scp -r /backups/* 目标主机:/backups

<2>在目标主机上

[1]预准备：确保数据一致，提交完成的事务，回滚未完成的事务

xtrabackup --prepare --target-dir=/backups/

[2]复制到数据库目录

注意：数据库目录必须为空，MySQL服务不能启动

xtrabackup --copy-back --target-dir=/backups/

[3]还原属性

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

[4]启动服务

systemctl start mariadb

21、xtrabackup的增量备份和还原

旧版

(1)在原主机

innobackupex /backups

mkdir /backups/inc{1,2}

修改数据库内容

innobackupex --incremental /backups/inc1 --incremental-basedir=/backups/2018-02-23_14-21-42(完全备份生成的路径)

再次修改数据库内容

innobackupex --incremental /backups/inc2 --incremental-basedir=/backups/inc1/2018-02-23_14-26-17 (上次增量备份生成的路径)

scp -r /backups/* 目标主机:/data/

(2)在目标主机

不启动mariadb

rm -rf /var/lib/mysql/*

innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/

innobackupex --apply-log --redo-only /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc1/2018-02-23_14-26-17

innobackupex --apply-log /data/2018-02-23_14-21-42/ --incremental-dir=/data/inc2/2018-02-23_14-28-29/

ls /var/lib/mysql/

innobackupex --copy-back /data/2018-02-23_14-21-42/

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

systemctl start mariadb

新版：

(1)备份过程

[1]完全备份：xtrabackup --backup --target-dir=/backups/base

[2]第一次修改数据

[3]第一次增量备份：xtrabackup --backup --target-dir=/backups/inc1 --incremental-basedir=/backups/base

[4]第二次修改数据

[5]第二次增量：xtrabackup --backup --target-dir=/backups/inc2 --incremental-basedir=/backups/inc1

[6]scp -r /backups/* 目标主机:/backups/

(2)备份过程生成三个备份目录

/backups/{base，inc1，inc2}

(3)还原过程

[1]预准备完成备份，此选项--apply-log-only阻止回滚未提完成的事务

xtrabackup --prepare --apply-log-only --target-dir=/backups/base

[2]合并第1次增量备份到完全备份，xtrabackup --prepare --apply-log-only --target-dir=/backups/base --incremental-dir=/backups/inc1

[3]合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only

xtrabackup --prepare --target-dir=/backups/base --incremental-dir=/backups/inc2

[4]复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动

xtrabackup --copy-back --target-dir=/data/backups/base

[5]还原属性：chown -R mysql:mysql /var/lib/mysql

[6]启动服务：systemctl start mariadb

22、Xtrabackup单表导出和导入

(1) 单表备份

innobackupex --include='hellodb.students' /backups

(2)备份表结构

mysql -e 'show create table hellodb.students' > student.sql

(3)删除表

mysql -e 'drop table hellodb.students‘

(4)innobackupex --apply-log --export /backups/2018-02-23_15-03-23/

(5) 创建表

mysql>CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID` int(10) unsigned DEFAULT NULL,

PRIMARY KEY (`StuID`)

) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

(6)删除表空间

alter table students discard tablespace;

(7)cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/

(8)chown -R mysql.mysql /var/lib/mysql/hellodb/

(9)mysql>alter table students import tablespace;