Mysql – Creating dump file with use command

backupMySQLmysqldump

I am taking backup of 2 tables from different schema.
I need to create the "use dbname" statement at the beginning of each table.

mysqldump --databases company --tables loadme --databases new_company --tables new_load

use company
...
usual loadme dump
...

use new_company
...
usual new_load dump
...

This does not seem to be possible with mysqldump command.

Best Answer

The mysqldump utility can dump the following ways from a MySQL Instance:

  • All Databases
  • One Whole Database
  • Part of a Database
    • It is possible to mysqldump a space-separated list of tables from one Database
  • One Whole Table
    • It is not possible to mysqldump a table from one Database and a table from another
  • Part of a Table Using --where Option
  • Stored Procedures
  • Schema Only
  • Data Only

You cannot mysqldump two tables from two different schema. You must script the tables you want dumps one at a time.

Here is a script to dump every table from a MySQL Instance into separate files

MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables"
SQL="${SQL} WHERE table_schema NOT IN ('information_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} > ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

In your case, you can create the /tmp/ListOfTables.txt file manually. In order to inject use dbname, just create the dump file with the use dbname as the first line. Then, append the output of mysqldump to it. Keep in mind that each table is described as dbname.tablename:

rm -f /tmp/ListOfTables.txt
echo "company.loadme" >> /tmp/ListOfTables.txt
echo "new_company.newload" >> /tmp/ListOfTables.txt
MYSQL_USER=root
MYSQL_PASS=rootpass
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
COMMIT_COUNT=0
COMMIT_LIMIT=10
for DBTB in `cat /tmp/ListOfTables.txt`
do
    DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
    echo "use ${DB}" > ${DB}_${TB}.sql
    mysqldump ${MYSQL_CONN} --hex-blob --triggers ${DB} ${TB} >> ${DB}_${TB}.sql &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Give it a Try !!!

Related Question