Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Clone a MySQL database to a new database on the same server without using a dump file. This is much faster than using mysqldump.
#!/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.";
@nclundsten

This comment has been minimized.

Copy link

commented Jan 20, 2014

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

@murich

This comment has been minimized.

Copy link

commented Oct 12, 2015

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

@murich

This comment has been minimized.

Copy link

commented Oct 12, 2015

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

@csonuryilmaz

This comment has been minimized.

Copy link

commented Jan 29, 2017

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.