Can you use the binary logs to undo a DELETE? Maybe, if you're using ROW format logging. The difference between a delete and an insert event could be just one byte - the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Let's try it.

I've already populated this table with a few rows:

CREATE TABLE `undo_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`v` varchar(20) DEFAULT NULL,
`b` blob,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Save a hash so we can see if the undo really works later:

mysql -e "SELECT * FROM test.undo_test" | md5sum > before.md5

Delete an unwanted row:

DELETE FROM undo_test;
Query OK, 1693 rows affected (0.14 sec)

Oops! Forgot the WHERE clause! And of course I don't have any recent …

Ubuntu 14.10 LAMP server tutorial with Apache 2, PHP 5 and MySQL (MariaDB)

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache 2 webserver on an Ubuntu 14.10 server with PHP5 support (mod_php) and MySQL support. Additionally, I will install phpmyadmin to make MySQL administration easier. A LAMP setup is the perfect basis for CMS systems like Joomla, Wordpress or Drupal.

As described in the MySQL 5.7 manual here, we can now resize the buffer pool without restarting the mysqld process starting with MySQL 5.7.5.

You can now use the "SET GLOBAL innodb_buffer_pool_size = xxxx" command which causes a resizing job to begin in background. We can monitor the progress and state of the resizing work using the …

VividCortex’s SaaS backend is a service-oriented architecture, which means that in addition to our external APIs that our agents and web UI use, we also have internal APIs. Deploy a bunch of API servers across a cluster of machines, hook everything up and make all the parts talk to each other, and what do you have? You have a distributed system that’s hard to troubleshoot, that’s what.

One of the primary problems in this type of system is finding out what’s happening, right now. Not status counters or metric rates, but what requests are in-flight and what status they are in, and most especially what they’re waiting for. At VividCortex we have built several relatively simple pieces of plumbing to help us get that kind of visibility. It’s been out there a while but we’ve never spent much time to explain what we did and what the benefits are. …

I ran into this the other day, and while the solution is documented in the manual, it was kind of buried, so I thought I’d mention it here (and hopefully make it easier for others searching for this in the future).

In this specific instance, one was running a huge LOAD DATA (10 hours into it) and had to terminate the LOAD DATA command.

Terminating the command is one thing, but preventing InnoDB from trying to undo the rows that were inserted is another.

In this case, the LOAD DATA will easily re-create the table in question, so the existing table can be “thrown out”, so to speak. This, of course, is a *critical* requirement for this operation (i.e., that you can easily re-create the table – either from a load, backup, dump, etc.).

Per the manual:

“If you know that a given table is causing a crash on rollback, you can drop it. You can also use this to stop a runaway rollback caused by a failing …

You can find a ‘Quick Usage Tour’ in our documentation. In this post, I’m going to walk you through that tour and elaborate on a few things. Enjoy! Things to consider I’m assuming you’ve already installed MySQL. Sphinx does not require that you use MySQL, but the following examples do. I’m installing Sphinx on Ubuntu [...]

Recently while writing a data loading application for a customer, we’ve come into a problematic situation with the Python MySQLdb module that can be installed with base RHEL repository or DVD. As a little background, this application uses an HA architecture where a Virtual IP can be assigned to different servers during a failover scenario. However, during failover, as long as the VIPs are not re-assigned, the application could hang waiting for query response. A quick strace reveals something like:

sendto(5, "W\0\0\0\3SELECT * FROM dataloader.bo"..., 91, 0, NULL, 0) = 91
recvfrom(5,

Further test reveals that this problem will not timeout until after about 15 minutes which matches the default tcp_retries2 value on the system (see man 7 tcp):

[root@node1 ~]# cat /proc/sys/net/ipv4/tcp_retries2
15

It turns out that this version of MySQLdb module has client net read and write timeouts set to 0 allowing the TCP setting …

After some grueling IO testing on 7200rpm disks, I got my hands on some shiny new Samsung 840 SSDs and wanted to share the performance results in similar fashion.

Dell r630, E5-2630 v3 @ 2.40GHz (16 cores), 256GB RAM, Samsung 840 EV SSD 960GB.

Testing was sysbench, fileio-rndrw, async+direct io, 16 threads, 5.5TB RAID-6 using XFS mounted with noatime,inode64 and using the deadline scheduler.

On 5 November, I’ll be speaking at #SFMySQL Meetup about Data Recovery Software for MySQL

Add Slipped & DROP’d your TABLE? Recover w/TwinDB’s Undrop for InnoDB toolkit to your calendar.

There will be a demo and if you want to try to undrop a table yourself bring in a laptop with Linux.

Download the latest revision of the TwinDB Data Recovery Toolkit from LaunchPad.

Internet connection isn’t necessary, but make sure you install dependencies: gcc, make, flex, bison.

The post Presenting TwinDB Data Recovery Toolkit …

Today I encountered a situation where MySQL Enterprise Backup caused to much load on the I/O subsystem of the server to cause the application to be so slow that it wasn't usable any longer. So I wanted to limit the mysqlbackup process so it wouldn't cause any more issues.

The mysqlbackup command has settings to for the number of read, write and process threads. The defaults are 1 read, 1 write and 6 process threads. So that isn't really useful for throttling as I was using the defaults.

Using the ionice utility wouldn't work as that requires the CFG I/O scheduler.

I found a solution in this blog post. It is to use cgroups on Linux. I had used cgroups before to test how a galera setup works when one of the three servers had a much slower CPU.

# mkdir /cgroup/blkio
# mount -t …