c# 使用 MySqlBackup 备份或还原 MySql数据库_c# mysqlbackup.dll

使用MySqlBackup快速备份或还原 MySql数据库，原理是使用MySqlBackup.dll 中的备份和还原方法，将数据库的信息转换为对应的sql语句，然后进行处理。简单demo如下：

1.所需关键dll：MySql.Data.dll 和 MySqlBackup.dll
下载地址：
https://github.com/MySqlBackupNET/MySqlBackup.Net/tree/master/Precompiled%20Binaries/2.0.10

2.备份MySql数据库

 /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="cmdText">指定准备备份的文件名 以***.sql为标准</param>
        /// <returns></returns>
        public bool  BackupDB(string  cmdText)
        {
            bool isBack = false;
            try
            {
                MySqlConnection myconn = new MySqlConnection(ConnectString);
                if (myconn.State == ConnectionState.Closed)
                {
                    myconn.Open();
                }
                try
                {
                    if (string.IsNullOrEmpty(cmdText))
                    {
                        return isBack;
                    }

                    using (MySqlCommand cmmd = new MySqlCommand())
                    {
                        using (MySqlBackup backCmd = new MySqlBackup(cmmd))
                        {
                            cmmd.Connection = myconn;
                            cmmd.CommandTimeout = 60;
                            backCmd.ExportInfo.MaxSqlLength = 1024;//指定备份文件的大小
                            backCmd.ExportToFile(cmdText);
                            isBack = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.Error($"BackupDB_备份数据库异常 sql:{cmdText}. {ex.Message}" , "MYSQLIMPL");
                }
                finally
                {
                    if (myconn.State == ConnectionState.Open)
                    {
                        myconn.Close();
                        myconn.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"BackupDB_备份数据库异常。ex.Message}", "MYSQLIMPL");
            }
            return isBack;
        }
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

3.还原备份的数据库文件.sql重点内容*

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="strPath">指定还原文件***.sql的绝对路径</param>
        /// <param name="dbName">还原到指定数据库</param>
        /// <returns></returns>
        public bool RestoreDB(string strPath,string dbName)
        {
            bool isImport = false;
            try
            {
                MySqlConnection myconn = new MySqlConnection(ConnectString);
                if (myconn.State == ConnectionState.Closed)
                {
                    myconn.Open();
                }
                try
                {
                    if (string.IsNullOrEmpty(strPath))
                    {
                        return isImport;
                    }

                    using (MySqlCommand cmmd = new MySqlCommand())
                    {
                        using (MySqlBackup backCmd = new MySqlBackup(cmmd))
                        {
                            cmmd.Connection = myconn;
                            cmmd.CommandTimeout = 3600;
                            backCmd.ImportInfo.TargetDatabase = dbName;//前提条件 当前 myconn 中的用户有建库等系列权限
                            backCmd.ImportInfo.DatabaseDefaultCharSet = "utf8";
                            backCmd.ImportFromFile(strPath);
                            isImport = true;
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.Error($"ImportDB_还原数据库异常 sql:{strPath}. {ex.Message}", "MYSQLIMPL");
                }
                finally
                {
                    if (myconn.State == ConnectionState.Open)
                    {
                        myconn.Close();
                        myconn.Dispose();
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error($"ImportDB_还原数据库异常。{ex.Message}", "MYSQLIMPL");
            }
            return isImport;
        }
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

4.其它： 本文中的Logger为Log4的用法

参考文献：https://www.codeproject.com/Articles/256466/MySqlBackup-NET#_comments