How to export multiple MySQL databases in a separate .sql file

The easiest way to export multiple MySQL databases in a separate .sql file is through a bash script.

Prerequisites

• MySQL installed
• sudo privileges

Solution

• The connection variables should be changed as per your requirements.

#!/usr/bin/env bash

MYSQL_HOST=<mysql_host>
MYSQL_USER=<mysql_user>
MYSQL_PASSWORD=<mysql_password>

databases=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql)")

for db in $databases; do
    mysqldump -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD --databases $db > $db.sql
done

• The script gets a list of all databases, excluding the system databases (information_schema, performance_schema, and mysql). It then loops through each database and exports it to a separate .sql file using the mysqldump command.

• Save the script and then run it using the following command:

  bash export_all_databases.sh
  

Conclusion

Feel free to leave a comment below and if you find this tutorial useful, follow our official channel on Telegram.