How to Parse the Most Selected Tables From MySQL General Query Log in One Line

Problem: Pin down redundant queries by finding out which tables are being selected from the most.

I like general query log parsing tools, such as mk-query-digest or mysqlsla.

However, there are times when I am on a server as a dba with limited access and can only read files in /tmp.  And other times I just need to get something done quickly.

So what did I do to find out the most selected tables from a database?

1. Make sure /tmp has enough space to do what I'm about to do
2. Turn on the general query log with the file at /tmp/mysql_general.log.  This is usually /var/log/mysqllog/mysql_general or similar, but I need access to this log without waking up the sysadmin.
3. Turn off the general query log before /tmp fills up (for some people this might be just minutes)
4. Parse using this nifty set of commands:

grep -i "SELECT " /tmp/mysql_general.log | grep -io "SELECT .*" | sed 's|\(FROM [^ ]*\) .*|\1|' | sort | uniq -c | sort -nr | head -100

That's all!

As my mother would say, "Try it... You'll like it."

My Ignite Talk at MySQL Conference

My Ignite Talk (5 minute talk, 20 slides, 15 seconds per slide) at the MySQL Conference about Data Visualization was picked among three for "the Best of Ignite".  I would say I barely scraped into third spot, and it was inspiring giving a talk after Baron Schwartz gave his at the keynote on the last day of the conference.

The other two talks were "Scale Fail" by Josh Berkus (hilarious, must see) and "Causes of Downtime" by Baron Schwartz.  Enjoy!

Need to Replicate to a Database with a Different Name?

This is the first time this has happened to me, so I got excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4).

The Situation
Master Server contains db1
Slave Server contains db1 and db2
Slave Server needs db1 from the Master to replicate to db2

The Solution
On my.cnf on the slave server:

--replicate-rewrite-db=from_name->to_name

Thus, this situation would dictate the following:

--replicate-rewrite-db=db1->db2

Easy!

The Wrinkle
Part 1
What if the Master Server contained db1 and db2; and
the Slave needs only db1 from the Master to replicate to db2 on the Slave?

Part 2
We don't want to replicate both db1 and db2 to this slave, although we want to replicate both databases to another slave - this means that the filter for databases should reside on my.cnf on the slave...

Assuming that we are using replicate-rewrite-db=db1->db2:
Using replicate-do-db on the my.cnf file on the slave, which database would you use with that command?

db1 or db2?  It only takes a moment in time to find out... :)

How to Log User Connections in MySQL

There is nothing that MySQL 5.1 explicitly performs to log user connections, but there is a combination of MySQL commands that can log user connections without having to turn on the general query log. I repeat: you do not have to turn the general query log on to make this work!

Would you like to find out what that is?

With the help of Baron Schwartz's post on http://www.xaprb.com/blog/2006/07/23/how-to-track-what-owns-a-mysql-connection/ I was able to find out as well. However, I wanted to simplify things a bit, because all I need is to track MySQL connections within MySQL for now. I'll probably increase the scope in the future, but for now, let's keep it simple.

Ingredients

init-connect

NOW()

CURRENT_USER()

CONNECTION_ID()

See it coming together?

Alrighty, here's the recipe...

Assumptions: The name of the database we will use to store this information will be called admin.

1. Create the table to store the connection information in. You don't have to include the unique key if you don't want to.

CREATE TABLE admin.connections (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, connect_time DATETIME NOT NULL, user_host VARCHAR(50) NOT NULL, connection_id INT UNSIGNED NOT NULL);

2. Set the init-connect variable. This is a string to be executed for each client that connects. Details here.

SET GLOBAL init_connect = "INSERT INTO admin.connections (connect_time, user, connection_id) VALUES (NOW(), CURRENT_USER(), CONNECTION_ID());";

3. Check permissions of all of your users to ensure that they can insert a record into the admin.connections table.

4. Login as a user without global privileges and a row should be inserted in the admin.connections table. Note that the init-connect system variable does not work on users with global privileges. For better or for worse, all of us DBAs know what that means.

5. Watch your connection table grow and flourish. You've just started a new pet (project).