FreeBSD - Tutorials, Security
Home   Archives   Sitemap   About   Contact

How to backup all mysql databases with one command

Home NEW! Unix Forum News 100 Tips and Tricks Website Development Server Operating Systems Databases
 Ivorde.ROarrow Databases arrowSecurity and BackuparrowHow to backup all mysql databases with one command 

Article Sections

    Hello, Guest !
User name:
Password:
 
Google

 SSD VPS Hosting - Vpsie.com
 Mo.nitor.me
 Ivorde forum
 FreeBSD Tutorials
 Linux LVM Commands
 Free Shell Accounts
 FreeBSD Project
 FreeBSD Handbook
 Advanced Bash-Scripting Guide
 The OpenBSD Project
 Distrowatch
 FreeBSD Handbook


Apache Webserver Home Page

Posted on: 22 Jan 2008
Author: mandrei
Section: Databases | Security and Backup
Views: 400
Comments: 1 (Add)
More articles in Databases / Security and Backup
Previous article: Recover a lost MySQL root password 

How to backup all mysql databases with one command
backup mysql database with one command, mysqldump backup, database backup

 



Many times I needed a command to transfer all my databases to a new server/environment. Following is the command I have used:

One command to backup all mysql databases

First we will need to create a directory where to store all the backups and change directory to it, and then backup all mysql databases with one command: 

$ mkdir ~/mysql_all_dbs && cd $_
$ for i in $(mysql -u root -ppassword -e "SHOW DATABASES"|tail +2); do mysqldump -u root -ppassword $i | gzip -9 > ./$i.sql.gz; done

You will need to replace -ppassword with -pYOUR_MYSQL_ROOT_PASSWORD (no spaces between -p and the password). If your mysql root user doesn't have a password, you don't need to use -u root and -ppassword flags.

Taking the command piece by piece:

$mysql -u root -ppassword -e "SHOW DATABASES"|tail +2
information_schema
some_database
some_database1
some_db2
db_some3
mysql
test

The above command will execute the mysql query: SHOW DATABASES without the following header, which is not needed in our case:
+----------------------+
| Database             |
+----------------------+

Next the command will take each database name (information_schema, some_database and so on) and provide it to the standard mysql database backup command which will be executed for each database:

$ mysqldump -u root -ppassword database_name | gzip -9 > database_name.sql.gz

Each database backup file will be gziped before finish.

Now to check that everything went well, list the curent directory:

$ ls
information_schema.sql.gz   some_database.sql.gz         some_database1.sql.gz
some_db2.sql.gz                 db_some3.sql.gz                mysql.sql.gz
test.sql.gz

Now all that is to be done, depending on your situation, is to gather all these files in one tar file or scp them directly to the new environment.

Related: How to copy a mysql database using mysqldump

 

Bookmarks: Echo "How to backup all mysql databases with one command" around:
del.icio.usdiggFurlYahooMyWebGoogleBookmarksFaceBookTechnocratti
-------------------advertising-----------------

Other articles in Databases / Security and Backup
» How to copy a mysql database using mysqldump
» How to set up a password for root user in MySql
» MySQL table - change or rename MySql table name
» Recover a lost MySQL root password
» How to calculate difference in days between two dates in MySQL




Contact webmaster regarding this article
Register or Login to post your article
Hello, Guest ! You can Login or Register to www.ivorde.ro!

 Post comment:

Name:
Title:
Comment:
Please type the word you see in the image (anti-spam verification). Refresh the page if you don't understand the word.
Allowed HTML Tags for comments:<p><strong><em><u><h1><h2><h3><h4><h5><h6><img><li>
<ol><ul><span><div><br><ins><del>

1 comment(s) to How to backup all mysql databases with one command:

1. Re: How to backup all mysql databases with one command
Backup all tables from one database by Andrei at January 30th, 2008 - 12:41
This command can also be used to backup all tables in one database:
"mysql -u root -ppassword dbname -e "SHOW TABLES" |tail +2" shows the tables.
for i in $(mysql -u root -ppassword dbname -e "SHOW TABLES" |tail +2); do mysqldump -u root -ppassword dbname $i | gzip -9 > ./dbname_$i.sql.gz backups all tables in database (dbname) to curent directory, in separate files.

   Latest topics on the forum:
 
   Most viewed articles:
How to copy a mysql database using mysqldump - 9376 views
How to change a user's password in AIX with the output from ECHO command - 8635 views
FreeBSD: Add/remove an additional IP alias - 5609 views
Qmail relay to smarthost: How to route all mail to a smarthost - 3929 views
Print queues in AIX 5L - How to list print queues in AIX - 2885 views

   Latest 10 articles:
Qmail relay to smarthost: How to route all mail to a smarthost - 03 Feb 2009
EXIM 4 relay to smarthost: How to route all mail except local domain - 03 Feb 2009
Windows XP: print LISTEN ports and network connections using netstat - 30 Jan 2009
How to cut out first last n characters from each file name, from a filelist - 04 Nov 2008
Mozilla Firefox3 is now released - 18 Jun 2008
How to switch lower case to upper case and upper case to lower case in a string - 17 Jun 2008
How to rename files/directories to uppercase/lowercase character names - 17 Jun 2008
How to convert lower case to upper case letters in a shell script/command - 17 Jun 2008
Unix,Linux,FreeBSD - How to rename a list of files, replacing spaces inside their names - 12 Jun 2008
How to change a user's password in AIX with the output from ECHO command - 21 May 2008


Archives
» 2007  |  June  |  October  |  November  |  December
» 2008  |  January  |  February  |  March  |  April  |  May  |  June  |  November
» 2009  |  January  |  February



Home | Archives | Sitemap | About | Contact

Designed and developed by Andrei Manescu. Optimized for Mozilla Firefox.  
Copyright 2007 Andrei Manescu
All trademarks and copyrights on this page are owned by their respective owners. Comments are owned by those who posted them.
Valid W3 Document Valid XHTML 1.0 Transitional Valid CSS! The FreeBSD Project Viewable With Any Browser