17 Apr

Diagnosing problems with SQL imports

Importing a text file containing a list of SQL commands into MySQL is a straightforward task. All you need to do is simply feed the file contents through pipe into MySQL command line client. For example: mysql app_production < dump.sql. The reasons for doing such imports can be very different - restoring MySQL backups created with mysqldump, manually replaying binary log events or performing database migrations during software roll-outs. While the task is simple, the import may not end successfully and when this happens, how to tell what the problem was?

Read More
07 Aug

Tip: how to save 10 minutes a day?

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.

Read More
28 Mar

How to work with a long process list in MySQL

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 easy and having to scroll backwards doesn't really help. What do I do in such cases to help the workflow?

Read More