How to use the command line on MySQL ?

If you’ve been using the command line on Linux, there’s no doubt you would already have fallen in love with it. Convenient, easy to use and powerful – on top of giving the “nerd” effect on your colleagues.

When it comes to database and tables, MySQL has a huge advantage over many of its competitors: its command line client.

When most of the users barely use PhpMyAdmin or log to their database using a SQL client like workbench, for easy and simple queries, the command line interface can be quicker to use – and undoubtedly more lightweight. You will be able in the blink of an eye to connect to a database, check the main tables and do your SELECT or UPDATE operations. If using “heavy” clients are good to structure code and have it more readable, sometimes we just want to retrieve a simple information without the hassle of waiting to connect to the database.

To connect MySQL via the CLI (Command Line Interface), it’s quite easy.

First you need to use your terminal or putty (on windows) to connect to your server via a SSH connection. Once logged to your sever where your MySQL databases are, you will need to type the following:

>> mysql -h myhostname -u myusername -pmypassword mydatabasename

Easy, nope?

Just press enter and if the four information are correct, then you will be connected to your database via CLI.

Once connected, you can type your SQL instructions as you would have done with any other SQL client.

Just a couple of tips there when you use MySQL command line interface.

First and not the least, don’t forget to put a “;” symbol at the end of each instruction before pressing enter after a query or you’ll get an error message. You can type CTRL +A to move the cursor at the beginning of the command, CTRL + E to move to the end of the SQL instruction. These SQL shortcuts are quite useful if you had to type quite a big query.

Another tip when using MySQL CLI, you can before the “;” symbol finish the instruction by typing \G; This will allow the results to appear in line and not in column, which is priceless if your SELECT was to return many fields.

By using the up and down arrows, you will be able to find and re-execute previously executed SQL commands. This is very convenient if you quickly want to re-execute something you wrote yesterday for instance.

Last tip finally, you can put in your .bash_profile an alias allowing to make a connection shortcut and avoid every time you want to connect to your database to write the same sentence (mysql -h …. ). One word will be enough for you to connect.

Cool nope?

Leave a Reply

Your email address will not be published. Required fields are marked *