dotnet core 实现MySQL数据库备份和还原

某个小项目需要支持一下 MySQL 全库备份，

本来想着要不做个从库之类的完事了，

但是本来数据库也就几万条数据，

搞这玩意实在是蛋疼。

又想着要不直接启定时任务，

用 MySQL dump 命令行折腾一下算了，

但是….

还是觉得懒。

要不看下有没有人封了库？

SO

找到了…

代码

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192


Backup/Export a MySQL Database
string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
conn.Open();
mb.ExportToFile(file);
conn.Close();
}
}
}

Import/Restore a MySQL Database

string constring = "server=localhost;user=root;pwd=qwerty;database=test;";
string file = "C:\\backup.sql";
using (MySqlConnection conn = new MySqlConnection(constring))
{
using (MySqlCommand cmd = new MySqlCommand())
{
using (MySqlBackup mb = new MySqlBackup(cmd))
{
cmd.Connection = conn;
conn.Open();
mb.ImportFromFile(file);
conn.Close();
}
}
}

代码写得很清晰，反正抄一下就好。

生产可用代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;

namespace DatabasePro.Service
{

    public class BackFileInfo
    {
        public int Id { get; set; }

        public string FileName { get; set; }

        public string FilePath { get; set; }

        public string FileSize { get; set; }

        public string FileSizeMB
        {
            get
            {
                var size = Convert.ToDouble(FileSize);
                return (size / 1024 / 1024).ToString("0.00");
            }
        }

        public string CreateTime { get; set; }

    }

    public class DBBackService : IService
    {
        private AppConfiguration configuration;

        private ILogger<DBBackService> _logger;



        public DBBackService(IOptions<AppConfiguration> options, ILogger<DBBackService> logger)
        {
            configuration = options.Value;
            _logger = logger;
        }


        public string ExportToFile()
        {
            string baseDir = configuration.StoredFilesPath + "/backup";
            _logger.LogInformation("Start backup database.");
            var conString = configuration.QCloudMySQL;
            var today = DateTime.Now;
            string importFileName = $"{baseDir}/{today.ToString("yyyyMMdd_HHmm")}.sql";
            _logger.LogInformation($"Backup file name: {importFileName}");

            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                _logger.LogInformation($"ConnectionTimeout:{conn.ConnectionTimeout}");
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandTimeout = 3600;
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        mb.ExportToFile(importFileName);
                        conn.Close();
                    }
                }
            }
            _logger.LogInformation($"Backup database to {importFileName} successfully.");
            return importFileName;
        }

        public string ImportFromFile(string filePath)
        {


            var conString = configuration.QCloudMySQL;
            _logger.LogInformation($"ImportFromFile filePath: {filePath} start.");
            using (MySqlConnection conn = new MySqlConnection(conString))
            {
                using (MySqlCommand cmd = new MySqlCommand())
                {
                    cmd.CommandTimeout = 3600;
                    using (MySqlBackup mb = new MySqlBackup(cmd))
                    {
                        cmd.Connection = conn;
                        conn.Open();
                        mb.ImportFromFile(filePath);
                        conn.Close();
                    }
                }
            }
            _logger.LogInformation($"ImportFromFile {filePath} successfully, user: {userInfo?.UserName}");
            return filePath;
        }

        public void DeleteFile(string filePath)
        {
            if (string.IsNullOrEmpty(filePath))
            {
                return;
            }
            if (!filePath.Contains("backup"))
            {
                _logger.LogInformation($"DeleteFile filePath: {filePath} not in backup folder, ignore.");
                return;
            }

            if (System.IO.File.Exists(filePath))
            {
                System.IO.File.Delete(filePath);
            }
            _logger.LogInformation($"DeleteFile {filePath} successfully, user: {userInfo?.UserName}");
        }
        public QueryResult<BackFileInfo> LoadBackupList()
        {
            var fileList = new List<BackFileInfo>();
            string baseDir = configuration.StoredFilesPath + "/backup";
            _logger.LogInformation("Start load backup list.");
            if (!System.IO.Directory.Exists(baseDir))
            {
                System.IO.Directory.CreateDirectory(baseDir);
            }
            var files = System.IO.Directory.GetFiles(baseDir);
            var index = 1;
            foreach (var file in files)
            {
                var fileInfo = new System.IO.FileInfo(file);
                var backFileInfo = new BackFileInfo();
                backFileInfo.Id = index;
                backFileInfo.FileName = fileInfo.Name;
                backFileInfo.FilePath = file;
                backFileInfo.FileSize = fileInfo.Length.ToString();
                backFileInfo.CreateTime = fileInfo.CreationTime.ToString("yyyy-MM-dd HH:mm:ss");
                fileList.Add(backFileInfo);
                index = index + 1;
            }
            var result = new QueryResult<BackFileInfo>();
            result.list = fileList.OrderByDescending(x => x.CreateTime).ToList();
            result.total = fileList.Count;
            return result;
        }
    }

}

