When using mysqldump, the tool has to be compatible with mysql server version. Some hosters might not provide such an environment. That's one reason when you wanna use mysqldump on your local system to create an mysql dump from a foreign database.
Most environments prevent remote access to the database server, which is a good thing. But this would prevent you from using mysqldump on your local environment. To overcome this issue, you can use ssh to create a temporary tunnel to the database server through another server, e.g. the production system.
This one is rather a short blog post. It will not go into detail and explain the used tools and concepts. Instead it is more or less used to keep and share the knowledge. I find it especially necessary if you are working with german TYPO3 hoster mittwald.de.
Of course one can also use SSH and some database GUIs instead, which will do exactly the same. Just I like the CLI approach.
Creating and using the tunnel ¶
ssh -L <local-port>:<database-server>:<remote-port> -N <server-used-for-tunnel>
So an example to create a tunnel to the database connection
db1234.dbserver.com with Port
3306, which is only available to
example.com could look like this:
ssh -L 3307:db1234.dbservers.com:3306 -N example.com
After the tunnel is created, the local port 3307 is passed through the tunnel. This enables the following mysqldump execution:
mysqldump -u <username> -P 3307 -h 127.0.0.1 -p <dbname> > dump.sql;
The important part is to provide
-P 3307 -h 127.0.0.1, in order to use the tunnel.