c#mysql数据库备份还原

mysql数据库备份还原

数据备份是数据安全的最后一道防线，对于任何数据丢失的场景，备份虽然不一定能恢复百分之百的数据(取决于备份周期)，但至少能将损失降到最低。

VS引用dll

需要用到dll程序包

nuget > MySql.Data.dll

nuget > MySqlbackup.dll

连接静态类

新建一个连接字符串静态类

public static class mysql
{
public static string constr = "database=test;Password=密码;user ID=root;server=ip地址";
public static MySqlConnection conn = new MySqlConnection(constr);
}

数据备份

DialogResult result = MessageBox.Show("备份路径默认在当前程序下", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if(result == DialogResult.Yes)
{
    string time1 = System.DateTime.Now.ToString("d").Replace("/", "-");
    string file = ".//mysql/" + time1 + "_test.sql";
    using(MySqlCommand cmd = new MySqlCommand())
    {
        using(MySqlBackup mb = new MySqlBackup(cmd))
        {
            cmd.Connection = mysql.conn;
            mysql.conn.Open();
            mb.ExportToFile(file);
            mysql.conn.Close();
            MessageBox.Show("已备份");
        }
    }
}

备份还原

string file = textBox1.Text;
if(file == "")
{
    MessageBox.Show("不能为空");
    return;
}
DialogResult result = MessageBox.Show("确定还原吗？", "还原", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if(result == DialogResult.Yes)
{
    try
    {
        using(MySqlCommand cmd = new MySqlCommand())
        {
            using(MySqlBackup mb = new MySqlBackup(cmd))
            {
                cmd.Connection = mysql.conn;
                mysql.conn.Open();
                mb.ImportFromFile(file);
                mysql.conn.Close();
                MessageBox.Show("已还原");
            }
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

定时备份

//winform
timer1.Interval = 1000; //代表一秒运行一次
timer1.Enabled = true; //启动

利用winform窗体 timer定时器控件

private void timer1_Tick(object sender, EventArgs e)
{
    if(booql)
    {
        booql = false;
        if(DateTime.Now.Hour == 10 && DateTime.Now.Minute == 00) //时间10点 
        {
            string time1 = System.DateTime.Now.ToString("d").Replace("/", "-");
            string file = ".//mysql/" + time1 + "_test.sql";
            using(MySqlCommand cmd = new MySqlCommand())
            {
                using(MySqlBackup mb = new MySqlBackup(cmd))
                {
                    cmd.Connection = mysql.conn;
                    mysql.conn.Open();
                    mb.ExportToFile(file);
                    mysql.conn.Close();
                    MessageBox.Show("数据库已自动备份本地");
                }
            }
        }
    }
}