Blogs

ORACLE won't kill MySQL!

Lets just get it over with!

"MySQL co-founder David Axmark says "Oracle has no real reason to support" the open-source database it's trying to acquire as part of Sun, but he also says "I doubt they'd 'kill' anything." Rather, the real intrigue will center on what happens when Oracle's database customers want to migrate downstream to MySQL.

From a cbronline.com article quoting Axmark:"

-- FULL Article…

Continue

Added by Chris on January 15, 2010 at 4:56pm — No Comments

MySQL UC 2010?

Soooooooo, is the MySQL conference going to fizzle out in 2010? If you have been busy waiting to see what will happen with Oracle/Sun/MySQL then you probably didn’t notice that the call for papers did not go out to the community for 2010 UC. I don’t know about you but the MySQL Conference is something I look forward to every year and enjoy attending. Just being there is great for networking and keeping up on innovation not to mention getting out of the office for the better part of a… Continue

Added by Chris on November 24, 2009 at 3:36pm — 2 Comments

Broken Index from InnoDB Hot Backup

A very interesting problem came up a while back when testing a rebuild and failover procedure. I had just run a rebuild of a slave server with InnoDB Hot Backup from the master. After the failover, one query on three tables in three different databases was not performing as it should. This was very odd to me given that the same table in all three databases was acting up. Below is the table structure and example query:

mysql> show create table… Continue

Added by Chris on November 9, 2009 at 1:44pm — No Comments

Data type confusion: what is an int(11)?

Over and over I see customers that don't understand what int(11) really means. Their confusion is understandable. Many know what defining a char(10) means (a fixed-sized character string that allows up to 10 characters). However, ints are different.

First of all, there are 5 types of integer. They are all fixed size.

Type

# of…

Continue

Added by Sarah Sproehnle on October 30, 2009 at 8:04pm — 1 Comment

Secure Your Server – Tips for DBAs

I have complied a list of a good tips for DBAs to consider when implementing security policies in MySQL.

-- The List

1. Make sure that ALL root accounts (ALL PRIVILEGED accounts) have strong passwords and apply them using hashes not plain text. If you leave the password blank anyone can connect as root without a password and be granted all privileges.

2. For ALL other accounts use passwords and apply them using hashes not plain text.

A good… Continue

Added by Chris on October 22, 2009 at 2:30pm — 2 Comments

Security Sensitive Grants

Consider excluding the following grants from users on any production MySQL server.

-- GRANT OPTION

"The GRANT OPTION privilege enables you to give to other users or remove from other users those privileges that you yourself possess." (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_grant-option)

-- RELOAD

"The RELOAD privilege enables use of the… Continue

Added by Chris on October 21, 2009 at 6:00pm — No Comments

Basics of MySQL failover (using replication)

For easy MySQL failover, replication is a great solution. This post will cover the basics of performing a failover in case your master dies.

