How to back up all MySQL databases via a command-line interface in Plesk for Linux

Question

How to back up all MySQL databases via a command-line interface in Plesk for Linux?

Answer

1. Connect to a server via SSH.

2. Create a directory where backup files will be stored:

   # mkdir /root/mysql_dumps_all

3. Get a list of all databases:

   # cd /root && /usr/sbin/plesk db -e "show databases" | grep -v -E "^Database|information_schema|performance_schema|phpmyadmin" > dblist.txt

4. Create a dump of each MySQL database:

   # cat /root/dblist.txt | while read i; do /usr/sbin/plesk db dump "$i" > /root/mysql_dumps_all/"$i".sql; done

5. List all database dumps:

   # ls -l /root/mysql_dumps_all

To restore all databases from dump files created above, run the command:

# for i in `cat /root/dblist.txt`; do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u admin < /root/mysql_dumps_all/"$i".sql; done