MySQL FAQ

Table of Contents

1. I am getting errors when using mysqldump
2. I want to tunnel my MySQL client through SSH

---

I am getting errors when using mysqldump

• Error 29: use --single-transaction e.g: mysqldump --opt --single-transaction -u root -p drupal_dataportal > drupal_dataportal-2009-12-11.sql
• something with "cannot open or write to tmp/#table.myd" -> use --skip-locktables ???

Maybe you will find a solution here: http://forge.mysql.com/wiki/Category:FAQ

I want to tunnel my MySQL client through SSH

Using bash:

ssh -L 3307:domain.name.of.mysqlserver:3306 username@domain.name.of.gatewayserver

This will open a tunnel, listening on localhost:3307 and forwarding everything to mysqlserver:3306, and doing it all via the ssh service on the gateway machine. Most of the time mysqlserver and gatewayserver are the same machine. This example shows us specifying port 3307 on the local end of the tunnel; We do this because maybe we are running a MySQL server on the local machine, so we can't re-use the default MySQL port.

You'll now have a terminal open on the gateway machine, but you don't need it for this procedure, so set it aside.

Now, on your local machine, execute a mysql connection like so:

mysql -u username -p -h 127.0.0.1 -P 3307 databasename

(or use HeidiSQL, MySQLadminstrator etc.)

In other words, mysql thinks it's connecting to localhost, but on a different port. In fact, the connection is being made securely to the remote mysql server, via the gateway machine and the local "mouth" of the ssh tunnel on your own machine.

When you're finished with your mysql session, log out of the session on the gateway machine. That will properly close the tunnel.