scene
Winform establishes a connection with Mysql8 and obtains all tables, then checks the specified table, regularly backs up the database and exports it as an sql file. And you can set the number of overwriting backups. For example, if it is set to 7, the sql file backed up for the first time will be replaced when backing up to the eighth time.
For example, back up some tables in mysql for nearly a month, once a day.
Note:
Blog:
BADAO_LIUMANG_QIZHI's blog_ Domineering rogue temperament_ CSDN blog
Official account
Domineering procedural ape
Get programming related e-books, tutorial push and free download.
realization
1. The page layout of Winform is as follows
2. First, establish a connection with the database
Connect Mysql8 in Winform and query the data in the table for display:
The above implementation connects to the Mysql8 database.
3. Get the path of mysqldump.exe
The path here is in double quotation marks because there are spaces in the path, and then obtaining the full path is also to ensure the universality of mysqldump in cmd, because it is not necessarily added to the environment variable.
4. Select the path to the backup file
private void button_select_path_Click(object sender, EventArgs e)
{
FolderBrowserDialog path = new FolderBrowserDialog();
path.ShowDialog();
this.textBox_bak_path.Text = path.SelectedPath;
}5. Get all table names
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 = "choice";
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("Incorrect password");
}
}Here, in order to prevent button misoperation, password verification is added for reference
In Winform, click the button pop-up window to enter the password. After passing the verification, execute the corresponding logic:
6. Implementation of select all function
Add a checkbox and override its checkchanged event
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["choice"].Value = 1;
}
}
else
{
for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
{
this.dataGridView_show_tables_name.Rows[i].Cells["choice"].Value = 0;
}
}
}7. Page add gets the number of override cycles and the number of seconds the timer executes
8. Timer start
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("Table name cannot be empty!!!");
}
else if (String.IsNullOrEmpty(mysqlDumpPath))
{
MessageBox.Show("mysqldump The path of cannot be empty!!!");
}
else if (String.IsNullOrEmpty(bakPath))
{
MessageBox.Show("The path of backup file cannot be empty!!!");
}
else
{
decimal interval = this.time_interval.Value * 1000;
_timer.Interval = (int)interval;
_timer.Tick += _timer_Tick;
_timer.Start();
}
}
else
{
MessageBox.Show("Incorrect password");
}
}The implementation logic is
Verify password - get the required parameters and verify whether it is empty - get the number of intervals executed by the timer - set the events executed by the timer - start the timer
The event in which the timer is set to execute
private void _timer_Tick(object sender, EventArgs e)
{
this.log_text.AppendText("Scheduled task execution start,execution time:" + 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("Scheduled task execution end,execution time:" + DateTime.Now.ToString());
this.log_text.AppendText("\r\n");
if (count>=500)
{
this.log_text.Clear();
}
}Add a log to a TextBox and clean up the log after judging that the log is greater than 500 lines.
Then, the backup method is 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["choice"].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 + "--Backup complete,Time:" + DateTime.Now.ToString());
this.log_text.AppendText("\r\n");
//Sleep for 1 second
Thread.Sleep(1000);
}
currentBakCount++;
if (currentBakCount == bakCycleCount+1)
{
currentBakCount = 1;
}
}In this method, get the selected table names, and then cycle these table names for backup
Splice it into cmd command, then backup a single table, and sleep for one second after performing a table backup.
For example, execute a table called bus_area, then if the set number of overrides is 7, it will appear
bus_area_1.sql,bus_area_2.sql to bus_area_7.sql and then overwrite the bus_area_1.sql
The tool class for executing cmd command is
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>
///Execute command
/// </summary>
public class CmdHelper
{
///
///Execute cmd.exe command
///
///Command text
///Command output text
public static string ExeCommand(string commandText)
{
return ExeCommand(new string[] { commandText });
}
///
///Execute multiple cmd.exe commands
///
///Command text array
///Command output text
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;
}
///
///Start external Windows applications and hide the program interface
///
///Application path name
///true indicates success and false indicates failure
public static bool StartApp(string appName)
{
return StartApp(appName, ProcessWindowStyle.Hidden);
}
///
///Start external application
///
///Application path name
///Process window mode
///true indicates success and false indicates failure
public static bool StartApp(string appName, ProcessWindowStyle style)
{
return StartApp(appName, null, style);
}
///
///Start the external application and hide the program interface
///
///Application path name
///Start parameters
///true indicates success and false indicates failure
public static bool StartApp(string appName, string arguments)
{
return StartApp(appName, arguments, ProcessWindowStyle.Hidden);
}
///
///Start external application
///
///Application path name
///Start parameters
///Process window mode
///true indicates success and false indicates failure
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;
}
}
}
}Complete sample code
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;
//timer
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; //When localhost does not support ssl connection, the last sentence must be added!!!
mySqlConnection = new MySqlConnection(connetStr);
try
{
mySqlConnection.Open(); //Connect to database
MessageBox.Show("Database connection succeeded", "Tips", MessageBoxButtons.OK);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message, "Tips", MessageBoxButtons.OK); //Display error message
}
}
else
{
MessageBox.Show("Incorrect password");
}
}
#region query table all data
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("Incorrect password");
}
}
#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("Incorrect password");
}
}
#The region timer starts
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("Table name cannot be empty!!!");
}
else if (String.IsNullOrEmpty(mysqlDumpPath))
{
MessageBox.Show("mysqldump The path of cannot be empty!!!");
}
else if (String.IsNullOrEmpty(bakPath))
{
MessageBox.Show("The path of backup file cannot be empty!!!");
}
else
{
decimal interval = this.time_interval.Value * 1000;
_timer.Interval = (int)interval;
_timer.Tick += _timer_Tick;
_timer.Start();
}
}
else
{
MessageBox.Show("Incorrect password");
}
}
private void _timer_Tick(object sender, EventArgs e)
{
this.log_text.AppendText("Scheduled task execution start,execution time:" + 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("Scheduled task execution end,execution time:" + 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("Are you sure to stop the timer?", "Confirmation box", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (AF == DialogResult.OK)
{
_timer.Stop();
}
else
{
//Code executed after the user clicks cancel or closes the dialog box
}
}
else
{
MessageBox.Show("Incorrect password");
}
}
#region get all table names
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 = "choice";
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("Incorrect password");
}
}
#endregion
#region backup list
private void button4_Click(object sender, EventArgs e)
{
PassForm passForm = new PassForm();
passForm.ShowDialog();
//Password verification passed
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("Table name cannot be empty!!!");
}
else if (String.IsNullOrEmpty(mysqlDumpPath))
{
MessageBox.Show("mysqldump The path of cannot be empty!!!");
}
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("Incorrect password");
}
}
#endregion
#region backup data implementation
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["choice"].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 + "--Backup complete,Time:" + DateTime.Now.ToString());
this.log_text.AppendText("\r\n");
//Sleep for 1 second
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 back up all tables
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"] + "--Backup complete");
this.log_text.AppendText("\r\n");
}
}
}
else
{
MessageBox.Show("Incorrect password");
}
}
#endregion
#region backs up the selected table
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("Incorrect password");
}
}
#endregion
#region select all
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["choice"].Value = 1;
}
}
else
{
for (int i = 0; i < this.dataGridView_show_tables_name.Rows.Count; i++)
{
this.dataGridView_show_tables_name.Rows[i].Cells["choice"].Value = 0;
}
}
}
#endregion
private void start_timer_Load(object sender, EventArgs e)
{
}
#region enter the password to close the form
private void start_timer_FormClosing(object sender, FormClosingEventArgs e)
{
PassForm passForm = new PassForm();
passForm.ShowDialog();
if (passForm.DialogResult == DialogResult.OK)
{
e.Cancel = false; //Closing Windows
}
else
{
MessageBox.Show("Incorrect password");
e.Cancel = true; //Do not perform operation
}
}
#endregion
}
}effect
