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

2023-11-07 15:11•html•阅读 1829

以前一直做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 = 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 (设置导入的完整列表)

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;u;

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 = "1234";//解密密钥
                mb.Import();
            }
            catch (Exception e)
            {
                Console.WriteLine("恢复失败，请检查");
            }
         }

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

上一篇 »mysql sql语句大全 mysql sql语句大全
下一篇 »MySQL sql常用语句大全

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

相关推荐

mysql导入导出.sql文件备份还原数据库

MySql实现分页查询的SQL，mysql实现分页查询的sql语句

C#实现SQL数据库备份与恢复

SQL之二Linux中Oracle数据库备份及还原

SQL--数据库的备份和还原，mysql

mysql数据库之基础SQL语句/语法

数据库SQL语法到MySQL实操

SQL--数据库的备份和还原，mysql