注释也没写，反正没几行代码。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

using Microsoft.AspNetCore.Mvc;
using DatabasePro.Filters;
using DatabasePro.RestAPI.Helpers;
using DatabasePro.Service;
using Swashbuckle.AspNetCore.Annotations;

namespace DatabasePro.Controllers
{
[ApiController]
public class DBBackController : ApiControllerBase
{
private readonly DBBackService \_dbBackService;

        public DBBackController(DBBackService dbBackService)
        {
            _dbBackService = dbBackService;
        }


        /// <summary>
        ///
        /// </summary>
        [HttpGet]
        [Route("/v1/admin/db-back-list")]
        [ServiceFilter(typeof(AdminTokenAttribute))]
        [SwaggerResponse(200, "successfully.")]
        public IActionResult LoadBackupList()
        {
            var fileList = _dbBackService.LoadBackupList();
            return Wrapper(fileList);
        }

        /// <summary>
        /// 导出到备份文件
        /// </summary>
        [HttpGet]
        [Route("/v1/admin/db-back-export")]
        [ServiceFilter(typeof(AdminTokenAttribute))]
        public IActionResult ExportToFile()
        {
            var backSQLFile = _dbBackService.ExportToFile();
            return Ok(new { data = backSQLFile, code = 0 });
        }

        /// <summary>
        /// 导入备份文件
        /// </summary>
        [HttpGet]
        [Route("/v1/admin/db-back-import")]
        [ServiceFilter(typeof(AdminTokenAttribute))]
        public IActionResult ExportToFile([FromQuery] string filePath)
        {
            var backSQLFile = _dbBackService.ImportFromFile(filePath);
            return Ok(new { data = backSQLFile, code = 0 });
        }

        /// <summary>
        /// 删除备份文件
        /// </summary>
        [HttpDelete]
        [Route("/v1/admin/db-back-delete")]
        [ServiceFilter(typeof(AdminTokenAttribute))]
        public IActionResult DeleteFile([FromQuery] string filePath)
        {
            _dbBackService.DeleteFile(filePath);
            return Ok(new { data = new { }, code = 0 });
        }


    }

}

接口大概是上面，自己照着调整一下完事。

糊个页面

大概，就这样。

彩蛋时间 可能会遇到 MySQL 连接超时错误。

看情况设置一下”connect-timeout“ 参数。

connect-timeout=3600

导入数据的时候，可能会遇到”mysql Packets larger than max_allowed_packet are not allowed.“

需要调整 ”max_allowed_packet“ 参数。

max-allowed-packet=1073741824

如果你的 MySQL 也是 docker-compose 部署的，可以参考这个~

      args:
        - --lower-case-table-names=1
        - --max-connections=4000
        - --connect-timeout=3600
        - --max-allowed-packet=1073741824
        - --sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

还有彩蛋

如果你想实现一个定时备份，

在 dotnet core 中可以用 IHostService 实现，

代码大概是

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71


using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.Hosting;
using System.Threading;

namespace DatabasePro.Service
{

    public class DBBackupTimeHostedService : IHostedService, IDisposable
    {
        private readonly DBBackService _dbBackService;

        private Timer _timer;

        public DBBackupTimeHostedService(DBBackService dBBackService)
        {
            _dbBackService = dBBackService;
        }

        public Task StartAsync(CancellationToken cancellationToken)
        {
            // 启动定时器
            _timer = new Timer(DoWork, null, GetDelayTo3AM(), TimeSpan.FromDays(1));
            Serilog.Log.Information($"DBBackupTimeHostedService: StartAsync");
            return Task.CompletedTask;
        }

        private void DoWork(object state)
        {
            try
            {
                var filePath = _dbBackService.ExportToFile();
                Serilog.Log.Information($"DBBackupTimeHostedService: DoWork finish, filePath:{filePath}");
            }
            catch (Exception ex)
            {
                Serilog.Log.Error($"DBBackupTimeHostedService: DoWork error, {ex.Message}");
            }
        }

        public Task StopAsync(CancellationToken cancellationToken)
        {
            // 停止定时器
            _timer?.Change(Timeout.Infinite, 0);
            return Task.CompletedTask;
        }

        public void Dispose()
        {
            _timer?.Dispose();
        }

        // 北京时间凌晨3点执行（UTC 19点）
        private TimeSpan GetDelayTo3AM()
        {
            DateTime nowInUTC = DateTime.UtcNow;
            DateTime next7PMInUTC = nowInUTC.Date.AddHours(19);

            if (nowInUTC.Hour >= 19)
            {
                next7PMInUTC = next7PMInUTC.AddDays(1);
            }
            return next7PMInUTC - nowInUTC;
        }
    }

}
services.AddHostedService<DBBackupTimeHostedService>();

完事。

结尾

简单粗暴的方案总有风险，

苟住苟住~

Dotnet Core
Licensed under CC BY-NC-SA 4.0
最后更新于 Oct 26, 2023 23:09 UTC