pt-table-checksum perfectly solves problem of checking if master and its slaves are in-sync. It answers the question “Are the slaves consistent with the master?”. However if the answer is “No” pt-table-checksum doesn’t actually tell what exactly is different. [crayon-59f9aff300dfa915077909/] pt-table-sync may give a partial answer. It can print SQL statements to sync the replication cluster. Reading the SQL […]

The post Show differences found by pt-table-checksum appeared first on TwinDB.

I use Pingdom's free service to monitor slaptijack.com. Apparently, late Friday night, oom-killer decided that the server needed more memory and took out the MySQL server. To make matters worse, I missed the alarm from Pingdom, and slaptijack.com was down for pretty much all of Saturday. The fact that oom-killer was invoked is annoying, but more on that later.

The beauty of using Pelican rather than Wordpress (or any other database-driven content engine) is one less point of failure for the site. Obviously, I don't have all of slaptijack.com converted to Pelican yet (and perhaps never will), but at least parts of the site were up and working despite MySQL being down. If nothing else, this incident is enough to convince me that moving to Pelican was a good idea.

OOM'ed

…

Not so long ago I had a customer who experienced data loss after MySQL restart. It was really puzzling. MySQL was up & running for many months, but after the customer restarted MySQL server all tables have gone. The tables were still visible in SHOW TABLES output, but they were not readable:

mysql> show tables like 'actor';
+--------------------------+
| Tables_in_sakila (actor) |
+--------------------------+
| actor                    |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from actor;
ERROR 1146 (42S02): Table 'sakila.actor' doesn't exist
mysql>

To understand what’s happened let make some experiments (WARNING: Don’t do it on production or with valuable data).

Let’s take a healthy MySQL instance with installed sakila database.

While MySQL is …

Naturally, I ran into this problem in the middle of the night when the client was asleep and I had no access to the Magento administrative interface. The short story is that after running a re-index of the site, the home page began returning a 404 erro...

Encryption is important component of secure environments. While being intangible property security doesn’t get enough attention when it comes to describing various systems. “Encryption support” is often the most of details what you can get asking how secure the system is. Other important details are often omitted, but the devil in details as we know. In this post I will describe how we secure backup copies in TwinDB.

See the picture. This is what happens when encryption is used incorrectly. The encryption algorithm can be perfect, but poor choice of the mode results in a quite readable encrypted image. This mode is called “Electronic Code Book”, avoid it at all means.

Another bright example of improper encryption use was illustrated in Venona project.

Have you ever tried to install Xtrabackup on Amazon EC2 instance with Oracle’s MySQL 5.6? Dependencies hell strikes when you ask pretty common and reasonable thing – run the GA version of MySQL and backup it with the most popular open-source tool – XtraBackup. From this post you will learn how to resolve the conflicts and make everybody happy.

mysql55-libs conflicts with mysql-community-libs-5.6.22

A fresh Amazon Linux AMI, 2014.09 EC2 instance comes with MySQL 5.5.40 in amzn-updates repository. Today MySQL 5.5 turns five years old. It’s a good and stable version. But many people want to run MySQL 5.6, because it’s better than 5.5, it supports full-text indexes and Oracle ends support of 5.5 this year.

Oracle distributes MySQL releases via YUM repository. Installing MySQL from the YUM repository is a good idea because YUM takes care of …

Benchmarking is a popular topic. People love drawing graphs as much as watching how X is 10% faster than Y; there must be something special in measurements.

For a DBA, however, more tangible improvements come from less popular area of database maintenance. While MariaDB spreads FUD around InnoDB (nonetheless still uses it) I have to admit InnoDB gets more friendly to DBAs.

In MySQL 5.6 new temporary table naming scheme was introduced – one of improvements. Invisible, yet important.

Temporary table names became more random and should not ever be reused.

Some time ago I wrote a post about how to remove …

When ALTER TABLE crashes MySQL server it leaves orphaned records in InnoDB dictionary. It is annoying because next time you run the same ALTER TABLE query it will fail with error:

ERROR 1050 (42S01) at line 1: Table 'sakila/#sql-ib712' already exists

The post explains why it happens and how to fix it.

When you run ALTER table InnoDB follows the plan:

1. Block the original table
2. Create an empty temporary table with the new structure. The name of the new table is something like #sql-ib712.
3. Copy all records from the original table to the temporary one
4. Swap the temporary and original tables
5. Unblock the original table

The temporary table is a normal InnoDB table except it’s not visible to a user. InnoDB creates a record in the dictionary for the temporary table as for any other table.

If MySQL crashes in the middle of the …

Installing MySQL Database on Ubuntu or CentOS is a trivial operation as long as they have nice package managers as Aptitute and YUM. Otherwise, in Mac OS X is a hard operation from downloading the correct package, apllying with several steps and it can break the whole install, if you But, with Homebrew, the install... Read More

The post Installing MySQL on Mac OS X easily appeared first on Devops for Dummies.

A fascinating post-mortem on high profile network failures:

This post is meant as a reference point–to illustrate that, according to a wide range of accounts, partitions occur in many real-world environments. Processes, servers, NICs, switches, local and wide area networks can all fail, and the resulting economic consequences are real. Network outages can suddenly arise in systems that are stable for months at a time, during routine upgrades, or as a result of emergency maintenance. The consequences of these outages range from increased latency and temporary unavailability to inconsistency, corruption, and data loss. Split-brain is not an academic concern: it happens to all kinds of systems–sometimes for days on end. Partitions deserve serious consideration.