How often did you download your production database to your development machine? And how often did you write a script for that?
You’re first dumping your MySQL production database – easy. Then you download it with scp or ftp or … to your local development machine and then you overwrite your existing database with that dump – easy. But: those are three steps. Often you do it manually, “because I don’t need it that often”… Don’t you?
Well, here’s the most easiest way I figured out, yet. And it’s a one-liner:
ssh <username>@<production-host> mysqldump -u root <production_database> | mysql -u root <development_database>
Where username is your user name for the production host prodcution-host. production_database is the name of the production database on the production server. And development_database is the name of the development database on your local machine, i.e. your dev box 😉
Important: If you have a different username for the mysql database, replace root with your username. And, if you are using a password on one of the databases, supply the “-p” parameter to the mysql/mysqldump calls.
For example:
ssh steffen@example.com mysqldump -u root prod_db | mysql -u root dev_db
Never think about it again 😉