Duplicate a database on a server: copying dabase db1 to db2 on the same server

Oct 11, 2014 at 9:44 AM
Hi,
Your project is really a big help , Thanks for that.

I would like to know how can :
  1. I duplicate my database db1 (with data and without) on the same server with different name not using file.
  2. I Copy a table (with data and without) from db1 to my database db2.
Coordinator
Oct 11, 2014 at 12:19 PM
Edited Oct 11, 2014 at 12:25 PM
Hi, your question is actually been answered in documentation.
You just need to read it all, then you'll be good to manipulate it on your own way.

Anyway, I can provide you the sample code.

Duplicate database with data:
string file = @"C:\backup.sql";
string constr1 = "server=localhost;user=root;pwd=1234;database=database1;";
using (MySqlConnection conn1 = new MySqlConnection(constr1))
{
    using (MySqlCommand cmd1 = new MySqlCommand())
    {
        using (MySqlBackup mb1 = new MySqlBackup(cmd1))
        {
            cmd1.Connection = conn1;
            conn1.Open();
            mb1.ExportToFile(file);
            conn1.Close();
        }
    }
}
string constr2 = "server=localhost;user=root;pwd=1234;database=database2;";
using (MySqlConnection conn2 = new MySqlConnection(constr2))
{
    using (MySqlCommand cmd2 = new MySqlCommand())
    {
        using (MySqlBackup mb2 = new MySqlBackup(cmd2))
        {
            cmd2.Connection = conn2;
            conn2.Open();
            mb2.ImportFromFile(file);
            conn2.Close();
        }
    }
}
Duplicate without data:
Just add one more line:
mb1.ExportInfo.ExportRows = false;
mb1.ExportToFile(file);
Copy a table with data:
mb1.ExportInfo.TablesToBeExportedDic = new Dictionary<string, string>();
mb1.ExportInfo.TablesToBeExportedDic["table1"] = "select * from table1;";
mb1.ExportToFile(file);
Copy a table without data:
mb1.ExportInfo.TablesToBeExportedDic = new Dictionary<string, string>();
mb1.ExportInfo.TablesToBeExportedDic["table1"] = "select * from table1 where 1 = 2;";
mb1.ExportToFile(file);
Oct 11, 2014 at 7:07 PM
Edited Oct 11, 2014 at 7:34 PM
adriancs wrote:
Hi, your question is actually been answered in documentation.
You just need to read it all, then you'll be good to manipulate it on your own way.

Anyway, I can provide you the sample code.

