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

时间:2021-07-08 14:53:45

以前一直做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("恢复失败,请检查");
}
}

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