jintropin hgh

MySQL Back to Basics: Lesser-known MySQL client scripts

January 16th, 2009

So there’s a whole bunch of MySQL scripts included with any release, who can say they know them all? In this blog post, we will take a look at some of these and show usage examples that may help you in your environment.

mysql_access

Let’s say you want to manage your users’ rights, and you have this user ‘john.doe’ that can connect from ‘host1′, but you aren’t sure what he can do to your db: company-staging as well as company-dev.

What do you do? Well, for example, you could use mysql_access and take a look:

/5075/bin:09:13:38:Qalbi $ ./mysqlaccess -U root -d company-staging -u john.doe -h host1
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.

Access-rights
for USER 'john.doe', from HOST 'host1', to DB 'company-staging'
	+-----------------+---+	+-----------------+---+
	| Select_priv     | Y |	| Lock_tables_priv | N |
	| Insert_priv     | N |	| Execute_priv    | N |
	| Update_priv     | N |	| Repl_slave_priv | N |
	| Delete_priv     | N |	| Repl_client_priv | N |
	| Create_priv     | N |	| Create_view_priv | N |
	| Drop_priv       | N |	| Show_view_priv  | N |
	| Reload_priv     | N |	| Create_routine_priv | N |
	| Shutdown_priv   | N |	| Alter_routine_priv | N |
	| Process_priv    | N |	| Create_user_priv | N |
	| File_priv       | N |	| Ssl_type        | ? |
	| Grant_priv      | N |	| Ssl_cipher      | ? |
	| References_priv | N |	| X509_issuer     | ? |
	| Index_priv      | N |	| X509_subject    | ? |
	| Alter_priv      | N |	| Max_questions   | 0 |
	| Show_db_priv    | N |	| Max_updates     | 0 |
	| Super_priv      | N |	| Max_connections | 0 |
	| Create_tmp_table_priv | N |	| Max_user_connections | 0 |
	+-----------------+---+	+-----------------+---+
BEWARE:	 Everybody can access your DB as user `john.doe' from host `host1'
      :	 WITHOUT supplying a password.
      :	 Be very careful about it!!

The following rules are used:
 db    : 'host1','company-staging','john.doe','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'
 host  : 'Not processed: host-field is not empty in db-table.'
 user  : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'

BUGs can be reported by email to bugs@mysql.com
/5075/bin:09:13:39:Qalbi $ ./mysqlaccess -U root -d company-dev -u john.doe -h host1
mysqlaccess Version 2.06, 20 Dec 2000
By RUG-AIV, by Yves Carlier (Yves.Carlier@rug.ac.be)
Changes by Steve Harvey (sgh@vex.net)
This software comes with ABSOLUTELY NO WARRANTY.

Access-rights
for USER 'john.doe', from HOST 'host1', to DB 'company-dev'
	+-----------------+---+	+-----------------+---+
	| Select_priv     | Y |	| Lock_tables_priv | N |
	| Insert_priv     | Y |	| Execute_priv    | N |
	| Update_priv     | Y |	| Repl_slave_priv | N |
	| Delete_priv     | Y |	| Repl_client_priv | N |
	| Create_priv     | N |	| Create_view_priv | N |
	| Drop_priv       | N |	| Show_view_priv  | N |
	| Reload_priv     | N |	| Create_routine_priv | N |
	| Shutdown_priv   | N |	| Alter_routine_priv | N |
	| Process_priv    | N |	| Create_user_priv | N |
	| File_priv       | N |	| Ssl_type        | ? |
	| Grant_priv      | N |	| Ssl_cipher      | ? |
	| References_priv | N |	| X509_issuer     | ? |
	| Index_priv      | N |	| X509_subject    | ? |
	| Alter_priv      | N |	| Max_questions   | 0 |
	| Show_db_priv    | N |	| Max_updates     | 0 |
	| Super_priv      | N |	| Max_connections | 0 |
	| Create_tmp_table_priv | N |	| Max_user_connections | 0 |
	+-----------------+---+	+-----------------+---+
BEWARE:	 Everybody can access your DB as user `john.doe' from host `host1'
      :	 WITHOUT supplying a password.
      :	 Be very careful about it!!

The following rules are used:
 db    : 'host1','company-dev','john.doe','Y','Y','Y','Y','N','N','N','N','N','N','N','N','N','N','N','N','N'
 host  : 'Not processed: host-field is not empty in db-table.'
 user  : 'host1','john.doe','','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','','0','0','0','0'

So, as you can see, on company-staging he has only select privileges but on company-dev he has select,update,insert,delete.

Fair enough, this can be done in SQL as well:

mysql> SHOW GRANTS FOR 'john.doe'@'host1';
+-------------------------------------------------------------------------------+
| Grants for john.doe@host1                                                     |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'john.doe'@'host1'                                      |
| GRANT SELECT ON `company-staging`.* TO 'john.doe'@'host1'                     |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `company-dev`.* TO 'john.doe'@'host1' |
+-------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM mysql.db WHERE user='john.doe' AND host = 'host1' AND db IN ('company-staging', 'company-dev') \G
*************************** 1. row ***************************
                 Host: host1
                   Db: company-dev
                 User: john.doe
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: host1
                   Db: company-staging
                 User: john.doe
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)

