Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.The issue, of course, lies in what the "certain" privileges are. If it is "who has the SUPER privilege?" then a simpleSELECT user,host FROM mysql.user WHERE Super_priv='Y';is sufficient. If it is "who has write access to the foo database", you might write:SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:

Note: This article is about the "WHERE" extension to "SHOW". I specifically use "SHOW STATUS" as an example, but "WHERE" is an extension to many "SHOW" statements.Often DBAs will assess the health of a system by looking at some of the status variables returned by "SHOW GLOBAL STATUS" (specifying "GLOBAL" is important; remember that "SHOW STATUS" is the same as "SHOW SESSION STATUS"). There are many status variables that "SHOW GLOBAL STATUS" returns. ("SHOW GLOBAL STATUS" on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the "SHOW STATUS LIKE" syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am looking for.But I did not know of a way to perform "SHOW  []

Every so often we have a client worrying about unauthenticated users. For example, as part of the output of "SHOW PROCESSLIST" they will see:+-----+----------------------+--------------------+------+---------+------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+-----+----------------------+--------------------+------+---------+------+-------+------------------+| 235 | unauthenticated user | 10.10.2.74:53216 | NULL | Connect | NULL | login | NULL || 236 | unauthenticated user | 10.120.61.10:51721 | NULL | Connect | NULL | login | NULL || 237 | user | localhost | NULL | Query | 0 | NULL | show processlist |+-----+----------------------+--------------------+------+---------+------+-------+------------------+Who are these unauthenticated users, how do they get there, and why aren't  []

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations: \tonline ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET\tOnline, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format...which you can query.

Do you "have_community_features"? I do!SHOW GLOBAL VARIABLES LIKE 'have_community_features';+-------------------------+-------+| Variable_name | Value |+-------------------------+-------+| have_community_features | YES | +-------------------------+-------+1 row in set (0.00 sec)I am pretty sure this is one of those variables that MySQL has put in as an unused placeholder, but for now, it is not even documented as unused (as are table_lock_wait_timeout,

Wheel of protocol, turn turn turn.Tell us the lesson that we should learn. (with apologies to the original source)Writing a book comes with many challenges. For me, writing a MySQL book for MySQL beginners, who may or may not be database beginners, has fed my compulsion to research and test bizarre interactions.Today's lesson is on what protocol is used when connecting to a local "mysqld" instance on a non-Windows machine. The TCP/IP protocol is used by default when connecting on a Windows machine, and connecting from any operating system to a non-local "mysqld" instance. I am assuming the connections are being made by a command line client such as "mysql", "mysqladmin" or "mysqldump". Connections made via connectors such as Connector/J, an ODBC connector, DBD::mysql, etc are not covered in this post.If you seem to be having trouble connecting, check your options against your  []

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.Except for one thing.The binary log file and position do not appear in their rightful place. Here's a snippet of the output from the backup:innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead090210 03:55:04 innobackup: innobackup completed OK!That's pretty misleading -- looks like the backup completed OK, but it did not show us the binary log position. What about the "ibbackup_binlog_info" file?[mysql@db3:~] more ibbackup_binlog_info Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead[mysql@db3:~]Yikes! What happened to the binary log position and file information?Have no fear, it's actually in another file.

You probably know that "mysql -h host_or_ip" can connect you to a remote host.But did you know that you can change the host you are connected to from within "mysql"?The undocumented (as far as I can tell, in the MySQL manual and in the "help" on the mysql command line) "CONNECT" statement can help.

I realized tonight exactly why MySQL's default behavior of silent truncation bothers me.It reminds me of people who use a ticketing system and close every ticket as soon as they are done working on the issue instead of actually asking the other party if they are satisfied, because closing more tickets make it look like they're doing more work.It reminds me of workers at fast food restaurants who hit the button to make the order disappear as if they have already served me my food, because

We've been running into a problem with one client:SELECT COUNT(*) FROM tbl;takes 0.25 seconds on one db, and 0.06 seconds on another. Consistently. That's a fourfold difference. There aren't any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it's a hardware difference.Th

"mysql-proxy" defaults to round-robin load balancing. There are fancy tricks around to get "mysql-proxy" to balance connections based on how many idle connections there are in a proxy-based connection pool. But there is no code that I found that would simply load balance based on "always go to one server, go to another server only when the first server is down."Well, I spent way t

Someone asked me what applications were good/bad for MySQL Cluster. As I've now actually had experience with a Cluster setup and a real-life application of it, and dug through the manual, I present a few characteristics of applications that will work with Cluster, and why they are so (so that if you have an application that meets some of the characteristics but not all of them, you can decide whether it's worth it to use Cluster or not).Firstly, I'll state this -- there's actually a very limited application to MySQL cluster. I haven't assessed the disk-based cluster, only the memory-based one, so I don't know what really changes with disk-based. But after you see this list, you certainly will want to re-think your use of disk-based cluster if a lot of the inner workings don't change.The factors are listed below, but the "ruler" I keep in my mind is the fact that MySQL Cluster was developed for telecom applications.  []

Open Source means that the source code is open. There are many inferences that can be made from this, and many stereotypes that can be applied, but in the end, all it means is that you can read the source code as well as use the binaries.I am not a developer. I fully admit this. What I mean by this is that my strengths do not lie in engineering new code to do things. Give me some Lego™s or some Wedgits™ and I look at the instructions to see what to build.However, I am an engineer.One of my team's current tasks is to restore a backup (using InnoDB Hot Backup, and compressed) from a client's production machine to a development instance.....Weekly -- thus we want to automate it......And did I mention the development machine has 5 different MySQL instances on it, running all on 127.0.0.1 on different ports?The steps are:0) stop the desired mysql instance 1) move the development datadir and logs to a backup  []

