Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

In your script

echo "SET autocommit=0" | mysql ${conn}
echo "SET unique_checks=0" | mysql ${conn}
echo "SET foreign_key_checks=0" | mysql ${conn}

Each time a command is executed, it is immediately undone because the DB connection terminates. Had you placed all you commands in a single file and executed the single file, all the options you are disabling would stay off through the session.

I rewrote the script to put all the commands into a single file, just like a mysqldump would have. I also changed the way the tables are written out as commands. Then, I would run the single file:

dbs=( analytics auth logs users )
server=localhost
conn="-h ${server} -u ${username} --password=${password}"

PREFIX=whatever
DUMPFILE=MySQLDataToClone.sql
echo "STOP SLAVE;" > ${DUMPFILE}
echo "SET autocommit=0;" >> ${DUMPFILE}
echo "SET unique_checks=0;" >> ${DUMPFILE}
echo "SET foreign_key_checks=0;" >> ${DUMPFILE}

for SRCDB in ${dbs[*]}
do
    echo "DROP DATABASE IF EXISTS ${PREFIX}${SRCDB};" >> ${DUMPFILE}
    echo "CREATE DATABASE ${PREFIX}${SRCDB};" >> ${DUMPFILE}
done

for SRCDB in ${dbs[*]}
do
    SQLTOGETTABLES="SELECT CONCAT('CREATE TABLE ${PREFIX}',dbtb,' LIKE ',dbtb,'; INSERT INTO ${PREFIX}',dbtb,' SELECT * FROM dbtb;') FROM (SELECT CONCAT(table_schema,'.',table_name) dbtb FROM information_schema.tables WHERE table_schema='${SRCDB}' AND engine IS NOT NULL) A;"
    mysql $conn -ANe"${SQLTOGETTABLES}" >> ${DUMPFILE}
done

echo "START SLAVE;" >> ${DUMPFILE}

mysql ${conn} < ${DUMPFILE}

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.