How many of you use terminal when operating mysql instead of a GUI client like sequel pro or mysql workbench? There is nothing bad in using either of it as it depends on your choice of comfort.

I like to use terminal for it. Here are few tips which you might find helpful. These are not relelated to how you use mysql database in general, these are specific to the option files and mysql terminal client.

Before diving into it, just make sure you know where your mysql option file my.cnf is place. Server may have multiple my.cnf files. How mysql prioritizes them can be seen by typing :

mysql --help | grep my.cnf

This will give you a fair idea of how mysql is prioritizing these option files. I would suggest to have the option file .my.cnf for each user inside their home directory, which has specific options not revealing anything secretive for all mysql users. Again when I say each user, it is NOT mysql's user but the user you are logged into the shell terminal.

1. Having a nice prompt is always great :

When you login to mysql client, you will see basic promt like below, nothing fancy. However, wouldn't it be great to know what user you have logged in as and which database you are currently in? You can add following in .my.cnf :

[mysql]

prompt=(\U):[\d]$\_

This will give you a handy promt to always keep an eye.

2. Setting up default database & credentials :

It is not good to always write mysql -u user -p database.. when you can have that set to defaults you use everytime. You can add following in .my.cnf :

[client]

user=mysqluser
password=mysqlpassword

[mysql]

prompt=(\U):[\d]$\_
database=databasename

Now you can just type mysql and you are good to go

3. Colorized output :

Usual output of mysql shell client is in white which becomes confusing when you have multiple columns, text of the row proceeds to next row. I feel this colour-mysql-console is very handy :

Url : https://github.com/nitso/colour-mysql-console

Once you follow the steps for installtion, you will see following :

There are few options which you can use not in option file but directly while starting mysql shell client. These can be used as and when needed.

4. Saving the query result in file :

Sometimes you might need to query something and then study those results later or maybe share it with a colleage etc. You can use the tee option :

mysql --tee="/filepath/output.txt"

MySQL client will acknowledge this by saying : Logging to file '/filepath/output.txt'. Now each query's result will be stored in this file which is very handy.

You can use notee command if you want to disable a particular query result to be pushed into the output file.

5. Saving the result in HTML table format :

This can be one of the cases but if you in case need to show a query result somewhere in pure table format, you can just use the html option :

mysql --html

Now you will see each query gives result in table, which you can easily put into your HTML page. Now this scenario may never likely come, but when it comes this will be a very quick option for you.

I use these on every local setup and it helps me very much in daily development activities. Hope you find it helpful.