以前一直做android客户端的项目,根本没有开发asp.net mvc的开发,现阶段做了一个模块,参数设置,以及数据库的备份与还原。其需求如下:
参数设置 本项参数设置为对自动数据备份进行设置,管理员可以对系统自动备份文件所存放的位置、自动备份的周期、自动备份的时间进行设定,并且可以开启或关闭自动备份功能。
数据备份 在本模块中,管理员可以通过录入系统备份文件的目标位置,对系统数据库进行备份,保证系统数据的安全性。每次备份都会记录在系统中,包含备份时间、备份位置及备份操作者。
数据还原 管理员通过选择曾经进行备份的数据库文件,在进行校对后,系统会进行数据库还原工作,本项功能用以数据灾难恢复,还原信息也会记录至系统中。 今天主要给大家认识一下,如何通过SQL语句实现MYSql数据库的备份与还原。 其实代码很简单,下来我们就直接看如何实现吧。 首先你在项目中需要导入两个Dll包,Mysql.Data.dll和Mysqlbackup.dll. 下来来看代码
1.databackup(备份)
voidBackup()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.Export();
}
2.dataRestore(还原)
voidRestore()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ImportInfo.FileName=file;
mb.Import();
}
3. Export and Compress as Zip File(出口和压缩,压缩文件)
voidBackup()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.ExportInfo.ZipOutputFile= true;
mb.Export();
}
4. Select tables to backup(选择要备份表)
voidBackupTables()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.ExportInfo.TablesToBeExported= new string[] { "member", "activity", "season"};
mb.Export();
}
5.Custom columns and rows backup conditions(自定义列和行备份条件)
voidBackupTablesCustomSQL()
{
Dictionary dic = new Dictionary();
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= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.ExportInfo.TableCustomSql=dic;
mb.Export();
}
6.Backup with Encryption(备份和加密)
voidBackupEncrypt()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.ExportInfo.EnableEncryption= true;
mb.ExportInfo.EncryptionKey= "my secret password";
mb.Export();
}
7.Resotre with Decryption(还原与解密)
voidRestoreDecrypt()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(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转储文件)
voidEncryptDecryptDumpFile()
{
MySqlBackup mb= newMySqlBackup();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 |复制代码崩溃)
voidBackupNoRows()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(constr);
mb.ExportInfo.FileName=file;
mb.ExportInfo.ExportRows= false;
mb.ExportInfo.ResetAutoIncrement= true;
mb.Export();
}
10.Full List of Settings for Export (Backup) (的完整列表设置出口)
voidBackupNoRows()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(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= 10000000;
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 (设置导入的完整列表)
voidRestore()
{string constr = "server=localhost;user=root;pwd=qwerty;database=test;";string file = "C:\\MyDumpFile.sql";
MySqlBackup mb= newMySqlBackup(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 voidBackupEncrypt()
{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= newMySqlBackup(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= newMySqlBackup(con);returnmb.DatabaseInfo.TableNames;
}
14.Get Create Table sql statement for specific table (为特定的表创建表的sql语句)
private string GetCreateTable(stringtableName)
{string con = "server=localhost;user=root;pwd=qwerty;database=test;";
MySqlBackup mb= newMySqlBackup(con);returnmb.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 voidDataRestore()
{try{string file = "D:\\mybackup.zip";//找到备份的路径所在的zip文件,进行还原恢复。string conn = "server=172.18.0.3;user=root;pwd=123;database=newtest;";
MySqlBackup mb= newMySqlBackup(conn);
mb.ImportInfo.FileName=file;
mb.ImportInfo.EnableEncryption= true;//解密
mb.ImportInfo.EncryptionKey= "1234";//解密密钥
mb.Import();
}catch(Exception e)
{
Console.WriteLine("恢复失败,请检查");
}
}
本文原创,请尊重劳动成果。也真诚希望和大家相互讨论,相互学习,相互提高。