Tweet

For any person actively working with MySQL databases on the command line level, logging in, logging out for a few moments, and then logging in again, all repeated many times, not only eventually becomes annoying, especially with passwords that aren’t easy to type, but it also can take a lot of time over the course of a day. For a long time I’ve been relaying on something that allows me to avoid most of this effort while working. The solution is of course not to log out unless you actually want to.

Linux, BSD and Solaris all allow suspending a running task and resuming its execution at a later time. This can be used to temporarily exit MySQL client without having it to leave the database.

When inside MySQL, you can press Ctrl+Z to suspend the client program and return to the system shell. You will be able to bring the database client back at any time by running fg command …

Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.

The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.

A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.

garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)'
COMMAND   PID  USER   FD   TYPE      DEVICE  SIZE/OFF     NODE NAME
mysqld  30257 mysql  cwd    DIR       253,1      4096 25346049 /data/mysql
mysqld  30257 mysql  rtd    DIR       253,2 …

Using mysqldump is a quick way to do backups, although usually limited to only smaller databases – perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren’t even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.

Rather than editing out parts of the file that I don’t need, I wrote simple one-liners that do that for me. These are not perfect as for example they can’t deal with multiple tables by the same name existing in several different schemas, but that so far never turned out to be a problem. I encourage you to suggest improved ways of doing this, just let me know in the …

How to check if any MySQL memory has been swapped out? This post explains it.

Check if system is currently using any swap:

server ~ # free -m
             total       used       free     shared    buffers     cached
Mem:          3954       2198       1755          0        190       1040
-/+ buffers/cache:        968       2985
Swap:         3906          0       3906

In the above example swap is not in use, so no further checks would be necessary.

However if free command would report some usage, how to check whether MySQL memory was swapped out or not?

It is not possible to determine that using standard tools such as ps or top. They will report various memory related information per each process, but no clear indication whether something is in RAM or in swap space. But it is possible with this trivial command:

awk '/^Swap:/ { SWAP+=$2 } END { print …

For as long as it is only about a few of them, it is as simple as looking at the SHOW PROCESSLIST output for thread identifiers to kill. They can be found in the first column called Id. These values can be passed to KILL thread_id command in MySQL. The problems appear with more complex scenarios. What if one needs to terminate all queries running longer than ten seconds? Doing copy&paste repetitively could take a lot of time with twenty or so candidate threads. This can be done much more efficiently.

INFORMATION_SCHEMA to the rescue!

Earlier today we showed “Anohter way to work with MySQL process list”. That post presents how PROCESSLIST table can be used to retrieve any information about connected threads or running queries.

In order to perform …

In an earlier post titled “How to work with a long process list in MySQL”, we showed a neat way to work with the process list by using various shell tools. But some of that can also be done using pure SQL.

Since version 5.0 a lot of MySQL meta and runtime information can be accessed by reading from predefined views in a database called INFORMATION_SCHEMA. The information which can be found there includes lists of threads, tables, user created views, triggers, stored procedures and many, many other things. The newer the MySQL version, the more items can found in there.

This post is about the process list, so it will focus on just one of the tables called PROCESSLIST. Its structure is virtually identical to what SHOW PROCESSLIST command returns.

mysql> DESC …

The MySQL query cache is a special buffer, where database stores the text of a SELECT statement together with the corresponding result that was sent to the client. For as long as no table that a statement refers to changes in any way, including the contents, the cached result can be re-used to answer any identical sub-sequent SELECT statements. But how to tell whether a query was executed or returned from the cache?

There are at least three ways to check it.

Method 1

MySQL exposes a number of runtime statistics that are accessible with SHOW STATUS statement. Among the long list of various counters, one is called Com_select which shows how many times a SELECT statement was executed. However if a SELECT is served from the query cache, it does not actually execute, so it is not accounted in Com_select. The conclusion must be that if a query runs, but it …

A customer called me today asking for help with locating the configuration file used by one of their production MySQL instances. From the description I was given it appeared that their server had at least six different copies of my.cnf file in different locations on disk. And all were similar enough that each could actually be the one. All superfluous files were the result of a bit negligent system administration. So what turned to be the quickest and the least destructive way to find the correct one?

Initially suspecting the server was simply running more than just one MySQL instance, I logged in to take a deeper look. But I found only one mysqld process and, indeed, several configuration files.

All of them seemed good candidates:

/etc/my.cnf
/etc/mysql/my.cnf
/var/lib/mysql/my.cnf
...

In many cases you could simply check system process list using ps:

server ~ …

I am generally a big fan of command line tools. This also applies to MySQL client software such as mysql or mysqladmin. To those spoiled by graphical interfaces, working in text mode may seem crude or even difficult. But the truth is that once you get used to these tools, you will be able to accomplish many things a lot faster than with any GUI client. Of course, using text terminal, which is the environment for any command line tool, has its drawbacks and limitations. For example on a relatively busy MySQL server, every so often when you run SHOW [FULL] PROCESSLIST, which lists client threads connected to a database, you can receive an output that will be many screens long. Sometimes it might be due to the high number of established connections – each takes at least one line on the screen, or sometimes due to some longish queries spanning over multiple lines. Finding relevant information there usually isn’t …