Winform中实现连接Mysql8使用mysqldump实现备份表的数据

﹏ヽ暗。殇╰゛Y 2022-08-28 10:56 185阅读 0赞

# 场景 #

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

[Winform中连接Mysql8并查询表中数据进行显示\_BADAO\_LIUMANG\_QIZHI的博客-CSDN博客][Winform_Mysql8_BADAO_LIUMANG_QIZHI_-CSDN]

在上面实现连接Mysql8的基础上，怎样借助于mysqldump实现数据备份。

注：

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

# 实现 #

1、继续上面的winform的布局，设计布局如下

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16][]

2、获取需要的参数

通过TextBox来获取备份单表的按钮的表名输入，通过Button"选择备份文件路径"以及后面的TextBox来选择要进行备份的路径。

其中选择备份文件的路径的点击事件为

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

然后通过一个TextBox来获取本机(即需要运行Winform的机器)的mysqldump.exe的路径，记得要带双引号。

3、实现单表的备份

然后再备份单表的按钮的点击事件中

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("密码不正确");
                }
            }

这里首先加了一个密码验证的逻辑，防止误操作乱点按钮，通过后，获取到表名和mysqldump的路径然后进行拼接成cmdStr，

在拼接之后需要打断点到这步获取完整的cmd命令，然后再cmd中先手动执行一下试试。

这里在执行cmd时调用了一个帮助类CmdHelper

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

3、测试单表效果

在建立连接成功并配置各项参数后，点击备份单表

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

验证通过后到指定路径下查看结果

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 2][]

4、备份所有表实现

在备份所有表的点击事件中

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("\r\n");
                        }
                    }
                }
                else
                {
                    MessageBox.Show("密码不正确");
                }
    
            }

逻辑是获取所有的表名然后循环拼接表名进行导出sql

效果

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 3][]

[Winform_Mysql8_BADAO_LIUMANG_QIZHI_-CSDN]: https://blog.csdn.net/BADAO_LIUMANG_QIZHI/article/details/120395988
[https_blog.csdn.net_badao_liumang_qizhi]: https://blog.csdn.net/badao_liumang_qizhi
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/be5297b4690a4d87a4d6d4ad52cfe62a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220828/32ee957840e84a0db61173ffba96a5a7.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 2]: /images/20220828/992503d8ec194261b5ab0ec754ffc7c8.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAQkFEQU9fTElVTUFOR19RSVpISQ_size_20_color_FFFFFF_t_70_g_se_x_16 3]: /images/20220828/226f72f6f31440838b0a5d3bc6460f1a.png