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

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

}

}

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