Many people have a master/slave MySQL environment of various different topologies, and many use the slave as a backup.
Is your slave schema identical to your production schema? As long as an SQL statements completes without an error, your slave schema can differ. Common examples are different indexes or storage engines for example. However if you use the slave as backup, you want to ensure when you recover, you are recovering a production environment, not a slave environment.

While the changes may be small, the can lead to different results. For a client I found that the default value of a price field was 10.00 in one schema and 0.00 in another. Not withstanding using defaults for important fields and not defining in an INSERT is a different issue, it highlighted the different schemas can easily exist.

My tool of choice is

How this happens or who does it is obviously a larger and more complex conversation however it is better then involving innocent animals.

How is it that trivial $#*! gets voted and has a string of comments I can not explain, however Planet MySQL should have practical MySQL related content. I would vote for the front page and RSS feed to show only moderated content, and content that did not pass the cut (and there is a lot of this) can be available on a less important page if necessary.

Rather then complaining like many open source communities, let us propose a way to improve the system we use.

Running a MySQL backup and ensuring it completed successfully and backup files exist is not enough. In my B&R Quiz from Checked your MySQL recovery process recently? one important step is “Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place?”

This is what I found when reviewing a backup log for a client today.

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

The backup script was completing, backup files were in place (and are listed in the log file) however these

This was a request yesterday on a mailing list about how to I block DROP statements from running on a slave. There were responses including MySQL could not do this, or try other replication technologies however I thought I would share how you can easily do this on a slave.

You simply deny access on the slave. It is as simple as changing the MySQL privileges for the user(s) in question to remove DROP or ALTER capabilities.

Of course this will break replication rather then simply ignore the statement however I suspect that is the intended goal anyway. The first statement that uses the table will fail regardless. With appropriate monitoring of replication you should know in about 15 seconds. (Hint: If you don’t monitor replication or monitor it frequently, DO SO NOW!)

These leads to the question, what if the statements are legitimate? There

Topology

Participating hosts

* 1x BIG-IP VE
* 1x Debian Squeeze “Provisioning Server” serving DHCP, TFTP & DNS
* 3x Nortel/Avaya 1120E hard phones flashed to SIP1120e04.01.13.00
* 2x Debian Squeeze + digium asterisk packages:

$ grep asterisk /etc/apt/sources.list
deb http://packages.asterisk.org/deb squeeze main
deb-src http://packages.asterisk.org/deb squeeze main

DUNDi

*CLI> module reload pbx_dundi.so
*CLI> dundi show peers
*CLI> dundi show mappings
*CLI> dundi lookup 4012@extensions bypass
*CLI> dundi set debug on

SIP

*CLI> sip show peers

dialplan

*CLI> dialplan show RegisteredDevices

Work in progress

Yesterday, I installed a trixbox virtual machine using the

Various people have complained about Linux swapping unexpectedly on boxes running mysqld, when apparently mysqld was not using all the memory and there was quite a bit of free memory available.

There’s also an article by Jeremy Cole. However, his solution requires a one-line change to mysqld_safe which while it’s small does not work very well if you have to maintain a large number of servers and are using packages asa package upgrade will overwrite the modified file mysqld_safe and then restart mysqld with the unmodified script. This leads to the need to repatch the script and then restart mysqld. Not very helpful.

So I was looking for another solution and came up with this option which basically requires a minor change to /etc/my.cnf and the

I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

• Monday 3:45-4:45pm in Orlando – Common MySQL Scalability Mistakes
• Wednesday 2:00-3:00pm in Santa Clara – Add to Your O’Reilly Schedule – Best Practices for MySQL DBAs and Developers Part 1
• Wednesday

For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

The config files that come with MySQL server are generally not that good. They almost never work well for enterprise server loads and will leave most people wondering why the database needs someone to come fix it. In fact that might be why they roll those configs by default, to keep the consulting services alive and profitable. Personally I have no issues with consulting services, but everyone should start out with good configs to begin with. Well, no worries… here are config files for different system RAM configurations. Just choose the one that fits how much RAM your server has and make sure the cnf file is in place before you install the MySQL server RPM/tar/deb packages. Obviously you’ll want to remove the extension of the filename so it reads “my.cnf” and not “my.cnf_64GB” or the like. These cnfs have

I thought I would upload a small script I use which I find quite useful. log_processlist.sh is normally run minutely from cron and collects statistics from any MySQL instance it finds. Data is kept for up to a week and then rotated. This allows you to check what happened over a period of time, for example if an instance crashes or seems to have a lot of load.

I have found it quite invaluable on the instances I’m monitoring and once setup it’s usually nice and silent. If you find this useful or have suggestions for improvements please let me know.

Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

Let’s look at the following example output of a production system:

mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr

While most people look at performance optimizations for SELECT statements, UPDATE and DELETE statements are often overlooked. These can benefit from the principles of analyzing the Query Execution Plan (QEP). You can only run an EXPLAIN on a SELECT statement, however it’s possible to rewrite an UPDATE or DELETE statement to perform like a SELECT statement.

To optimize an UPDATE, look at the WHERE clause. If you are using the PRIMARY KEY, no further analysis is necessary. If you are not, it is of benefit to rewrite your UPDATE statement as a SELECT statement and obtain a QEP as previously detailed to ensure optimal indexes are used. For example:

UPDATE t
SET	c1 = ‘x’, c2 = ‘y’, c3 = 100
WHERE c1 = ‘x’
AND	d = CURDATE()

You can rewrite this UPDATE statement as a SELECT statement for using EXPLAIN:

EXPLAIN SELECT

It can be very easy to improve scalability with a MySQL server by a few simple rules. Here is one of them.

“The most efficient way to improve an SQL statement is to eliminate it”

There are numerous ways to eliminate SQL statements, however before I give a classic example that I’ve observed again with a client, let me explain the basic premise of why this improves scalability?

The MySQL kernel can only physically process a certain number of SQL statements for a given time period (e.g. per second). Regardless of the type of machine you have, there is a physical limit. If you eliminate SQL statements that are unwarranted and unnecessary, you automatically enable more important SQL statements to run. There are numerous other downstream affects, however this is the simple math. To run more SQL, reduce the number of

You blink and there is a new version. I have not seen an Planet MySQL release as yet about this new version. Release Notes.

I’d like to say I installed it, but I downloaded the Linux – Generic 2.6 (x86, 64-bit), TAR file, only to find it contains 6 rpm files. #fail, I’m using Ubuntu.

You have to scroll to the bottom of the list (another stupid thing for a generic binary choice) to get Linux – Generic 2.6 (x86, 64-bit), Compressed TAR Archive. Double #fail

Beginning with MySQL 5.1 as an additional plugin and included by default in MySQL 5.5 the InnoDB plugin includes many performance improvements. To leverage the support of new file formats however a very important setting is necessary.

#my.cnf
[mysqld]
innodb_file_per_table

The use of innodb_file_per_table with an existing system or during an upgrade to 5.1 or 5.5 requires a complete reload of your database to use effectively. In summary.

• Backup all InnoDB tables via mysqldump
• Drop InnoDB tables
• Verify InnoDB not used
• Stop MySQL
• Enable innodb_file_per_table & simplified innodb_data_file_path (if applicable)
• Remove ibdata? files
• Start MySQL
• Create Tables
• Reload Data
• Verify InnoDB Operation

The primary

Today I discovered an interesting upgrade problem with a client migrating from MySQL 5.0 to 5.5. The client who is undertaking the upgrade reported that MySQL 5.5 did not support the DECIMAL(18,5) data type. I easily confirmed this not to be the case:

mysql> drop table if exists x;
mysql> create table x (col1 DECIMAL(18,5));
Query OK, 0 rows affected (0.01 sec)

Delving more into the issue in question, I looked at the complete CREATE TABLE statement, recreating the syntax.

mysql> drop table if exists x;
mysql> create table x ( MinValue DECIMAL(18,5));
Query OK, 0 rows affected (0.00 sec)

No problem there.

