欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

阿里云CentOS自动备份MySql 8.0并上传至七牛云

程序员文章站 2022-11-24 09:02:12
本文主要介绍一下阿里云CentOS7下如何对MySql 8.0数据库进行自动备份,并使用.NET Core 将备份文件上传至七牛云存储上,并对整个过程所踩的坑加以记录. 环境、工具、准备工作 服务器:阿里云64位CentOS 7.4.1708版本;并安装MySql 8.0 数据库(如何安装点击) 客 ......

本文主要介绍一下阿里云centos7下如何对mysql 8.0数据库进行自动备份,并使用.net core 将备份文件上传至七牛云存储上,并对整个过程所踩的坑加以记录.

环境、工具、准备工作

  • 服务器:阿里云64位centos 7.4.1708版本;并安装mysql 8.0 数据库(如何安装点击)
  • 客户端:windows 10;
  • sftp客户端:filezilla;用来进行文件传输;
  • ssh工具:putty;用来在windows 上远程访问centos;

编写shell脚本实现自动备份mysql数据库

1.创建相关目录

使用mkdir命令创建两个目录,一个用来存放shell和上传七牛云的app;命令如下

mkdir /home/software/mysql/backupapp      //此目录用来存放相关shell和应用程序
mkdir /home/software/mysql/backupfiles    //此目录用来存放mysql的备份文件

2.编写mysql备份shell

  •  使用命令进如shell存放的目录,命令如下:
    cd /home/software/mysql/backupapp
  • 创建备份的shell文件,命令如下:
    vi backup.sh //vi命令学习
  • 编写mysql备份shell命令,内容如下:
    db_user="dbuser"       //输入你的数据库用户名 
    db_password="password" //输入你的数据库密码
    db_name="dbname" //输入你要备份的数据库名 mysqldump -u$db_user -p$db_password $db_name | gzip > /home/software/mysql/backupfiles/$(date +%y%m%d%h%m%s).sql.gz
    //将导出的脚本按照年月日时分秒命名并压缩成gz文件,要保证/home/software/mysql/backupfiles存在,并注意脚本不能在windows环境下编写,注意脚本的空格与相关字符的全半角.
  • 测试脚本是否正常,命令如下:

    sh /home/software/mysql/backupapp/backup.sh 
    //没有错误信息或者提示 using a password on the command line interface can be insecure 表示正常
    cd /home/software/mysql/backupfile //进入备份文件目录并使用ls命令查看是否备份成功
  • 设置shell的权限并设置可运行,命令如下:

    chmod 777 /home/software/mysql/backupapp/backup.sh    //chmod命令学习

3.设置crontab实现自动执行shell备份数据库

  •  创建定时任务使备份脚本能定时运行,命令如下:
    crontab -e  //进入调度器编辑窗口
    //编写如下命令,此命令表示每天凌晨01:00分执行脚本backup.sh crontab命令学习
    0 1 * * * /home/software/mysql/backupapp/backup.sh
    //注意事项:保证/home/software/mysql/backupapp/backup.sh至少有读和可执行权限
    //为方便测试可设置10 * * * * 表示每10分钟执行一次shell
  • 重启crontab使命令生效,命令如下:
    systemctl restart crond

开发app实现将数据库备份文件上传至七牛云

1.注册七牛云(已注册可忽略)

因为七牛云免费提供10g的对象存储空间,所以本文选择七牛云做远程备份.相关注册操作请跳转至并创建存储空间.

2.新建控制台应用程序编写代码实现上传功能

使用.net core创建一个console应用程序,并使用nuget安装newtonsoft.json和qiniu.shared;上传功能代码如下:

using microsoft.extensions.configuration;
using qiniu.common;
using qiniu.http;
using qiniu.io;
using qiniu.io.model;
using qiniu.util;
using system;
using system.io;
using system.runtime.interopservices;

namespace qiniumanagerapp
{
    internal class program
    {
        private const string accesskey = "";//设置七牛accesskey
        private const string secretkey = "";设置七牛secretkey
        private const string bucket = "";//设置你的bucket,就是你新建的存储空间名称

