It’s been written about everywhere, but since we keep spotting installations in the wild where people don’t know about it, it probably deserves another mention.

By default, Linux uses the atime option on a disk mount, which means it writes a timestamp (e.g. a write to the drive) every time it reads anything. So in this case, reads cause writes – and also disk seeks, because a read from a file will then trigger having to write to the directory that contains the file. This even occurs if a file is read from the file system’s page cache (reading from the machine’s memory rather than the drive).

Unless you require an audit trail of users reading files, you generally you don’t want this. Thus, you want to add the noatime option to the disk mount in /etc/fstab. If you have just the defaults in there, you just make it defaults,noatime. It’ll doesn’t necesarily require a reboot as you …

Ever wondered how a database cluster ticks? Here’s how! During my talk at the International PHP Conference (#ipc13) I tried to build a cluster. I brought a soldering iron, sold, pink, orange and brown cables with me. Then, I tried to sold the thick (reliable, high throughput) brown cable at my company notebook (video coming). Eventually, I failed. Probably, I lacked the theoretical background?! Luckily, I got very theoretical slides with me…

DIY: A distributed database cluster, or: MySQL Cluster from Ulf Wendel

…

I love Percona’s XtraBackup utility.  It’s basically a GPL answer to Oracle’s proprietary (and expensive) MySQL Enterprise Backup.  Percona claims that it has even more features that Enterprise Backup.  I do not have access to Oracle’s product, though, so I cannot evaluate that claim.

I have my backups set up in a particular way, for convenience and security.  I have a backup machine that has ssh access to the backup user on other machines.  It has terrabytes of spare disk space, and runs a script that uses rsync and other utilities to take backups of my other servers.

Since my backup server is where all backup scripts are run, I have previously used mysqldump to take a backup of one of my slave servers.  This worked well, but was …

I recently had to do a backup of a 30 very large databases within a MySQL instance that had a total of 60+ databases.  I did NOT want to back up the other databases. In fact, the other databases had very large tables in them and I had very little disk space, so I could not afford to back them up. In this post I will share what I learned.

I’ve simplified the problem to illustrate the idea.  My goal in this post is to backup one database when an instance has many, with mixed InnoDB and MyIsam tables.

• I have 3 databases called dbtest1, dbtest2, and dbtest3, in one instance of MySQL .  I only want to back up  the tables in database dbtest2

• Each database has one InnoDB and one MyISAM table
• The InnoDB tables are quite large, and I have limited disk space …

“Application designers need to start by thinking about what level of data integrity they need, rather than what they want, and then design their technology stack around that reality. Everyone would like a database that guarantees perfect availability, perfect consistency, instantaneous response times, and infinite throughput, but it´s not possible to create a product with [...]

I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

1. Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
2. Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
3. Master: INSERT INTO rpltmpbreak VALUES (1);
4. Slave: SHOW SLAVE STATUS \G

If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but nevertheless it’s quite …

You might find a warning like the below in your error log:

130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)

What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.

Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not necessarily.

For this type of query, it primarily refers to the fact that without …

Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven’t, let’s discuss why InnoDB is a more scalable solution than MyISAM.

MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn’t crash-safe.  In other words, you better have frequent backups.

MyISAM tables have a read queue and a write queue.  Queries are placed into one of those two queues to be processed.  The write queue has higher priority than the read queue, but the table can only process one write query at a time.  Multiple read queries can occur at once, so the read queue will often be empty.

If a single query is added to the write queue, the read queue will …

At Open Query we steer clear of code development for clients. We sometimes advise on code, but as a company we don’t want to be in the programmer role. Naturally we do write scripts and other necessities to do our job.

Assisting with an Open Source project, I encountered three old UDFs. User Defined Functions are native functions that are compiled and then loaded by the server similar to a plugin. As with plugins, compiling can be a pest as it requires some of the server MySQL header files and matching build switches to the server it’s going to be loaded in. Consequentially, binaries cannot be considered safely portable and that means that you don’t really want to have a project rely on UDFs as it can hinder adoption quite severely.

Since MySQL 5.0 we can also use SQL stored functions and procedures. Slower, of course, but …

This is the start of the heavy travel season for the MySQL Community Team. So if you are attending PHPTek, Drupalcon, Texas LinuxFest, SELF, Redhat Summit, or Lonestar PHP please make sure to say ‘hi’ while you are in Chicago, Portland, Austin, Charlotte, Boston, or Addison. This group of trips is starting with a swag bag full of MySQL stickers, thumb drives, and ‘boogie bots’

MySQL Boogie Bots

Talks PHPTek – Ten Things to Make Your MySQL Servers Faster and Happier — May 16th, 2:45p – 3:45p Texas Linuxfest – The Proper Care and Feeding of a MySQL Database for Linux Administrators — May 31st, 11:25a – 12:20p SELF – Two full days of MySQL talks but I am talking on Ten Things to Make Your MySQL Servers Faster and Happier. MySQL 101, MySQL User Administration — June 8th – 9th Redhat Summit – Big & Traditional Databases — June 12th, 4:40p – 5:50p</li?

• 
  …