Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

share|improve this answer
    
I will like to avoid script. Is it possible to dump 1 table from 1 database but with "use schema" at the top? –  shantanuo Aug 23 '12 at 12:52
    
mysqldump was not designed to do that –  RolandoMySQLDBA Aug 23 '12 at 14:10
    
I did update my answer about 42 minutes ago to append mysqldump to use dbname. –  RolandoMySQLDBA Aug 23 '12 at 16:03

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.