MySQL backups are normally done using mysqldump – a MySQL database backup program. The problem is all the backups will be written to a single output SQL file. I prefer to dump each MySQL database to a separate SQL file.
To suit my requirements, I wrote a small shell script to dump MySQL databases to separate SQL files. Below is the script.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
#!/bin/bash db_to_skip="mysql|information_schema|performance_schema" read -p "Enter Username: " mysqluser read -s -p "Enter Password: " mysqlpass echo "" for db in $(mysql -u"$mysqluser" -p"$mysqlpass" -N -e 'show databases' | grep -viE "$db_to_skip") ; do echo exporting database $db mysqldump -u"$mysqluser" -p"$mysqlpass" --databases $db > $db.sql done |
A sample output the script is shown below.
|
1 2 3 4 5 |
$ /path/to/mydump.sh Enter Username: root Enter Password: exporting database db1 exporting database db2 |
The script will not export databases specified in the “db_to_skip” variable. You can modify the variable to suit your requirements.
I have only tested the script on Linux on bash shell. Modifications might be required to run the script on other UNIX distributions – especially on the grep and read commands.
Do take note that when the shell script is running, there is a chance that a logged in user could view the specified username and password of the MySQL server by running the ps command.
Mohamed Ibrahim
ibrahim = { interested_in(unix, linux, android, open_source, reverse_engineering); coding(c, shell, php, python, java, javascript, nodejs, react); plays_on(xbox, ps4); linux_desktop_user(true); }