Duplicate database with data:
string file = @"C:\backup.sql";
string constr1 = "server=localhost;user=root;pwd=1234;database=database1;";
using (MySqlConnection conn1 = new MySqlConnection(constr1))
{
    using (MySqlCommand cmd1 = new MySqlCommand())
    {
        using (MySqlBackup mb1 = new MySqlBackup(cmd1))
        {
            cmd1.Connection = conn1;
            conn1.Open();
            mb1.ExportToFile(file);
            conn1.Close();
        }
    }
}
string constr2 = "server=localhost;user=root;pwd=1234;database=database2;";
using (MySqlConnection conn2 = new MySqlConnection(constr2))
{
    using (MySqlCommand cmd2 = new MySqlCommand())
    {
        using (MySqlBackup mb2 = new MySqlBackup(cmd2))
        {
            cmd2.Connection = conn2;
            conn2.Open();
            mb2.ImportFromFile(file);
            conn2.Close();
        }
    }
}
Duplicate without data:
Just add one more line:
mb1.ExportInfo.ExportRows = false;
mb1.ExportToFile(file);
Copy a table with data:
mb1.ExportInfo.TablesToBeExportedDic = new Dictionary<string, string>();
mb1.ExportInfo.TablesToBeExportedDic["table1"] = "select * from table1;";
mb1.ExportToFile(file);
Copy a table without data:
mb1.ExportInfo.TablesToBeExportedDic = new Dictionary<string, string>();
mb1.ExportInfo.TablesToBeExportedDic["table1"] = "select * from table1 where 1 = 2;";
mb1.ExportToFile(file);
Your explaination is so great thanks, I did'nt want to a file for doing it and the test project shows how to use memory stream.
I am facing a problem: I cannot access ExportInfo properties(TablesToBeExportedDic and ExportRows). The dll that I imported in my project is the one in the folder :MySqlBackup.NET 2.0.4\Binaries\v4.0 (MysqlBackup.dll) what can be the problem AND When I test my code using the test project everything works fine but back in my project nothing works (stream memory contains nothing).
I SHOULD TELL ALSO THAT I AM CODING IN VB.NET I TESTED THE CODE IN A C# PROJECT I was able to acces those properties but back to vb.net it did work.
MySqlCommand comm = new MySqlCommand();
System.IO.MemoryStream mstream = new System.IO.MemoryStream();
System.Text.UTF8Encoding encodage = new System.Text.UTF8Encoding(false);
System.IO.StreamWriter textWriter = new System.IO.StreamWriter(mstream, encodage);
createur = new MySqlBackup(comm);
condb.ConnectionString = BDconnexionstring("premier");
this.TXT_desc.Text = BDconnexionstring("premier");
condb.Open();
comm.Connection = condb;
//On ecrit la BD dans la memoire temporaire
createur.ExportToTextWriter(textWriter);
condb.Close();
byte[] lecte ;
lecte = mstream.ToArray();
if ((lecte == null)) {
    this.Cursor = Cursors.Default;
    Interaction.MsgBox("NOTHING");
    return;
}
if (lecte.Length) {
    Interaction.MsgBox("LENGTH ZERO");
    this.Cursor = Cursors.Default;
    return;
Coordinator
Oct 12, 2014 at 4:28 AM
Edited Oct 12, 2014 at 4:29 AM
VB.NET version
This will export to and import from Memory
Dim con1 As String = "server=localhost;user=root;pwd=1234;database=database1;"
Dim con2 As String = "server=localhost;user=root;pwd=1234;database=database2;"
Dim encoding As UTF8Encoding = New UTF8Encoding(False)

Dim memoryStream As MemoryStream = New MemoryStream()

Dim textWriter As TextWriter = New StreamWriter(memoryStream, encoding)
Dim mySqlConnection As MySqlConnection = New MySqlConnection(con1)
Dim mySqlCommand As MySqlCommand = New MySqlCommand()
Dim mySqlBackup As MySqlBackup = New MySqlBackup(mySqlCommand)
mySqlConnection.Open()
mySqlCommand.Connection = mySqlConnection
mySqlBackup.ExportToTextWriter(textWriter)
mySqlConnection.Close()

memoryStream.Position = 0

Dim textReader As TextReader = New StreamReader(memoryStream)
Dim mySqlConnection2 As MySqlConnection = New MySqlConnection(con2)
mySqlCommand.Connection = mySqlConnection2
mySqlConnection2.Open()
mySqlBackup = New MySqlBackup(mySqlCommand)
mySqlBackup.ImportFromTextReader(textReader)
mySqlConnection2.Close()

MessageBox.Show("done")
Duplicate without data:
mySqlBackup.ExportInfo.ExportRows = False
mySqlBackup.ExportToTextWriter(textWriter)
Export a table with data:
mySqlBackup.ExportInfo.TablesToBeExportedDic = New Dictionary(Of String, String)
mySqlBackup.ExportInfo.TablesToBeExportedDic("table1") = "select * from table1"
mySqlBackup.ExportToTextWriter(textWriter)
Export a table without data:
mySqlBackup.ExportInfo.TablesToBeExportedDic = New Dictionary(Of String, String)
mySqlBackup.ExportInfo.TablesToBeExportedDic("table1") = "select * from table1 where 1 = 2"
mySqlBackup.ExportToTextWriter(textWriter)
Oct 12, 2014 at 6:05 PM
SO Great Thanks a lot , I've understood where I was mistaken But When I am running the code it saying "Cannot create stored procedure nameoftheprocedure . checks warning " error code is : 1607 even using the file method ANd when I try to use the exported file into dbforge or mysqlworkbench I don't get error.
Coordinator
Oct 13, 2014 at 12:47 AM
Can you show me the stored procedure?

try executing the code with
mySqlBackup.ExportInfo.ExportTableStructure = False
mySqlBackup.ExportInfo.ExportRows = False
and get only the stored procedure,
I want to have a check on it.
Maybe there is a bug in MySqlBackup.NET.
Oct 13, 2014 at 5:43 AM
Edited Oct 13, 2014 at 5:44 AM
Here are the procedures grouped into(working,not working) is the procedure it not only those given here but others too(on 34 procedures only 10 works but in mysqlworkbench or dbforge all works):
WORKING PROCEDURE :

DROP PROCEDURE IF EXISTS `AjoutCompte`;
DELIMITER |
CREATE PROCEDURE `AjoutCompte`(pclient tinyint(1),pdep varchar(50),pcompte varchar(50),puti varchar(20),pdateu datetime,pdesc varchar(255),porg tinyint(1),ppays varchar(100),pidnat varchar(50))
BEGIN
If pclient<> 0 then
INSERT INTO comptecs (comptec, depot, pays, idnat, description, utilisateur, dateu, org)
  VALUES (pcompte, pdep, ppays, pidnat, pdesc, puti, pdateu, porg); ELSE INSERT INTO comptef (comptec, depot, pays, idnat, description, utilisateur, dateu, org)
  VALUES (pcompte, pdep, ppays, pidnat, pdesc, puti, pdateu, porg);
END IF; END |
DELIMITER ;
NOT WORKING PROCEDURE :
DROP PROCEDURE IF EXISTS `Connexionutilisateur`;
DELIMITER |
CREATE PROCEDURE `Connexionutilisateur`(IN p_utilisateur varchar(20),IN p_mot varchar(20))
    COMMENT 'Cette procédure vérifier si l''utilisateur spécifié peut se connecter maintenant au logiciel,elle retournera les informations sur l''utilisateur si l''utilisateur peut se connecter.'
BEGIN
SELECT
  personnes.payso,
  personnes.idnatp,
  personnes.nom,
  personnes.postnom,
  personnes.photo,
  utilisateurs.utilisateur,
  utilisateurs.passeword,
  utilisateuractif.dated,
  utilisateuractif.datef
FROM personnes
  INNER JOIN utilisateurs
    ON Personnes.idnatp = utilisateurs.idnat
    AND Personnes.payso = utilisateurs.pays
  INNER JOIN utilisateuractif
    ON utilisateurs.utilisateur = BINARY utilisateuractif.utilisateur
WHERE (utilisateurs.utilisateur = BINARY p_utilisateur
AND utilisateurs.passeword = BINARY p_mot)
AND (utilisateuractif.dated <= NOW()
AND (utilisateuractif.datef IS NULL
OR utilisateuractif.datef >= NOW())); END |
DELIMITER ;
Oct 13, 2014 at 9:44 AM
Edited Oct 13, 2014 at 1:04 PM
I did it !!!!!! It working !!!!! thanks !!!! thanks for such great great project. I followed the idea you gave : separating the exportation of procedure and tables structure. now everything is ok!!!!

Here is the working code:
 Dim con1 As String = BDconnexionstring("premier")
                                Dim con2 As String = BDconnexionstring(Me.CB_dep.Text.Trim(""))
                                Dim encoding As UTF8Encoding = New UTF8Encoding(False)

                                Dim memoryStream As MemoryStream = New MemoryStream()

                                Dim textWriter As TextWriter = New StreamWriter(memoryStream, encoding)
                                Dim mySqlConnection As MySqlConnection = New MySqlConnection(con1)
                                Dim mySqlCommand As MySqlCommand = New MySqlCommand()
                                Dim mySqlBackup As MySqlBackup = New MySqlBackup(mySqlCommand)

                                'Exportation of all database objects a part from procedures and functions
                                mySqlConnection.Open()
                                mySqlCommand.Connection = mySqlConnection
                                mySqlBackup.ExportInfo.ExportRows = False
                                mySqlBackup.ExportInfo.ExportProcedures = False
                                mySqlBackup.ExportInfo.ExportFunctions = False
                                mySqlBackup.ExportToTextWriter(textWriter)
                                memoryStream.Position = 0

                                'exportation of all database objects a part from procedures and functions
                                Dim textReader As TextReader = New StreamReader(memoryStream)
                                Dim mySqlConnection2 As MySqlConnection = New MySqlConnection(con2)
                                mySqlCommand.Connection = mySqlConnection2
                                mySqlConnection2.Open()
                                mySqlBackup = New MySqlBackup(mySqlCommand)
                                mySqlBackup.ImportInfo.ErrorLogFile = "nouveau.sql"
                                mySqlBackup.ImportInfo.DatabaseDefaultCharSet = "utf8"
                                mySqlBackup.ImportFromTextReader(textReader)
                                memoryStream = Nothing
                                textWriter = Nothing
                                textReader = Nothing
                                'Exportation of  procedures and functions
                                memoryStream = New IO.MemoryStream
                                textWriter = New StreamWriter(memoryStream, encoding)
                                mySqlCommand.Connection = mySqlConnection
                                mySqlBackup.ExportInfo.ExportRows = False
                                mySqlBackup.ExportInfo.ExportTriggers = False
                                mySqlBackup.ExportInfo.ExportTableStructure = False
                                mySqlBackup.ExportInfo.ExportViews = False
                                mySqlBackup.ExportInfo.ExportEvents = False
                                mySqlBackup.ExportToTextWriter(textWriter)
                                mySqlConnection.Close()
                                memoryStream.Position = 0
                                'Importation of  procedures and functions
                                textReader = New StreamReader(memoryStream)
                                mySqlCommand.Connection = mySqlConnection2
                                mySqlBackup = New MySqlBackup(mySqlCommand)
                                mySqlBackup.ImportInfo.ErrorLogFile = "nouveau.sql"
                                mySqlBackup.ImportInfo.DatabaseDefaultCharSet = "utf8"
                                mySqlBackup.ImportFromTextReader(textReader)
Now It only remains to make the process of backing up to not bloc the all program as it doing now.
Coordinator
Oct 20, 2014 at 4:42 PM
Hi, I have just released a new version which simplifies a bit on exporting into memory stream.
In new version v2.0.5, textwriter and textreader are no more needed :)
You may want to check the demo application (MySqlBackupTestApp.exe) for some new features.
Good luck :)