Skip to content

Instantly share code, notes, and snippets.

@christopher-hopper
Last active January 15, 2021 17:16
  • Star 13 You must be signed in to star a gist
  • Fork 8 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save christopher-hopper/8431737 to your computer and use it in GitHub Desktop.
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
Copy link

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

murich 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
Copy link

murich commented Oct 12, 2015

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

@csonuryilmaz
Copy link

csonuryilmaz 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