But mysql_access can give you that without logging into mysql.

mysqlshow

How many people know that there is a program to show what databases or tables you have? For example, take a look at this:

/5075/bin:09:31:36:Qalbi $ ./mysqlshow -uroot
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| company-dev        |
| company-staging    |
| employees          |
| mysql              |
| test               |
+--------------------+
/5075/bin:09:31:44:Qalbi $ ./mysqlshow -uroot employees
Database: employees
+---------------+
|    Tables     |
+---------------+
| departments   |
| dept_emp      |
| dept_manager  |
| employees     |
| salaries      |
| titles        |
| v_emp_by_dept |
+---------------+

This shows the databases this user has access to, as well as the tables in the ‘employees’ database.

If you want to see how many rows (and columns) you have in the tables in the employees db:

/5075/bin:09:31:49:Qalbi $ ./mysqlshow --count -uroot employees
Database: employees
+---------------+----------+------------+
|    Tables     | Columns  | Total Rows |
+---------------+----------+------------+
| departments   |        2 |          9 |
| dept_emp      |        4 |     331603 |
| dept_manager  |        4 |         24 |
| employees     |        6 |     300024 |
| salaries      |        4 |    2844047 |
| titles        |        4 |     443308 |
| v_emp_by_dept |        2 |          9 |
+---------------+----------+------------+
7 rows in set.

