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 -n "-- Do you want to create destination database? (if exists will be dropped) (y/n)?";
read answer
if echo "$answer" | grep -iq "^y" ;then
    DATABASE_COLLATION=$(echo "SELECT @@character_set_database, @@collation_database;" | mysql $DBCONN $DB_OLD | tail -n 1 | awk '{ print $2 }');
    echo "DROP DATABASE IF EXISTS ${DB_NEW}; CREATE DATABASE ${DB_NEW} COLLATE ${DATABASE_COLLATION}" | mysql ${DBCONN};
    [ $? -ne 0 ] && exit $?;
fi

echo -e "\n -- Select source table schemas";
MYSQL_TABLES=$(echo "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" | mysql $DBCONN $DB_OLD | tail -n +2 | awk '{ print $1 }');

[ $? -ne 0 ] && exit $?;
COUNT=0
for TABLE in $MYSQL_TABLES; do
    echo -n "${TABLE}";
    TABLE="\`${TABLE}\`";

    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";

    CREATE_SQL=$(echo "SHOW CREATE TABLE ${TABLE}" | mysql -B -r ${DBCONN} ${DB_OLD} |tail -n +2 | cut -f 2- | sed -e "s/NOT NULL DEFAULT '0000-00-00 00:00:00'/NOT NULL/g" | sed -e "s/NOT NULL DEFAULT '0000-00-00'/NOT NULL/g");
    MYSQL_CREATE="${MYSQL_CREATE}; ${CREATE_SQL}";

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

    echo -e "\t [ok]";

    COUNT=$((COUNT+1))
done;
echo "-- Total ${COUNT} tables will be cloned ..."

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.";

I'm running into an error when the script tries to insert data into a table with a generated column. Do you if there's a way to detect and omit generated columns from the inserts?

ERROR 3105 (HY000) at line 1: The value specified for generated column 'column_name' in table 'table' is not allowed.

Either way, this is incredibly useful, especially for big databases. Thanks a lot for putting it together.

You're still saving the sql in the shell then running it. It'll be no quicker than a mysql < mysqldump. In fact it's worse because it absolutely destroys memory on the server running this script. It may be quicker if your database is only a couple of gigs, that's hard to say. But in my case with 150GB it just doesn't work.