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.

Has anyone got a one liner (command line) that will dump all databases to seperate dump files e.g.

forumdb.dump
wikidb.dump
etc.
share|improve this question

3 Answers 3

up vote 3 down vote accepted

Why not, still a long one liner though :)

mkdir mysql-all-dumps ; for x in `mysql -u root -pXXXXXXXX -e "SHOW DATABASES;" | tr -d "| " | grep -vE '(Database|information_schema)'` ; do mysqldump -v -u root -pXXXXXXXXX $x > mysql-all-dumps/$x.sql ; done

Make sure to replace the XXXX with the correct mysql root password and the verbose option in the mysqldump is optional as well.

share|improve this answer
    
Thanks, I can tell you have used this before :P It's well thought out –  James Sep 13 '11 at 21:08
    
Yes, when I had to backup all the individual databases prior to a mysql upgrade :) –  SparX Sep 13 '11 at 21:29
    
Nice - how would we go about this if we wanted a backup-user to dump the files as opposed to have root do it (so we don't have to have root passwords in cron files)? –  g18c Jan 20 '13 at 20:47

can do this

cd mysql_data_dir ; for d in $(ls -d */ | xargs basename) ; do mysqldump $d > /path/to/dumps/$d.dump; done

Basically list all the directories in the data directory, and then just dump them. I've done the above in a pinch if I didn't have a jobs set to backup individual files.

share|improve this answer

I don't believe there is a good one-liner, but here is a script fragment. You might be able to adapt this to your usage. If you want to hard-code your database list, you could probably get it down to a one-liner. But I needed something that would be able to handle database adds and drops.

OPTIONS="--verbose --lock-tables --flush-logs"

BACKUPDATE=`date +"%y%m%d%H"`

DBSQL="SELECT SCHEMA_NAME FROM information_schema.SCHEMATA where SCHEMA_NAME!='information_schema' \
       AND SCHEMA_NAME!='performance_schema' order by SCHEMA_NAME"

# retrieve list of databases
DBS=`/usr/bin/mysql --defaults-extra-file=/etc/mysql/credentials.cnf --batch \
                                  --skip-column-names --execute "$DBSQL"`
# cleat it up a bit
DBS=`echo $DBS | tr -d '\n' | sed -e "s/ \+/ /g"`

# backup each database into a separate file
for DB in $DBS; do
  /usr/bin/mysqldump --defaults-extra-file=/etc/mysql/credentials.cnf ${OPTIONS} $DB > /backup_${DB}_${BACKUPDATE}
done
share|improve this answer
    
Nice script - what would one put in credentials.cnf? How would we add a user to MySQL that is only allowed to backup? –  g18c Jan 20 '13 at 21:12

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.