“There are 10 types of people in the world — those who understand binary, and those who don’t.”

The term “binary” in MySQL has many different meanings. How many can you come up with? I have 6, but I am willing to believe there are more!

0) “Binary distribution” is the name for the package that contains a binary. Another use is “binary installation” but that’s pretty much the same usage pattern as “binary distribution”, so I won’t count “binary installation” as a separate usage.
1) “Server binary” or “client binary” is the actual program (mysqld, mysql).
2) “Binary format” is a compressed format. For example, DECIMAL is stored in a “binary format” — each group of nine digits is compressed into 4 bytes.
3) “Binary log” is the change log. You can argue that this is an extension of #3, because the binary log is a compressed log, but “binary log” is encountered ubiquitously in the MySQL world, and “binary format” is somewhat obscure knowledge.
4) “Binary CHARSET” - sets the collation to be case-insensitive. For instance, utf8_bin and latin1_bin are the binary collations for the utf8 and latin1 character sets, respectively.
5) “Binary string” - a byte string. This is also known as the BINARY data type. There is some kind of wit/pun in the fact that a number in binary is actually a “bit string”.

Any more I missed? There are over 1600 mentions of the word “binary” in the 5.0 manual!

So, you have a binlog. You want to find out something specific that happened inside of it. What to do? mysqlbinlog has some neat features, which I thought we would look at here.

I should first explain what mysqlbinlog really is. It is a tool that lets you analyze and view the binlogs/relaylogs from mysql, which are stored in binary format. This tool converts them to plaintext, so that they’re human-readable.

For the first tip, let’s start with the --read-from-remote-server option, which allows you to examine a binlog on a master server in order, perhaps, to dump it onto your slave and compare master/slave logs for potential problems*.

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | head -5
Enter password:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080815 19:25:23 server id 101  end_log_pos 107 	Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:23 at startup

Pretty useful!

Now, let’s assume we have a binlog that is 94 lines long*:

(more…)

Or, When MySQL Lies!

When I do a show slave status\G, sometimes mysqld will lie to me and give me a wrong Exec_Master_Log_Pos. Let me explain with a situation from last night.

This is the output of show slave status\G from mysql version 5.0.41-community-log:

mysql> show slave status \G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: XXX.XXX.XXX.XXX
                   Master_User: replic_username
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000480
           Read_Master_Log_Pos: 690470773
                Relay_Log_File: db2-relay-bin.000028
                 Relay_Log_Pos: 683977007
      Relay_Master_Log_File: mysql-bin.000480
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave.
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 126
               Relay_Log_Space: 690471192
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: NULL

So in summary, the slave SQL thread is stuck (in this case because of a problem during the transfer of the binlog data to the slave’s relay log). The show slave status\G command tells me that it is stuck at the master binlog file mysql-bin.000480, position 126.

But, if I look at the error log file entries when the slave got stuck I see:

(more…)