MySQL backup and restore some times become hack-trick task because of unnecessary lines on code generated when exporting data from mysql workbench in individual file for tables. It becomes harder if there are large number of tables.
Following is a sample console application that will copy data from a source database table and bulk insert to a destination database table. It loops through all tables and perform the data transfer of all tables.
Install MySQLConnection nuget package.
using MySqlConnector;
using System.Data;
string schemaName = "db_dev";
string sourceConnectionString = "server=srchost;user id=admin;password=mypwd;persistsecurityinfo=True;database=db_dev;";
string destinationConnectionString = "server=desthost;user id=myadmin;password=mypass;persistsecurityinfo=True;database=destdb_dev;AllowLoadLocalInfile=true;";
int i = 0;
using (MySqlConnection sourceConnection = new MySqlConnection(sourceConnectionString))
{
sourceConnection.Open();
// Get the list of tables in the source database
DataTable tables = sourceConnection.GetSchema("Tables");
DataTable filteredRows = tables.Select($"TABLE_SCHEMA = '{schemaName}'").CopyToDataTable();
foreach (DataRow row in filteredRows.Rows)
{
string tableName = row[2].ToString();
int dataCount = Convert.ToInt32(row[7].ToString());
i++;
// Read data from the source table
string selectQuery = $"SELECT * FROM `{schemaName}`.`{tableName}`";
MySqlCommand selectCommand = new MySqlCommand(selectQuery, sourceConnection);
if (dataCount > 0 && i>=0)
using (MySqlDataReader reader = selectCommand.ExecuteReader())
{
var rows = reader.HasRows;
using (MySqlConnection destinationConnection = new MySqlConnection(destinationConnectionString))
{
destinationConnection.Open();
// Prepare the bulk insert command
using (MySqlTransaction transaction = destinationConnection.BeginTransaction())
{
MySqlBulkCopy bulkCopy = new MySqlBulkCopy(destinationConnection, transaction);
bulkCopy.DestinationTableName = tableName;
try
{
// Bulk insert data into the destination table
bulkCopy.WriteToServer(reader);
transaction.Commit();
Console.WriteLine($"Data from table {tableName} has been successfully transferred." +i.ToString());
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error transferring data from table {tableName}: {ex.Message}");
}
}
}
}
}
}
Console.WriteLine("Hello, World!");
