Dump MYSQL databases to induvidual files
Solution 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.
Solution 2:
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
Solution 3:
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.