mysql> drop table if exists x;
mysql> create table x (Department INT NOT NULL, MinValue DECIMAL(18,5) NULL, MaxValue DECIMAL(18,5) NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check

Many early startups that use a single server for all services or a single database server for their website talk about how they would like to achieve higher availability with MySQL. This is not possible without at least two database servers. Using MySQL replication you can then support higher availability in several varying capacities. An additional MySQL database server can satisfy several infrastructure needs including:

• A primary hot backup
• A datasource for performing backups
• Read scalability infrastructure
• A reporting server
• A benchmarking server
• A fail-over/fail back master environment

MySQL replication is very easy to configure and deploy, a task that takes

About the Author

I’ve been playing around with some quick system automation scripts that are handy to use when you don’t want / need to setup a chef or puppet action. I like to keep all of my hostnames and login details in a MySQL database (a cmdb actually) but for this example we’ll just use a couple of nested lists. This script executes commands in parallel across the hosts you choose in the menu system via the “pdsh” command, so make sure you have that installed before running. Alternately you can change the command call to use ssh instead of pdsh for a serialized execution, but that’s not as fun or fast. With some customizations here and there you can expand this to operate parallelized jobs for simplifying daily work in database administration, usage reporting, log file parsing, or other system automation as you see fit. Here’s the code. Comments welcome as

If so then were is it installed on your publicly accessible website. If the location is where the documentation states not to put it, or in other popular locations then you can easily become open to an attack. The following are apache logs of a simple hacker test to find a potential security hole on a new IP public address for a client.

My advice is never have PHPMyAdmin accessible by default. You should use HTTP authentication, firewall rules and additional security practices all to protect any level of access to your data.

78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpmyadmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //phpMyAdmin/ HTTP/1.1" 401 290 "-" "Made by ZmEu @ WhiteHat Team - www.whitehat.ro"
78.111.81.180 - - [02/Feb/2011:05:29:24 -0500] "GET //pma/ HTTP/1.1" 401

While Oracle provides no official information they are planning on improving MySQL and using as a product to compete with Microsoft SQL Server, it is rather obvious from what little information you can glean from public announcements this is a clear business goal.

Microsoft however are publicly seeking a Senior Product Manager, MySQL Compete in the Marketing department. Your goal is nothing technical, it’s all PR to dispel MySQL as a viable product. I quote “you will equip field and partners to win in competitive engagements against MySQL, and you will influence market perception in favor of Microsoft technologies.” Here is the Full job description for those that want an amusing read.

This information came from an Oracle colleague of mine based in Asia.

I am pleased to announce that the Oracle ACE program has two new MySQL inductees. These people actively contribute to the MySQL community via a varied means in an unbiased and non commercial way.

Giuseppe Maxia (aka DataCharmer) is no stranger to the MySQL community having filled a position with MySQL Inc/Sun Microsystems/Oracle Corporation in the Community team for many years. Giuseppe was an active member in the community before this position, and continues to provide great input with tools such as the MySQL sandbox and many project and code snippets on the MySQL Forge as well as many writings for the Developer zone.

In the past I’ve used the profiling features (e.g. SHOW PROFILES) in MySQL to help with timing SQL statements, especially those in the

Out of habit I did use this to time all SQL statements however in MySQL 5.5.8 GA I've found this no longer to be representative.

As you can see, the query takes some 50+ms longer with profiling enabled, not to mention they have broken the Source_file column which I've actually used to troll the source code with.

mysql> set profiling=1;

4 rows in set (1.14 sec)
4 rows in set (1.15 sec)
4 rows in set (1.17 sec)

mysql> set profiling=0;

4 rows in set (0.37 sec)
4 rows in set (0.37 sec)
4 rows in set (0.37 sec)

Investigating further showed the cause. There appears to be some new overhead that causes profiling to log excessive amount of information.

mysql> show profile source for

My recent account of The State of MySQL forks seems to have gotten quite a lot of attention. I promised to follow up with a separate piece about Drizzle and also PostgreSQL, as the other major open source database, so I'd better keep that promise now.

read more

Today I discovered that you are unable to compile the current stable PHP version 5.3.4 with yesterday’s MySQL 5.5.8 GA release. I was able to download the current MySQL 5.1.54 and compile without issue.

You can find all the gory details in Bug #58987 however I was able to edit a number of MySQL include file to get a build. Does this mean it’s a MySQL packaging problem or a PHP problem I don’t know, but I would hope that Oracle in the testing phase of a GA release test this against popular programming languages starting with the LAMP stack to ensure compatibility such as what I uncovered.

I have been looking forward to the general availability (GA) release of MySQL 5.5 since is was publically announced in September that we would see this in 2010. While I already have a production client with 5.5.7rc, the badge of general availability is a great way to promote why environments should consider moving to using MySQL 5.5. Here is my quick short list of why I’d promote moving to MySQL 5.5.

1. Improved integration

The first significant improvement is that InnoDB is now again firmly a default included storage engine. The InnoDB plugin 1.1.x is now the builtin version of the engine, not a plugin version. Also the 1.1.x version has continued improvements over the 1.0.x version available as an included but not enabled plugin in current MySQL 5.1.x versions. Removing the complexity for end users over the choice of InnoDB and the necessary

It is great to draw inspiration from other Open Source communities. Brad Fitzpatrick recently wrote about Android Strict Mode. His twitter tag line for this post was “I see you were doing 120 ms in a 16 ms zone” which is all I needed to hear from somebody who also worries unreasonably about responsiveness (Web site quote).

How would I apply this to a MySQL context? This is what happens in Android. “Strict Mode lets you set a policy on a thread declaring what you’re not allowed to do on that thread, and what the penalty is if you violate the policy. Implementation-wise, this policy is simply a thread-local integer bitmask. By default everything is allowed and it

The follow-up blog post on moving your MySQL applications to CouchDB has been posted on the CouchOne blog. Part 2 digs into a bit more detail on the specifics of views, and how to perform some of the more common operations used in MySQL, such as paging and aggregation in your CouchDB view. You can read Part 2 here