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 thestandard mysql database backup command which will be executed for each database:
Hello, Guest ! You can Login or Register to www.ivorde.ro!
Post comment:
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.
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.