Sometimes you need to make a dump of all MySQL databases, possibly prior to an upgrade or before you apply a required fix.
I prefer to dump all database as .SQL as an added safety measure, just in case a back out plan is required.
First we create a folder for our database dumps:
# mkdir /root/mysqlbackup
If you are running Plesk, let’s take a dump of the psa database:
# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` psa > /root/mysqlbackup/psa.`date +%F_%H.%M`.sql
We certainly want a dump of the mysql database itself:
# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` mysql > /root/mysqlbackup/mysql.`date +%F_%H.%M`.sql
Now we can perform a dump of all other databases:
# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow ` --all-databases > /root/mysqlbackup/all.`date +%F_%H.%M`.sql
If you now need to complete a MySQL upgrade (which may have failed prior) you can complete it as follows:
# mysql_upgrade -uadmin -p` cat /etc/psa/.psa.shadow ` --debug-check --debug-info --verbose