标签：-- 恢复备份 MySQL sql mysql root centos8

1. 备份恢复概述

1.1 为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景
参考链接: https://www.toutiao.com/a6939518201961251359/

1.2 备份类型

完全备份，部分备份
完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
完全备份、增量备份、差异备份
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份快，还原复杂

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

增量和差异备份的基础和前提是完全备份
注意：二进制日志文件不应该与数据文件放在同一磁盘

冷、温、热备份
- 冷备：读、写操作均不可进行，数据库停止服务
- 温备：读操作可执行；但写操作不可执行
- 热备：读、写操作均可执行
  - MyISAM：温备，不支持热备
  - InnoDB：都支持
物理和逻辑备份
- 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
- 逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

1.3 备份什么

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

1.4 备份注意要点

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

1.5 还原要点

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

1.6 备份工具

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 组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、
FLUSH TABLES和cp或scp来快速备份数据库

1.7 数据库冷备份和还原

MySQL8.0

Mariadb10.3
#备份过程
[root@centos8 ~]#systemctl stop mysqld
#备份数据
[root@centos8 ~]#rsync -a /var/lib/mysql 10.0.0.28:/data/
#如果配置及二进制文件相关有特殊设置也需要备份

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

Mariadb10.3

#在目标服务器（10.0.0.18）安装mariadb-server，不启动服务
[root@centos8 ~]#dnf install mariadb-server

#在源主机（10.0.0.8）执行
[root@centos8 ~]# systemctl stop mariadb

#复制相关文件
[root@centos8 ~]# scp -r /var/lib/mysql/* 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]# scp /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@centos8 ~]# scp -r /data/logbin/ 10.0.0.18:/data/   #10.0.0.18须事先存
在/data/目录

#复制相关文件并保留属性：可以用rsync
[root@centos8 ~]#rsync /etc/my.cnf.d/mariadb-server.cnf 10.0.0.18:/etc/my.cnf.d/
[root@centos8 ~]#rsync -av /var/lib/mysql/ 10.0.0.18:/var/lib/mysql/
[root@centos8 ~]#rsync -av/data/logbin/ 10.0.0.18:/data/   #10.0.0.18 须事先存在/data/目录

#在目标主机（10.0.0.18）执行
[root@centos8 ~]#chown -R mysql.mysql /var/lib/mysql/
[root@centos8 ~]#chown -R mysql.mysql /data/logbin/
[root@centos8 ~]#systemctl start mariadb

2. mysqldump 备份工具

2.1 mysqldump 说明

逻辑备份工具：
mysqldump, mydumper, phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份

命令格式:

mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

mysqldump参考：

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

mysqldump 常见通用选项：

