Here’s a list of things that I’ve had to do on mysql from time to time. You dont always need these but when you do, its good to have them put together in one place.

Enable logging and get all sql queries hitting a mysql DB written to a log file

mysql> show variables like “general_log%”; This shows you two values usually. One ‘’ is a flag for setting general logging on and off. The other ‘’ is where to actually log to.

mysql> SET GLOBAL general_log = ‘ON’;

mysql> show variables like “general_log%”;
| Variable_name    | Value                   |
| general_log      | ON                      |
| general_log_file | /var/lib/mysql/logname.log |
2 rows in set (0.00 sec)

tail -f /var/log/mysqld/logname.log

When you’ve gotten what you’re looking for, go back and turn off the logging.

mysql> SET GLOBAL general_log = 'OFF’;

Enabling slow query logging

You notice a web page loading slowly and you suspect it might be because its taking a long time to fetch the results from the DB. Or you’re running performance tests against your system and you want to find out which of your queries are running slowly and hurting your system’s response time? Well - mysql has a slow query logging facility that can help you narrow down the issue.

ssh into your mysql server as a user that has superuser privileges etc and add the below lines to /etc/my.cnf and restart mysql service on that box.


The ‘slow_query_log’ flag turns on or off slow query logging. The ‘slow_query_log_file’ setting specifies the actual file to log to and the ‘long_query_time’ is the setting where you specify what is to be considered a slow query. Here, 2 stands for anything longer than 2 seconds.

And you’re all set! Run your tests, your UI scripts etc and then check in on the /tmp/slow.log file and you should see the queries you’re interested in.

Listing out all the currently executing queries on a mysql server

The show processlist command is really useful in that, it shows you whats currently executing on your mysql server, what state they are in etc etc. Sort of like the ps -ef for mysql.

mysql> show processlist;
| Id | User | Host      | db    | Command | Time  | State    | Info
| 29 | test  | localhost | test   | Sleep   |   281 |          | NULL
| 30 | test  | localhost | test   | Sleep   |   281 |          | NULL
| 31 | test  | localhost | test   | Sleep   |   281 |          | NULL
| 32 | test  | localhost | test   | Sleep   | 15280 |          | NULL
| 35 | test  | localhost | dbmap | Sleep   | 13481 |          | NULL
| 41 | test  | localhost | dbmap | Sleep   |  3033 |          | NULL
| 42 | test  | localhost | test   | Sleep   |  3033 |          | NULL
| 43 | root | localhost | NULL  | Query   |     0 | starting | show
processlist |
8 rows in set (0.00 sec)

Getting mysql server status from the command line

After you’ve logged into your mysql server, issue the status command to see various bits of information about your mysql server and your current session, including current user, current database, is SSL being used or not, mysql version, Server characterset, uptime of the server, number of slow queries, no of open tables, average number of queries per second etc. Here’s a sample output,

mysql> status
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine

Connection id:          59
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ’’
Using delimiter:        ;
Server version:         5.7.17 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 4 hours 39 min 28 sec

Threads: 8  Questions: 7605  Slow queries: 0  Opens: 626  Flush tables:
1  Open tables: 588  Queries per second avg: 0.453

Finding simple things about the DB like - what are the different

databases available, what are the different tables in current DB etc

show databases;

show tables;

The easier way to get deadlock data to find deadlocks in your DB

Install innotop (yum install innotop).

Once you’ve got that installed, you can connect innotop to your DB from the command line like this

innotop –user root –askpass

and you will be prompted for the password on the command line. Then you will be placed in the Dashboard view of innotop. Hit ? there and you will see a list of different information screens that you can then navigate to depending on what you’re trying to find out. L for locks, U for user statistics, D for InnoDB deadlocks, M for replication status, K for InnoDB Lock Waits etc. Try it out!

This article has a whole lot more information on using innotop

Finding deadlocks with queries running against your DB (The harder way)

The show engine innodb status; gives you a whole lot of data about your database. One of the things that I find useful is the data on deadlocks.

Here’s a sample output from this command, (Warning - wall of text coming your way, my apologies!)

| InnoDB |      |
2017-02-06 21:32:28 0x7f5326922700 INNODB MONITOR OUTPUT
Per second averages calculated from the last 2 seconds
srv_master_thread loops: 32 srv_active, 0 srv_shutdown, 15943 srv_idle
srv_master_thread log flush and writes: 15975
OS WAIT ARRAY INFO: reservation count 1101
OS WAIT ARRAY INFO: signal count 1033
RW-shared spins 0, rounds 399, OS waits 218
RW-excl spins 0, rounds 127, OS waits 3
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 399.00 RW-shared, 127.00 RW-excl, 0.00 RW-sx
Trx id counter 4075
Purge done for trx’s n:o < 3940 undo n:o < 0 state: running but idle
History list length 17
—TRANSACTION 421470258491456, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258489632, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258488720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258490544, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258487808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258486896, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258485984, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
—TRANSACTION 421470258485072, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
Log sequence number 5145450
Log flushed up to   5145450
Pages flushed up to 5145450
Last checkpoint at  5145441
0 pending log flushes, 0 pending chkp writes
1490 log i/o’s done, 0.00 log i/o’s/second
Total large memory allocated 137428992
Dictionary memory allocated 2551617
Buffer pool size   8191
Free buffers       7084
Database pages     1096
Old database pages 384
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 231, created 865, written 1002
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 1096, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=5270, Main thread ID=139994953017088, state: sleeping
Number of rows inserted 5124, updated 362, deleted 17, read 21639
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
⤧  Next post Automated accessibility testing ⤧  Previous post My top linux commands