The recently revived MySQL Meetup group had its first meeting. A big thanks to Venu Anuganti, our host and Schooner our sponsor. If anyone is interested in joining the fun please check out the Meetup.com site here.

During our first meetup we went over basic MySQL topics including configuration, performance tuning, locking, along with running MySQL in the cloud. All in all it was a well done presentation and I am looking forward to the next.

Thanks again for getting the Meetup going!

Chris

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
http://www.informationweek.com/blog/main/archives/2010/01/oracle_wont_kil.html;jsessionid=R2UWL0QMOQMSHQE1GHPSKHWATMY32JVN

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 week!

I was planning on submitting a few talks of my own this year but am wondering if the UC in 2010 will even happen. Sure there are some dates around it, April 12-15, found on http://www.mysqlconf.com/ but no other information.

I know that the Percona conference is …

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 plx_async_job\\\\G
*************************** 1. row ***************************
Table: plx_async_job
Create Table: CREATE TABLE `plx_async_job` (
`async_job_id` int(11) NOT NULL AUTO_INCREMENT,
`db_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`user_id` int(11) NOT NULL DEFAULT '0',
`created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`job_status` tinyint(4) NOT …

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 practice is to use the following:

mysql> grant SELECT, INSERT……. ON `database`.* to ‘user’@’10.%’ IDENTIFIED BY ‘*9B9C8E678DB2D62877E829D633535CBEB2B7E6E0’;

NOTE: make sure you are using HASHS and not plain text!
If you run something like below…

mysql> update user set password = password(‘somepassword’) where user = ‘someuser’;
Make sure …

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 FLUSH statement. It also enables mysqladmin commands that are equivalent to FLUSH operations: flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, and reload." (http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_reload)

-- SUPER
"The SUPER …

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:

1. Redirecting the clients to the slave. There are several ways to handle this, such as Heartbeat or MySQL's JDBC driver.
2. Checking if the slave is caught up. This is trickier. If the master's binary logs are still available, then you can do SHOW SLAVE STATUS on the slave and compare the READ_MASTER_LOG_POS to the master's binary log. For example, if SHOW SLAVE STATUS shows:
   Master_Log_File: localhost-bin.000051
…

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 current and newly added or modified user account to work.

This option is a good way to gain access to the server in the event that you have no elevated (root) privileges on the MySQL server. You do need root on the server you are on so you can kill the pid and restart with --skip-grant-tables.

Option 2: elevating your privileges

You will need to have an account on the server with WRITE access to the …

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 to:

1. Primary key lookup
2. Unique key lookup
3. Ordered index scan (i.e., non-unique indexes that use T-trees)
4. Full table scan

Let's say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you've controlled the partitioning using the 5.1 partitioning features). So for …

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, probably “Sleeping”. You speak with the lead developer and they state that there have not been any code changes for 1 month. It’s an odd problem, when you see it; however, the solution is not that obscure when you think about how MySQL uses DNS.

The Solution

When you restart MySQL with “skip-name-resolve” enabled you …