备份本地mysql数据到Ucloud存储,支持STANDARD, IA, ARCHIVE 标准存储,低频存储(IA)或者冷存储(ARCHIVE) 这3中存储类型

#注意如果,欲使用低频存储(IA)或者冷存储(ARCHIVE),请在命令参数storageclass中指定,支持三种值:STANDARD, IA, ARCHIVE #注意如果,备份时指定了storageclass参数为ARCHIVE,需要提前对该文件restore ./filemgr-linux64 --action restore --bucket <bucketName> --key <backupKey>

一、演示环境:

centos7.6 X86_64位最小化安装 mysql为5.7.28 二进制版本安装 提前在服务器本地安装filemgr命令 安装配置过程参考: https://blog.51cto.com/wujianwei/2497929

提前在服务器上准备好链接Ucloud存储的配置文件: [root@mysql-redis105 ~]# cat /data/soft/linux64/beijing-config.cfg { "public_key" : "TOKEN_3816a393-5f22-4bc4-a0a4-71a6a2e4", "private_key" : "af6ecb83-6f34-4bc0-9bcb-880100db5", "proxy_host" : "www.cn-bj.ufileos.com", "api_host" : "api.spark.ucloud.cn" }

二、本地mysqldump逻辑备份到远程: # 全库备份

  mysqldump -A | ./filemgr-linux64 --action stream-upload --bucket <bucketName> --key <all-backupKey> --file stdin --threads <threads> --retrycount <retry> --storageclass <storage-class>
   	

#分库备份

mysqldump -B database1 database2 | ./filemgr-linux64 --action stream-upload --bucket <bucketName> --key <part-backupKey> --file stdin --threads <threads> --retrycount <retry> --storageclass <storage-class>

**说明:**备份文件存储在ucloud存储的文件 wbvs_dbbak 下的2020-05-24.db.sql文件 wbvs_dbbak 这个文件要提前在Ucloud控制台提前创建

备份多个库到远程存储:

[root@mysql-redis linux64]# mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db gogs |filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-upload --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/2020-05-24.db.sql  --file stdin  --retrycount 10 --storageclass IA
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
2020/05/24 14:35:03.016310 [INFO]StreamUpload blk size:  4194304
2020/05/24 14:35:03.034545 [INFO]part[1] size[46797]
2020/05/24 14:35:03.607199 [INFO]part[0] upload done [ok]
2020/05/24 14:35:03.791041 [INFO]StreamUpload success!!!

恢复远程存储备份的文件2020-05-24.db.sql 到本地库中:

[root@mysql-redis ~]#  filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-download --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/2020-05-24.db.sql --file stdout  --threads 1 --retrycount 2|mysql 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

恢复报错,原因是本地服务器库开启了Gtid,备份本地数据库并存储到远程云存储时,mysqldump没有关闭关闭gtid,致使gtid信息存储到了备份的库文件2020-05-24.db.sql 中,所以在恢复2020-05-24.db.sql 数据到本地库时,会把2020-05-24.db.sql 文件中的gtid信息也恢复到本地库里面,从而导致报错

下面是正确mysqldump逻辑备份和恢复的姿势:

mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db gogs --single-transaction --set-gtid-purged=OFF |filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-upload --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/`date +%F`.db.sql    --retrycount 10 --storageclass IA

**提示:**在mysqldump的过程中 --file stdin 参数可加可不加

[root@mysql-redis ~]#  mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db gogs --single-transaction --set-gtid-purged=OFF |filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-upload --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/`date +%F`.db.sql    --file stdin  --retrycount 10 --storageclass IA
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2020/05/25 16:35:20.193048 [INFO]StreamUpload blk size:  4194304
2020/05/25 16:35:20.208247 [INFO]part[1] size[46445]
2020/05/25 16:35:21.713947 [INFO]part[0] upload done [ok]
2020/05/25 16:35:21.879204 [INFO]StreamUpload success!!!

回复到线程服务器库里面:

[root@mysql-redis ~]# filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-download --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/`date +%F`.db.sql --file stdout  --threads 1 --retrycount 2 2>./error.log  |mysql 
[root@mysql-redis ~]# echo $?
0
恢复完成:
[root@mysql-redis ~]# mysql -e "show databases"|egrep 'gogs|test00001db'
gogs
test00001db

三、压缩备份到远程存储:

下面是官方给出的样例:

#压缩 备份和恢复
    #备份
    mysqldump -A | gzip | ./filemgr-linux64 --action stream-upload --bucket <bucketName> --key <all-backupKey> --file stdin --threads <threads> --retrycount <retry> --storageclass <storage-class>   
 #恢复
    ./filemgr-linux --action stream-download --bucket <bucketName> --key <all-backupKey> --threads <threads> --retrycount <retry> 2>./error.log | gzip -d | mysql

** 线上正确的姿势:**

[root@mysql-redis ~]#  mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db gogs --single-transaction --set-gtid-purged=OFF |gzip |filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-upload --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/`date +%F`.db.sql.gz   --file stdin  --retrycount 10 --storageclass IA
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2020/05/25 16:54:13.321441 [INFO]StreamUpload blk size:  4194304
2020/05/25 16:54:13.324886 [INFO]part[1] size[7760]
2020/05/25 16:54:14.570615 [INFO]part[0] upload done [ok]
2020/05/25 16:54:14.844144 [INFO]StreamUpload success!!!

