Dump MYSQL databases to induvidual files

Question

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

forumdb.dump
wikidb.dump
etc.

Answer 1

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.

Answer 2

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.

Answer 3

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