Ref: http://stackoverflow.com/questions/9497869/export-and-import-all-mysql-databases-at-one-time
Here I want to backup all my database (more than 100 databases) in separate files. I can backup all the database in one single file like this:
|
1 |
mysqldump -u root -p --all-databases > alldb.sql |
But I want to pick only some of the databases to be imported on the different MySQL version and left out the rest (some of the databases I don’t want to be imported). From the top link, I create a new bash file ‘backup_each_db.sh’. Here is the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
#!/bin/bash USER="root" PASSWORD="pass" #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 |
Make sure the ‘USER’ and ‘PASSWORD’ values for your MySQL server are correct!.
Then modify the file permission:
|
1 |
sudo chmod +x backup_each_db.sh |
Then run the script:
|
1 |
./backup_each_db.sh |
SORRY, ONLY ADMIN CAN SHOW THIS!
To restore all the databases from the sql files, please run like this:
teddy@teddy:/media/teddy/Data/MASTER/MySQL/20180124_db_backup$ for SQL in *.sql; do DB=${SQL/\.sql/}; echo importing $DB; mysql -uroot -pYOURPASSWORD < $SQL; done
ref: https://stackoverflow.com/questions/4708013/import-multiple-sql-dump-files-into-mysql-database-from-shell
SORRY, ONLY ADMIN CAN SHOW THIS!