First, setup one-way replication (or two-way, but don't plan to use both servers for writes at the same time). Next, you'll want to direct all activity, or at least the writes, to the master. If the master dies, there are two major concerns:

Redirecting the clients to the slave. There are several ways to handle this, such as…

Continue

Added by Sarah Sproehnle on October 21, 2009 at 8:50am — 2 Comments

No Password – No Problem

In the past I have needed to gain access to MySQL servers when the password had been strangely forgotten. Below there are thee different processes to gain access to MySQL if you just don’t have the right kind of access you need or want.

Option 1: --skip-grant-tables

This will allow you to login to the server and change what you need to in the mysql.user table. Of course you will need to restart the server again without --skip-grant-tables if you want the… Continue

Added by Chris on October 20, 2009 at 6:49pm — 3 Comments

How MySQL Cluster executes queries

This post describes how MySQL Cluster executes queries. First of all, Cluster is a storage engine. It doesn't actually execute queries because it doesn't speak SQL. That is why you use a MySQL server, which parses your queries and sends low-level storage engine API calls to the Cluster data nodes. The data nodes know how to retrieve or store data. Or you can talk to the data nodes directly using the NDB API(s).

MySQL Cluster has various means of executing queries. They boil down… Continue

Added by Sarah Sproehnle on October 19, 2009 at 3:44am — No Comments

A MySQL problem or something else?

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.

The Problem

You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state,… Continue

Added by Chris on October 5, 2009 at 3:53pm — 2 Comments

Pager - but not on call!

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.

-- Example 1

Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…

Mysql> pager less –S

By running the… Continue

Added by Chris on October 2, 2009 at 5:25pm — 2 Comments

Fun with mysqlslap benchmarking

Mysqlslap is a good benchmarking tool but can be much more versatile in my opinion. So, recently, I was benchmarking a problem that was, not hard to solve, but somewhat tricky to benchmark. Using mysqlslap was the fastest way to get the answers I wanted with the least amount of BS, or so I thought!

Lets take a look:

-- The problem

1. A MySQL instance with 5… Continue

Added by Chris on September 22, 2009 at 7:06pm — No Comments

What's faster than REPAIR?

After Chris' great post, What's faster than ALTER, I got thinking about REPAIR. REPAIR TABLE can be slow, but may seem necessary if your table is corrupt.

A little background:

MyISAM tables are prone to corruption if they are receiving updates/inserts/deletes/etc and your server crashes. Doing a REPAIR TABLE can generally "fix" them, but it can take time. The larger the table,… Continue

Added by Sarah Sproehnle on September 16, 2009 at 9:00pm — 6 Comments

The Project Formally Known as Golden Gate

So I was watching the discovery channel about the maintenance of the Golden Gate bridge. I was surprised to find out that it takes two years to paint the whole thing, then, when they are "finished" the job starts all over again. Basically, this is a never ending project! I started to think about the larger, several thousand server, MySQL installations I've worked on and how to improved performance. Oddly enough The Project Formally Known as Golden Gate was born! Thought I would change the name… Continue

Added by Chris on September 14, 2009 at 10:00pm — 1 Comment

Using TMPFS for MySQL's tmpdir

There have been a lot of systems I have looked at where the Created_tmp_disk_tables was very high. MySQL has good documentation on how it uses memory, specifically temp tables, here.

I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is… Continue

Added by Chris on September 12, 2009 at 9:27pm — 3 Comments

What's faster than ALTER?

Well, the answer I always get is "DROP". Then I would say... "what if you wanted to preserve your data?" It is very common for DBAs to think only in terms of what is obviously available in MySQL, ALTER, than all of the tools that are really there. I have been placed in situations in many companies where ALTER was just not the way to go.

Given the amount of MySQL usage throughout the WEB2.0/IT industry and the fact that data sets seem to keep growing... I have come up with a great… Continue

Added by Chris on September 10, 2009 at 2:30pm — 8 Comments

Partitioning by dates: the quick how-to

There is thorough documentation about the Partitioning feature in MySQL 5.1. There are also nice articles like this one by Robin. However, I thought it would be useful to have a quick "how-to" guide to partitioning by dates. I'll use the world schema to keep it easy.

Partitioning a… Continue

Added by Sarah Sproehnle on September 8, 2009 at 12:18pm — 8 Comments

Testing Replication Over the Pond – Part 2 Secure

Testing Secure Replication

For the first test I used encrypted (SSL) replication and inserted 200,000 records using three 10 minute disconnection intervals per hour.

After several hundred thousand of inserts, deletes and updates on the SOA and RR tables simultaneously over a normally connected SSL replication channel I have the following results:

RR Update Test

Test 1:

RR Records Updated (Changes Set) = 129145

Elapsed… Continue

Added by Chris on September 6, 2009 at 8:41pm — No Comments

Testing Replication Over the Pond - Part 1 Non-Secure

Testing Non-Secure Replication

A series of experiments were conducted to determine whether MySQL replication would prove to be reliable with SSL enabled. Please note that all tests were conducted using the MyDNS schema with includes the SOA and RR tables on MySQL 5.1.

The first experiment sets focused on replication operations and not on a predetermined set of Insert, Update or Delete patterns. So Inserts were used since they are the easiest to tag and verify.… Continue

Added by Chris on September 6, 2009 at 8:40pm — No Comments

Index for god sake!

This is an old post I put on the old mysqlhow2.com that ended up on modphp.org. Both sites are run by people I know so it's coo. Anyway, I thought it should be here as well.

Lets look at the results of the query without returning all the results:

NOTE: Use EXPLAIN

- Explain will show the possible rows to be examined in your query. (The more rows the slower the return)

- Example of EXPLAIN: (we will be using the world database by… Continue

Added by Chris on September 6, 2009 at 8:39pm — No Comments

All Blog Posts (41)