Export All MySQL Databases

Here we cover a method to automatically create a backup of all MySQL databases on your VPS or Dedicated server via command line.

It is recommended to do this prior to upgrading the MySQL or MariaDB version on your server. It may also be used prior to reinstalling MySQL to address more severe issues or as a precaution in case of potential future failures.

Related Articles

How to SSH into VPS or Dedicated Servers
Export Database Using mysqldump
Export MySQL Database Using phpMyAdmin

What to do or how to resolve:

IMPORTANT: This solution will require root access. If this needs to be obtained, follow the guide on requesting root access.

1. Access the server via SSH as the root user or using the Terminal in Root WHM
2. Run the following command

   (
   TIMESTAMP=$(date +%Y-%m-%d_%H%M)
   mkdir -p /root/dbbackups/$TIMESTAMP
   LIST="/root/dbbackups/${TIMESTAMP}/01_LIST"
   echo -e "\tBacking up each database individually to /root/dbbackups/${TIMESTAMP}/"
   echo -e "\tDB Backup list located at ${LIST}"
   touch $LIST
   for DB in $( mysql -e 'show databases' | grep -v "Database\|information_schema" | awk '{print $1}' ); do
   mysqldump --add-drop-table $DB > /root/dbbackups/$TIMESTAMP/$DB.sql && echo $DB >> $LIST
   done
   cat $LIST
   )

NOTE: This will export all MySQL/MariaDB databases to a timestamped directory inside of /root/dbbackups/ and provide a list of all backed up MySQL databases to /root/dbbackups/$TIMESTAMP/01_LIST.