But yet again, we can see this with pure SQL too (databases first, then tables in ‘employees’ and then the rows per table in ‘employees’:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------------+
| SCHEMA_NAME        |
+--------------------+
| information_schema |
| company-dev        |
| company-staging    |
| employees          |
| mysql              |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'employees';
+---------------+
| TABLE_NAME    |
+---------------+
| departments   |
| dept_emp      |
| dept_manager  |
| employees     |
| salaries      |
| titles        |
| v_emp_by_dept |
+---------------+
7 rows in set (0.63 sec)

mysql> SELECT
    ->  t.TABLE_NAME, COUNT(1) AS "Cols", t.TABLE_ROWS
    -> FROM
    ->  TABLES t
    -> INNER JOIN
    ->  COLUMNS c ON (
    ->   t.TABLE_NAME=c.TABLE_NAME
    -> )
    -> WHERE
    ->  t.TABLE_SCHEMA='employees'
    -> GROUP BY
    ->  c.TABLE_NAME;
+---------------+------+------------+
| TABLE_NAME    | Cols | TABLE_ROWS |
+---------------+------+------------+
| departments   |    2 |          9 |
| dept_emp      |    4 |     332289 |
| dept_manager  |    4 |         24 |
| employees     |    6 |     300695 |
| salaries      |    4 |    2844513 |
| titles        |    4 |     443803 |
| v_emp_by_dept |    2 |       NULL |
+---------------+------+------------+
7 rows in set (0.17 sec)

Please note that the TABLE_ROWS column is only a estimate, as all the tables (except for the view used in another blog) are InnoDB.

So this covers some initial lesser-known scripts, and I will continue to report on the usage of other less-known scripts and what they do in this series of blogs. Please stay tuned.

Refactoring MySQL Applications: Part 1

January 15th, 2009

First off, let me wish you all a Happy 2009, and my apologies for being a bit slack with blogging in the last two months of 2008. It’s been a bunch of busy weeks, but I’m fully expecting to remedy that in this year.

Second, let me welcome you to this series on refactoring MySQL applications. I will cover best practices and useful tips, as well as show working examples of potential loopholes and solutions.

So, you are a DBA or a developer, and you’re faced with a problem. Your years-old application (perhaps you inherited it from a former co-worker) is now starting to keel over, and your choice of responses is limited.

Let’s start by saying that there are other ways to reach performance, but in some cases refactoring may be the only way that is possible to pursue, for one reason or another. Let’s take the refactoring way, focusing on SQL rather than applications, as there’s a lot that can be done on this side.

But first, why refactoring? Refactoring normally sits between parameter-tuning and throwing more hardware at the problem, and can be a very cost-effective way of solving performance or scalability issues.

So let’s start with a good point about composite indexes that seems to be forgotten sometimes. Given the following data . . . 

group, category, name, manufacturer

 . . . we want to grab all Electronics from Apple. Given that we have an index on category and manufacturer, the data for category could look like this (visually explained, not following the column specifications): category=electronics, group=mp3, name=whatever name is used, manufacturer=apple/microsoft . . . 

...
Electronics|MP3|iPod|Apple
Electronics|MP3|Zune|Microsoft
Electronics|TV|Apple TV|Apple
...

And so on. But, if we want to grab all the Apple electronics, we can quickly see that we’re also fetching other data (Microsoft zune in this case), which means that it’s not really that optimal, given that this is potentially a big table and fetching unneeded data is never good.

What if we use a index on (category,manufacturer) instead?

...
Electronics|Apple|MP3|iPod
Electronics|Apple|TV|Apple TV
Electronics|Microsoft|MP3|Zune
...

As shown here, the even more selective index allows us to group together all apple electronics and pull them out without including the Microsoft ones (if the query is for example SELECT ... FROM t1 WHERE category='Electronics' AND manufacturer='Apple'). However, don’t go around asking for example: SELECT ... FROM t1 WHERE manufacturer='Apple' if you only have this composite index, as it does not form a prefix of our (category,manufacturer) index, and will not be usable.

Using autocommit=1 can also be a major player in performance, so investigate how often you really should commit, and try to optimize that, as committing includes overhead, and will slow down your application. On the other hand, not committing may result in concurrency issues and/or data loss, so you need to find the sweet spot there.

To use views, or not to? Well, in some cases views can increase performance, and in some cases they can reduce it. Let’s take a look at the “employees” example database, trying to count the number of employees in a specific department (whether or not they’re still working there).

Let us also compare a view with another example. So, first let’s create a view:

mysql> CREATE VIEW v_emp_by_dept AS
    -> SELECT dept_no, COUNT(emp_no) Employees
    -> FROM dept_emp
    -> GROUP BY dept_no;
Query OK, 0 rows affected (0.11 sec)

mysql> set query_cache_type=0;
Query OK, 0 rows affected (0.02 sec)

And then let’s get some data out of it:

mysql> SELECT SQL_NO_CACHE dept_no, Employees
    -> FROM v_emp_by_dept
    -> WHERE
    ->  dept_no = 'd002';
+---------+-----------+
| dept_no | Employees |
+---------+-----------+
| d002    |     17346 |
+---------+-----------+
1 row in set (0.18 sec)

All fine and okay. But, 0.18 seconds, even on my (admittedly overloaded) laptop? We gotta be able to decrease that. Let’s try another approach:

mysql> SELECT SQL_NO_CACHE dept_no, Employees
    -> FROM (
    ->  SELECT SQL_NO_CACHE dept_no, COUNT(emp_no) Employees
    ->  FROM
    ->   dept_emp
    ->  WHERE
    ->   dept_no='d002'
    ->  GROUP BY
    ->   dept_no
    -> ) t1;
+---------+-----------+
| dept_no | Employees |
+---------+-----------+
| d002    |     17346 |
+---------+-----------+
1 row in set (0.01 sec)

0.01 sec, much better (obviously). Basically, the difference here is that I push the WHERE clause up to before the group by, and minimize the number of rows that have to be grouped.

Let’s do another simple example, but this time on unnecessary joins that may be the result of copy/pasted code from some earlier employee. Let’s say you want to grab the employees first name, last name, and department number (perhaps for automatic processing later on). That previous employee apparently grabbed the query from something that also showed the name of the department:

mysql> SELECT
    ->  e.first_name, e.last_name, de.dept_no
    -> FROM
    ->  employees e INNER JOIN
    ->  dept_emp de ON (e.emp_no=de.emp_no)
    ->  LEFT JOIN departments d ON(de.dept_no=d.dept_no)
    -> LIMIT 2;
+------------+-----------+---------+
| first_name | last_name | dept_no |
+------------+-----------+---------+
| Georgi     | Facello   | d005    |
| Bezalel    | Simmel    | d007    |
+------------+-----------+---------+
2 rows in set (0.02 sec)

And the explain plan:

+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
| id | select_type | table | type   | possible_keys  | key     | key_len | ref                  | rows   | Extra       |
+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
|  1 | SIMPLE      | e     | ALL    | PRIMARY        | NULL    | NULL    | NULL                 | 300201 |             |
|  1 | SIMPLE      | de    | ref    | PRIMARY,emp_no | PRIMARY | 4       | employees.e.emp_no   |      1 | Using index |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY        | PRIMARY | 4       | employees.de.dept_no |      1 | Using index |
+----+-------------+-------+--------+----------------+---------+---------+----------------------+--------+-------------+
3 rows in set (0.00 sec)

Now, this isn’t the worst example, but still it does include a totally unnecessary table, departments, which we can get rid of and still get the needed number. Basically it’s a useless table in this query, and should be removed. It’s simply a result of someone not checking what she/he really needed for the query to work.

To wrap up this first part on refactoring, remember to push WHERE clauses as far up as you can to eliminate fetching unneeded rows and then later removing them. Also remember how indexes work and do not include useless tables in a join—identify what you want the query to accomplish, and how you can do that without touching more rows than needed. And to really top it off, use transactions and don’t commit after every insert/update unless you really need to. Grouping them together may very well increase performance.

In Part 2, we will cover example programs, loops/cursors, procedures, and how to optimize those without having to change the application behaviour. Stand by.

Initial day at OpenSQL Camp

November 15th, 2008

So, I actually started my trip early, the taxi picked me up from my house in Malta at 5:15am, and after a short stop in the lounge for some breakfast, I boarded KM100 bound for Heathrow. After a layover there, I eventually arrived at Dulles International, and there was no queue for US customs and border patrol!

Picked up the rental car and drove the 101 miles to Charlottesville, VA — in dense fog.

Met with Sheeri and talked for a bit on Thursday, before just falling asleep, due to time difference and the long travel.

So on Friday, we had a few hours to run errands and pick up last minute stuff that we had forgot back home and so on, before the start at 6pm. Met up with everyone, had some nice interesting conversations and towards the end of the night, 14 of us decided that it was time for dinner, so we went over to a local restaurant. Lots of interesting talk, mainly MySQL related, and lots of food, just like it should be. There were some pictures taken, but I’ve yet to see them online, but keep watching Planet MySQL and I’m sure you’ll see them.

It was a nice start to the weekend, so I’m hoping that today (Saturday) will exceed my expectations as well! I’ll post updates and reviews of the talks, in a daily digest.

Until then,
Nick.

DBD::mysql on OS X Quirks: Architectures, MySQL Binaries and the Filesystem

October 6th, 2008

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from make test showed, was that certain symbols was missing:

#     Tried to use 'DBD::mysql'.
#     Error:  Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefix

Fair enough, this is related to a 64-bit issue with MySQL—at least with my Perl version, which is now:

Summary of my perl5 (revision 5 version 10 subversion 0) configuration:
  Platform:
    osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2level

If you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from perl Makefile.PL:

  cflags        (mysql_config) = -I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL
  embedded      (mysql_config) =
  libs          (mysql_config) = -L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc
  mysql_config  (Users choice) = /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/bin/mysql_config

That looks all good and nice, the right paths, versions and everything. So I compiled it OK, but when running make test again:

#     Error:  Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Library not loaded: /usr/local/mysql/lib/libmysqlclient.16.dylib
#   Referenced from: /Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle
#   Reason: image not found at /usr/local/lib/perl5/5.10.0/darwin-thread-multi-64int-2level/DynaLoader.pm line 207.

Okay, that is also a quite easy problem to solve. It couldn’t load /usr/local/mysql/lib/libmysqlclient.16.dylib because it wasn’t there. But wait a minute, I don’t even have /usr/local/mysql, so why would it try to load something from there? I was tired, so I just symlinked /usr/local/mysql to /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86 and that solved it. The tests went fine and now DBD works like a charm. But I still couldn’t let go of the fact that it was looking somewhere I never told it to. So I checked the Makefile:

$ grep '/usr/local/' Makefile
LDDLFLAGS = -arch i386 -arch ppc -bundle -undefined dynamic_lookup -L/usr/local/lib
LDFLAGS = -arch i386 -arch ppc -L/usr/local/lib
INSTALLSITEBIN = /usr/local/bin
INSTALLVENDORBIN = /usr/local/bin
INSTALLSITEMAN1DIR = /usr/local/share/man/man1
INSTALLVENDORMAN1DIR = /usr/local/share/man/man1
INSTALLSITEMAN3DIR = /usr/local/share/man/man3
INSTALLVENDORMAN3DIR = /usr/local/share/man/man3
CCFLAGS = -arch i386 -arch ppc -g -pipe -fno-common -DPERL_DARWIN -no-cpp-precomp -fno-strict-aliasing -Wdeclaration-after-statement -I/usr/local/include

That looks okay. Perhaps mysql_config gave it the idea of going there?

$ ./mysql_config
Usage: ./mysql_config [OPTIONS]
Options:
        --cflags         [-I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL]
        --include        [-I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include]
        --libs           [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc]
        --libs_r         [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient_r -lz -lm     -lmygcc]
        --plugindir      [/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib/plugin]
        --socket         [/tmp/mysql.sock]
        --port           [0]
        --version        [5.1.28-rc]
        --libmysqld-libs [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqld -lz -lm       -lmygcc]

Nope, there is no reason whatsoever that it should look in /usr/local/mysql, so I am still not sure why it is. Looking through the files, I can find a couple possible reasons as to why this is happening:

  • mysql_config has pkglibdir='/usr/local/mysql/lib' set as default in the binary releases.
  • mysql itself is configured with --prefix=/usr/local/mysql for binary releases, which also shows in the actual library that we are trying to load:
    $ strings libmysqlclient.16.dylib | grep /usr/local/mysql
    /usr/local/mysql/etc
    /usr/local/mysql/share
    /usr/local/mysql
    

As for why it looks there, I don't know. I do know, however, that if you follow the common practice of symlinking /usr/local/mysql to your local tarball installation of mysql, this symptom would not exist. But, what would still exist is the issue of DBD trying to load from /usr/local/mysql even though nothing tells it to do so. Maintenance is also easier when symlinking, as you don't have to change paths when upgrading (just redo the symlink). Still, I fail to see why this would be required to install DBD::mysql.

DBD::mysql on OS X Quirks: Architectures, MySQL Binaries and the Filesystem

October 6th, 2008

Yesterday evening, a friend of mine had some issues with installing DBD::mysql, and asked if I had encountered the same issue. The problem, as the output from make test showed, was that certain symbols was missing:

#     Tried to use 'DBD::mysql'.
#     Error:  Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Symbol not found: _is_prefix

Fair enough, this is related to a 64-bit issue with MySQL—at least with my Perl version, which is now:

Summary of my perl5 (revision 5 version 10 subversion 0) configuration:
  Platform:
    osname=darwin, osvers=9.5.0, archname=darwin-thread-multi-64int-2level

If you try to link to a x86_64 version of MySQL, then you get the above mentioned error. So, I downloaded an x86 version of MySQL and tried again. The output from perl Makefile.PL:

  cflags        (mysql_config) = -I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL
  embedded      (mysql_config) =
  libs          (mysql_config) = -L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc
  mysql_config  (Users choice) = /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/bin/mysql_config

That looks all good and nice, the right paths, versions and everything. So I compiled it OK, but when running make test again:

#     Error:  Can't load '/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle' for module DBD::mysql: dlopen(/Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle, 2): Library not loaded: /usr/local/mysql/lib/libmysqlclient.16.dylib
#   Referenced from: /Users/westerlund/src/perl/DBD-mysql-4.008/blib/arch/auto/DBD/mysql/mysql.bundle
#   Reason: image not found at /usr/local/lib/perl5/5.10.0/darwin-thread-multi-64int-2level/DynaLoader.pm line 207.

Okay, that is also a quite easy problem to solve. It couldn’t load /usr/local/mysql/lib/libmysqlclient.16.dylib because it wasn’t there. But wait a minute, I don’t even have /usr/local/mysql, so why would it try to load something from there? I was tired, so I just symlinked /usr/local/mysql to /Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86 and that solved it. The tests went fine and now DBD works like a charm. But I still couldn’t let go of the fact that it was looking somewhere I never told it to. So I checked the Makefile:

$ grep '/usr/local/' Makefile
LDDLFLAGS = -arch i386 -arch ppc -bundle -undefined dynamic_lookup -L/usr/local/lib
LDFLAGS = -arch i386 -arch ppc -L/usr/local/lib
INSTALLSITEBIN = /usr/local/bin
INSTALLVENDORBIN = /usr/local/bin
INSTALLSITEMAN1DIR = /usr/local/share/man/man1
INSTALLVENDORMAN1DIR = /usr/local/share/man/man1
INSTALLSITEMAN3DIR = /usr/local/share/man/man3
INSTALLVENDORMAN3DIR = /usr/local/share/man/man3
CCFLAGS = -arch i386 -arch ppc -g -pipe -fno-common -DPERL_DARWIN -no-cpp-precomp -fno-strict-aliasing -Wdeclaration-after-statement -I/usr/local/include

That looks okay. Perhaps mysql_config gave it the idea of going there?

$ ./mysql_config
Usage: ./mysql_config [OPTIONS]
Options:
        --cflags         [-I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include  -g -Os -arch i386 -fno-common   -D_P1003_1B_VISIBLE -DSIGNAL_WITH_VIO_CLOSE -DSIGNALS_DONT_BREAK_READ -DIGNORE_SIGHUP_SIGQUIT  -DDONT_DECLARE_CXA_PURE_VIRTUAL]
        --include        [-I/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/include]
        --libs           [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient -lz -lm     -lmygcc]
        --libs_r         [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqlclient_r -lz -lm     -lmygcc]
        --plugindir      [/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib/plugin]
        --socket         [/tmp/mysql.sock]
        --port           [0]
        --version        [5.1.28-rc]
        --libmysqld-libs [-L/Users/westerlund/src/perl/mysql-5.1.28-rc-osx10.5-x86/lib -lmysqld -lz -lm       -lmygcc]

Nope, there is no reason whatsoever that it should look in /usr/local/mysql, so I am still not sure why it is. Looking through the files, I can find a couple possible reasons as to why this is happening:

  • mysql_config has pkglibdir='/usr/local/mysql/lib' set as default in the binary releases.
  • mysql itself is configured with --prefix=/usr/local/mysql for binary releases, which also shows in the actual library that we are trying to load:
    $ strings libmysqlclient.16.dylib | grep /usr/local/mysql
    /usr/local/mysql/etc
    /usr/local/mysql/share
    /usr/local/mysql
    

As for why it looks there, I don’t know. I do know, however, that if you follow the common practice of symlinking /usr/local/mysql to your local tarball installation of mysql, this symptom would not exist. But, what would still exist is the issue of DBD trying to load from /usr/local/mysql even though nothing tells it to do so. Maintenance is also easier when symlinking, as you don’t have to change paths when upgrading (just redo the symlink). Still, I fail to see why this would be required to install DBD::mysql.

Log Buffer #117: a Carnival of the Vanities for DBAs

October 3rd, 2008

Welcome to the 117th edition of Log Buffer, the weekly review of database blogs.

For those of you who don’t know me, my name is Nicklas Westerlund, and I’m a MySQL DBA with The Pythian Group. This is my first time writing Log Buffer, and I hope I’ll do it right.

Let’s start off with SQL Server, where Simon Sabin asks if you know what concurrency is and how to improve it. And on SatisticsIO, Jason Massie focuses on the SQL Server 2008 experience instead, which should provide more inside knowledge of the technology used.

Continuing on with SQL Server 2008, the engineering team is sending loads of engineers to the SQL PASS Conference, as the SQL Server Customer Advisory Team tells us in their post on what, in their opinion, just may be the best PASS Conference yet. And if you’re into meeting engineers, then perhaps you’d also like to know how that patching is done in SQL Server 2008, which PSS SQL informs us about.

The folks over at sqlserver-qa.net also give us an overview of the SQL Server Web Edition.

Let’s move over to Oracle, where there’s still a lot of buzz about Exadata, and let’s start with with the second part of the Exadata FAQ by Kevin Closson. In that post he also mentions his interview on the Exadata with Paul and Christo here at Pythian.

Continuing with the Exadata, the Oracle Insider Blog gives us the inside scoop of the Exadata announcement via a podcast interview with Rich Palmer of HP and Oracle’s Willie Hardie.

In other news, Jonathan Lewis talks about finding the root cause, in a different way.

Speaking of performance also brings me to the changes in Oracle 11g, where H.Tonguç Yılmaz talks about enhancements to ALTER TABLE … ADD COLUMN functionality.

And to round off the Oracle part, we should take a look at what Mark Rittman is saying about the Oracle BI Server query log file.

On the PostgreSQL side, depesz (Hubert Lubaczewski) talks about LC_COLLATE and LC_CTYPE in 8.4.

The Postgres OnLine Journal looks into how to delete functions, which they say can help if you need to streamline your SQL.

You can see the future of the Firebird DBMS, as captured and blogged about by Martijn Tonies, which was taken from the recent Firebird Conference 2008. Marco Cantù also offers his summary of the conference.

Now, let’s move on over to MySQL, where Jay Pipes has confirmed that he is leaving the MySQL Community team. Giuseppe Maxia announced that they are now seeking clone of Jay Pipes. But Jay is moving over to the Drizzle team instead.

As you may know, Drizzle is a more lightweight MySQL. Giuseppe looks at drizzling MySQL 6, with its performance improvements in subqueries. This seems very promising.

Over at the MySQL Performance Blog, Vadim takes a look at what is stored in the InnoDB buffer pool, and Ewen talks about why triggers are bad for replication.

As we are getting closer to April and the MySQL Conference & Expo, Jay reminds us to submit our papers.

Here at the Pythian Group Blog, Sheeri Cabral reports that the Community Contribution Agreement has changed to the standard Sun Agreement.

And as a closing item, does anyone remember the request (Google cache, as the page is offline) for donations to help Andrii Nikitin’s son Ivan? Well, Kaj Arno gives us some good news—Ivan is feeling better and better.

In closing, I’d like to thank you for your time in reading this, and I’ll pass the stick back over to David Edwards.

Have a good weekend everyone.

MMUG: Second Meeting Review and Slides

August 29th, 2008

The Malta MySQL User Group (MMUG) met for the second time this Thursday, and compared to last time, we had a much better venue: Ixaris Systems let us use their board room, so we had all the tools we needed to have a good meeting.

We managed to get a group picture before everyone has arrived, so I guess we can call the people in this picture “early birds”.

MMUG: Second Meeting

Once we all arrived, however, Sandro Gauci from EnableSecurity gave us a very interesting talk on SQL Injection security, and general security flaws from a developer point a view. You can find the slides here: sql-injection.pdf.

Here’s a picture of Mr. Gauci while presenting. (Sorry for the obvious problem with the over-white picture — seems like I forgot to turn down the flash, and this was the only non-blurry shot I got.)

MMUG: Second Meeting, Sandro Gauci

Following Mr. Gauci, I went on and delivered a presentation on general backup and recovery scenarios, and ended with a overview of the MySQL Backup functionality as of MySQL 6.0.5. I will go into more detail on this in another blog post, so stay tuned for that.

We had a goal of having presentations take 15 to 20 minutes each. I did 18 on this one, although in the end I went over-time by demonstrating some issues I encountered with a live demo. So although there are only a few slides, there was a lot to talk about at each one. Here they are: mmug-080828.pdf.

In good order after our two presentations, we had an enjoyable discussion around the table while enjoying our refreshments. Some topics that we discussed were master-master replication, maria, data warehousing on MySQL, MySQL Cluster, HA solutions, and general best practices.

Overall, I’m very pleased with this meeting, we had some valuable talks and questions raised during the presentations, and some very good company in good venue. I’d like to thank Pythian, Ixaris, and the MySQL Community team for their support in helping us.

What Data Type is Returned by a Mathematical Function?

August 27th, 2008

Or, “Missing information in the MySQL Manual”.

Just earlier today, I was using POW(), which I’ve grown quite fond of, simply because it makes life easier. I prefer using it like SELECT 512*POW(1024,2) to find out the number of bytes to put in a variable, for example.

First, let’s take a look at the POW function:

Name: 'POW'
Description:
Syntax:
POW(X,Y)

Returns the value of X raised to the power of Y.

Okay, so it gives us a value; but what about the data type? Let’s take 512*POW(1024,2) as an example.

5067 (blogs) > SELECT 512*POW(1024,2) AS example;
+-----------+
| example   |
+-----------+
| 536870912 |
+-----------+
1 row in set (0.00 sec)

What is that? Well, it sure does look like an INT at this point, doesn’t it?

5067 (blogs) > CREATE TABLE post1184_1 (a INT UNSIGNED);
Query OK, 0 rows affected (0.10 sec)

5067 (blogs) > INSERT INTO post1184_1 (a) VALUES (512*POW(1024,2));
Query OK, 1 row affected (0.03 sec)

Yup, fair enough, we could insert it into an INT. But what is it really? Considering that it surely can give us something else, let’s take a look:

5067 (blogs) > CREATE TABLE post1184_2 AS SELECT POW(1024,2) AS pow;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_2\G
*************************** 1. row ***************************
       Table: post1184_2
Create Table: CREATE TABLE `post1184_2` (
  `pow` double default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Oh, so all of a sudden, it’s a double. I do agree with this though, as the value should be a double, but shouldn’t this be documented somewhere?

Now, for my original purpose, I can do something like this to set my variable: SET GLOBAL <variable> = CAST(512*POW(1024,2) AS UNSIGNED); — which will work, or even SET GLOBAL <variable> = 512 * ABS(1024*1024);, or perhaps you’d prefer the more “standard” method: SET GLOBAL <variable> = 512 *1024*1024;.

Also, it seems the default message for a DOUBLE exceeding 1.7976931348623157E+308 has changed between versions 5.0.67 and 6.0.6:

5067 (blogs) > SET SESSION sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

5067 (blogs) > SELECT POW(18446744073709551615,16) AS example;
+---------+
| example |
+---------+
|     inf |
+---------+
1 row in set (0.01 sec)

606 (blogs) > SET SESSION sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

606 (blogs) > SELECT POW(18446744073709551615,16) AS example;
+---------+
| example |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

I’m not sure which one I prefer, really. inf is not really correct, as there can be a larger value (for example, to the power of 17 instead of 16, in this case) but is NULL right? NULL doesn’t sound right to me, I would prefer something like a SQL error here, something like “Error: Value out of bounds”.

Let’s get back to how to find out a data type returned by a function. Sure, we can take a look at the source. But, when I go to the documentation, I would really like to see what is the data type being returned.

Let’s take another example, the ABS() function — it should return the absolute value, but what’s the data type? Performing the same task as with POW() we get a few different results:

5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-3) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
       Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
  `abs` int(3) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-3.1) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
       Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
  `abs` decimal(3,1) NOT NULL default '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) > DROP TABLE IF EXISTS post1184_3; CREATE TABLE post1184_3 AS SELECT ABS(-3*-30000000000000000) AS abs;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

