The latest from the MySQL community

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

Let me start with example to make it clear. Lets say you're doing some reports from your apache log files - how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility - you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not working any more with response times for individual queries taking half an …

Forget tedious hours of searching links to the renamed object and replacing them.

To save your efforts, dbForge Studio for MySQL automates renaming of tables, columns of tables, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script, right-click it and select the Rename option from the menu. The Rename dialog box opens where you can enter a new name and view all references to the renamed object in the expressions, strings, and even comments.

All you have to do is to select required references and press OK!

The refactoring functionality is indispensable while working with database projects. You can export a database to a project, rename …

When I'm looking at a server, I often want to see the /etc/my.cnf file nicely formatted, and with comments stripped. This Perl one-liner will pretty-print the file:

PLAIN TEXT CODE:

1. perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' /etc/my.cnf
2. [client]                           
3. port                               = 3306
4. socket                             = /var/run/mysqld/mysqld.sock
5. [mysqld_safe]                     
6. socket      …

Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State.

PLAIN TEXT CODE:

1. mysql> show processlist;
2. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
3. | Id | User | Host      | db     | Command | Time | State                     | Info                        |
4. +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+
5. |  3 | root | localhost | sbtest | Query   |   30 | Updated about 764000 rows | update sbtest set email=zip |
6. ...
7. mysql> …

In addition to being practical, …

In addition to being practical, …

In addition to being practical, …

I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

PLAIN TEXT SQL:

1. SELECT * FROM tbl WHERE STATUS='waiting' AND source='twitter'
2.  AND no_send_before <= '2009-05-28 03:17:50' AND tries <= 20
3.  ORDER BY date ASC LIMIT 1;

Don't try to figure out the meaning of the query, because that'll add complexity to the example In the simplest case, we want to put the most …

This is mainly a cheat sheet for me to remember. Nothing rocket science.

It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster.

Also note MySQL should be down when you copy data unless you're copying from snapshot etc.

So to copy go to the mysql data directory on both boxes; such as cd /var/lib/mysql . Make sure target directory is empty. Now on the TARGET server do nc -l 4000 | tar xvf - and on the SOURCE server do tar -cf - . | nc target_ip 4000

Also note - the port you're using should be open in the firewall.

Entry posted by peter | 22 comments

Add to: …