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
02 Jun

How to find MySQL binary logs, error logs, temporary files?

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.

Read More
19 Apr

Extracting one table from mysqldump or phpMyAdmin backup

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.

Read More
10 Apr

How to check if MySQL has been swapped out?

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

Read More
04 Apr

How to selectively kill queries in MySQL?

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.

Read More