21 Apr

Improved script for extracting table from MySQL text dump

A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

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
18 Apr

Analyzing I/O performance

There are probably thousands of articles on the Internet about disk statistics in Linux, what various columns mean, how accurate the information is, and so on. I decided to attack the problem from a little bit more practical side. Hopefully this will be just the first of many future posts on identifying various I/O related performance problems on a MySQL server.

Read More
17 Apr

Why a statement can be unsafe when it uses LIMIT clause?

MySQL 5.1 or newer can sometimes start throwing a strange message into an error log. The message states that a query was unsafe for binary logging along with some additional information. What does it mean? Is it a problem?

Read More
13 Apr

Choosing a MySQL package

As with any open source software, anyone can download MySQL sources, build binaries for some platforms and distribute their own packages. So we have the official ones from Oracle, MariaDB, and Percona - the makers of MySQL and its forks respectively. There are also those distributed with operating systems or that are available for install from their standard repositories. Some people or groups also create packages out of the most recent versions of various software and make them available to the world. Finally, some mash up a vanilla MySQL with various patches creating even more options. What to choose?

Read More