{"id":2136,"date":"2017-03-18T04:32:55","date_gmt":"2017-03-18T04:32:55","guid":{"rendered":"http:\/\/myprojects.advchaweb.com\/?p=2136"},"modified":"2018-01-25T06:29:15","modified_gmt":"2018-01-25T06:29:15","slug":"script-to-backup-each-mysql-database-in-separate-files","status":"publish","type":"post","link":"https:\/\/myprojects.advchaweb.com\/index.php\/2017\/03\/18\/script-to-backup-each-mysql-database-in-separate-files\/","title":{"rendered":"Script To Backup & Restore Each MySQL Database In Separate Files"},"content":{"rendered":"

Ref: http:\/\/stackoverflow.com\/questions\/9497869\/export-and-import-all-mysql-databases-at-one-time<\/a><\/p>\n

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:<\/p>\n

mysqldump -u root -p --all-databases > alldb.sql<\/pre>\n
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:<\/p>\n
#!\/bin\/bash\r\n\r\nUSER=\"root\"\r\nPASSWORD=\"pass\"\r\n#OUTPUT=\"\/Users\/rabino\/DBs\"\r\n\r\n#rm \"$OUTPUTDIR\/*gz\" > \/dev\/null 2>&1\r\n\r\ndatabases=`mysql -u $USER -p$PASSWORD -e \"SHOW DATABASES;\" | tr -d \"| \" | grep -v Database`\r\n\r\nfor db in $databases; do\r\n    if [[ \"$db\" != \"information_schema\" ]] && [[ \"$db\" != \"performance_schema\" ]] && [[ \"$db\" != \"mysql\" ]] && [[ \"$db\" != _* ]] ; then\r\n        echo \"Dumping database: $db\"\r\n        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql\r\n       # gzip $OUTPUT\/`date +%Y%m%d`.$db.sql\r\n    fi\r\ndone<\/pre>\n
Make sure the ‘USER’ and ‘PASSWORD’ values for your MySQL server are correct!.
\nThen modify the file permission:<\/p>\n
sudo chmod +x backup_each_db.sh<\/pre>\n
Then run the script:<\/p>\n
.\/backup_each_db.sh<\/pre>\nSORRY, ONLY ADMIN CAN SHOW THIS!<\/span>\n
To restore all the databases from the sql files, please run like this:
\nteddy@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
\nref: https:\/\/stackoverflow.com\/questions\/4708013\/import-multiple-sql-dump-files-into-mysql-database-from-shell
\nSORRY, ONLY ADMIN CAN SHOW THIS!<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
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: 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 … Continue reading “Script To Backup & Restore Each MySQL Database In Separate Files”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[72],"tags":[],"class_list":["post-2136","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/posts\/2136","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/comments?post=2136"}],"version-history":[{"count":6,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/posts\/2136\/revisions"}],"predecessor-version":[{"id":2138,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/posts\/2136\/revisions\/2138"}],"wp:attachment":[{"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/media?parent=2136"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/categories?post=2136"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/myprojects.advchaweb.com\/index.php\/wp-json\/wp\/v2\/tags?post=2136"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}