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.
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.
Again, don't forget the semi-colon!
This one is easy. This will take you back to the terminal prompt.
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]
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]
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.