场景
Winform中实现与Mysql8建立连接并获取所有的表，然后勾选指定的表，定时进行数据库备份导出为sql文件。并且可以设定覆盖备份的次数，比如设置为7，则备份到第8次时就会将第一次备份的sql文件替换掉。
比如将mysql中的部分表备份近一个月的数据，每天备份一次。
注：
博客：
BADAO_LIUMANG_QIZHI的博客_霸道流氓气质_CSDN博客
关注公众号
霸道的程序猿
获取编程相关电子书、教程推送与免费下载。
实现
1、设计Winform的页面布局如下
2、首先实现与数据库建立连接
Winform中连接Mysql8并查询表中数据进行显示：
Winform中连接Mysql8并查询表中数据进行显示_BADAO_LIUMANG_QIZHI的博客-CSDN博客
在上面实现连接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中实现点击按钮弹窗输入密码验证通过后执行相应逻辑：
Winform中实现点击按钮弹窗输入密码验证通过后执行相应逻辑_BADAO_LIUMANG_QIZHI的博客-CSDN博客
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("\r\n");this.BackupDB();int count = this.log_text.Lines.GetUpperBound(0);this.log_text.AppendText("count="+count);this.log_text.AppendText("\r\n");this.log_text.AppendText("定时任务执行结束,执行时间:" + DateTime.Now.ToString());this.log_text.AppendText("\r\n");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("\r\n");//休眠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 onp.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("密码不正确");}}#endregionprivate 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("\r\n");this.BackupDB();int count = this.log_text.Lines.GetUpperBound(0);this.log_text.AppendText("count="+count);this.log_text.AppendText("\r\n");this.log_text.AppendText("定时任务执行结束,执行时间:" + DateTime.Now.ToString());this.log_text.AppendText("\r\n");if (count>=500){this.log_text.Clear();}}#endregionprivate 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("\r\n");//休眠1秒Thread.Sleep(1000);}currentBakCount++;if (currentBakCount == bakCycleCount+1){currentBakCount = 1;}}#endregionprivate 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("\r\n");}}}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;}}}#endregionprivate 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}}
