In order to backup all databases on a mysql server at once, you can use the following command:
# mysqldump --all-databases -u xxx --password="xxx" | gzip > full.backup.sql.gz
This will create an sql file with a dump of all databases and compress it. It's nice, fast and easy. But it's then not so easy to work with this one single file (especially if only one database needs to be restored).
But this can also be done with a one-liner (though a long one...):
# echo 'show databases' | mysql -u xxx --password="xxx" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u xxx --password="xxx" {} | gzip > /backup/mysqldump-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz'
This first gets all databases in the local mysql server, dumps each of them and stores them compressed.
# mysqldump --all-databases -u xxx --password="xxx" | gzip > full.backup.sql.gz
This will create an sql file with a dump of all databases and compress it. It's nice, fast and easy. But it's then not so easy to work with this one single file (especially if only one database needs to be restored).
But this can also be done with a one-liner (though a long one...):
# echo 'show databases' | mysql -u xxx --password="xxx" --skip-column-names | grep -v information_schema | xargs -I {} -t bash -c 'mysqldump -u xxx --password="xxx" {} | gzip > /backup/mysqldump-$(hostname)-{}-$(date +%Y-%m-%d-%H.%M.%S).sql.gz'
This first gets all databases in the local mysql server, dumps each of them and stores them compressed.
No comments:
Post a Comment