Mysql how to dump schema of all databases

It is a good idea to frequently take schema dump of all your databases. This may be useful in case there is some accidental change. This is also useful for viewing schema history.

Mysql schema dump using mysqldump

$ mysqldump -u username -ppassword --no-data --all-databases

Daily schema dump

To take daily schema dump in a shell script use the following bash code in cron

DATE=$(date +"%Y%m%d")
mysqldump -u username -ppassword --no-data --all-databases > mysql.schema.$DATE

Or you can use the following one liner in cron

mysqldump -u username -ppassword --no-data --all-databases > mysql.schema.`date +"%Y%m%d"`

Schema dump over ssh

In case you want to run it over ssh so that you can take mysqldump on you local Mac or Linux machine, you can use the following command:

$ ssh -i id_rsa -l user machine.com "mysqldump -u username -ppassword -h localhost --no-data --all-databases"
Share this article: share on Google+ share on facebook share on linkedin tweet this submit to reddit

Comments

Click here to write/view comments