MySql Database Backup using C#.NET

It shows how to backup MySql Databases using C#.NET.
keywords: mysql, sql, database, backup, tables, C Sharp, .NET

You have add following Controls
1) SaveFileDialog (sfd)
2) ComboBox (cmbDatabase)
3) TextBox (txtExpImpPath, txtWorkingDirectoryPath)

First load the MySql Database to the comboBox.

 

private void getDatabase()

        {

            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();

            //Connection String

            strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

            myChk = "";

            myChk = strMySQL;

            //Open Odbc Connection

            conOdbc = new OdbcConnection(myChk);

            conOdbc.Close();

            conOdbc.Open();

            selectQuery = "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES ";

            OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

            odbcAdptr.Fill(dt);

            dtseCt = 0;

            cmbDatabase.Items.Clear();

            cmbDatabase.Items.Add("---Select---");

            while (dtseCt < dt.Rows.Count)

            {

                cmbDatabase.Items.Add(dt.Rows[dtseCt][0].ToString());

                dtseCt = dtseCt + 1;

            }

            cmbDatabase.SelectedIndex = 0;

        }

Connect the database using...

strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

 Here the Complete code for the Database Backup

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

using Microsoft.VisualBasic;

using System.Diagnostics;

using System.IO;

using System.Data.Odbc;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        string strMySQL = null;

        string myChk = null;

        string selectQuery = null;

        int dtseCt = 0;

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            getDatabase();

        }

        private void getDatabase()

        {

            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();

            //Connection String

            strMySQL = "";

            strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

            myChk = "";

            myChk = strMySQL;

            //Open Odbc Connection

            conOdbc = new OdbcConnection(myChk);

            conOdbc.Close();

            conOdbc.Open();

            selectQuery = "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES ";

            OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

            odbcAdptr.Fill(dt);

            dtseCt = 0;

            cmbDatabase.Items.Clear();

            cmbDatabase.Items.Add("---Select---");

            while (dtseCt < dt.Rows.Count)

            {

                cmbDatabase.Items.Add(dt.Rows[dtseCt][0].ToString());

                dtseCt = dtseCt + 1;

            }

            cmbDatabase.SelectedIndex = 0;

        }

        private void gettingPath()

        {

            //Getting MySQL Path

            //Declaration

            OdbcConnection conOdbc = new OdbcConnection();

            DataTable dt = new DataTable();

            int len = 0;

            string tempWorkPath = "";

            try

            {

                //Connection String

                strMySQL = "";

                strMySQL = "Driver={MySQL ODBC 3.51 Driver};option=0;port=3306;server= localhost;uid=root;password=root";

                myChk = strMySQL;

                //Open Odbc Connection

                conOdbc = new OdbcConnection(myChk);

                conOdbc.Close();

                conOdbc.Open();

                selectQuery = "select @@datadir";

                OdbcDataAdapter odbcAdptr = new OdbcDataAdapter(selectQuery, conOdbc);

                odbcAdptr.Fill(dt);

                tempWorkPath = dt.Rows[0][0].ToString();

                len = tempWorkPath.Length  - 5;

                txtWorkingDirectoryPath.Text = tempWorkPath.Substring(0, len) + "bin";

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.Message);

            }

        }

        private void button1_Click(object sender, EventArgs e)

        {

            try

            {

                //Getting Path

                gettingPath();

                //Save Dialog Box

                sfd.Filter = "SQL Files |.sql";

                sfd.DefaultExt = ".sql";

                DialogResult = sfd.ShowDialog();

                if (DialogResult == System.Windows.Forms.DialogResult.Cancel)

                {

                    MessageBox.Show("Backup Creation Failed");

                    return;

                }

                txtExpImpPath.Text = sfd.FileName;

                //Getting Drives in System

                string[] drives = Environment.GetLogicalDrives();

                string drive = drives[1] + "Temp.sql";

                //Do Events

                Application.DoEvents();

                //Processing

                Process myProcess = new Process();

                myProcess.StartInfo.FileName = "cmd.exe";

                myProcess.StartInfo.UseShellExecute = false;

                myProcess.StartInfo.WorkingDirectory = txtWorkingDirectoryPath.Text;

                myProcess.StartInfo.RedirectStandardInput = true;

                myProcess.StartInfo.RedirectStandardOutput = true;

                myProcess.Start();

                StreamWriter myStreamWriter = myProcess.StandardInput;

                StreamReader mystreamreader = myProcess.StandardOutput;

                myStreamWriter.WriteLine("mysqldump --routines -u root -proot " + cmbDatabase.Text + " -r " + drive + "");

                myStreamWriter.Close();

                myProcess.WaitForExit();

                myProcess.Close();

                //Copying File from user selected path to System Created Path

                File.Move(drive, txtExpImpPath.Text);

                MessageBox.Show("Backup Created Successfully");

            }

            catch (Exception ex)

            {

                MessageBox.Show("Backup Creation Failed");

            }

        }

    }

}

Name the file to backup

Backup created successfully

Backup file views like this

 Note: If you have any suggestions or new topic, please free to contact us

Email: sivodayatech@dotnetdevelopertool.com