SiteName

Boss got you to log into that Linux server, sitting in the basement, to make that database backup?

Here are a handful of useful mySQL terminal commands to get you started. The order of operation is that of "next step" logic.

This article assumes you're logging in as user 'root'.

Export a database

From the command line, let's say you want to make a backup of a database:

mysqldump -u root -p [database name] > [export name].sql

Log in to mySQL

Don't know the name of the database? Let's log in to mySQL and check:

mysql -u root -p

You'll be required to enter your password

Display the databases

This will display all the mySQL databases you have.

show databases;

The little semi-colon at the end is important! If you forget to add it, no worries. Simply add it on the next line.

Selecting a database

Want to see the tables for a particular database? Let's select a database to use:

use [database name]

Display the tables

Display the tables for the selected database.

show tables;

Again, don't forget the semi-colon!

Exit mySQL

This one is easy. This will take you back to the terminal prompt.

exit;

Zip up the sql

Woah! that SQL we just exported is massive. I got to get that to my PC with a connection slower than your cell phone! So let's compress this puppy.

tar -jcvf [compressed file].tbz2 [files to compress]

Example

tar -jcvf my-sql-files.tbz2 *.sql

Yep, we didn't use zip like I said. We instead used bzip2 to compress our files. The reason for this because the zip program is not always installed. bzip2 very likely is.

Notice the *.sql? We can use wildcards which is handy if you have multiple sql files to compress.

And here is the zip version - for the Windows folk out there - assuming it's installed.

zip [compressed file].zip [files to compress]

Example

zip my-sql-files.zip my-database.sql

Zip also supports wildcards but here I specified an actual filename.

Now your file should be a lot smaller to transfer.

Creating a database

Now the last step is to import the sql back into your database. I.E., restore. In order to do this, you need to create the database to import the data to.

Follow the instructions Log in to mySQL, above, to log in to mySQL and create your databae.

create database [database name];

Importing your data into mySQL

Now we can restore our data:

mysql -u root -p [database name] < [sql file]

And there you have it, some basic commands to get you started with mySQL and the terminal.