Backup MySQL Database to a file

Backing up your database is a very important system administration task, and should generally be run from a cron job at scheduled intervals. We will use the mysqldump utility included with mysql to dump the contents of the database to a text file that can be easily re-imported.

Syntax:

mysqldump -h localhost -u root -pmypassword databasename > dumpfile.sql

Example:

mysqldump -h localhost -u root -p2Uad7as9 database01 > dumpfile.sql

This will give you a text file containing all the commands required to recreate the database.

Access Your MySQL Server Remotely Over SSH

So you’ve got MySQL on your web server, but it’s only opened to local ports by default for security reasons. If you want to access your database from a client tool like the MySQL Query Browser, normally you’d have to open up access from your local IP address… but that’s not nearly as secure.

So instead, we’ll just use port-forwarding through an SSH tunnel, so your MySQL client thinks it’s connecting to your localhost machine, but it’s really connecting to the other server through the tunnel.

If you are using the command line ssh, the command would look like this. (You can do the same thing graphically in Putty or SecureCRT options if you need to)

ssh -L 3306:localhost:3306 geek@webserver.com

The syntax is ssh -L <localport>hostname<remoteport> <username>@<servername>. We’re using localhost as the hostname because we are directly accessing the remote mysql server through ssh. You could also use this technique to port-forward through one ssh server to another server.

If you already have mysql running on your local machine then you can use a different local port for the port-forwarding, and just set your client tools to access MySQL on a different port.

Once you’ve got the ssh tunnel going, you can open up MySQL Query Browser and enter in the details for your remote server, using localhost as the server host, and adjust the port to whatever you used.

Rename or change name of MySQL table

If you change your mind and want to rename an existing MySQL table, with or without data in it, it is no problem. One simple command will change the table’s name.

To change the name of an existing table old to new, use this command as a user with adequate privileges:

RENAME TABLE old TO new;

It is good DBA manners to make sure that no one and no program are using this table before making the name change.