版本:mysql8.0
官方文档 :MySQL :: MySQL 8.0 Reference Manual :: 7 Backup and Recovery
1.物理备份恢复
物理备份是以数据文件形式备份。这种方式效率高点,适合大型数据库备份。物理备份可冷备可热备。
使用mysqlbackup
命令进行物理备份,非常遗憾 mysqlbackup命令只在mysql企业版才有。
还可以使用第三方软件xtrabackup进行物理热备
我的mysql版本是8.0.35
XtraBackup 2.4 与较新版本的 MySQL 5.7 兼容
XtraBackup 8.0.34 允许备份MySQL 8.0.35 和 MySQL 8.0.35 及更高版本。
1.0 准备工作
创建一个有备份相关权限的mysql用户 ,用于XtraBackup备份
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';
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> GRANT SELECT ON performance_schema.keyring_component_status TO 'bkpuser'@'localhost';
mysql> GRANT SELECT ON performance_schema.replication_group_members TO 'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;
查看用户权限
SHOW GRANTS FOR 'bkpuser'@'localhost';
1.1 Percona XtraBackup安装
使用yum安装
1.安装Percona yum仓库
sudo yum install \ https://repo.percona.com/yum/percona-release-latest.\ noarch.rpm
2.启动仓库
sudo percona-release enable-only tools release
3.安装 Percona XtraBackup
sudo yum install percona-xtrabackup-80
4.安装压缩算法
sudo yum install zstd
1.2 完整备份
xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/ --user=bkpuser --password=123456
xtrabackup默认会读取mysql配置文件/etc/my.cnf,但是我服务器中有多个mysql,我想要指定备份mysql就需要参数--defaults-file=指定配置文件
1.3 增量备份
在 /data/backups/完全备份的基础上进行增量备份
xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/ --user=bkpuser --password=123456
在 /data/backups/inc1/增量备份的基础上进行增量备份
xtrabackup --defaults-file=/etc/my8.cnf --backup --target-dir=/data/backups/inc2 --incremental-basedir=/data/backups/inc1/ --user=bkpuser --password=123456
1.4 压缩备份和解压
压缩
xtrabackup --defaults-file=/etc/my8.cnf --backup --compress --compress-threads=4 --compress-zstd-level=1 --target-dir=/data/backups2/ --user=bkpuser --password=123456
--compress:Zstandard (ZSTD)算法进行压缩
--compress-threads=4:使用四个线程进行压缩
--compress-zstd-level=1:指定压缩级别,默认1
解压
xtrabackup --decompress --target-dir=/data/backups4/
--decompress:解压
默认解压不会删除压缩文件,他会把压缩文件和解压好的文件放在一起
可以使用--remove-original选项解压后删除压缩文件
1.5 备份恢复
在恢复之前一定要先准备备份(Prepare backup)
1.5.1 准备备份
关于Prepare backup的说明可以参考「XtraBackup」- 备份数据的“预备”(Prepare)操作 @20210220_xtrabackup --prepare-CSDN博客
XtraBackup 复制 InnoDB 数据文件,导致数据内部不一致;然后,该阶段对文件执行崩溃恢复,以再次创建一致、可用的数据库 。
1.5.1.1 准备完整备份
xtrabackup --prepare --target-dir=/data/backups/
1.5.1.1 准备增量备份
/data/backups8/base 基础备份
/data/backups8/inc1 base为基础进行的增量备份
/data/backups8/inc2 inc2为基础进行的增量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
xtrabackup --prepare --apply-log-only --target-dir=/data/backups8/base --incremental-dir=/data/backups8/inc1
xtrabackup --prepare --target-dir=/data/backups/base --incremental-dir=/data/backups/inc2
--apply-log-only:防止回滚,增量恢复一定要加上这个选项,最后一个准备备份不用加这个选项
执行完准备备份后,增量备份文件都整合到基础备份中了,恢复时直接把基础备份复制到数据目录中进行恢复。
1.5.2 还原备份
1.关闭数据库
2.清空原数据库的数据目录
3.把备份目录复制到数据目录
xtrabackup --defaults-file=/etc/my8.cnf --copy-back --target-dir=/data/backups8/base
或者使用 rsync 或 cp 来恢复文件
3.修改数据目录的归属权
chown mysql8:mysql8 /opt/mysql/mysql8/data -R
4.启动数据库
2.逻辑备份恢复
逻辑备份是把数据转成SQL格式文件备份。这种方式转换需要耗时,效率较低,适合小中型数据库。优点是灵活,可以备份指定数据库、指定表等。移植性也好,恢复时不用一定要和备份时的mysql版本一样。逻辑备份只能热备。
2.1 使用mysqldump进行备份
2.1.1 以 SQL 格式转储数据
备份所以数据库
mysqldump --all-databases > dump.sql
备份指定数据库
mysqldump --databases db1 db2 db3 > dump.sql
备份单个数据库
方式一: 输出包含 create database 和 use 语句。
mysqldump --databases test > dump.sql
方式二:没有 --databases,输出不包含 create database 和 use 语句,在恢复时需要主动 create database 和 use 数据库。
mysqldump test > dump.sql
2.1.2 以 SQL 格式恢复数据
使用 --databases的数据备份文件数据恢复
方法一:
mysql < dump.sql
方法二:
mysql> source dump.sql
如果没有使用 --databases的数据备份文件数据恢复
方法一:
先创建数据库
mysqladmin create db1
再加载转储文件
mysql db1 < dump.sql
方法二:
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;文章来源:https://www.toymoban.com/news/detail-741012.html
mysql> source dump.sql
2.1.3 以分隔文本格式转储数据
备份test数据库到 /opt/mysql/mysql8/下
mysqldump -uroot -p -P 3308 -S /tmp/mysql3.sock --tab=/opt/mysql/mysql8 test
注意:如果出现错误
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'
需要在my.cnf配置文件下[mysqld]下添加secure-file-priv = /opt/mysql/mysql8表示这个位置可以放分隔文本格式转储数据文件,记得还要用chonw更换这个目录的用户所属权成mysql用户
这个命令将生成两个文件
这个命令还有很多其他选项指定数据存储文本的格式
mysqldump -uroot -p -P 3308 -S /tmp/mysql3.sock --tab=/opt/mysql/mysql8 --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a test
生成数据文本
"2","cxk"
"1","hwz"
--fields-terminated-by=str
用于分隔列值的字符串 (默认值: tab)
--fields-enclosed-by=char
将列值括起来的字符(默认值: 无字符)
--fields-optionally-enclosed-by=char
要将非数字列括起来的字符值 (默认值:无字符)
--fields-escaped-by=char
用于转义特殊字符的字符(默认值: 否) 转义)
--lines-terminated-by=str
行终止字符串 (默认值: 换行符)
2.1.4 以分隔文本格式数据恢复
方法一:
mysql db1 < t1.sql
mysqlimport db1 t1.txt
方法二:
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1; 文章来源地址https://www.toymoban.com/news/detail-741012.html
到了这里,关于mysql简单备份和恢复的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!