Export and Import all MySQL databases at one time

Question

I want to keep a backup of all my MySQL databases. I have more than 100 MySQL databases. I want to export all of them at the same time and again import all of them into my MySQL server at one time. How can I do that?

Answer 1

Export:

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

Look up the documentation for mysqldump. You may want to use some of the options mentioned in comments:

mysqldump -u root -p --opt --all-databases > alldb.sql mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql 

Import:

mysql -u root -p < alldb.sql 

Answer 2

Other solution:

It backs up each database into a different file

#!/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