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.

Copy an existing MySQL table to a new table

This is a great set of two commands that allow the creation and population of a new table with the structure and data of an existing table. This provides a quick means of making a point-in-time copy of a table and is a safe, easy way to make a quick copy of a table for testing an application in development on live data without risking a production environment.

To make a copy of the table mytable which is in a different database called production into a new table called mytable_new in the currently selected database, use these two commands:

CREATE TABLE mytable_new LIKE production.mytable;
INSERT mytable_new SELECT * FROM production.mytable;

The first command creates the new table mytable_new by duplicating the structure of the existing table. The second command copies the data from old to new.

The nomenclature production.mytable is a means of specifying the database and table in the same way that a file can be specified by its directory path. It is optional. If production was left off, MySQL would assume that the mytable table was also in the currently selected database.

Import CSV file directly into MySQL

Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile 'mine.csv' into table tblUniq
fields terminated by ','
enclosed by '"'
lines terminated by 'n'
(mineName, mineCity, mineComments)

The fields here are the actual tblMine table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

Ignore duplicate entries in MySQL select using DISTINCT keyword

Sometimes every occurance of a value which may be duplicated multiple times in a result set is not needed. For example, if making a pulldown menu list of options, each option should be seen only once. The DISTINCT keyword in a select statement eliminates duplication in the result set.

The column party from the presidents sample table has many repeats in it. To select a list of the parties from the table, use:

SELECT DISTINCT party FROM presidents;

This returns a result set that looks like:

+-----------------------+
| party |
+-----------------------+
| no party |
| Federalist |
| Democratic-Republican |
| Democratic |
| Whig |
| Republican |
+-----------------------+

Using the DISTINCT keyword on queries with multiple columns will return unique combinations of values on all of the columns. For example, the query

SELECT DISTINCT givenname, party FROM presidents;

returns 36 out of a possible 43 rows. Each row is a unique combination of first name and political party.

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.