如何用SQL语句实现Mysql数据库的备份与还原

2023-08-15 13:12:04

以前一直做android客户端的项目，根本没有开发asp.net mvc的开发，现阶段做了一个模块，参数设置，以及数据库的备份与还原。其需求如下：

参数设置　　　本项参数设置为对自动数据备份进行设置，管理员可以对系统自动备份文件所存放的位置、自动备份的周期、自动备份的时间进行设定，并且可以开启或关闭自动备份功能。

数据备份　　　在本模块中，管理员可以通过录入系统备份文件的目标位置，对系统数据库进行备份，保证系统数据的安全性。每次备份都会记录在系统中，包含备份时间、备份位置及备份操作者。

数据还原　　　管理员通过选择曾经进行备份的数据库文件，在进行校对后，系统会进行数据库还原工作，本项功能用以数据灾难恢复，还原信息也会记录至系统中。今天主要给大家认识一下，如何通过SQL语句实现MYSql数据库的备份与还原。其实代码很简单，下来我们就直接看如何实现吧。首先你在项目中需要导入两个Dll包，Mysql.Data.dll和Mysqlbackup.dll. 下来来看代码

1.databackup（备份）

void Backup()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.Export();

}

2.dataRestore(还原)

void Restore()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ImportInfo.FileName = file;

    mb.Import();

}

3. Export and Compress as Zip File(出口和压缩,压缩文件)

void Backup()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.ZipOutputFile = true;

    mb.Export();

}

4. Select tables to backup(选择要备份表)

void BackupTables()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.TablesToBeExported = new string[] { "member", "activity", "season" };

    mb.Export();

}

5.Custom columns and rows backup conditions(自定义列和行备份条件)

void BackupTablesCustomSQL()

{

    Dictionary<string, string> dic = new Dictionary<string, string>();

    dic.Add("member", "SELECT * FROM `member` WHERE `membertype` = 1;");

    dic.Add("payment", "SELECT `id`,`total` FROM `payment`;");

    dic.Add("activity", "SELECT * FROM `activity` a INNER JOIN `season` s ON a.`seasonid` = s.`id` WHERE s.`name` = 'Spring';");

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.TableCustomSql = dic;

    mb.Export();

}

6.Backup with Encryption(备份和加密)

void BackupEncrypt()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.EnableEncryption = true;

    mb.ExportInfo.EncryptionKey = "my secret password";

    mb.Export();

}

7.Resotre with Decryption(还原与解密)

void RestoreDecrypt()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ImportInfo.FileName = file;

    mb.ImportInfo.EnableEncryption = true;

    mb.ImportInfo.EncryptionKey = "my secret password";

    mb.Import();

}

8.Encrypt & Decrypt a SQL Dump File(加密和解密SQL转储文件)

void EncryptDecryptDumpFile()

{

    MySqlBackup mb = new MySqlBackup();

    string file1 = "C:\\MyDumpFileOld.sql";

    string file2 = "C:\\MyDumpFileNew.sql";

    string file3 = "C:\\MyDumpFileBackToOri.sql";

    string password = "my secret password";

    mb.EncryptSqlDumpFile(file1, file2, password);

    mb.DecryptSqlDumpFile(file2, file3, password);

}

9.Backup Table Structures without rows and reset auto-increment to 1 Collapse | Copy Code (自动增量备份表结构没有行和重置为1 |复制代码崩溃)

void BackupNoRows()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.ExportRows = false;

    mb.ExportInfo.ResetAutoIncrement = true;

    mb.Export();

}

10.Full List of Settings for Export (Backup) (的完整列表设置出口)