This is the first place I am announcing this: The Pythian Group has made me a Team Lead. I am extremely honored and somewhat humbled by this, and I am determined to do a good job. I started officially on Monday, March 3rd, and my first week went pretty well. On Saturday, I spent a short bit of time automating one process. And while I was waiting for a 300G backup to copy from one machine to another, I worked on automatin

One of my colleagues made a typo in a query today that led to me discovering this issue -- I think it's a bug, though it may just be how the language is defined. But certainly a subquery that cannot run should not act as if it returns TRUE.mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> create table foo (fid tinyint unsigned not null primary key); create tabl

Consistent backup:A consistent backup is one that represents a snapshot of all data at a point in time. Consistent backups are used for disaster recovery. An inconsistent backup can be useful for retrieving partial data -- for example, if a long-time customer accidentally deletes information from his profile, you can go back to an inconsistent backup and retrieve that information. It is not important that all the data be consistent with each other when retrieving a partial amount of point-in-time data.Logical backups:

Today's contradiction:MySQL has server variables named "new" and "old".The "new" variable can be set per-session and globally, and is dynamic. The "old" variable is not dynamic, and only global in scope. Both default to FALSE in MySQL 5.1.According to the manual, the "new" variable:was used in MySQL 4.0 to turn on some 4.1

As Baron points out, Percona's Xtrabackup tool can be used just like InnoDB Hot Backup.Many are wondering, "is it good enough?" In fact, I wondered the same thing, and after a few weeks of using and testing Xtrabackup (on machines that have MyISAM and InnoDB tables), I can say:0) We have not run into any problems with

If you have a 12-server MySQL Cluster with:1 Management Node3 SQL Nodes2 Data Node Groups, 4 Data Nodes per groupAnd each machine is configured to allocate 1G of memory for its function, how much data (data + indexes) can you store in total in your cluster?You can guess, but you get bonus points if you explain why and the explanation is correct.

As folks are making their plans to go to the MySQL User Conference, I just wanted to remind folks of the schedule of MySQL Camp.One feature I put together for MySQL Camp but anyone can use is a restaurant list for the hotel area. There's very little within walking distance, but many people will be local or will rent a car, so finding someone to drive with should not be a problem. The restaurant list is on the MySQL Forge Wiki at http://forge.mysql.com/wiki/SantaClaraRestaurants -- updates are welcome!About MySQL Camp: MySQL Camp is completely free, just walk on in and enjoy the sessions. All sessions are in the Bayshore room off the Mezzanine, and there will be signs directing you to the MySQL Camp room. I describe it as being like "an additional room for the MySQL Conference, but it's free." It also has some of the more technical, hands-on talks that did not make it to the conference.