devops+备份mysql_MySQL备份和还原

MySQL备份和还原

Percona XtraBackup

很多DBA以及阿里云的RDS都是基于Percona XtraBackup进行物理备份的. 但是此工具跟谁MySQL的版本更新也是分版本的.

MySQL 5.6及之前的版本需要安装 Percona XtraBackup 2.3, 安装指导请参见官方文档Percona XtraBackup 2.3.

MySQL 5.7版本需要安装 Percona XtraBackup 2.4，安装指导请参见官方文档Percona XtraBackup 2.4。

MySQL 8.0版本需要安装 Percona XtraBackup 8.0，安装指导请参见官方文档Percona XtraBackup 8.0。

安装解压缩功能根据qpress

wget "http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/183466/cn_zh/1608011575185/qpress-11-linux-x64.tar"

tar xvf qpress-11-linux-x64.tar

chmod 775 qpress

cp qpress /usr/bin

目前物理备份集文件有3种格式：

tar 压缩包 (.tar.gz 后缀)

xbstream 压缩包 (.xb.gz 后缀)

xbstream 文件包(_qp.xb 后缀)

对于tar 压缩包 (.tar.gz 后缀)，使用命令:

tar -izxvf -C /home/mysql/data

对于xbstream 压缩包 (.xb.gz 后缀)，使用命令：

gzip -d -c | xbstream -x -v -C /home/mysql/data

对于xbstream 文件包(_qp.xb 后缀)，使用命令：

## 解包

cat | xbstream -x -v -C /home/mysql/data

## MySQL 5.6/5.7解压

innobackupex --decompress --remove-original /home/mysql/data

## MySQL 8.0解压

xtrabackup --decompress --remove-original --target-dir=/home/mysql/data

执行如下命令，恢复解压好的备份文件:

## MySQL 5.6/5.7

innobackupex --defaults-file=/home/mysql/data/backup-my.cnf --apply-log /home/mysql/data

## MySQL 8.0

xtrabackup --prepare --target-dir=/home/mysql/data

xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/home/mysql/data

若系统返回如下报错，可以用rm -rf /var/lib/mysql命令清空文件夹内文件，然后用chown -R mysql:mysql /var/lib/mysql修改权限。

为避免版本问题，需修改backup-my.cnf参数，具体操作步骤如下。

执行如下命令，以文本方式编辑backup-my.cnf文件

vi /home/mysql/data/backup-my.cnf

添加如下参数：

lower_case_table_names=1

注释掉如下自建数据库不支持的参数：

#innodb_log_checksum_algorithm

#innodb_fast_checksum

#innodb_log_block_size

#innodb_doublewrite_file

#innodb_encrypt_algorithm

#rds_encrypt_data

#redo_log_version

#master_key_id

#server_uuid

执行如下命令，修改文件属主，并确定文件所属为MySQL用户:

chown -R mysql:mysql /home/mysql/data

执行如下命令，启动MySQL进程:

mysqld --defaults-file=/home/mysql/data/backup-my.cnf --user=mysql --datadir=/home/mysql/data &

执行如下命令，登录MySQL数据库以验证进程启动成功:

mysql -u -p

MySQLdump备份和还原

在自建数据库中创建迁移账号，命令如下:

CREATE USER ''@'%' IDENTIFIED BY '';

CREATE USER 'migrate'@'%' IDENTIFIED BY 'Migrate123';

在自建数据库授权迁移账号SELECT和REPLICATION SLAVE权限，命令如下：

GRANT SELECT ON . TO ''@'%' WITH GRANT OPTION;

GRANT REPLICATION SLAVE ON . TO ''@'%' WITH GRANT OPTION;

如果为该账号所有的数据库或表授权，可以使用通配符*。

如果为该账号授权所有的权限，可以用ALL表示所有权限。

GRANT ALL ON *.* TO 'migrate'@'%' WITH GRANT OPTION;

使用mysqldump导出自建数据库的数据、存储过程、触发器和函数, 导出期间请勿进行数据更新.

i. 导出自建数据库的数据，命令如下:

mysqldump -h -u -p --opt --default-character-set=utf8 --hex-blob --skip-triggers --skip-lock-tables > /tmp/.sql

mysqldump -h 127.0.0.1 -u migrate -p --opt --default-character-set=utf8 --hex-blob testdb --skip-triggers --skip-lock-tables > /tmp/testdb.sql

ii. 导出存储过程、触发器和函数，命令如下：

mysqldump -h -u -p --opt --default-character-set=utf8 --hex-blob -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/Trigger.sql

mysqldump -h 127.0.0.1 -u migrate -p --opt --default-character-set=utf8 --hex-blob testdb -R | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > /tmp/testdbTrigger.sql

将导出的文件导入到目标RDS中，命令如下：

mysql -h -u -p < /tmp/.sql

mysql -h -u -p < /tmp/Trigger.sql

mysql -h rm-bpxxxxx.mysql.rds.aliyuncs.com -u testuser -p testdb < /tmp/testdb.sql

mysql -h rm-bpxxxxx.mysql.rds.aliyuncs.com -u testuser -p testdb < /tmp/testdbTrigger.sql

PS:

Q：OPERATION need to be executed set by ADMIN报错怎么解决？

A：可能是SQL脚本里面包括视图，触发器，存储过程等对象的definer问题，或者含有set global类SQL导致。详情请参见RDS MySQL出现“OPERATION need to be executed set by ADMIN”报错。

Q：Access denied; you need (at least one of) the SUPER privilege(s) for this operation报错怎么解决？

SQL脚本里面包括SUPER权限的语句，将相关语句删除再执行。