Performance Zone is brought to you in partnership with:

I specialise MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications I love the most. Peter is a DZone MVB and is not an employee of DZone and has posted 191 posts at DZone. You can read more from them at their website. View Full User Profile

Be productive with the MySQL command line

12.26.2012
| 3672 views |
  • submit to reddit

Even if you are using a GUI tool to connect to your MySQL servers, one day or another, you will have to deal with the command line. So it is nice to know a few tips that can really make your work easier.

Note: The commands below are only available for Unix/Linux.

Using pager

Most of the graphical tools paginate results, which is very handy. But this is not the way the command line client works: it just outputs all results. It can be annoying but it is easily solved by using the pager command:

mysql> pager more
PAGER set to 'more'
mysql> select title from sakila.film;
+-----------------------------+
| title                       |
+-----------------------------+
| ACADEMY DINOSAUR            |
| ACE GOLDFINGER              |
| ADAPTATION HOLES            |
| AFFAIR PREJUDICE            |
| AFRICAN EGG                 |
| AGENT TRUMAN                |
| AIRPLANE SIERRA             |
| AIRPORT POLLOCK             |
| ALABAMA DEVIL               |
| ALADDIN CALENDAR            |
| ALAMO VIDEOTAPE             |
| ALASKA PHANTOM              |
| ALI FOREVER                 |
| ALICE FANTASIA              |
| ALIEN CENTER                |
| ALLEY EVOLUTION             |
| ALONE TRIP                  |
| ALTER VICTORY               |
| AMADEUS HOLY                |
--Plus--

Another example of the pager command is when you want to estimate a good size for you InnoDB redo logs: the estimation is based on the variation of the Log Sequence Number during a given period of time. Instead of manually looking for the right line in the output of SHOW ENGINE INNODB STATUS (which can be huge), you can call pager to the rescue:

mysql> pager grep sequence
PAGER set to 'grep sequence'
mysql> show engine innodb status\Gselect sleep(60);show engine innodb status\G
Log sequence number 380166807992
1 row in set (0.41 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 380170274979
1 row in set (0.00 sec)

When you are done and you want to disable paging, you can simply run:

mysql> pager
Default pager wasn't set, using stdout.

Using edit

When you try to optimize a query, it often involves manipulating the text of the query, and sometimes it would be great to have a text editor inside the client. Well, this can be achieved by using the edit command.

Let’s say you have the following query:

mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name='Music';

and let’s say you want to change the left joins to inner joins and use capital letters for reserved SQL words. Instead of manually editing the statement, which will be boring, you simply call edit:

mysql> edit

and it will open your default text editor with the text of the last query. The default text editor is vi, so you now have the power of vi inside the mysql client!
Once you have made your changes, save and exit the editor: you are back in the mysql client where you can type ; or \G to execute the query.

Using tee

In some situations, like when you are testing a set of commands to write documentation or when you are in the middle of an emergency, you want to be able to record the queries that you have executed. The command line client offers the tee command, which will log to a file the statements you typed and their output, pretty much like the Unix tee command:

mysql> tee queries.log
Logging to file 'queries.log'
mysql> use sakila
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select count(*) from sakila;
ERROR 1146 (42S02): Table 'sakila.sakila' doesn't exist
mysql> select count(*) from film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> exit

And now if you look at the content of the queries.log file, you will see a copy of your session.

Conclusion

The mysql command line client is not as glossy as most of the graphical tools, but if you know some of its hidden features, it can be very powerful. If you enjoyed these tips, I will write another post with other useful but overlooked features.



Published at DZone with permission of Peter Zaitsev, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Kenneth Mark replied on Wed, 2012/12/26 - 9:42pm

 Very handy tips, thanks for sharing

Alper Kalaycioglu replied on Wed, 2013/01/16 - 8:04am

You can also access the underlying command line by putting \! in the beginnig of the command. For example

mysql > \! date

This will output system date. or you can switch to bash during a mysql session by
mysql > \! bash

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.