場(chǎng)景

Winform中實(shí)現(xiàn)與Mysql8建立連接并獲取所有的表，然后勾選指定的表，定時(shí)進(jìn)行數(shù)據(jù)庫(kù)備份導(dǎo)出為sql文件。并且可以設(shè)定覆蓋備份的次數(shù)，比如設(shè)置為7，則備份到第8次時(shí)就會(huì)將第一次備份的sql文件替換掉。

比如將mysql中的部分表備份近一個(gè)月的數(shù)據(jù)，每天備份一次。

注：

博客：
BADAO_LIUMANG_QIZHI的博客_霸道流氓氣質(zhì)_CSDN博客
關(guān)注公眾號(hào)
霸道的程序猿
獲取編程相關(guān)電子書(shū)、教程推送與免費(fèi)下載。

實(shí)現(xiàn)

1、設(shè)計(jì)Winform的頁(yè)面布局如下

2、首先實(shí)現(xiàn)與數(shù)據(jù)庫(kù)建立連接

Winform中連接Mysql8并查詢(xún)表中數(shù)據(jù)進(jìn)行顯示：

Winform中連接Mysql8并查詢(xún)表中數(shù)據(jù)進(jìn)行顯示_BADAO_LIUMANG_QIZHI的博客-CSDN博客

在上面實(shí)現(xiàn)連接Mysql8數(shù)據(jù)庫(kù)。

3、獲取mysqldump.exe的路徑

這里的路徑是帶雙引號(hào)的，因?yàn)槁窂街杏锌崭?#xff0c;然后獲取全路徑也是為了保證cmd中執(zhí)行mysqldump的通用性，因?yàn)椴灰欢ǘ紝⑵涮砑舆M(jìn)環(huán)境變量。

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

這里為了防止按鈕誤操作，添加了密碼校驗(yàn)，實(shí)現(xiàn)方式參考

Winform中實(shí)現(xiàn)點(diǎn)擊按鈕彈窗輸入密碼驗(yàn)證通過(guò)后執(zhí)行相應(yīng)邏輯：

Winform中實(shí)現(xiàn)點(diǎn)擊按鈕彈窗輸入密碼驗(yàn)證通過(guò)后執(zhí)行相應(yīng)邏輯_BADAO_LIUMANG_QIZHI的博客-CSDN博客

6、全選功能實(shí)現(xiàn)

添加一個(gè)checkbox并重寫(xiě)其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、頁(yè)面添加獲取覆蓋循環(huán)的次數(shù)和定時(shí)器執(zhí)行的秒數(shù)

8、定時(shí)器啟動(dòng)

??????? 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("密碼不正確");}}

實(shí)現(xiàn)邏輯是

驗(yàn)證密碼-獲取需要參數(shù)并驗(yàn)證是否為空-獲取定時(shí)器執(zhí)行的間隔數(shù)-設(shè)置定時(shí)器執(zhí)行的事件-啟動(dòng)定時(shí)器

其中設(shè)置定時(shí)器執(zhí)行的事件中

???? private void _timer_Tick(object sender, EventArgs e){this.log_text.AppendText("定時(shí)任務(wù)執(zhí)行開(kāi)始,執(zhí)行時(shí)間:" + 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("定時(shí)任務(wù)執(zhí)行結(jié)束,執(zhí)行時(shí)間:" + DateTime.Now.ToString());this.log_text.AppendText("\r\n");if (count>=500){this.log_text.Clear();}}

向一個(gè)TextBox中追加日志，并判斷日志大于500行之后清理日志。

然后具體執(zhí)行備份的方法是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 + "--備份完成,時(shí)間：" + DateTime.Now.ToString());this.log_text.AppendText("\r\n");//休眠1秒Thread.Sleep(1000);}currentBakCount++;if (currentBakCount == bakCycleCount+1){currentBakCount = 1;}}

在此方法中，獲取選中的表名，然后循環(huán)這些表名進(jìn)行備份

拼接成cmd命令，然后單個(gè)表進(jìn)行備份，執(zhí)行完一個(gè)表備份后休眠一秒。

比如執(zhí)行一個(gè)表叫bus_area,那么設(shè)定的覆蓋次數(shù)為7的話(huà)，就會(huì)出現(xiàn)

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

這其中執(zhí)行cmd命令的工具類(lèi)為

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>/// 執(zhí)行命令/// </summary>public class CmdHelper{////// 執(zhí)行cmd.exe命令//////命令文本/// 命令輸出文本public static string ExeCommand(string commandText){return ExeCommand(new string[] { commandText });}////// 執(zhí)行多條cmd.exe命令//////命令文本數(shù)組/// 命令輸出文本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;}////// 啟動(dòng)外部Windows應(yīng)用程序，隱藏程序界面//////應(yīng)用程序路徑名稱(chēng)/// true表示成功，false表示失敗public static bool StartApp(string appName){return StartApp(appName, ProcessWindowStyle.Hidden);}////// 啟動(dòng)外部應(yīng)用程序//////應(yīng)用程序路徑名稱(chēng)///進(jìn)程窗口模式/// true表示成功，false表示失敗public static bool StartApp(string appName, ProcessWindowStyle style){return StartApp(appName, null, style);}////// 啟動(dòng)外部應(yīng)用程序，隱藏程序界面//////應(yīng)用程序路徑名稱(chēng)///啟動(dòng)參數(shù)/// true表示成功，false表示失敗public static bool StartApp(string appName, string arguments){return StartApp(appName, arguments, ProcessWindowStyle.Hidden);}////// 啟動(dòng)外部應(yīng)用程序//////應(yīng)用程序路徑名稱(chēng)///啟動(dòng)參數(shù)///進(jìn)程窗口模式/// 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;//定時(shí)器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連接時(shí)，最后一句一定要加！！！mySqlConnection = new MySqlConnection(connetStr);try{mySqlConnection.Open(); //連接數(shù)據(jù)庫(kù)MessageBox.Show("數(shù)據(jù)庫(kù)連接成功", "提示", MessageBoxButtons.OK);}catch (MySqlException ex){MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);???? //顯示錯(cuò)誤信息}}else{MessageBox.Show("密碼不正確");}}#region 查詢(xún)表所有數(shù)據(jù)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 定時(shí)器啟動(dòng)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("定時(shí)任務(wù)執(zhí)行開(kāi)始,執(zhí)行時(shí)間:" + 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("定時(shí)任務(wù)執(zhí)行結(jié)束,執(zhí)行時(shí)間:" + 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("您確定停止計(jì)時(shí)器嗎？", "確認(rèn)框", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);if (AF == DialogResult.OK){_timer.Stop();}else{//用戶(hù)點(diǎn)擊取消或者關(guān)閉對(duì)話(huà)框后執(zhí)行的代碼}}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();//密碼驗(yàn)證通過(guò)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 備份數(shù)據(jù)實(shí)現(xiàn)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 + "--備份完成,時(shí)間：" + 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 輸入密碼才能關(guān)閉窗體private void start_timer_FormClosing(object sender, FormClosingEventArgs e){PassForm passForm = new PassForm();passForm.ShowDialog();if (passForm.DialogResult == DialogResult.OK){e.Cancel = false;???????????????? //關(guān)閉窗體}else{MessageBox.Show("密碼不正確");e.Cancel = true;????????????????? //不執(zhí)行操作}}#endregion} }

效果

?

?

總結(jié)

以上是生活随笔為你收集整理的Winform中使用mysqldump实现选择部分表定期备份mysql数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。