跳至主要內容

Mysql备份与恢复

zhengcog...大约 10 分钟数据库数据库mysql

本文主要记录在centos7.9备份mysql8.0方法

一 通过mysqldump备份

此备份为逻辑备份

mysqldump重要参数说明

  • --single-transaction:在备份开始前,先执行 START TRANSACTION命令,以此来获得备份的一致性,当前该参数只对 InnoDB存储引擎有效。当启用该参数并进行备份时,确保没有其他任何的DDL语句执行,因为一致性读并不能隔离DDL操作。
  • --lock-tables(-1):在备份中,依次锁住每个架构下的所有表。一般用于 MyIsAM存储引擎,当备份时只能对数据库进行读取操作,不过备份依然可以保证一致性。对于 InnoDB存储引擎,不需要使用该参数,用- - single-transaction即可。并且--lock-tables和--single-transaction是互斥( exclusive)的,不能同时使用。如果用户的 MySQL数据库中,既有 MyISAM存储引擎的表,又有 InnoDB存储引擎的表,那么这时用户的选择只有--lock-tables了。此外,正如前面所说的那样,--lock-tables选项是依次对每个架构中的表上锁的,因此只能保证每个架构下表备份的一致性,而不能保证所有架构下表的一致性。
  • --lock--all-tables(-x):在备份过程中,对所有架构中的所有表上锁。这个可以避免之前说的- lock-tables参数不能同时锁住所有表的问题。
  • -add-dop-database:在 CREATE DATABASE前先运行 DROP DATABASE。这个参数需要和--all-databases或者--databases选项一起使用。在默认情况下,导出的文本文件中并不会有 CREATE DATABASE,除非指定了这个参数。
  • --master-data[=value]:通过该参数产生的备份转存文件主要用来建立一个replication。当value的值为1时,转存文件中记录CHANGE MASTER语句。当value的值为2时, CHANGE MASTER语句被写出SQL注释。在默认情况下,value的值为空。当 value值为1时,在备份文件中会看到:CHANGE MASTER TO MASTER_LOG_FILE='xen-server-bin.000006',MASTER_LOG_POS=8095;当 value为2时,在备份文件中会看到 CHANGE MASTER语句被注释了。
  • --master-data:会自动忽略--lock-tables选项。如果没有使用--single-transaction选项,则会自动使用--lock-all-tables选项。
  • --events(-E):备份事件调度器。
  • --routines(-R):备份存储过程和函数。
  • --triggers:备份触发器。
  • --hex-blob:将 BINARY、 VARBINARY、BLOG和BIT列类型备份为十六进制的格式。 mysqldump导出的文件一般是文本文件,但是如果导出的数据中有上述这些类型,在文本文件模式下可能有些字符不可见,若添加--hex-blob选项,结果会以十六进制的方式显示。
  • --tab=path(- T path):产生TAB分割的数据文件。对于每张表, mysqldump创建一个包含 CREATE TABLE语句的 table_name. sql文件,和包含数据的tbl_name. txt文件。可以使用--fields-terminated-by=.…,--fields-enclosed-by=.…,--fields-optionally-enclosed-by=....,--fields-escaped-by=....,--lines-terminated-by=....来改变默认的分割符、换行符等。

备份过程

  1. 先发出一条 flush tables 关闭实例上所有打开的表
  2. 创建一个全局锁，FLUSH TABLES WITH READ LOCK获得 db 一致性状态。
  3. 设置事务隔离级别为 RR ，确保备份事务中任何时刻的数据都相同。
  4. 创建一个 RR 级别的事务一致性快照 ，执行START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
  5. 根据参数–master-data=2 打印 SHOW MASTER STATUS 获取文件名和位置点信息。
  6. 执行UNLOCK TABLES 释放锁。
  7. 在开始做所有的事情之前创建了一个事务的还原点，然后先备份一张表，然后再回到事务的还原点，再继续备份下一张表，一直这样重复直到所有的表备份完成。最后把事务还原点释放掉。然后把这个事务 rollback 掉就可以了。

优缺点

优点：
mysqldump的优点就是逻辑备份，把数据生成SQL形式保存，在单库，单表数据迁移，备份恢复等场景方便，SQL形式的备份文件通用，也方便在不同数据库之间移植。对于InnoDB表可以在线备份。

缺点：
mysqldump是单线程，数据量大的时候，备份时间长，甚至有可能在备份过程中非事务表长期锁表对业务造成影响(SQL形式的备份恢复时间也比较长)。mysqldump备份时会查询所有的数据，这可能会把内存中的热点数据刷掉

开始备份

新建mysqlbackup.sh文件，贴入以下内容，注意更换主机地址、帐号、密码

#!/bin/bash

# 备份数据库
database_name=$1

# 保存备份个数，备份31天数据
number=31

# 备份保存路径
backup_dir=/data/backup/mysql/$database_name