void BackupNoRows()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ExportInfo.FileName = file;

    mb.ExportInfo.AddCreateDatabase = true;

    mb.ExportInfo.AsynchronousMode = true;

    mb.ExportInfo.AutoCloseConnection = true;

    mb.ExportInfo.CalculateTotalRowsFromDatabase = true;

    mb.ExportInfo.EnableEncryption = true;

    mb.ExportInfo.EncryptionKey = "my secret password";

    mb.ExportInfo.ExportEvents = true;

    mb.ExportInfo.ExportFunctions = true;

    mb.ExportInfo.ExportRows = true;

    mb.ExportInfo.ExportStoredProcedures = true;

    mb.ExportInfo.ExportTableStructure = true;

    mb.ExportInfo.ExportTriggers = true;

    mb.ExportInfo.ExportViews = true;

    mb.ExportInfo.MaxSqlLength = ;

    mb.ExportInfo.RecordDumpTime = true;

    mb.ExportInfo.ResetAutoIncrement = true;

    mb.ExportInfo.TableCustomSql = //Shown in example 4

    mb.ExportInfo.TablesToBeExported = //Shown in example 3

    mb.ExportInfo.ExportRows = false;

    mb.ExportInfo.ResetAutoIncrement = true;

    mb.ExportInfo.ZipOutputFile = false;

    mb.Export();

}

11.Full List of Settings for Import (设置导入的完整列表)

void Restore()

{

    string constr = "server=localhost;user=root;pwd=qwerty;database=test;";

    string file = "C:\\MyDumpFile.sql";

    MySqlBackup mb = new MySqlBackup(constr);

    mb.ImportInfo.FileName = file;

    mb.ImportInfo.AsynchronousMode = true;

    mb.ImportInfo.AutoCloseConnection = true;

    mb.ImportInfo.EnableEncryption = true;

    mb.ImportInfo.EncryptionKey = "my secret password";

    mb.ImportInfo.IgnoreSqlError = true;

    mb.ImportInfo.SetTargetDatabase("MyNewDatabase", ImportInformations.CharSet.latin1);

    mb.Import();

}

12.Export BLOB data and save as files on local drive (出口BLOB数据并保存为本地驱动器上的文件)

private void BackupEncrypt()

{

    string folder = "C:\\exportedfiles";

    string table = "upload";

    string column_Blob = "blobdata";

    string column_FileName = "filename";

    string column_FileSize = "filesize";

    string con = "server=localhost;user=root;pwd=qwerty;database=member;";

    MySqlBackup mb = new MySqlBackup(con);

    mb.ExportBlobAsFile(folder, table, column_Blob, column_FileName, column_FileSize);

}

13.Get all tables' name from database (从数据库获取所有表的名字)

private string[] GetTableNames()

{

    string con = "server=localhost;user=root;pwd=qwerty;database=test;";

    MySqlBackup mb = new MySqlBackup(con);

    return mb.DatabaseInfo.TableNames;

}

14.Get Create Table sql statement for specific table (为特定的表创建表的sql语句)

private string GetCreateTable(string tableName)

{

    string con = "server=localhost;user=root;pwd=qwerty;database=test;";

    MySqlBackup mb = new MySqlBackup(con);

    return mb.DatabaseInfo.Tables[tableName].CreateTableSql;

}

下面是我项目中写的备份与还原方法

//备份newtest database 里面所有的表

public void DataBackup()
{
try
{
//var time = DateTime.Now;
string file = "D:\\mybackup.sql";//备份的路径
string conn = "server=172.18.0.3;database=newtest;uid=root;pwd=123;";
MySqlBackup mb = new MySqlBackup(conn);
mb.ExportInfo.FileName = file;
mb.ExportInfo.EnableEncryption = true;//加密
mb.ExportInfo.EncryptionKey = "1234";//加密密钥
mb.ExportInfo.ZipOutputFile = true;//压缩
mb.Export();

}
catch (Exception e)
{
Console.WriteLine("备份失败，请检查");
}

}

//还原newtest database 里面所有的表（这里不要解压，解压是自动的）

 public void DataRestore()

         {

            try

            {

                string file = "D:\\mybackup.zip";//找到备份的路径所在的zip文件，进行还原恢复。

                string conn = "server=172.18.0.3;user=root;pwd=123;database=newtest;";

                MySqlBackup mb = new MySqlBackup(conn);

                mb.ImportInfo.FileName = file;

                mb.ImportInfo.EnableEncryption = true;//解密

                mb.ImportInfo.EncryptionKey = "";//解密密钥

                mb.Import();

            }

            catch (Exception e)

            {

                Console.WriteLine("恢复失败，请检查");

            }

         }

本文原创，请尊重劳动成果。也真诚希望和大家相互讨论，相互学习，相互提高。

码农公寓

相关文章