Winform中使用mysqldump实现选择部分表定期备份mysql数据库

场景

Winform中实现与Mysql8建立连接并获取所有的表，然后勾选指定的表，定时进行数据库备份导出为sql文件。并且可以设定覆盖备份的次数，比如设置为7，则备份到第8次时就会将第一次备份的sql文件替换掉。

比如将mysql中的部分表备份近一个月的数据，每天备份一次。

注：

博客：
https://blog.csdn.net/badao_liumang_qizhi
关注公众号
霸道的程序猿
获取编程相关电子书、教程推送与免费下载。

实现

1、设计Winform的页面布局如下

2、首先实现与数据库建立连接

Winform中连接Mysql8并查询表中数据进行显示：

https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/120395988

在上面实现连接Mysql8数据库。

3、获取mysqldump.exe的路径

这里的路径是带双引号的，因为路径中有空格，然后获取全路径也是为了保证cmd中执行mysqldump的通用性，因为不一定都将其添加进环境变量。

4、选择备份文件的路径

        private void button_select_path_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog path = new FolderBrowserDialog();
            path.ShowDialog();
            this.textBox_bak_path.Text = path.SelectedPath;
        }

5、获取所有表名

        private void button_getAllTableNames_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                DataGridViewColumn checkCol = new DataGridViewCheckBoxColumn();
                checkCol.Name = "选择";
                this.dataGridView_show_tables_name.Columns.Add(checkCol);
                DataTable tbName = mySqlConnection.GetSchema("Tables");
                if (tbName.Columns.Contains("TABLE_NAME"))
                {
                    foreach (DataRow dr in tbName.Rows)
                    {
                        tableNameList.Add((string)dr["TABLE_NAME"]);
                    }
                }
                this.dataGridView_show_tables_name.DataSource = this.tableNameList.Select(x => new { Value = x }).ToList();
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
        }

这里为了防止按钮误操作，添加了密码校验，实现方式参考

Winform中实现点击按钮弹窗输入密码验证通过后执行相应逻辑：

https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/120565920

6、全选功能实现

添加一个checkbox并重写其checkchanged事件

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (this.checkBox_select_all.Checked == true)
            {
                for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
                {
                    this.dataGridView_show_tables_name.Rows[i].Cells["选择"].Value = 1;
                }
            }
            else
            {
                for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
                {
                    this.dataGridView_show_tables_name.Rows[i].Cells["选择"].Value = 0;
                }
            }
        }

7、页面添加获取覆盖循环的次数和定时器执行的秒数

8、定时器启动

        private void button3_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                string tableName = this.text_one_table.Text.Trim();
                string bakPath = this.textBox_bak_path.Text.Trim();
                if (String.IsNullOrEmpty(tableName))
                {
                    MessageBox.Show("表名不能为空！！！");
                }
                else if (String.IsNullOrEmpty(mysqlDumpPath))
                {
                    MessageBox.Show("mysqldump的路径不能为空！！！");
                }
                else if (String.IsNullOrEmpty(bakPath))
                {
                    MessageBox.Show("备份文件的路径不能为空！！！");
                }
                else
                {
                    decimal interval = this.time_interval.Value * 1000;
                    _timer.Interval = (int)interval;
                    _timer.Tick += _timer_Tick;
                    _timer.Start();
                }

            }
            else
            {
                MessageBox.Show("密码不正确");
            }

        }

实现逻辑是

验证密码-获取需要参数并验证是否为空-获取定时器执行的间隔数-设置定时器执行的事件-启动定时器