# 日期
dd=`date +%Y-%m-%d-%H-%M-%S`

# 备份工具
tool=mysqldump

# 主机地址
dbhost=127.0.0.1

# 端口
dbport=3306

# 用户名
username=你的数据库用户名

# 密码
password="你的数据库连接密码"

# 如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
mkdir -p $backup_dir
fi

# 开始备份
$tool -h$dbhost -P$dbport -u$username -p$password $database_name > $backup_dir/$database_name-$dd.sql

# 写创建备份日志
echo "create $backup_dir/$database_name-$dd.sql" >> $backup_dir/log.txt

# 找出要删除的备份
delfile=`ls -l $backup_dir/$database_name-*.sql|awk '{print $9}' |head -1`

# 判断现在的备份是否大于$number
count=`ls -l $backup_dir/$database_name-*.sql |awk '{print $9}' |wc -l`

if [ $count -gt $number ];
then
# 删除最早生成的备份，只保留number数量的备份
rm $delfile
# 写删除日志文件
echo "delete $delfile" >> $backup_dir/log.txt
fi

在linux上可以配置定时任务执行以上脚本，如：

chmod +x mysqlbackup.sh
0 2 * * * /root/mysqlbackup.sh

开始恢复数据

先命令行登录数据库

[root@server-test ~]# mysql -uroot -p

mysql> use 您的数据库名

mysql> source 备份文件路径

二 通过percona xtraBackup8.x备份

更多详细信息查看官网文档open in new window

安装

安装percona xtraBackup仓库

