数据库备份和还原 mysqldump（重点，重点，重点）分库备份_mysql mydumper备份的是完整数据库吗

一. 备份和恢复（建议，入职备份数据库）

为什么要备份
灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
备份注意要点
能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据

还原要点

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

2. 还原演练

1.备份类型：

1. 完全备份，部分备份
   完全备份：整个数据库（建议一周做一次）
   部分备份：只备份数据子集，如部分库或表

2. 增量备份、差异备份
   增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
   很可能这个增量备份就是将二进制日志—导成备份文件

   差异备份：仅备份最近一次完全备份变化的数据，备份较慢，还原简单
   注意：二进制日志文件不应该与数据文件放在同一磁盘

差异备份实例

备份变化的数据，备份较慢，还原简单

增量备份实例

备份变化的数据，备份较快，还原复杂
很可能这个增量备份就是将二进制日志—导成备份文件

mysqllogbin增量备份

2.冷、温、热备份

1.冷备（速度读块）

冷备：读写操作均不可进行，将磁盘对应的文件考出来/var/lib/mysql/事务日志ibdata1，原有的（库文件，数据文件，my.cnf配置文件，二进制文件）

例如：
开始备份
1.首先停止服务systemctl stop mariadb（sock文件就没了）
2.将整个文件都压缩tar Jcvf all.bak.tar.xz /var/lib/mysql/
3.如果二进制日志，或其他日志独立在其他目录也需要备份tar Jcfv
4.备份 /etc/my.cfg 直接考就可以
将其放到一个文件夹里面

5.将整个目录拷贝到另一台机器上scp /backup/ 192.168.4.24 /data/backup :
开始恢复
首先得有数据库mariadb（与上一台机器版本一致）
1.覆盖（并备份my.cnf文件）
cp /data/back/my.cng /etc/my.cng -b （这里-b用于备份/etc/my.cnf为my.cnf-）
2.创建独立的二进制日志文件/data/login/，更改权限mysql
然后还原二进制日志
tar -xvf /data/login.tar.xz -C /data/login/
3.还原/va/lib/mysql/
tar xvf /data/backup/all.bak.tar.xz -C /va/lib/mysql/
4.最后启动数据库
检测：
select 数据库
show master logs查看二进制日志

2.温备

温备：读操作可执行；但写操作不可执行
需要加读锁，最后撤销读锁

3.热备（建议）

热备：读写操作均可执行

用事务的方式（可重复读 最合理）
MyISAM：不支持热备

3.注意点

MyISAM：温备，不支持热备
InnoDB：都支持

3.物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快

逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度（把数据抽出来）

4.备份和恢复

备份时需要考虑的因素
温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长
备份什么
1.数据
2. 二进制日志、InnoDB的事务日志
3.程序代码（存储过程、函数、触发器、事件调度器）
4.服务器的配置文件

5.备份工具（怎样备份）

1. cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
2. LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
3. mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
4. xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
5. MariaDB Backup： 从MariaDB 10.1.26开始集成，基于PerconaXtraBackup 2.3.8实现
6. mysqlbackup：热备份， MySQL Enterprise Edition组件
7. mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

基于LVM的备份

(1) 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
(2) 记录二进制日志文件及事件位置
mysql> FLUSH LOGS; 刷新日志
mysql> SHOW MASTER STATUS;查查看二进制日志日志
mysql -e ‘SHOW MASTER STATUS’ > /PATH/TO/SOMEFILE 记录二进制文件路径
(3) 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
(4) 释放锁
mysql> UNLOCK TABLES;
(5) 挂载快照卷，执行数据备份
(6) 备份完成后，删除快照卷
(7) 制定好策略，通过原卷备份二进制日志

二. 备份和恢复

逻辑备份工具：mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump工具

mysqldump工具：客户端命令，通过mysql协议连接至mysql服务器进行备份
mysqldump database 只是备份其中一个数据库（需要重定向）

–B 多个不同的库

备份所有数据库

mysqldump [OPTIONS] –A [OPTIONS] 
1

但不会备份
information_schema(内存)和
performance_schema（性能）

mysqldump参考：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump常见选项：

-A，--all-databases 备份所有数据库，含create database（但不会备份information_schema和performance_schema）
-B,--databases db_name… 指定备份的数据库，**包括create database语句**

-E, --events：备份相关的所有event scheduler
-R, --routines：备份所有存储过程和自定义函数
--triggers：备份表相关**触发器**，默认启用,
	用--skip-triggers，不备份触发器
	
--default-character-set=utf8 指定字符集

              ***********
