What is the fastest way to clone an INNODB table within the same server?
- by Vic
Our development server is a replication slave of our production server. We have a script that developers use if they want to run their applications/bug fixes against fresh data. That script looks like this:
dbs=( analytics auth logs users )
server=localhost
conn="-h ${server} -u ${username} --password=${password}"
# Stop the replication client so we don't encounter weird data.
echo "STOP SLAVE" | mysql ${conn}
# Bunch of bulk insert optimizations
echo "SET autocommit=0" | mysql ${conn}
echo "SET unique_checks=0" | mysql ${conn}
echo "SET foreign_key_checks=0" | mysql ${conn}
# Restore all databases and tables.
for sourcedb in ${dbs[*]}
do
destdb=${prefix}${sourcedb}
echo "Dropping database ${destdb}..."
echo "DROP DATABASE IF EXISTS ${destdb}" | mysql ${conn}
echo "CREATE DATABASE ${destdb}" | mysql ${conn}
# First, all the tables.
for table in `echo "SHOW FULL TABLES WHERE Table_type <> 'VIEW'" | mysql $conn $sourcedb | tail -n +2`;
do
if [[ "${table}" != 'BASE' && "${table}" != 'TABLE' && "${table}" != 'VIEW' ]] ; then
createTable=`echo "SHOW CREATE TABLE ${table}"|mysql -B -r $conn $sourcedb|tail -n +2|cut -f 2-`
echo "Restoring ${destdb}/${table}..."
echo "$createTable ;" | mysql $conn $destdb
insertData="INSERT INTO ${destdb}.${table} SELECT * FROM ${sourcedb}.${table}"
echo "$insertData" | mysql $conn $destdb
fi
fi
done
done
echo "SET foreign_key_checks=1" | mysql ${conn}
echo "SET unique_checks=1" | mysql ${conn}
echo "COMMIT" | mysql ${conn}
# Restart the replication client
echo "START SLAVE" | mysql ${conn}
All of these operations are, as I mentioned, within the same server. Is there a faster way to clone the tables I'm not seeing? They're all INNODB tables.
Thanks!