[root@mysql-redis ~]# filemgr -config /data/soft/linux64/beijing-config.cfg --action stream-download --bucket wbtdsp-cdn-beijing --key wbvs_dbbak/`date +%F`.db.sql.gz --file stdout  --threads 1 --retrycount 2 2>./error.log  |gzip -d|mysql

四、加密备份和恢复 下面是官方给出的样例,加密备份是不正确的,测试过程中一直云存储端一直报400错误,目前ucloud官网不支持下面的加密备份指令的,而且官网一直没给出解决方案 # 备份,使用aes256,指定密码文件key file在备份路径中进行压缩 mysqldump -A | openssl enc -e -aes256 -in - -out - -kfile <key file> | ./filemgr-linux64 --action stream-upload --bucket <bucketName> --key <all-backupKey> --file stdin --threads <threads> --retrycount <retry> --storageclass <storage-class> # 恢复 ./filemgr-linux --action stream-download --bucket <bucketName> --key <all-backupKey> --threads <threads> --retrycount <retry> 2>./error.log | openssl enc -d -aes256 -in - -out - -kfile <key file> | mysql

虽然官方给出上面的方式不支持,但是经过测试下面的方式是可以实现的:

mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db |gzip - | openssl enc -e -aes256  -a -salt   -k manager1    -out 2.sql.gz.aes 

对上面的指令简单描述:

-B  test00001db  指定备份单个库
gzip -  对指定备份test00001db 库进行压缩备份
openssl enc -e -aes256  -a -salt  :采用openssl -aes256的加密算法方式对gzip 压缩备份出来的内容进行加密
-k manager1  : 指定openssl 加密过程中提示要输入加密密码的密码文件。此文件manager1  存放加密密码,在解密数据备份文件2.sql.gz.aes 时要用到,所以这个密码文件千万不能丢。
 -out 2.sql.gz.aes  :把压缩备份出的数据内容经过加密后,输出保存在文件2.sql.gz.aes  中。这个文件加密后内容就是一堆字符字母。
-kfile manager  指定加密过程中存放密码的文件

下面是正确的操作样例:

加密:
root@mysql-redis scripts]# mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db |gzip - | openssl enc -e -aes256  -a -salt   -kfile /data/scripts/manager  -out 3.sql.gz.aes
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

[root@mysql-redis ~]# cat  /data/scripts/manager 
STDwdsuyd12345678
解密:
-kfile manager  指定解密过程中存放密码的文件:
openssl enc -d -aes256  -a -salt -kfile  /data/scripts/manager -in 3.sql.gz.aes  -out 3.sql.gz

把加密备份的文件远程同步到ucloud云存储中:

 mysqldump -uroot -p'rRt&8UiJpN3' -B test00001db |gzip - | openssl enc -e -aes256  -a -salt   -kfile /data/scripts/manager -out /data/backup/dbbackup/3.sql.gz.aes|filemgr -config /data/soft/linux64/beijing-config.cfg --action sync  --dir ./dbbackup   --trimpath /data/backup/ --bucket wbtdsp-cdn-beijing 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
2020/05/26 15:08:22.106045 [INFO]Syncing...
2020/05/26 15:08:22.387666 [INFO]	Sync Successed[ /data/backup/dbbackup/3.sql.gz.aes ] => wbtdsp-cdn-beijing : 3.sql.gz.aes
2020/05/26 15:08:22.410341 [INFO]	Sync Successed[ /data/backup/dbbackup/test/3.sql.gz.aes ] => wbtdsp-cdn-beijing : test/3.sql.gz.aes
========================
|      succ|      fail|
------------------------
|         2|         0|
========================

解密:把存放在云存储的文件3.sql.gz.aes 下载到服务器本地进行解密

下面是解密过程:

manager  文件为存放加密过程的密码
[root@mysql-redis ~]# cat  /data/scripts/manager 
STDwdsuyd12345678
-kfile manager  指定解密过程中存放密码的文件:
openssl enc -d -aes256  -a -salt -kfile /data/scripts/manager -in 3.sql.gz.aes  -out 3.sql.gz
 openssl enc -d -aes256  -a -salt -kfile /data/scripts/manager -in /data/backup/dbbackup/test/3.sql.gz.aes  -out 3.sql.gz

备份全库机密后到远程云存储:

[root@mysql-redis backup]#  mysqldump -uroot -p'rRt&8UiJpN3' -A |gzip - | openssl enc -e -aes256  -a -salt -kfile /data/scripts/manager -out /data/backup/dbbackup/test/3.sql.gz.aes&&filemgr -config /data/soft/linux64/beijing-config.cfg --action sync  --dir ./dbbackup   --trimpath /data/backup/ --bucket wbtdsp-cdn-beijing 
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
2020/05/26 15:18:26.216807 [INFO]Syncing...
2020/05/26 15:18:29.047693 [INFO]	Sync Successed[ /data/backup/dbbackup/test/3.sql.gz.aes ] => wbtdsp-cdn-beijing : test/3.sql.gz.aes
========================
|      succ|      fail|
------------------------
|         1|         0|
========================

以上远程备份本地mysql到云存储简单介绍到此处,欢迎一起交流学些