[root@server-test ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

执行安装

[root@server-test ~]# yum install percona-xtrabackup-80.x86_64

备份前准备

开启MySQL二进制日志

[root@server-test ~]# vi /etc/my.conf

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
log_bin=binlog
default-authentication-plugin=mysql_native_password

binlog为文件名，详细目录为：/var/lib/mysql/binlog.000001、/var/lib/mysql/binlog.index

命令行登录MySQL

[root@server-test ~]# mysql -uroot -p

mysql> show variables like 'log_bin'
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

出现以上结果表示，binlog已经开启

完整备份还原流程

备份

[root@server-test ~]# mkdir /data/backup/mysql/full/
[root@server-test ~]# xtrabackup --backup=1 --port=3306 --databases=testdb --target-dir=/data/backup/mysql/xtrabackup/full/ --user=root --password

执行以上命令后如果出现以下错误，按照下面的处理方法处理即可：

错误1:

failed to execute query 'LOCK INSTANCE FOR BACKUP' : 1227 (42000) Access denied; you need (at least one of) the BACKUP_ADMIN privilege(s) for this operation

处理方法：

登录mysql

[root@server-test ~]# mysql -uroot -p

mysql> grant BACKUP_ADMIN on *.* to 'root'@'%'
mysql> FLUSH PRIVILEGES;
mysql> exit

错误2：

Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup;port=3306','root',...) failed: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so

登录mysql

[root@server-test ~]# mysql -uroot -p

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '你的密码' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密码';
mysql> FLUSH PRIVILEGES;
mysql> exit

处理完上述错误后，重新执行备份命令即可

还原

先停止数据库

[root@server-test ~]# systemctl stop mysqld

模拟数据库损坏(慎用)

[root@server-test ~]# rm -rf /var/lib/mysql/*
[root@server-test ~]# rm -rf /var/log/mysqld.log

还原一致性检查

[root@server-test ~]# xtrabackup --prepare --target-dir=/data/backup/mysql/full/

开始恢复

[root@server-test ~]# xtrabackup --copy-back --target-dir=/data/backup/mysql/full/

还原后要给mysql用户再授权

[root@server-test ~]# chown -R mysql.mysql /var/lib/mysql
[root@server-test ~]# systemctl start mysqld

增量备份还原流程

备份

# 基于完整备份生成第一次增量备份。
[root@server-test ~]# xtrabackup -uroot -p'mysql-root用户密码' --backup --target-dir=/data/backup/mysql/inc1 --incremental-basedir=/data/backup/mysql/full
# 基于第一次增量备份生成第二次增量备份
[root@server-test ~]# xtrabackup -uroot -p'mysql-root用户密码' --backup --target-dir=/data/backup/mysql/inc2 --incremental-basedir=/data/backup/mysql/inc1

还原

# 先stop mysqld
[root@server-test ~]# systemctl stop mysqld
# 再进行一致性检查（先回滚全量包，再回滚第一次增量包，再回滚第二次增量包，这时增量数据都到了全量包中）【每次增量包都要回滚！】
[root@server-test ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full
[root@server-test ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full --incremental-dir=/data/backup/mysql/inc1
[root@server-test ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full --incremental-dir=/data/backup/mysql/inc2
# 回滚完成后，进行恢复
[root@server-test ~]#  xtrabackup --copy-back --target-dir=/data/backup/mysql/full
# 授权文件权限
[root@server-test ~]#  chown -R mysql.mysql /var/lib/mysql
# 重启服务
[root@server-test ~]# systemctl restart mysqld

差异备份还原流程

备份

# 基于完全备份生成第一次增量备份，与完全备份的差异就是差异备份
[root@server-test ~]# xtrabackup -uroot -p'mysql-root用户密码' --backup --target-dir=/data/backup/mysql/inc1 --incremental-basedir=/data/backup/mysql/full

# 仍是基于完全备份生成第二次增量备份，与完全备份的差异就是差异备份。（后续的差异备份仍是基于完全备份进行，以此类推。）
[root@server-test ~]# xtrabackup -uroot -p'mysql-root用户密码' --backup --target-dir=/data/backup/mysql/inc2 --incremental-basedir=/data/backup/mysql/inc1

如上所示，进行了两次差异备份，那么回滚不用像增量备份一样，每个增量包都需要回滚，只需回归完全备份包和最后一次差异备份包即可。

[root@server-test ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full
[root@server-test ~]# xtrabackup --prepare --apply-log-only --target-dir=/data/backup/mysql/full --incremental-dir=/data/backup/mysql/inc2

# 回滚完成后，进行恢复
[root@server-test ~]#  xtrabackup --copy-back --target-dir=/data/backup/mysql/full
# 授权文件权限
[root@server-test ~]#  chown -R mysql.mysql /var/lib/mysql
# 重启服务
[root@server-test ~]# systemctl restart mysqld

完整备份shell例子：

#!/bin/bash

# 备份数据库
database_name=$1

# --parallel:指定备份使用的线程数
bakthreads=4

# 保存备份个数，备份31天数据
number=31

# 备份保存路径
backup_dir=/data/backup/mysql/xtrabackup/full/$database_name

# 日期
dd=`date +%Y-%m-%d-%H-%M-%S`

# 备份工具
tool=xtrabackup

# 主机地址
dbhost=127.0.0.1

# 端口
dbport=3306

# 用户名
username=你的数据库用户名

# 密码
password="你的数据库连接密码"

# 如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
mkdir -p $backup_dir
fi

# 开始备份
$tool --backup=1 --host=$dbhost --port=$dbport --user=$username --password=$password --databases=$database_name --target-dir=$backup_dir/$dd --parallel=$bakthreads

# 写创建备份日志
echo "create $backup_dir/$dd" >> $backup_dir/log.txt

# 找出要删除的备份
delfile=`ls -l $backup_dir |grep "^d" |awk '{print $NF}' |head -1`

# 判断现在的备份是否大于$number
count=`ls -l $backup_dir |grep "^d" |awk '{print $NF}' |wc -l`

if [ $count -gt $number ];
then
# 删除最早生成的备份，只保留number数量的备份
rm -rf $backup_dir/$delfile
# 写删除日志文件
echo "delete $backup_dir/$delfile" >> $backup_dir/log.txt
fi

三 二进制日志备份与恢复

修改配置文件启用二进制日志

[root@server-test ~]# vi /etc/my.conf

[mysqld]
log-bin=mysql-bin
sync_binlog=1
innodb_support_xa=1

备份二进制日志

在备份二进制日志文件前,可以通过 FLUSH LOGS命令来生成一个新的二进制日志文件,然后备份之前的二进制日志。

恢复二进制日志

[root@server-test ~]# mysqlbinlog [options] log file

# 例如要还原binlog.000001,执行如下命令
[root@server-test ~]# mysqlbinlog binlog.000001 | mysql -uroot -p

# 如果需要恢复多个二进制日志文件,最正确的做法应该是同时恢复多个二进制日志文件,而不是一个一个地恢复,如:
[root@server-test ~]# mysqlbinlog binlog.[0-10]* | mysql -uroot -p

# 也可以先通过 mysqlbinlog命令导出到一个文件,然后再通过 SOURCE命令来导人,这种做法的好处是可以对导出的文件进行修改后再导入,如
[root@server-test ~]# mysqlbinlog binlog.000001 > /tmp/test.sql
[root@server-test ~]# mysqlbinlog binlog.000002 >> /tmp/test.sql
[root@server-test ~]# mysql -u root -p -e "source /tmp/statements.sql"

# --start-position和--stop-position选项可以用来指定从二进制日志的某个偏移量来进行恢复,这样可以跳过某些不正确的语句,如:
[root@server-test ~]# mysqlbinlog --start-position 12345 binlog.0000001 | mysql-uroot -p
# --start-datetime和--stop-datetime选项可以用来指定从二进制日志的某个时间点来进行恢复,用法和--start-position和--stop-position选项基本相同。
上次编辑于:
贡献者: Hyman
0  字
评论
  • 按正序
  • 按倒序
  • 按热度
Powered by Waline v2.15.5