Sometimes it may be easier to do things from the command line rather than using a GUI such as phpMyAdmin, this can be the case if you want to either run things in the background or have them run automatically from scripts, also if you're connected to a server then you may not have a GUI to work from anyway and need to use console commands. Also with applications such as phpMyAdmin you are limited to the PHP constraints in place which may cause time outs or other problems. So, command line it is.

Dumping or Exporting a Database

If you wish to dump a MySQL database to a file then you can use the following command (replacing the sections in bold with ones that apply to you):

mysqldump -uUSERNAME -pPASSWORD -hHOSTNAME DATABASE -Cce --hex-blob > DATABASE-`date +%y%m%d`.sql

What does this do? Well the main parts are obvious (Username, Password, Database, Hostname, etc). The other parts that follow help the process:

hex-blob ensures that any binary blobs are converted to hex so that they aren't mangled when exported.

The C command tells the server to use compression (speeding up the transfer).

The c uses complete inserts.

Finally e tells the server to produce larger inserts covering multiple rows, not one insert per row, speeding up the process for importing.

If you'd rather export the database and then compress it (to save space) then you can use the following line instead:

mysqldump -uUSERNAME -pPASSWORD -hHOSTNAME DATABASE -Cce --hex-blob | gzip -9 > DATABASE-`date +%y%m%d`.sql.gz

Pipe followed by gzip -9 tells your command prompt to push the data through gzip to compress the content. This normally works very well with databases due to their high text content.

Finally `date +%y%m%d` tells your command line to add the current date (in YYMMDD format) to the file name.

When you run this you will end up with a file named something like database-100924.sql.gz which is useful in automated scripts as you won't have to worry about renaming files.

If you export the database in a compressed format as shown above and later need to extract it, you can use the following command to unGZip it:

gunzip sqlfile.sql.gz

Parts can be left out of the command line as necessary; for example leaving out | gzip -9 will mean the file isn't compressed, leaving out -C can be done if you want to skip network compression and so forth.

Importing an SQL File

To import a database, tables or rows you need to ensure you have the content in an SQL file. If your SQL script is compressed then you will first need to extract it so you are only left with the .sql script file. The importing can then be done using the command below (replacing the sections in bold with your own details):

mysql -uUSERNAME -pPASSWORD -hHOSTNAME -DDATABASE -C < myfile.sql

Note there are no spaces between the switches and the credential after them (for example if your password was 'cheese' then it would say -pcheese). This may take some time to complete as it runs through each SQL query individually.

Checking the Status of a MySQL Server

This is quite simple and only requires a single command, if you're already connected to the MySQL server via the mysql application then you can simply run status but if you aren't connected then you can use the command below which will connect and print out the server's status information:

mysql -uUSERNAME -pPASSWORD -hHOSTNAME -e "status;"

Allow or Restrict Remote Access for a User

MySQL allows you to restrict where a user can log in from. The most common entries are '%' which means anywhere and 'localhost' which means "from this machine only". You can also specify a specific IP address if that user will always be logging in from a specific location:

mysql -uUSERNAME -pPASSWORD -hHOSTNAME -e "UPDATE mysql.user SET host = '%' WHERE user = 'user';"

The command above will change the access for 'user' (replace with the real username) to '%' so that this user can log in from anywhere. You can replace '%' with 'localhost' or an IP as required.


If there are any common actions you perform or would like to know how to do that are missing from this page then please comment below and I'll add them in.