Wednesday, October 31, 2012

Binlogs 101

The mysqlbinlog are not new. We often reference it and all of the valuable information that it provides. I thought it might be useful to show some simple examples on how to get some of that information, for those unfamiliar with the mysqlbinlog tool.

For this example, I used the MySQL benchmark tools to populate data.

You can review binary log events a couple if different ways.
You can use the command line :

      mysqlbinlog mysql56rc-bin.000010
or within MySQL via a simple query.
      SHOW BINLOG EVENTS IN 'mysql56rc-bin.000010' ;

These will both dump out all of the data, and this demo currently has 2284725 rows of data, to much to review one by one.

Keep in mind that some of the differences between these two options. You will gather more information via the command line mysqlbinlog versus just the query "SHOW BINLOG EVENTS".

example: If we wanted to get an idea of how long a query took to run?

# mysqlbinlog mysql56rc-bin.000010
# at 915
#121031  7:52:49 server id 1  end_log_pos 1029 CRC32 0xd3201bdc         Query   thread_id=47    exec_time=0     error_code=0
insert into bench1 values ('C',2,9997,6)

I could also be more direct and check the logs for just that bin log position: 
# mysqlbinlog mysql56rc-bin.000010 --start-position=915 --stop-position=1029

The mysqlbinlog will also allow you to gather an idea of the execution time a query took on a master server. It is not perfect when checking the same query on a slave. Baron has a blog about this if you want to learn more.

We are unable to see execution time via the "SHOW BINLOG EVENTS".

MySQL 5.6 RC> SHOW BINLOG EVENTS IN 'mysql56rc-bin.000010' FROM  915 LIMIT 1\G
*************************** 1. row ***************************
   Log_name: mysql56rc-bin.000010
        Pos: 915
 Event_type: Query
  Server_id: 1
End_log_pos: 1029
       Info: use `test`; insert into bench1 values ('C',2,9997,6)
1 row in set (0.00 sec)

The mysqlbinlog also allows you to be able to search within a date range.

# mysqlbinlog mysql56rc-bin.000010 --start-datetime="2012-10-31 07:52:49" --stop-datetime="2012-10-31 07:52:50"

# at 915
#121031  7:52:49 server id 1  end_log_pos 1029 CRC32 0xd3201bdc         Query   thread_id=47    exec_time=0     error_code=0
SET TIMESTAMP=1351695169/*!*/;
insert into bench1 values ('C',2,9997,6)

This is just a simple small sample intro into MySQL Binlog. Find more information via these great blogs and sites.

Friday, October 26, 2012

Keyser Soze in MySQL?

Who is Keyser Soze?

Some recall:
"...a rarely seen, nearly mythical kingpin... Most believe he is not real, but rather boogeyman story or name-drop to intimidate people."

Some people think of it as :
"Keyser Soze – DBA or developer who looks ordinary but has insanely good skills, hardly anybody knows about it."

What does any of this have to do with MySQL ?

Well for this blog post, "Keyser Soze" is the nearly mythical query that most believe is not real, but rather a "boogeyman sql query" that wastes resources in a MySQL Database. Typically, every application has at least one. Your developers blame the database for being slow and they have caught the ear of the CEO. It is time to resolve the boogeyman issue and find out. The difference is we want a better agent than Dave Kujan, so we have the MySQL Query Analyzer and Enterprise Monitor.

First, we get them on the case:

FYI: Requires MySQL Server version 5.1.43 or later with partitioning and InnoDB storage engine enabled is required.

The install is very straight forward and just follow the prompts.
You will be asked:

Database Installation

Please select which database configuration you wish to use

[1] I wish to use the bundled MySQL database
[2] I wish to use an existing MySQL database

It is up to you which database you decide. In my opinion, a monitor should not alter the environment of what is is monitoring. I would choose option 1. It would not hurt to still backup this database, just in case.

Once this is installed we start to look over our graphs and advisor...

Very mellow server load here.
 ( granted this is a demo)

The MySQL Query Analyzer can shed more light on curious queries. 
It will take a couple edits to the ../enterprise/agent/etc/mysql-monitor-agent.ini file.  I prefer the server method because it is quick and simple and catches all default interactions with the database.

So now if an event was to hit your database you are aware whenever it happens. You will be able to scroll back overnight for example to see not only what ran, but how it ran.  If someone decided to put a small query into a infinite loop by mistake, you will find it here.

Once you do see something you are curious about... (Maybe it is "Keyser Soze" or maybe it is a planned query)

You can then highlight the graph and then pull up all queries related to that time frame.

 You will then be allowed to see the explain (as long as it meets time thresholds)  as well as graphs of how the selected query was executed via your server.