-u, --user=name     User for login if not current user
-p, --password[=name]  Password to use when connecting to server
-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[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-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    #只备份表结构,不备份数据,即只备份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结合一起使用

2.2 生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

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

2.3 mysqldump 备份还原实战案例

2.3.1 实战案例：分库备份并压缩

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

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

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

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

2.3.2 实战案例：分库备份的实战脚本

[root@centos8 ~]#cat backup_db.sh
[root@centos8 ~]#cat mysql_backup.sh
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=123456
[ -d "$DIR" ] || mkdir $DIR

for DB in `mysql -uroot -p"$PASS" -e 'show databases' | grep -Ewv "^Database|.*schema$"`;do
mysqldump -uroot -p"$PASS" -F --single-transaction --source-data=2 --default-character-set=utf8 -q -B $DB 2>/dev/null| gzip >  ${DIR}/${DB}_${TIME}.sql.gz
done

2.3.3 实战案例：完全备份和还原

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

#备份
[root@centos8 ~]#mysqldump -uroot -pmagedu -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-2019-11-27.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source /backup/all-2019-11-27.sql
MariaDB [(none)]> set sql_log_bin=on;

2.3.4 实战案例：利用二进制日志，还原数据库最新状态-yum安装

#二进制日志独立存放
[mysqld]
log-bin=/data/mysql/mysql-bin

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

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

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

#还原
cd /backup
gzip -d all_2019-11-25.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 /data/all_2019-11-25.sql

[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /data/all_2019-11-25.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

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

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

2.3.5 实战案例：利用二进制日志，还原数据库最新状态-二进制安装

#二进制日志独立存放与数据文件不在同一目录
[mysqld]
server-id=1
log-bin=/opt/mysql/mysql-bin #二进制日志位置
datadir=/data/mysql
socket=/data/mysql/mysql.sock                                                                                                   
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock


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

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

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

#还原
cd /backup
gzip -d all_2024-12-27.sql.gz

#/data/mysql目录被删后，需要重新生成数据库相关文件，
mysqld --initialize --user=mysql --datadir=/data/mysql
systemctl restart mysqld

#过滤临时root密码，并修改密码
awk '/A temporary password/{print $NF}' /data/mysql/mysql.log
mysqladmin  -uroot -p'Tr_7-tZnyzzE' password 123456


mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |     11408 | No        |
| mysql-bin.000002 |       204 | No        |
| mysql-bin.000003 |       204 | No        |
| mysql-bin.000004 |       204 | No        |
| mysql-bin.000005 |       204 | No        |
| mysql-bin.000006 |       204 | No        |
| mysql-bin.000007 |       204 | No        |
| mysql-bin.000008 |       204 | No        |
| mysql-bin.000009 |       485 | No        |
| mysql-bin.000010 |       180 | No        |
| mysql-bin.000011 |   1272459 | No        |
+------------------+-----------+-----------+
11 rows in set (0.00 sec)


mysql> set sql_log_bin=0;
mysql> source /backup/all_2024-12-27.sql

[root@centos8 ~]#grep '^-- CHANGE MASTER TO' /backup/all_2024-12-27.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=157;

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

#将后续二进制内容追加到sql文件中，执行source恢复
[root@centos8 mysql]#mysqlbinlog mysql-bin.0000010 >> /backup/inc.sql
[root@centos8 mysql]#mysqlbinlog mysql-bin.0000011 >> /backup/inc.sql
mysql> set sql_log_bin=0;
mysql> source /backup/inc.sql
mysql> set sql_log_bin=1;

2.3.6 实战案例：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.3.7 实战案例：恢复误删除的表

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

#完全备份,要求必须开启二进制日志
[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql
[root@centos8 ~]#ll /backup/
total 2992
-rw-r--r-- 1 root root 3060921 Nov 27 10:20 allbackup_2019-11-27_10:20:08.sql

#完全备份后数据更新
MariaDB [testdb]> insert students (name,age,gender) values('rose',20,'f');
Query OK, 1 row affected (0.001 sec)
MariaDB [testdb]> insert students (name,age,gender) values('jack',22,'M');
Query OK, 1 row affected (0.001 sec)

#10：00误删除了一个重要的表
MariaDB [testdb]> drop table students;
Query OK, 0 rows affected (0.021 sec)

#后续其它表继续更新
MariaDB [testdb]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> insert teachers (name,age,gender)values('wang',30,'M');
Query OK, 1 row affected (0.002 sec)
MariaDB [hellodb]> insert teachers (name,age,gender)values('mage',28,'M');
Query OK, 1 row affected (0.002 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 | wang         |  30 | M     |
|   6 | mage         |  28 | M     |
+-----+---------------+-----+--------+
6 rows in set (0.001 sec)

#10：10发现表删除，进行还原
#停止数据库访问

#从完全备份中，找到二进制位置
[root@centos8 ~]#grep '\-\- CHANGE MASTER TO' /backup/allbackup_2019-11-27_10\: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 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000004 >> /backup/inc.sql
[root@centos8 ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000005 >> /backup/inc.sql

#找到误删除的语句，从备份中删除此语句
[root@centos8 ~]#vim /backup/inc.sql
#DROP TABLE `students` /* generated by server */
#如果文件过大，可以使用sed实现
[root@centos8 ~]#sed -i '/DROP TABLE `students`/d' /backup/inc.sql

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

标签：--,恢复,备份,MySQL,sql,mysql,root,centos8
From： https://www.cnblogs.com/Dxj01/p/18635194

MySQL 备份和恢复