其中设置定时器执行的事件中

     private void _timer_Tick(object sender, EventArgs e)
        {
           
            this.log_text.AppendText("定时任务执行开始,执行时间:" + DateTime.Now.ToString());
            this.log_text.AppendText("
");
            this.BackupDB();
            int count = this.log_text.Lines.GetUpperBound(0);
            this.log_text.AppendText("count="+count);
            this.log_text.AppendText("
");
            this.log_text.AppendText("定时任务执行结束,执行时间:" + DateTime.Now.ToString());
            this.log_text.AppendText("
");
            if (count>=500)
            {
                this.log_text.Clear();
            }

        }

向一个TextBox中追加日志，并判断日志大于500行之后清理日志。

然后具体执行备份的方法是BackupDB

        public void  BackupDB()

        {
            this.bakCycleCount = (int)this.numericUpDown_cycle_count.Value;
            this.selectedTableNameList.Clear();
            for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
            {
                if ((bool)this.dataGridView_show_tables_name.Rows[i].Cells["选择"].EditedFormattedValue == true)
                {
                    selectedTableNameList.Add(this.dataGridView_show_tables_name.Rows[i].Cells[1].Value.ToString());
                }
            }
            for (int i = 0; i < this.selectedTableNameList.Count; i++)
            {
                string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                string tableName = this.selectedTableNameList[i];
                string cmdStr = mysqlDumpPath + " -h " + this.host.Text.Trim() + " -u" + this.username.Text.Trim() + " -p" + this.password.Text.Trim() + " " + this.database.Text.Trim() + " " + tableName + " > " + """ + this.textBox_bak_path.Text.Trim() + "\" + tableName + "_" + currentBakCount + ".sql"";
                CmdHelper.ExeCommand(cmdStr);
                this.log_text.AppendText(tableName + "_" + currentBakCount + "--备份完成,时间：" + DateTime.Now.ToString());
                this.log_text.AppendText("
");
                //休眠1秒
                Thread.Sleep(1000);
            }
            currentBakCount++;
            if (currentBakCount == bakCycleCount+1)
            {
                currentBakCount = 1;
            }
        }

在此方法中，获取选中的表名，然后循环这些表名进行备份

拼接成cmd命令，然后单个表进行备份，执行完一个表备份后休眠一秒。

比如执行一个表叫bus_area,那么设定的覆盖次数为7的话，就会出现

bus_area_1.sql、bus_area_2.sql一直到bus_area_7.sql然后重新覆盖bus_area_1.sql

这其中执行cmd命令的工具类为

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace mysqldatabak
{
    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Diagnostics;

    namespace Helper
    {
        /// <summary>
        /// 执行命令
        /// </summary>
        public class CmdHelper
        {
            ///
            /// 执行cmd.exe命令
            ///
            ///命令文本
            /// 命令输出文本
            public static string ExeCommand(string commandText)
            {
                return ExeCommand(new string[] { commandText });
            }
            ///
            /// 执行多条cmd.exe命令
            ///
            ///命令文本数组
            /// 命令输出文本
            public static string ExeCommand(string[] commandTexts)
            {
                Process p = new Process();
                p.StartInfo.FileName = "cmd.exe";
                p.StartInfo.UseShellExecute = false;
                p.StartInfo.RedirectStandardInput = true;
                p.StartInfo.RedirectStandardOutput = true;
                p.StartInfo.RedirectStandardError = true;
                p.StartInfo.CreateNoWindow = true;
                string strOutput = null;
                try
                {
                    p.Start();
                    foreach (string item in commandTexts)
                    {
                        p.StandardInput.WriteLine(item);
                    }
                    p.StandardInput.WriteLine("exit");
                    strOutput = p.StandardOutput.ReadToEnd();
                    //strOutput = Encoding.UTF8.GetString(Encoding.Default.GetBytes(strOutput));
                    p.WaitForExit();
                    p.Close();
                }
                catch (Exception e)
                {
                    strOutput = e.Message;
                }
                return strOutput;
            }
            ///
            /// 启动外部Windows应用程序，隐藏程序界面
            ///
            ///应用程序路径名称
            /// true表示成功，false表示失败
            public static bool StartApp(string appName)
            {
                return StartApp(appName, ProcessWindowStyle.Hidden);
            }
            ///
            /// 启动外部应用程序
            ///
            ///应用程序路径名称
            ///进程窗口模式
            /// true表示成功，false表示失败
            public static bool StartApp(string appName, ProcessWindowStyle style)
            {
                return StartApp(appName, null, style);
            }
            ///
            /// 启动外部应用程序，隐藏程序界面
            ///
            ///应用程序路径名称
            ///启动参数
            /// true表示成功，false表示失败
            public static bool StartApp(string appName, string arguments)
            {
                return StartApp(appName, arguments, ProcessWindowStyle.Hidden);
            }
            ///
            /// 启动外部应用程序
            ///
            ///应用程序路径名称
            ///启动参数
            ///进程窗口模式
            /// true表示成功，false表示失败
            public static bool StartApp(string appName, string arguments, ProcessWindowStyle style)
            {
                bool blnRst = false;
                Process p = new Process();
                p.StartInfo.FileName = appName;//exe,bat and so on
                p.StartInfo.WindowStyle = style;
                p.StartInfo.Arguments = arguments;
                try
                {
                    p.Start();
                    p.WaitForExit();
                    p.Close();
                    blnRst = true;
                }
                catch
                {
                }
                return blnRst;
            }
        }

    }
}

完整示例代码

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Threading;
using System.Windows.Forms;
using System.Windows.Forms.VisualStyles;
using MySql.Data.MySqlClient;
using mysqldatabak.Helper;

namespace mysqldatabak
{
    public partial class start_timer : Form
    {
        string connetStr = String.Empty;
        MySqlConnection mySqlConnection = null;
        String hostaddress = String.Empty;
        String databaseName = String.Empty;
        String name = String.Empty;
        String pass= String.Empty;
        List<string> tableNameList = new List<string>();
        List<string> selectedTableNameList = new List<string>();
        int bakCycleCount = 7;
        int currentBakCount = 1;
        //定时器
        System.Windows.Forms.Timer _timer = new System.Windows.Forms.Timer();
        public start_timer()
        {
            InitializeComponent();
        }

        private void connection_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                hostaddress = this.host.Text.Trim();
                databaseName = this.database.Text.Trim();
                name = this.username.Text.Trim();
                pass = this.password.Text.Trim();
                connetStr = "server=" + hostaddress + ";User Id=" + name + ";password=" + pass + ";database=" + databaseName; //localhost不支持ssl连接时，最后一句一定要加！！！
                mySqlConnection = new MySqlConnection(connetStr);
                try
                {
                    mySqlConnection.Open(); //连接数据库
                    MessageBox.Show("数据库连接成功", "提示", MessageBoxButtons.OK);

                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);     //显示错误信息
                }
            }
            else
            {
                MessageBox.Show("密码不正确");
            }

        }

        #region 查询表所有数据
        private void button1_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                string searchStr = "select * from " + this.tablename.Text;
                MySqlDataAdapter adapter = new MySqlDataAdapter(searchStr, mySqlConnection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet, "table1");
                this.dataGridView1.DataSource = dataSet.Tables["table1"];
            }
            else
            {
                MessageBox.Show("密码不正确");
            }

        }
        #endregion

        private void button2_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                mySqlConnection.Close();
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
          
        }

        #region 定时器启动
        private void button3_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                string tableName = this.text_one_table.Text.Trim();
                string bakPath = this.textBox_bak_path.Text.Trim();
                if (String.IsNullOrEmpty(tableName))
                {
                    MessageBox.Show("表名不能为空！！！");
                }
                else if (String.IsNullOrEmpty(mysqlDumpPath))
                {
                    MessageBox.Show("mysqldump的路径不能为空！！！");
                }
                else if (String.IsNullOrEmpty(bakPath))
                {
                    MessageBox.Show("备份文件的路径不能为空！！！");
                }
                else
                {
                    decimal interval = this.time_interval.Value * 1000;
                    _timer.Interval = (int)interval;
                    _timer.Tick += _timer_Tick;
                    _timer.Start();
                }

            }
            else
            {
                MessageBox.Show("密码不正确");
            }

        }

        private void _timer_Tick(object sender, EventArgs e)
        {
           
            this.log_text.AppendText("定时任务执行开始,执行时间:" + DateTime.Now.ToString());
            this.log_text.AppendText("
");
            this.BackupDB();
            int count = this.log_text.Lines.GetUpperBound(0);
            this.log_text.AppendText("count="+count);
            this.log_text.AppendText("
");
            this.log_text.AppendText("定时任务执行结束,执行时间:" + DateTime.Now.ToString());
            this.log_text.AppendText("
");
            if (count>=500)
            {
                this.log_text.Clear();
            }

        }

        #endregion

        private void stop_timer_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                DialogResult AF = MessageBox.Show("您确定停止计时器吗？", "确认框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (AF == DialogResult.OK)
                {
                    _timer.Stop();
                }
                else
                {
                    //用户点击取消或者关闭对话框后执行的代码
                }
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
        }

        #region 获取所有表名

        private void button_getAllTableNames_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                DataGridViewColumn checkCol = new DataGridViewCheckBoxColumn();
                checkCol.Name = "选择";
                this.dataGridView_show_tables_name.Columns.Add(checkCol);
                DataTable tbName = mySqlConnection.GetSchema("Tables");
                if (tbName.Columns.Contains("TABLE_NAME"))
                {
                    foreach (DataRow dr in tbName.Rows)
                    {
                        tableNameList.Add((string)dr["TABLE_NAME"]);
                    }
                }
                this.dataGridView_show_tables_name.DataSource = this.tableNameList.Select(x => new { Value = x }).ToList();
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
        }

        #endregion

        #region 备份单表
        private void button4_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            //密码验证通过
            if (passForm.DialogResult == DialogResult.OK)
            {
                string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                string tableName = this.text_one_table.Text.Trim();
                if (String.IsNullOrEmpty(tableName))
                {
                    MessageBox.Show("表名不能为空！！！");
                }
                else if (String.IsNullOrEmpty(mysqlDumpPath))
                {
                    MessageBox.Show("mysqldump的路径不能为空！！！");
                }
                else
                {
                    string cmdStr = mysqlDumpPath + " -h " + this.host.Text.Trim() + " -u" + this.username.Text.Trim() + " -p" + this.password.Text.Trim() + " " + this.database.Text.Trim() + " " + this.text_one_table.Text.Trim() + " > " + """ + this.textBox_bak_path.Text.Trim() + "\" + "bus_area.sql"";
                    CmdHelper.ExeCommand(cmdStr);
                }
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
        }

        #endregion

        #region 备份数据实现
        public void  BackupDB()

        {
            this.bakCycleCount = (int)this.numericUpDown_cycle_count.Value;
            this.selectedTableNameList.Clear();
            for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
            {
                if ((bool)this.dataGridView_show_tables_name.Rows[i].Cells["选择"].EditedFormattedValue == true)
                {
                    selectedTableNameList.Add(this.dataGridView_show_tables_name.Rows[i].Cells[1].Value.ToString());
                }
            }
            for (int i = 0; i < this.selectedTableNameList.Count; i++)
            {
                string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                string tableName = this.selectedTableNameList[i];
                string cmdStr = mysqlDumpPath + " -h " + this.host.Text.Trim() + " -u" + this.username.Text.Trim() + " -p" + this.password.Text.Trim() + " " + this.database.Text.Trim() + " " + tableName + " > " + """ + this.textBox_bak_path.Text.Trim() + "\" + tableName + "_" + currentBakCount + ".sql"";
                CmdHelper.ExeCommand(cmdStr);
                this.log_text.AppendText(tableName + "_" + currentBakCount + "--备份完成,时间：" + DateTime.Now.ToString());
                this.log_text.AppendText("
");
                //休眠1秒
                Thread.Sleep(1000);
            }
            currentBakCount++;
            if (currentBakCount == bakCycleCount+1)
            {
                currentBakCount = 1;
            }
        }

        #endregion

        private void button_select_path_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog path = new FolderBrowserDialog();
            path.ShowDialog();
            this.textBox_bak_path.Text = path.SelectedPath;
        }

        #region 备份所有表
        private void button_bak_all_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                DataTable tbName = mySqlConnection.GetSchema("Tables");
                if (tbName.Columns.Contains("TABLE_NAME"))
                {
                    foreach (DataRow dr in tbName.Rows)
                    {
                        string mysqlDumpPath = this.text_mysqldump_path.Text.Trim();
                        string tableName = (string)dr["TABLE_NAME"];
                        string cmdStr = mysqlDumpPath + " -h " + this.host.Text.Trim() + " -u" + this.username.Text.Trim() + " -p" + this.password.Text.Trim() + " " + this.database.Text.Trim() + " " + tableName + " > " + """ + this.textBox_bak_path.Text.Trim() + "\" + tableName + ".sql"";
                        CmdHelper.ExeCommand(cmdStr);
                        this.log_text.AppendText((string)dr["TABLE_NAME"] + "--备份完成");
                        this.log_text.AppendText("
");
                    }
                }
            }
            else
            {
                MessageBox.Show("密码不正确");
            }

        }
        #endregion

        #region 备份选中的表
        private void button_bak_selected_table_Click(object sender, EventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                this.BackupDB();
            }
            else
            {
                MessageBox.Show("密码不正确");
            }
          
        }
        #endregion

        #region 全选
        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            if (this.checkBox_select_all.Checked == true)
            {
                for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
                {
                    this.dataGridView_show_tables_name.Rows[i].Cells["选择"].Value = 1;
                }
            }
            else
            {
                for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
                {
                    this.dataGridView_show_tables_name.Rows[i].Cells["选择"].Value = 0;
                }
            }
        }
        #endregion

        private void start_timer_Load(object sender, EventArgs e)
        {
         
        }

        #region 输入密码才能关闭窗体
        private void start_timer_FormClosing(object sender, FormClosingEventArgs e)
        {
            PassForm passForm = new PassForm();
            passForm.ShowDialog();
            if (passForm.DialogResult == DialogResult.OK)
            {
                e.Cancel = false;                 //关闭窗体
            }
            else
            {
                MessageBox.Show("密码不正确");
                e.Cancel = true;                  //不执行操作
            }
        }
        #endregion
    }
}

效果