If you find yourself not getting the explains like you expected make sure to check your configuration and adjust the threshold.

While this is just a demo, this tool is very useful for exploring trends in your data, as well as find the critical events. The advisers can help you fine tune the server per the data that has been executed on it.  The Enterprise monitor will allow you to do a deep dive into the system and explore all options as you look for the "Keyser Soze" in your application.

Of course don't forget, the monitor also delivers replication information. So you can keep track of topology, server type, position, errors, and log space.

While all this  might not make an interesting movie like the Usual Suspects did, it does show that while a DBA might have a list of usual suspects when it comes to a problem, they also can lean on the tools available for a wealth of additional information.

MySQL Enterprise Edition

More Reference urls:

Friday, October 19, 2012

Amsterdam, MySQL User Group

A comment was recently posted on the MySQL User Group Wiki page about a new user group in the Netherlands available via

I wanted to take a moment and say thank you and ask everyone to support this new user group.

If people are aware of any user groups not listed, please feel free to post on the wiki page this information.

Wednesday, October 17, 2012

MySQL Failover Utility

I was giving a replication talk at Ohio Linux Fest last week and we touched a little bit on the MySQL Failover Utility and how it works with MySQL 5.6 .

The first thing that this along with Innodb and other replication enhancements are big GPL offerings from Oracle to the community.

First the usage:

[root@mysql-master python]# ./mysqlfailover --help
Usage: mysqlfailover --master=roo@localhost --discover-slaves-login=root --candidates=root@host123:3306,root@host456:3306

mysqlfailover - automatic replication health monitoring and failover


                        connection information for candidate slave servers for
                        failover in the form:
                        <user>:<password>@<host>:<port>:<socket>. Valid only
                        with failover command. List multiple slaves in comma-
                        separated list.
                        at startup, query master for all registered slaves and
                        use the user name and password specified to connect.
                        Supply the user and password in the form
                        user:password. For example, --discover-slaves-
                        login=joe:secret will use 'joe' as the user and
                        'secret' as the password for each discovered slave.
                        name of script to execute after failover or switchover

So I set up a test, I will monitor the master @ and have a slave @ all from a server @

Personally I would run this in screen, with a password on it, so I can have access to it easily from any location.

# screen
# ./mysqlfailover --master=root:<PASSWORD>@ --discover-slaves-login=root --candidates=root:<PASSWORD>@

Keep in mind that the password would be easily found in history and ps -ef views.

Now once it executes you realize that you never had GTID on in the first place..... 

ERROR: Topology must support global transaction ids and have GTID_MODE=ON

This is simple enough but 'gtid_mode' is a read only variable so it must be done via the my.cnf file.

vi /etc/my.cnf
gtid_mode                                             =ON
disable-gtid-unsafe-statements          =1

show variables like '%gtid%';
| Variable_name                  | Value     |
| disable_gtid_unsafe_statements | ON        |
| gtid_done                      |           |
| gtid_lost                      |           |
| gtid_mode                      | ON        |
| gtid_next                      | AUTOMATIC |
| gtid_owned                     |           |

 So now that those are all updated...

Again if your not in a screen this might be a good point to do so..
# ./mysqlfailover --master=root:<PASSWORD>@ --discover-slaves-login=root --candidates=root:<PASSWORD>@

# Discovering slaves for master at
# Checking privileges.
# Checking privileges on candidates.

 We get a simple display to start

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct  3 13:52:01 2012

Master Information
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000016  151                                      

Replication Health Status
| host          | port  | role    | state  | gtid_mode  | health  |
|  | 3306  | MASTER  | UP     | ON         | OK      |
Q-quit R-refresh H-health G-GTID Lists U-UUIDs

Transactions executed on the servers:
| host          | port  | role    | gtid                                    |
|  | 3306  | MASTER  | 98CCBD8A-EB09-11E1-B4D5-08002701A7D7:1  |

| host          | port  | role    | uuid                                  |
|  | 3306  | MASTER  | 98ccbd8a-eb09-11e1-b4d5-08002701a7d7  |

Since we are in screen we can control-A D and exit the screen and resume it later whenever we want to check on the status.

# screen -r

Now lets crash the master...   a quick power off of the virtual box should do it.

The output now refreshes and soon displays the new master.

# Discovering slaves for master at

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Wed Oct 17 08:50:19 2012

Master Information
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB 
mysql56rc-bin.000020  191                                      

Replication Health Status
| host         | port  | role    | state  | gtid_mode  | health  |
|  | 3306  | MASTER  | UP     | ON         | OK      |

