MySQL之(1)数据备份与还原

备份类型

完全备份,部分备份

完全备份:整个数据集

部分备份:只备份数据子集,如部分库或表

备份的类型一般分为三种:完全备份、增量备份、差异备份

增量备份:仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快,还原复杂

差异备份:仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

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

增量备份:
差异备份:

备份的种类:冷、温、热备份

冷备:读、写操作均不可进行,数据库停止服务

温备:读操作可执行;但写操作不可执行

热备:读、写操作均可执行

MyISAM:温备,不支持热备

InnoDB:都支持
物理和逻辑备份

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

逻辑备份:从数据库中”导出”数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可
能丢失精度

备份
数据
二进制日志、InnoDB的事务日志
用户帐号,权限设置,程序代码(存储过程、函数、触发器、事件调度器)
服务器的配置文件

备份注意要点

能容忍最多丢失多少数据
备份产生的负载
备份过程的时长
温备的持锁多久
恢复数据需要在多长时间内完成
需要备份和恢复哪些数据

还原要点

做还原测试,用于测试备份的可用性
还原演练,写成规范的技术文档

备份工具

cp, tar等复制归档工具:物理备份工具,适用所有存储引擎;只支持冷备;完全和部分备份
LVM的快照:先加读锁,做快照后解锁,几乎热备;借助文件系统工具进行备份
mysqldump:逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备;支持完全或部
分备份;对InnoDB存储引擎支持热备,结合binlog的增量备份
xtrabackup:由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份、增量备份
MariaDB Backup: 从MariaDB 10.1.26开始集成,基于Percona XtraBackup 2.3.8实现
mysqlbackup:热备份, MySQL Enterprise Edition 组件

这里重点讲 mysqldump和xtrabackup

实战案例:数据库冷备份和还原

MySQL8.0

# 首先停止数据库
[root@localhost ~]# systemctl stop mysqld

# 安装rsync
[root@localhost ~]# yum install rsync -y

# 备份数据到远端服务器
[root@localhost ~]# rsync -a /data/mysql 172.31.0.29:/opt/

# 开始还原
[root@centos8 ~]#yum -y install mysql-server
[root@centos8 ~]#cp -a /data/mysql/* /var/lib/mysql/
[root@centos8 ~]#systemctl start mysqld

Mariadb10.3

#在目标服务器(172.31.0.28)安装mariadb-server,不启动服务
[root@centos8 ~]# dnf install mariadb-server
#在源主机(172.31.0.8)执行
[root@centos8 ~]# systemctl stop mariadb
#复制相关文件
[root@centos8 ~]# scp -r /var/lib/mysql/* 172.31.0.28:/var/lib/mysql/
[root@centos8 ~]# scp /etc/my.cnf.d/mariadb-server.cnf 172.31.0.28:/etc/my.cnf.d/
[root@centos8 ~]# scp -r /data/logbin/ 172.31.0.28:/data/ #172.31.0.28须事先存
在/data/目录
#复制相关文件并保留属性:可以用rsync
[root@centos8 ~]# rsync /etc/my.cnf.d/mariadb-server.cnf 172.31.0.28:/etc/my.cnf.d/
[root@centos8 ~]# rsync -av /var/lib/mysql/ 172.31.0.28:/var/lib/mysql/
[root@centos8 ~]# rsync -av /data/logbin/ 172.31.0.28:/data/ #172.31.0.28 须事先存
在/data/目录
#在目标主机(172.31.0.28)执行
[root@centos8 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@centos8 ~]# chown -R mysql.mysql /data/logbin/
[root@centos8 ~]# systemctl start mariadb

mysqldump 备份工具

mysqldump 说明

逻辑备份工具:

mysqldump, mydumper, phpMyAdmin
Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件
mysqldump是MySQL的客户端命令,通过mysql协议连接至mysql服务器进行备份

mysqldump参考:

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

命令格式:

mysqldump [OPTIONS] database [tables] #支持指定数据库和指定多表的备份,但数据库本身定义
不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份,包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS] #备份所有数据库,包含数据库本身定义也会备份
mysqldump 常见通用选项:
-A, --all-databases #备份所有数据库,含create database
-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 选项时,会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新,可通过和--singletransaction
或-x,--master-data 一起使用实现,此时只刷新一次二进制日志
--compact #去掉注释,适合调试,节约备份占用的空间,生产不使用
-d, --no-data #只备份表结构,不备份数据,即只备份create table
-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 #不缓存查询,直接输出,加快备份速度

mysqldump的MyISAM存储引擎相关的备份选项:

MyISAM不支持事务,只能支持温备;不支持热备,所以必须先锁定要备份的库,而后启动备份操作

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

mysqldump的InnoDB存储引擎相关的备份选项:

InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备,也可以实现温备但不建议用