--master-data[=#]： 此选项**必须启用二进制日志**
	 =1：所备份的数据之前加一条记录为CHANGE MASTER TO语句（配合主从复制），非注释，不指定#，默认为1
	 =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 使用十六进制符号转储二进制列，当有包括BINARY，
	VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
 -q, --quick 不缓存查询，直接输出，加快备份速度
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

备份数据库----并还原（建议）

1.备份，并重定向（默认只在屏幕上打印）
-B （支持多个数据库）默认备份中有创建数据库的命令（如果将书籍库删除，直接还原就可以）

2.还原直接导入数据库

备份整个数据库----并还原

1.备份所有数据库（默认备份中有创建新的数据库的命令）

利用二进制还原(备份还原至最新状态)

记录二进制日志的位置（分备份前后日志）

1.在 完全备份的时候开启二进制日志（用于记录两个完全日志之间的记录）会生成新的二进制日志（建议二进制日志分开放）
[mysql]
log-bin=/data/logbin/mysql-bin
sql_log_bin=ON

2.开始备份
- -master-data
=1 将change master to （用于主从复制）默认
=2将change master to注释掉
目前用2

抽取二进制日志内容到一个新的文件（这就是这个文件记录2：00到18:00之间的内容）

mysqllogbin增量备份

3.当数据库崩溃时（恢复）

重新启动服务mariadb
限制用户（禁止访问）加配置skip-networking 或者防火墙

先恢复完全备份
(要临时停止二进制服务，然后恢复完全备份，防止生成新的垃圾二进制文件)

开始恢复
source /data/all.sql恢复完全备份

soure /data/inc.sql恢复二进制备份

最后开启二进制日志

set sql_log_bin=on;
1

最后确认数据库是否恢复

如果是某张表被（无意间删除）

最后这段时间的二进制日志（删除那个删除表的操作）
然后，恢复就可以
1备份（前提二进制日志开启）
完全备份

中间误操作删除表，
最好的是（紧急枷锁）

首先查看完全备份中：
完全备份开始的位置 和 二进制日志开头的位置


抽取二进制日志内容到一个新的文件（记录的是指定位置的内容）

将文件中的删除表的误操作给他删掉

2.恢复
关闭日志（还原的日志不用记录）
还原—删库还原，
临时关闭二进制日志set sql_log_bin=off;
还原完全备份source /data/all_…
还原二进制文件source /data/inc.sql

最后测试（查看内容是否恢复）

最后启用二进制日志set sql_log_bin=on;

MyISAM备份选项：

支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作
锁定方法如下：
-x,--lock-all-tables：加全局读锁，锁定所有库的所有表，同时加–single-transaction或–lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,–lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB备份选项

支持热备，可用温备但不建议用
--single-transaction只隔离dml语言

此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在
支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其
他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的
表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，
DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将–single-transaction选项和–quick结合一起使用

建议当MyISAM和InnoDB都存在建议只加 --single-transaction

生产环境实战备份策略

InnoDB建议备份策略

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

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
1
2
3
4

三. 分库备份（脚本）

指定数据库 ，就是用B选项，分开备份

mysqldump -B 库  --single-transaction master-data=2 > /data/`date +%F`.sql
1

分库备份脚本

cat backup_hellodb.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=nice
mysqldump -uroot -p "$PASS" -F –E –R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz

1
2
3
4
5
6
7
8

mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --master-data=2 --default-character-set=utf8 -q  -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
1

cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=magedu
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ev 
"^Database|.*schema$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
1
2
3
4
5
6
7
8
9
10

也可以直接压缩

也可以用替换

分库备份

自动按库名分类备份，这样便于后期只有某个库出现问题的时候恢复数据，而且还不影响其他的库

mkdir /data/backup -p
1

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B"
DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "_schema|mysql")" #不备份的数据库列表写在这里，自带的mysql、test等可以不备份

for dbname in ${DATABASE}
  do
   MYDIR=/data/backup/${dbname}
   [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
 $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

通过参数备份

可以接收多个不固定的参数，将传递的参数作为数据库进行备份

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B"
DATABASE=$* #接收所有的参数

for dbname in ${DATABASE} #循环参数
  do
    MYDIR=/data/backup/${dbname}
    [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
    $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

推荐使用分库备份的脚本

#!/bin/sh
#Author:ZhangJie
BAK_TIME=$(date  +%Y-%m-%d_%H:%M:%S)
USER_PSWD=123456
USER_NAME=root
SOCKET="/var/lib/mysql/mysql.sock"
MYLOGIN="mysql -u${USER_NAME} -p${USER_PSWD} -S ${SOCKET}"
DUMP_CMD="/usr/local/mysql/bin/mysqldump -u${USER_NAME} -p${USER_PSWD} -S${SOCKET} -B --master-data=2 --single_transaction --flush-logs --skip-add-drop-table"
DATABASE="$($MYLOGIN -e "show databases;"|egrep -vi "_schema|mysql")"

for dbname in ${DATABASE}
  do
   MYDIR=/data/backup/${dbname}
   [ ! -d ${MYDIR} ] && mkdir -p ${MYDIR}
 $DUMP_CMD ${dbname}|gzip >${MYDIR}/${dbname}_${BAK_TIME}.sql.gz
done
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

常出现的问题

1. 文件，目录权限问题
2. 服务路径，脚本scripts/mysql_install_db
3. 配置文件等