Now if you added the "--exec-after= " you will be able to execute a script that fits your environment. This script can be used to adjust the reference point for your application to the new master automatically.

Test it out with the new MySQL 5.6 RC... Oracle would love your feedback.

Tuesday, October 16, 2012

RMOUG Training Days 2013 & MySQL

Call for Abstracts Deadline Extended until October 22! 

MySQL Sessions are needed ! Submit an abstract for RMOUG Training Days 2013! They have extended the abstract submission deadline to Monday, October 22.

They are looking for presentations on the following topics:
  • Application Development
  • Business Intelligence
  • Database Administration
  • DBA Deep Dive
  • Database Tools of the Trade
  • Middleware
All primary authors of selected abstracts receive a complimentary conference registration.

Visit for topic descriptions and details on how to submit your abstract online. 

Sunday, October 14, 2012

MySQL Cluster - Web Scalability with Carrier Grade Availability

Just a quick note that the Colorado MySQL User Group will be have Craig Sylvester in town November 7th 2012 for a MySQL Cluster talk. Please feel free to RSVP and join us!

Thursday, October 11, 2012


Many of us know the joys of the "Screen" command. I did notice that this topic became a point of many questions at the recent Ohio LinuxFest, so I thought I could touch more on it here.

Being able to detach and reattach to a shell is a wonderful thing.  It is very useful when executing long query transactions like MySQL data exports or even running backups at times. It is also very useful to be able to change computers or locations and keep the same working shell environment. I often used Screen when I started a shell script that executed a full compressed backup, scp the files to a remote slave, and then would start to uncompress the backup onto the new slave. The entire process for this was around 24hrs easily so being able to reattached and monitor the progress was very helpful.

Being able to have a visual display that is quick and clear as to what host your connected on is of course critical. A fast an easy way to know MySQL Hosts is also very valuable.  At the very least you can change the mysql prompt in the my.cnf file
(   ie my.cnf --> mysql --prompt=" this is a mysql box>"  )

or just via your session

( mysql> prompt MySQL Master>
PROMPT set to 'MySQL Master> '
MySQL Master> show master status\G


Screen becomes helpful keeping the shells organized. Personally, I do not like to have a different colors in my shell backgrounds.  A simple white or black is just fine for me.

Here are a few other things I do with Screen:

1st  create or edit a file for yourself vi ~/.screenrc

I like to enhance my status line at the bottom with the following:

hardstatus string '%{gk}[ %{G}%H %{g}][%= %{wk}%?%-Lw%?%{=b kR}(%{W}%n*%f %t%?(%u)%?%{=b kR})%{= kw}%?%+Lw%?%?%= %{g}][%{Y}%l%{g}]%{=b C}[ %m/%d %c ]%{W}'

You end up with something like the following on the bottom of all screen windows.

[ Remote Hostname ][            (0*$ bash)                             ][1.00 1.00 1.00][ 03/07  0:20 ]

if you have multiple screens you get this:

[ Remote Hostname ][          (0*$ bash) (1*$ bash)            ][1.00 1.00 1.00][ 03/07  0:20 ]

Yes you can have multiple screens just type screen when your inside a current screen to start another.

It is always best to name your screens as well.

Cntrl A then :
Then type "Title < title name>" of what you want to label the tab window. Your results are similar to below.

[ Remote Hostname ][    0-$ MySQL_Master  (1*$ MySQL_Slave)        ][1.00 1.00 1.00][ 03/07  0:25 ]

Then Cntrl A <0--9> to switch between tab windows.

You can access a screen session via any computer that is allowed to connect to the screen host. So always keep in mind to lock your screen sessions.

Cntrl A password  -- So you can lock with a password of your choice.

You might have several different Screen sessions so try to also name your screen sessions.  You might be executing a MySQL dump in one Screen session and working with a data export in another.

screen -S MySQL_dump
-S sockname   Name this session <pid>.sockname instead of <pid>.<tty>.<host>.

This naming comes in very handy when you have to reattach.
I often use the handy shell script found here:

Another big advantage of Screen is being able to work with remote workers in training or just reviewing MySQL queries.  For example, you can have a few different developers on the same screen session and execute explain on their recent queries so you can all review the best index options together.
If you want to learn more about that here is a nice article.

Some good tips on screen are also available here

Monday, October 8, 2012

Thanks for the support!

I wanted to post a big thanks to the Chinese MySQL Bloggers.  Almost a month ago we published the  Chinese version of Planet ( my previous blog post ). In the last month, we have collected 68 different blog posts about MySQL across 8 different MySQL Bloggers.

 Thanks for the support, this is an encouraging start.