--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启
事务
#此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表
(目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致。 在进行单事务转储时,要确保有效的转储
文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句:ALTER TABLE,DROP TABLE,
RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互
排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使用
InnoDB建议备份策略
mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1
--flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
MyISAM建议备份策略
mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --
triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

实战案例:特定数据库的备份脚本

[root@localhost ~]# vim mysql_backup.sh
#!/bin/bash
# Date: 2021-05-18
# Author: xuanlv
TIME=`date +%F_%H-%M-%S`
DIR=/mysql/backup
DB=test1
PASS=123456

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

实战案例:分库备份并压缩

[root@localhost ~]# mysql -uroot -p123456 -e 'show databases' | grep -Ev '^(Database|information_schema|performance_schema)$' | sed -rn 's@(.*)@mysqldump -B \1 | gzip > /mysql/backup/\1.sql.gz@p' | bash

[root@localhost ~]# mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'|while read db;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done

[root@localhost ~]# for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;done

[root@localhost ~]# mysql -uroot -e 'show databases'|sed -rn '/^(Database|information_schema|performance_schema)$/!s#(.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash

案例:分库备份的实战脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/mysql/backup
PASS=123456

[ -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 | g
zip > ${DIR}/${DB}_${TIME}.sql.gz
done

案例:完全备份和还原

#开启二进制日志
[root@centos8 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin

#备份
[root@centos8 ~]# mysqldump -uroot -p123456 -A -F --single-transaction --masterdata= 2 | gzip > /backup/all-`date +%F`.sql.gz

#还原
[root@centos8 backup]# dnf install mariadb-server
[root@centos8 backup]# gzip -d all-2021-05-18.sql.gz
[root@centos8 ~]# mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2021-05-18.sql
MariaDB [(none)]> set sql_log_bin=on;

案例:利用二进制日志,还原数据库最新状态

#二进制日志独立存放
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=/data/mysql/mysql-bin

#完全备份,并记录备份的二进制位置
mysqldump -uroot -pmagedu -A -F --default-character-set=utf8 --singletransaction --master-data=2 | gzip > /mysql/backup/all_`date +%F`.sql.gz

#修改数据库
insert students (name,age,gender)value('jack',20,'M');
insert students (name,age,gender)value('long',22,'M');

#损坏数据库
rm -rf /var/lib/mysql/*

#还原
cd /mysql/backup
gzip -d all_2021-05-18.sql.gz

#CentOS 8 需要事先生成数据库相关文件,CentOS7 不需要执行此步
mysql_install_db --user=mysql
systemctl restart mariadb
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 998 |
| mysql-bin.000002 | 28090 |
| mysql-bin.000003 | 342 |
+------------------+-----------+
3 rows in set (0.000 sec)

MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /mysql/backup/all_2021-05-18.sql
[root@centos8 ~]# grep '^-- CHANGE MASTER TO' /mysql/backup/all_2021-05-18.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

#二进制日志的备份
[root@centos8 mysql]# mysqlbinlog mysql-bin.000001 --start-position=328 > /mysql/backup/inc.sql

[root@centos8 mysql]# mysqlbinlog mysql-bin.000002 >> /mysql/backup/inc.sql
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /mysql/backup/inc.sql
MariaDB [(none)]> set sql_log_bin=1;

案例:mysqldump 和二进制日志结合实现差异(增量)备份

[root@centos8 ~]# mysqldump -uroot -p -A -F --single-transaction --master-data=2 | gzip > /backup/all-`date +%F`.sql.gz
#观察上面备份文件中记录的二进制文件和位置,定期将其之后生成的所有二进制日志进行复制备份
[root@centos8 ~]# cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadbbin.
000003是后续生成的二进制日志
[root@centos8 ~]# mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

案例:恢复误删除的表

案例说明:每天2:00做完全备份,早上11:00误删除了表students,11:20才发现故障,现需要将数
据库还原到11:20的状态,且恢复被删除的students表

# 创建目录存放数据
[root@localhost ~]# mkdir /mysql/backup -p

查看binlog路径的权限
#
[root@localhost ~]# ll -d /data/
drwxr-xr-x 3 root root 19 May 18 17:08 /data/
[root@localhost ~]# ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 May 18 17:08 /data/mysql/

# 完全备份
[root@localhost ~]# mysqldump -uroot -A -F --single-transaction --master-data=2 > /mysql/backup/all_`date +%F_%T`.sql

#完全备份后数据更新
MariaDB [hellodb]> insert students (name,age,gender)values('long',20,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students (name,age,gender)values('jack',22,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert students (name,age,gender)values('rose',20,'F');
Query OK, 1 row affected (0.00 sec)

# 11:00误删除了一个重要的表
MariaDB [hellodb]> drop table students;
Query OK, 0 rows affected (0.00 sec)

#后续其它表继续更新
MariaDB [hellodb]> use testdb1;
Database changed

MariaDB [hellodb]> insert teachers (name,age,gender)values('bob',23,'F');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> insert teachers (name,age,gender)values('monk',19,'F');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | bob           |  23 | F      |
|   6 | may           |  19 | F      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

#11:20发现表删除,进行还原
#停止数据库访问

#从完全备份中,找到二进制位置
[root@centos8 ~]# grep '\-\- CHANGE MASTER TO' /backup/allbackup_2021-05-18_11\:20\:08.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

#备份从完全备份后的二进制日志
[root@centos8 ~]# mysqlbinlog --start-position=389 /var/lib/mysql/mariadbbin.000003 > /backup/inc.sql

#找到误删除的语句,从备份中删除此语句
[root@centos8 ~]# vim /data/inc.sql
#DROP TABLE `student_info` /* generated by server */

#如果文件过大,可以使用sed实现
[root@centos8 ~]# sed -i.bak '/^DROP TABLE/d' /data/inc.sql

#利用完全备份和修改过的二进制日志进行还原
[root@centos8 ~]# mysql -uroot -p
MariaDB [hellodb]> set sql_log_bin=0;
MariaDB [hellodb]> source /backup/allbackup_2021-05-18_11:20:08.sql;
MariaDB [hellodb]> source /backup/inc.sql
MariaDB [hellodb]> set sql_log_bin=1;

原文地址:https://www.cnblogs.com/xuanlv-0413/p/14782929.html