我尝试将存储在AWS S3上的MySQL备份文件直接恢复到MySQL DB系统中
首先
在将存储在AWS上的MySQL数据库迁移至OCI的MySQL DB系统时，我们验证了在不移动存在于S3上的备份数据的情况下，直接将其恢复到MySQL DB系统的步骤。
这次我们在Oracle Autonomous Linux 7.9的Compute实例上安装并使用了”goofys”，可以将S3作为文件系统进行挂载。
1. 安装Goofys
由于goofys需要使用Go和fuse，因此需要使用yum命令安装golang和fuse。
[opc@compute1 ~]$ sudo yum install -y golang fuse
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package fuse.x86_64 0:2.9.4-1.0.9.el7 will be installed
---> Package golang.x86_64 0:1.9.4-1.el7 will be installed
--> Processing Dependency: golang-bin = 1.9.4-1.el7 for package: golang-1.9.4-1.el7.x86_64
--> Processing Dependency: golang-src = 1.9.4-1.el7 for package: golang-1.9.4-1.el7.x86_64
--> Running transaction check
---> Package golang-bin.x86_64 0:1.9.4-1.el7 will be installed
---> Package golang-src.noarch 0:1.9.4-1.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================
Package Arch Version Repository Size
===================================================================================================================
Installing:
fuse x86_64 2.9.4-1.0.9.el7 ol7_latest 88 k
golang x86_64 1.9.4-1.el7 ol7_optional_latest 610 k
Installing for dependencies:
golang-bin x86_64 1.9.4-1.el7 ol7_optional_latest 48 M
golang-src noarch 1.9.4-1.el7 ol7_optional_latest 5.1 M
Transaction Summary
===================================================================================================================
Install 2 Packages (+2 Dependent packages)
Total download size: 54 M
Installed size: 222 M
Downloading packages:
(1/4): fuse-2.9.4-1.0.9.el7.x86_64.rpm | 88 kB 00:00:01
(2/4): golang-1.9.4-1.el7.x86_64.rpm | 610 kB 00:00:01
(3/4): golang-src-1.9.4-1.el7.noarch.rpm | 5.1 MB 00:00:00
(4/4): golang-bin-1.9.4-1.el7.x86_64.rpm | 48 MB 00:00:02
-------------------------------------------------------------------------------------------------------------------
Total 20 MB/s | 54 MB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : golang-src-1.9.4-1.el7.noarch 1/4
Installing : golang-1.9.4-1.el7.x86_64 2/4
Installing : golang-bin-1.9.4-1.el7.x86_64 3/4
Installing : fuse-2.9.4-1.0.9.el7.x86_64 4/4
Verifying : golang-bin-1.9.4-1.el7.x86_64 1/4
Verifying : golang-1.9.4-1.el7.x86_64 2/4
Verifying : fuse-2.9.4-1.0.9.el7.x86_64 3/4
Verifying : golang-src-1.9.4-1.el7.noarch 4/4
Installed:
fuse.x86_64 0:2.9.4-1.0.9.el7 golang.x86_64 0:1.9.4-1.el7
Dependency Installed:
golang-bin.x86_64 0:1.9.4-1.el7 golang-src.noarch 0:1.9.4-1.el7
Complete!
[opc@compute1 ~]$
使用wget命令从GitHub下载goofys。
[opc@compute1 ~]$ wget https://github.com/kahing/goofys/releases/latest/download/goofys
--2022-02-24 01:01:15-- https://github.com/kahing/goofys/releases/latest/download/goofys
Resolving github.com (github.com)... 13.114.40.48
Connecting to github.com (github.com)|13.114.40.48|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github.com/kahing/goofys/releases/download/v0.24.0/goofys [following]
--2022-02-24 01:01:15-- https://github.com/kahing/goofys/releases/download/v0.24.0/goofys
Reusing existing connection to github.com:443.
HTTP request sent, awaiting response... 302 Found
Location: https://objects.githubusercontent.com/github-production-release-asset-2e65be/42475296/92e1ec00-768c-11ea-82dd-825b7e124210?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20220224%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220224T010115Z&X-Amz-Expires=300&X-Amz-Signature=8b8605da0670a0357a9d53da81ee05fbb010ae9b74465bf64dbfe51cef9b5f53&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=42475296&response-content-disposition=attachment%3B%20filename%3Dgoofys&response-content-type=application%2Foctet-stream [following]
--2022-02-24 01:01:15-- https://objects.githubusercontent.com/github-production-release-asset-2e65be/42475296/92e1ec00-768c-11ea-82dd-825b7e124210?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20220224%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20220224T010115Z&X-Amz-Expires=300&X-Amz-Signature=8b8605da0670a0357a9d53da81ee05fbb010ae9b74465bf64dbfe51cef9b5f53&X-Amz-SignedHeaders=host&actor_id=0&key_id=0&repo_id=42475296&response-content-disposition=attachment%3B%20filename%3Dgoofys&response-content-type=application%2Foctet-stream
Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.108.133, 185.199.109.133, 185.199.110.133, ...
Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.108.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 23296633 (22M) [application/octet-stream]
Saving to: ‘goofys’
100%[=========================================================================>] 23,296,633 7.01MB/s in 3.2s
2022-02-24 01:01:19 (7.01 MB/s) - ‘goofys’ saved [23296633/23296633]
[opc@compute1 ~]$ ls -l
total 22752
-rw-rw-r--. 1 opc opc 23296633 Dec 8 02:48 goofys
将下载的goofys移动到一个路径正确的目录（这次是/usr/local/bin），然后更改所有者和权限。
[opc@compute1 ~]$ sudo mv goofys /usr/local/bin
[opc@compute1 ~]$ sudo chown root:root /usr/local/bin/goofys
[opc@compute1 ~]$ sudo chmod 775 /usr/local/bin/goofys
创建用于S3访问的凭证文件。
创建用于S3访问的凭据文件”/root/.aws/credentials”。
[opc@compute1 ~]$ sudo vi /root/.aws/credentials
[aws]
aws_access_key_id = S3バケットにアクセス可能なユーザのアクセスキー
aws_secret_access_key = S3バケットにアクセス可能なユーザのシークレットキー
3. S3存储桶的挂载
创建将S3存储桶作为文件系统挂载的挂载点。这次选择了”/mnt_s3″作为挂载点。
[opc@compute1 ~]$ sudo mkdir -p /mnt_s3
在/etc/fstab中，添加用于使用goofys挂载S3存储桶的条目。
[opc@compute1 ~]$ sudo vi /etc/fstab
将以下内容添加进去。
这次将名为”s3bucketformds”的S3存储桶挂载到”/mnt_s3″。
goofys#s3bucketformds /mnt_s3 fuse _netdev,allow_other,--file-mode=0644,--uid=0,--gid=0,--region=ap-northeast-1,--profile=aws 0 0
使用“mount -a”命令将文件系统挂载。
[opc@compute1 ~]$ sudo mount -a
我将确认能否在S3的挂载点上创建文件。
[opc@compute1 mnt]$ cd /mnt_s3
[opc@compute1 mnt_s3]$ sudo touch test.txt
[opc@compute1 mnt_s3]$ ls -l
total 0
-rw-r--r--. 1 root root 0 Feb 24 01:18 test.txt
4. 安装MySQL客户端
使用yum命令来设置仓库。
[opc@compute1 ~]$ sudo yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
使用yum命令安装MySQL客户端。
[opc@compute1 ~]$ sudo yum install -y mysql-community-client
5. 使用MySQL客户端连接到MySQL数据库系统
使用mysql命令以admin用户身份连接到MySQL数据库系统。
[opc@compute1 ~]$ mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.28-u1-cloud MySQL Enterprise - Cloud
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
创建名为employees的数据库。
mysql> CREATE DATABASE employees;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| employees |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
关闭mysql。
mysql> exit
Bye
[opc@compute1 ~]$
备份文件准备
确认S3挂载点的内容。
[opc@compute1 ~]$ ls -l /mnt_s3/
total 164430
-rw-r--r--. 1 root root 168375940 Feb 24 05:32 employees.dmp
-rw-r--r--. 1 root root 0 Feb 24 01:18 test.txt
[opc@compute1 ~]$
确认了存在 employees.dmp。
7. 恢复存放在 S3 存储桶中的备份文件。
使用mysql命令将/mnt_s3中的employees.dmp文件恢复到MySQL数据库系统中。
[opc@compute1 ~]$ time mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com -t employees < /mnt_s3/employees.dmp
Enter password:
real 1m9.092s
user 0m2.026s
sys 0m0.167s
[opc@compute1 ~]$
使用mysql命令以admin用户身份连接到MySQL DB系统，并确认备份文件已正确恢复。
[opc@compute1 ~]$ mysql -u admin -p -h mysqlds1.subnet1.vcn1.oraclevcn.com
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.28-u1-cloud MySQL Enterprise - Cloud
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
将数据库切换为员工数据库。
mysql> use employees;
Database changed
mysql>
我要检查数据库中employees表。
mysql> show tables;
+----------------------+
| Tables_in_employees |
+----------------------+
| current_dept_emp |
| departments |
| dept_emp |
| dept_emp_latest_date |
| dept_manager |
| employees |
| salaries |
| titles |
+----------------------+
8 rows in set (0.00 sec)
mysql>
我会检查表格内的数据。
mysql> SELECT COUNT(*) FROM employees;
+----------+
| COUNT(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.02 sec)
mysql> SELECT COUNT(*) FROM departments;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.01 sec)
mysql>
我要关闭MySQL。
mysql> exit
Bye
[opc@compute1 ~]$
使用Goofys将S3存储桶挂载到文件系统，并直接从S3存储桶中恢复备份文件到MySQL数据库系统。
请参考以下信息
傻傻的
MySQL客户端命令
mysqldump – 数据库备份程序
