Productive work with MySQL on the command line
- Transfer
Even if you use GUI tools for working with MySQL, one day you will encounter a command line. Therefore, it is useful to know some tricks that make work easier.
Note: these commands work only for Unix / Linux.
Most GUI utilities display the result page by page, which is very convenient. But at the mysql prompt, you get the full result. This is easily solved with the command
Another example of using this command is that you, for example, want to figure out what size to set for InnoDB redo logs. Estimation of this size is based on changing the Log Sequence Number value for a given period of time. Instead of manually searching for the desired line in the output
To disable pagination, run:
When optimizing queries on the command line, it is very inconvenient to edit them and there is a wildly lacking text editor. This is also solvable. For example, you have this request:
and you want to change
and a text editor will open in front of you containing your last request. The default editor is vi . When you finish editing, save the changes and exit the editor. You will find yourself back in the mysql client, where you can enter
In some situations, for example, when you test a set of commands when writing documentation or when you are in an emergency, you want to record the requests that you execute. The console client provides you with this opportunity with the help of a command
If you now look at the file
A console client may not be as convenient as graphical tools, but if you can cook it, it can be very powerful.
Note: these commands work only for Unix / Linux.
Pagination
Most GUI utilities display the result page by page, which is very convenient. But at the mysql prompt, you get the full result. This is easily solved with the command
pager
: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 using this command is that you, for example, want to figure out what size to set for InnoDB redo logs. Estimation of this size is based on changing the Log Sequence Number value for a given period of time. Instead of manually searching for the desired line in the output
SHOW ENGINE INNODB STATUS
(which can be quite large), you can use pager
: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)
To disable pagination, run:
mysql> pager
Default pager wasn't set, using stdout.
Editing
When optimizing queries on the command line, it is very inconvenient to edit them and there is a wildly lacking text editor. This is also solvable. For example, you have this request:
mysql> select count(*) from film left join film_category using(film_id) left join category using(category_id) where name='Music';
and you want to change
LEFT
joins to INNER
joins and use uppercase for the words reserved in SQL. Instead of dull manual editing, just call edit
:mysql> edit
and a text editor will open in front of you containing your last request. The default editor is vi . When you finish editing, save the changes and exit the editor. You will find yourself back in the mysql client, where you can enter
;
or \G
to start the query.Using tee
In some situations, for example, when you test a set of commands when writing documentation or when you are in an emergency, you want to record the requests that you execute. The console client provides you with this opportunity with the help of a command
tee
that logs all requests that you launched and their output: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
If you now look at the file
queries.log
, you will see a copy of your session.Conclusion
A console client may not be as convenient as graphical tools, but if you can cook it, it can be very powerful.