5067 (blogs) > SHOW CREATE TABLE post1184_3 \G
*************************** 1. row ***************************
       Table: post1184_3
Create Table: CREATE TABLE `post1184_3` (
  `abs` bigint(19) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

5067 (blogs) >

So, true to its word, ABS() really does return the absolute value. The documentation also states that this is safe for use with BIGINT, but there is no such note on POW().

Finally, I’d like to ask you if you have another, better method of finding out what data type is returned (no, looking at the source does not count).

MMUG: Community, Education, and Good Company in Malta

August 21st, 2008

So, we’re coming up on that time again. When I moved back to Malta in order to work for Pythian one of the things that I wanted to do was to involve myself more in the community. Currently, I’m doing this by trying to keep an active blog with tips and tricks, good standard knowledge, and just overall trying to enlighten people. I’m also doing this by organizing a MySQL User Group here in Malta.

We’re set to have our 2nd meeting this coming Thursday, the 28thof August, in Ta’ Xbiex where we have graciously been donated a board room and projector (and parking space, luckily) for use. I’m trying to get one of our members to give a session about security (not only on MySQL, but also on the OS level to secure the process), and I know there is work being done on a presentation on MySQL Cluster, which I personally find very interesting and can’t wait to see.

In this second meeting, we’ll again focus on general best practices and free discussions, in order to share our knowledge as much as we can. The last meeting was in Mellieha and it was a success — we had a good turnout and some very interesting discussions. Darren, one of our members, blogged about our meeting here and so did I in one of my previous posts.

If you’d like to learn more about MySQL, or if you’re a DBA (doesn’t matter which level) I encourage you to email us (and sign up to our mailing list) at malta (at) ug.mysql.org. Or subscribe here. You can also, as always, email me directly at: westerlund (at) pythian.com

I’m looking forward to seeing familiar, and new, faces this coming Thursday, in what promises to be a fun evening filled with education, sharing of knowledge, and good company.

If you are interested, here is the address and a map of the area:

Casa Roma,
Sir Augustus Bartolo Street
Ta’ Xbiex

MMUG Map

Here’s a description of what you’ll find on the map:

  • Red is Casa Roma – where we’ll be meeting
  • Blue is Irrera – restaurant
  • Green is Black Perl – restaurant
  • Yellow is WhiteHall Mansions

mysqlbinlog Tips and Tricks

August 18th, 2008

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

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 | wc -l
Enter password:
      94

If we know that we have correctly replicated all data until position 932, we can make less output for our plaintext file*:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 --start-position=932 | wc -l
Enter password:
      57

And even better if we know between which two locations we need to look, but one problem is that we need to know both the start position and stop position, we can’t just insert a random value.

First looking at the binlog and show what it would look like:

# at 1132
#080815 19:25:25 server id 101  end_log_pos 1217     Query    thread_id=1    exec_time=8    error_code=0
SET TIMESTAMP=1218821125/*!*/;
flush privileges
/*!*/;
# at 1217
#080815 19:34:41 server id 101  end_log_pos 1307     Query    thread_id=8    exec_time=1    error_code=0
SET TIMESTAMP=1218821681/*!*/;
create database blogs

then trying to start at position 1200: (just like inserting a random value instead of an actual position):

$ mysqlbinlog mysql-bin.000001 --start-position=1200
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#080815 19:25:30 server id 102  end_log_pos 107     Start: binlog v 4, server v 6.0.5-alpha-log created 080815 19:25:30 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
BINLOG '
CrylSA9mAAAAZwAAAGsAAAABAAQANi4wLjUtYWxwaGEtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAKvKVIEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1818850921, event_type: 115
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

So the error we see here is:

ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1818850921, event_type: 115

We can pinpoint specific locations this way*:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.000001 -h 127.0.0.1 -P 3306 --start-position=932 --stop-position=1132 | wc -l
Enter password:
      32

But what if your developer says, “I ran a statement at exactly 19:34:55 and I can’t remember what the statement was, can you help me find out?” Sure, you can look at which binlog contains that one, and dump the whole thing, then search for 19:34:55 (granted, the clocks have to be synchronized, it won’t be pretty if the developer’s clock is three minutes off). But, there’s also another way:

$ mysqlbinlog --read-from-remote-server -uwesterlund -p mysql-bin.[0-9]* -h 127.0.0.1 -P 3306 --start-datetime="2008-08-15 19:34:55" --stop-datetime="2008-08-15 19:34:56"
Enter password: 

#080815 19:34:55 server id 101  end_log_pos 1426 	Query	thread_id=8	exec_time=0	error_code=0
use blogs/*!*/;
SET TIMESTAMP=1218821695/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table t1 (a int auto_increment primary key)
/*!*/;
# at 1426
#080815 19:34:55 server id 101  end_log_pos 1454 	Intvar
SET INSERT_ID=1/*!*/;
# at 1454
#080815 19:34:55 server id 101  end_log_pos 1567 	Query	thread_id=8	exec_time=0	error_code=0
SET TIMESTAMP=1218821695/*!*/;
insert into t1 values (NULL), (NULL), (NULL)
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

So this introduced us to --start-datetime and --stop-datetime , both of which accept DATETIME or TIMESTAMP entries, and which together set the start/stop of what kind of information we’re interested in.

Also, if you have a problem on your master, but your slaves are fine, and you need to restore the master, you can use --skip-write-binlog to avoid the point-in-time recovery to replicate to the slaves, which would cause more problems.

Another point that I touched on above is the option to use certain regexps, i.e., instead of listing mysqlbinlog.01, .02, .03 and so on, you can use mysqlbinlog.[0-9]* to match all of them.

When it comes to --start-position, --start-datetime, --stop-position and --stop-datetime, they can be used together. So if you know that at 10:00:01 someone dropped your main database, and your last backup has a start position of 1454, you can do this . . .

mysqlbinlog binlog.02 --start-position=1454 --stop-datetime="2008-08-15 10:00:00"

. . . to recover from that point to a second before your crash. (Unless you used the earlier method to find out the exact position of the drop, in which case: recover to it, then skip one entry on your slave, and continue from there.)

Now for the last of these gems. What do you do when you have 500mb of binlog that check for corruption? Do you dump it to a plaintext and read through it looking for errors? No. What you want to do is run mysqlbinlog binlog.000003 binlog.000004 > /dev/null. That will return an error if there’s any problem with the binlog, for example, “Event too big”.

* For those of you unfamiliar with Unix tools, wc -l returns a count of lines from its input, a file or stream; head shows you only the top few lines of its input. back