I'm writing a single line command that backups all databases into their respective names instead using of dumping all in one sql.
Eg: db1 get saved to db1.sql and db2 gets saved to db2.sql
So far, I'd gathered the following commands to retrieve all databases.
mysql -uuname -ppwd -e 'show databases' | grep -v 'Database'I'm planning to pipe it with awk to do something like
awk '{mysqldump -uuname -ppwd $1 > $1.sql}'But that doesn't work.
I'm new to bash, so I could be wrong in my thinking.
What should I do to make it export
the db in their respective names?
update:
Ok, have to finally managed to get it working from the hints below.
This is the final script
The echoing part of doesn't work though.
Mattias Geniar, August 24, 2015
Follow me on Twitter as @mattiasgeniar
If’s often very useful to have a couple of MySQL oneliners nearby. This guide will show you how to take a mysqldump of all databases on your server, and write each database to its own SQL file. As a bonus, I’ll show you how to compress the data and import it again – if you ever need to restore from those files.
Take a mysqldump back-up to separate files
To take a back-up, run the mysqldump tool on each available database.
$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; doneThe result is a list of all your database files, in your current working directory, suffixed with the .sql file extension.
$ ls -alh *.sql -rw-r--r-- 1 root root 44M Aug 24 22:39 db1.sql -rw-r--r-- 1 root root 44M Aug 24 22:39 db2.sqlIf you want to write to a particular directory, like /var/dump/databases/, you can change the output of the command like this.
$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/dump/databases/"$dbname".sql; doneMysqldump each database and compress the SQL file
If you want to compress the files, as you’re taking them, you can run either gzip or bzip on the resulting SQL file.
$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; [[ $? -eq 0 ]] && gzip "$dbname".sql; doneThe result is again a list of all your databases, but gzip'd to save diskspace.
$ ls -alh *.gz -rw-r--r-- 1 root root 30K Aug 24 22:42 db1.sql.gz -rw-r--r-- 1 root root 1.6K Aug 24 22:42 db1.sql.gzThis can significantly save you on diskspace at the cost of additional CPU cycles while taking the back-up.
Import files to mysql from each .SQL file
Now that you have a directory full of database files, with the database name in the SQL file, how can you import them all again?
The following for-loop will read all files, strip the “.sql” part from the filename and import to that database.
Warning: this overwrites your databases, without prompting for confirmation. Use with caution!
$ for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; doneThe output will tell you which database has been imported already.
$ for sql in *.sql; do dbname=${sql/\.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done Now importing db1 ... done. Now importing db2 ... done.These are very simple one-liners that come in handy when you’re migrating from server-to-server.
Want to subscribe to the cron.weekly newsletter?
I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.
It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.
No spam. Just some good, practical Linux & open source content.
Sometimes we need to to dump all databases form a MySQL server. This can be done using the mysqldump utility with the –all-databases switch enabled. In this case mysqldump will dump all databases to the standard output or to a file if explicitly specified. Let’s assume a situation where we want to backup all of the databases from the server, with the possibility to restore only one database at a time. To achieve this, you need to dump all the databases one by one or to use a pre-made script to do the heavylifting for you. I’ve found a simple solution here: //soniahamilton.wordpress.com/2005/11/16/backup-multiple-databases-into-separate-files/. I used this script as a starting point, and made some enhancements to it:
MySQL username can be specified from the command line (it’s not hardcoded in the file)
The script asks for the corresponding MySQL password for security reasons (the password is not echoed or saved anywhere – as you would expect on a Unix systems)
The output dir can also be specified from the command line
Support for gzipped output files (optional)
Usage
Download the dump-all-databases.sh. Make it executable using chmod +x dump-all-databases.sh.
Run it:
./dump-all-databases.sh -u user -o my\_backup\_dir -z
The command line options are the following:
- -u – the MySQL user
- -o – output dir (optional, if not specified the dump files will be placed in current directory)
- -z – enables the gzipped output (optional)
Download
dump-all-databases.sh
System requirements
Unix/Linux system with mysql and mysqldump installed, for gzipped output gzip is also required.
The post How to dump all MySQL databases into separate files appeared first on Daniel Werner.