Import & Export all MySQL databases

This tutorial explains how to backup and restore MySQL or MariaDB databases from the command line using the mysqldump utility.

I. Export Databases

We have 2 options to export all databases

1. Export all databases into only 1 file

mysqldump -u root -p --all-databases > alldb.sql

2. Export all databases into files for each database

#!/bin/bash
USER="zend" PASSWORD="" #OUTPUT="/Users/rabino/DBs"
#rm "$OUTPUTDIR/*gz" > /dev/null 2>&1
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
for db in $databases; do
     if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
         echo "Dumping database: $db"
         mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
        # gzip $OUTPUT/`date +%Y%m%d`.$db.sql
     fi
 done

II. Import Database

Split database into standalone file

sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' alldatabases.sql > output.sql

Run following command to import database

mysql -u <username> -p <dbname> < output.sql