        private static void main(string[] args)
        {
            var systempath = environment.currentdirectory;
            if (runtimeinformation.isosplatform(osplatform.linux))
            {
                systempath = path.getdirectoryname(typeof(program).assembly.location);
            }
            var logpath = systempath + @"/logs/";
            var uploadbackuppath = systempath + @"/filebackup/";
            deletefile(uploadbackuppath, 7);
            var config = new configurationbuilder()
                          .setbasepath(systempath)
                          .addjsonfile("appsettings.json")
                          .build();
            //读取配置
            var filepath = config["filepath"];
            if (!string.isnullorempty(filepath))
            {
                console.writeline("当前文件路径:" + filepath);
                var logname = datetime.now.tostring("yyyy-mm-dd") + ".txt";
                if (!file.exists(logpath + logname))
                {
                    file.create(logpath + logname);
                }
                using (streamwriter sw = new streamwriter(logpath + logname, true))
                {
                    try
                    {
                        var files = directory.getfiles(filepath);
                        if (files.length > 0)
                        {
                            sw.writeline("[start]=============================================================================================================================");
                            for (int i = 0; i < files.length; i++)
                            {
                                fileinfo file = new fileinfo(files[i]);
                                if (file.extension == ".gz")
                                {
                                    sw.writeline($"[datetime]:  {datetime.now.tostring("yyyy-mm-dd hh:mm:ss.ffff")}");
                                    var reslut = string.empty;
                                    if (uploadfile(file.directoryname + @"/", file.name, out reslut))
                                    {
                                        sw.writeline($"[filename]:{file.name} upload successfully.");
                                        console.writeline($"[filename]:{file.name} upload successfully.");
                                        file.moveto(uploadbackuppath + @"/" + file.name);
                                    }
                                    else
                                    {
                                        sw.writeline($"[filename]:{file.name} upload unsuccessfully.");
                                        console.writeline($"[filename]:{file.name} upload unsuccessfully.");
                                        sw.writeline($"[ result ]:{reslut}");
                                    }
                                }
                                sw.writeline();
                            }
                            sw.writeline("[ end ]=============================================================================================================================");
                        }
                    }
                    catch (exception ex)
                    {
                        sw.writeline("[exception]=============================================================================================================================");
                        sw.writeline(ex.message);
                        sw.writeline("[exception]=============================================================================================================================");
                        throw;
                    }
                }
                console.writeline("上传完成!");
            }
        }

        private static bool uploadfile(string filepath, string filename, out string error)
        {
            mac mac = new mac(accesskey, secretkey);
            // 设置上传策略,详见:https://developer.qiniu.com/kodo/manual/1206/put-policy
            putpolicy putpolicy = new putpolicy();
            // 设置要上传的目标空间
            putpolicy.scope = bucket;
            // 上传策略的过期时间(单位:秒)
            putpolicy.setexpires(3000);
            // 生成上传token
            string token = auth.createuploadtoken(mac, putpolicy.tojsonstring());
            // 设置上传区域
            config.setzone(zoneid.cn_north, false);
            // 表单上传
            formuploader target = new formuploader();
            // 上传文件
            var filebytes = file.readallbytes(filepath + filename);
            httpresult result = target.uploaddata(filebytes, filename, token);
            error = result.tostring();
            if (result.code == 200)
            {
                return true;
            }
            return false;
        }

        private static void deletefile(string filepath, int createddays)
        {
            if (!directory.exists(filepath))
            {
                directory.createdirectory(filepath);
            }
            directoryinfo di = new directoryinfo(filepath);
            var files = di.getfiles();
            for (int i = 0; i < files.length; i++)
            {
                if (files[i].creationtime >= datetime.now.adddays(createddays))
                {
                    files[i].delete();
                }
            }
        }
    }
}

//appsetting.json的内容,文件要新建,默认console程序没有该文件
{
  "filepath": "/home/software/mysql/backupfiles"//设置你mysql备份文件所在路径
}

注意事项:

  • 注意设置七牛云相关配置如accesskey ,secretkey ,bucket 选择正确的上传区域;
  • 发布程序时,需要将console依赖的相关文件都拷贝至应用程序根目录下(此处可能是.net core的一个bug);
  • 注意区分获取程序根目录的方式(在linux服务器和window 服务器获取方式不一样)

3.将console部署至阿里云centos并进行测试

  • 将程序部署至centos(参见此文或),目录路径为:/home/software/mysql/backupapp/qiniuapp
  • 使用chmod命令,设置console程序的可执行权限;命令如下:
    chmod 777 /home/software/mysql/backupapp/qiniuapp/qiniumanagerapp.dll
    
  • 测试程序;命令如下:
    dotnet /home/software/mysql/backupapp/qiniuapp/qiniumanagerapp.dll
  • 进入七牛站点,查看文件是否上传成功;
  • 编写app shell脚本;内容如下:
    cd /home/software/mysql/backupapp 
    vi backupapp.sh
    //编写如下内容:
    dotnet /home/software/mysql/backupapp/qiniuapp/qiniumanagerapp.dll

4.设置crond 实现自动将备份文件上传至七牛云

  •  创建定时任务使备份app能定时运行,命令如下:
    crontab -e  //进入调度器编辑窗口
    //编写如下命令
    10 1 * * * /home/software/mysql/backupapp/backupapp.sh
    //注意事项:保证/home/software/mysql/backupapp/backupapp.sh至少有读和可执行权限
  • 重启crontab使命令生效,命令如下:
    systemctl restart crond

 

至此,阿里云centos自动备份mysql 8.0并上传至七牛云 已经结束;

如果这篇博文对你有所帮助,麻烦动动手指点个赞^-^

作者:
    
出处:
    
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。