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: http://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
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.
Top comments (0)