运维技能杂记（二）：阿里云数据库备份文件本地恢复流程

发表于 | 分类于 ，

【引言】偶然的机会，阿里云的某个测试库被误删，线上恢复只能全库恢复，一来有数据丢失风险，二来又担心影响到在线业务，于是来了这么个曲线救国的招儿，将备份副本到本地恢复，然后单独对单个库进行导出同步到线上。


前言

  说实话，对这类工作，还真的是接触的机会少之又少，所以有机会做这么一次，也是一波三折，磁盘容量的预估，然后到source.list安装源的准备，实践中总会发现这样那样的问题。

环境准备

操作系统安装

  这次是借助Oracle的virtualbox来完成的操作系统安装，操作系统的版本选择了ubuntu18.04，实际安装过程很简单，也不用赘述，实在是不会的度娘一搜一大把的资料。

MySQL安装

  阿里云的MySQL是5.7的版本，据网上的资料推荐本地和阿里云安装相同版本的MySQL，否则会出现一些异常问题，这里因为我们就简单的做一个库的恢复，所以没有那么严谨，直接apt-get安装了应该是最新Release版本吧。

1
2
apt-get install mysql-server 
apt-get install libmysqlclient-dev

MySQL基础配置

1
2
3
4
5
6
7
8
9
10
11
12
13
# 首先打开MySQL的配置文件
vi /etc/mysql/mysql.conf.d/mysqld.cnf

# 然后将bind-address注释掉，不注释的话远程连接会报错误码
# bind-address = 127.0.0.1

# 重启服务
systemctl restart mysql

# 配置root密码和权限
root@ubuntu:/etc/mysql/mysql.conf.d# mysql -u root -p
mysql> update mysql.user set authentication_string=password('smart2018') where user='root'and Host = 'localhost';
mysql> grant all privileges on *.* to 'root'@'%' identified by 'smart2018';

安装Percona XtraBackup

参考网站

https://www.percona.com/doc/percona-xtrabackup/LATEST/installation/apt_repo.html

安装步骤

1
2
3
4
5
6
7
8
9
10
11
# Fetch the repository packages from Percona web:
root@ubuntu:~# wget https://repo.percona.com/apt/percona-release_0.1-6.$(lsb_release -sc)_all.deb

# Install the downloaded package with dpkg. To do that, run the following commands as root or with sudo:
root@ubuntu:~# sudo dpkg -i percona-release_0.1-6.$(lsb_release -sc)_all.deb

# Remember to update the local cache:
root@ubuntu:~# sudo apt-get update

# After that you can install the package:
root@ubuntu:~# sudo apt-get install percona-xtrabackup-24

恢复现网备份

  • 下载现网备份文件，一般来说是一个很大的压缩包（比如这里的文件名是：hins5069097_data_20180910120955.tar.gz）
  • 停止本地服务器的mysql服务：systemctl stop mysqld/mysql
  • 备份当前数据目录：mv /var/lib/mysql/ /var/lib/mysqlbak
  • 新建一个数据目录：mkdir /var/lib/mysql
  • 解压备份文件到数据目录：bash rds_backup_extract.sh -f hins5069097_data_20180910120955.tar.gz -C /var/lib/mysql
  • 执行恢复操作直至命令运行结束：innobackupex –defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf –apply-log /var/lib/mysql
  • 给数据目录赋权：chown -R mysql:mysql /var/lib/mysql
  • 启动服务：systemctl start mysqld/mysql
  • 导出库：mysqldump -uroot -psmart2018 test > test.dump
  • 导入库：mysql -uroot -psmart2018 test < test.dump
    【补充】： 点击下载rds_backup_extract.sh脚本

source.list参考

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
root@mysql:/tmp# cat /etc/apt/sources.list
deb http://archive.ubuntu.com/ubuntu bionic main
deb http://archive.ubuntu.com/ubuntu bionic-security main
deb http://archive.ubuntu.com/ubuntu bionic-updates main
deb http://mirrors.aliyun.com/ubuntu/ bionic main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-security main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-updates main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-proposed main restricted universe multiverse
deb http://mirrors.aliyun.com/ubuntu/ bionic-backports main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-security main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-updates main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-proposed main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ bionic-backports main restricted universe multiverse
root@mysql:/tmp#
------2019 Lin.C ------