场景
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("\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 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("\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();
}
}
#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("\r\n");
//休眠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("\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;
}
}
}
#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
}
}
效果