The following commands dump a MySQL database from a remote server and create a corresponding database on the local computer.
The instructions have been tested on OSX and Linux (Ubuntu/Debian). On-line SSH compression is applied, so transferring SQL files, which are text content and compress well, should be around 6x faster than normal.
(Well… actually the script is six lines, but because this is my blog I’ll decide it doesn’t count)
- Remotely runs mysqldump and puts the result to a local file
- Creates a MySQL database and corresponding user with full access to this database
- Reads the content of mysqldump to the newly created database
ssh firstname.lastname@example.org -C -o CompressionLevel=9 mysqldump -u YOURDATABASEUSER --password=YOURDATABASEPASSWORD --skip-lock-tables --add-drop-table YOURDATABASENAME > YOURDATABASENAME.sql mysql -uroot -p create database YOURDATABASENAME; connect YOURDATABASENAME; source YOURDATABASENAME.sql GRANT ALL ON YOURDATABASENAME.* TO 'YOURDATABASEUSER'@'localhost' identified by 'YOURDATABASEPASSWORD';
Leave out create database and GRANT for the subsequent runs – all data on the local computer will be replaced.