Clone a MySQL database to a new database on the same server without using a dump file. This is much faster than using mysqldump.

clone-mysql-db.sh

#!/bin/bash

DBUSER="root";
DBPASS="";
DBHOST="localhost";

DB_OLD=mydatabase
DB_NEW=clone_mydatabase

DBCONN="--host=${DBHOST} --user=${DBUSER} --password=${DBPASS}";

MYSQL_CREATE='set foreign_key_checks = 0';
MYSQL_INSERT='set foreign_key_checks = 0';

echo "Begin database clone (may take a while)";
echo "-- Create destination database";
echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW}" | mysql ${DBCONN};

[ $? -ne 0 ] && exit $?;

echo "-- Select source table schemas";
MYSQL_TABLES=$(echo "SHOW TABLES" | mysql $DBCONN $DB_OLD | tail -n +2);

[ $? -ne 0 ] && exit $?;
COUNT=1
for TABLE in $MYSQL_TABLES; do
    MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} DISABLE KEYS";
    MYSQL_INSERT="${MYSQL_INSERT}; INSERT INTO ${DB_NEW}.${TABLE} SELECT * FROM ${DB_OLD}.${TABLE}";
    MYSQL_INSERT="${MYSQL_INSERT}; ALTER TABLE ${DB_NEW}.${TABLE} ENABLE KEYS";
    MYSQL_CREATE="${MYSQL_CREATE}; $(echo SHOW CREATE TABLE ${TABLE}|mysql -B -r ${DBCONN} ${DB_OLD}|tail -n +2|cut -f 2-)";

    [ $? -ne 0 ] && echo && exit $?;

    [ $COUNT -le 1 ] && echo -n "   [ ";
    echo -n ".";
    [ $COUNT -ge 65 ] && echo " ]" && COUNT=0;

    COUNT=$((COUNT+1))
done;

MYSQL_CREATE="${MYSQL_CREATE}; set foreign_key_checks = 1";
MYSQL_INSERT="${MYSQL_INSERT}; set foreign_key_checks = 1";

[ $COUNT -gt 1 ] && echo " ]";

echo "-- Create destination table schemas";
echo "${MYSQL_CREATE};" | mysql $DBCONN $DB_NEW

[ $? -ne 0 ] && exit $?;

echo "-- Insert source data into destination tables";
echo "${MYSQL_INSERT};" | mysql $DBCONN $DB_NEW

[ $? -ne 0 ] && exit $?;

echo "Done.";

sql syntax errors..

Begin database clone (may take a while)
-- Create destination database
-- Select source table schemas
[ ................................................................. ]
[ ..................................ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order' at line 1
............................... ]
[ ................................................ ]
-- Create destination table schemas
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'utf8 utf8_general_ci' at line 1

-- Create destination table schemas ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'utf8 utf8_general_ci' at line 1
I also have this

Ok, that happens when you have some views in your source database

Hi, I have made some improvements while using it in a big db migration. This fork contains my changes. You can take some/all of them if you like.

Change log:

• Show tables modified to list only tables. Views ignored.
• New database creation made optional and asked user as y/n options.
• While new database creation, default collation and character set is got from old database.
• Backtick added to table name for tables whose name is also a reserved word at mysql. Ex: order, option.
• Table name is logged to console instead of dot.
• Number of tables is logged to console as a summary information.
• Invalid default column values for DATE and DATETIME fields removed.