So I realized I had not made a post about this after this situation that recently came up.

Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.

So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.

To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.

…

MariaDB 10.0.17 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.0.17/

This is the eighth GA release of MariaDB 10.0, and 18th overall release of MariaDB 10.0.

For the most part, there are not a whole lot of new changes to report for this release, but there is a new version of the Audit Plugin and many other engines have been updated as well.

Here are the main items of note:

1. The new version of the Audit Plugin is 1.2 is included with the following new features:
   • In the audit log passwords are now masked, i.e. the password characters are replaced with asterisks.
   • It’s now possible to filter logging to include only DDL (CREATE, ALTER, etc.) or DML (INSERT, UPDATE, etc.) statements.
   • For more information …

As a MySQL DBA/consultant, it is part of my job to decode the MySQL binary logs – and there are a number of reasons for doing that. In this post, I’ll explain how you can get the important information about your write workload using MySQL row-based binary logs and a simple awk script.

First, it is important to understand that row-based binary logs contain the actual changes done by a query. For example, if I run a delete query against a table, the binary log will contain the rows that were deleted. MySQL provides the mysqlbinlog utility to decode the events stored in MySQL binary logs. You can read more about mysqlbinlog in detail in the reference manual here.

The following example illustrates how mysqlbinlog displays row events that specify data modifications. These correspond to events with the WRITE_ROWS_EVENT, UPDATE_ROWS_EVENT, …

Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| …

IntroductionMySQL replication slave features  a powerful capability of ignoring conflicts like duplicate key error, key not found errors etc. while applying row events.  This is exceptionally useful while doing row based replication(RBR) from the master when the slave already contains some data which may conflict with the data coming from the master. In MySQL 5.7 we extend this capability while applying row events from mysql-binlog files using mysqlbinlog tool.  This enhancement will prevent such errors from aborting mysql client in case of conflicts like the ones mentioned above.

RationalePrior to MySQL 5.7 we have been using the mysqlbinlog tool as follows.

shell$> mysqlbinlog master.000001|mysql -uroot -hexample.com -ps3cret  

This allows us to pipe the output of mysqlbinlog to mysql, and works as long as the row events from the mysqlbinlog do not conflict with …

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 …

When using the replication slave stream, or mysql command line client and mysqlbinlog output from a binary/relay log, all statements are executed in a single thread as quickly as possible.

I am seeking a tool to simulate the replay of the binary/relay log for a benchmark at a pace that is more representative to original statements. For a simple example, if the Binary Log has 3 transactions in the first second, 2 transactions in the second second, and 5 transactions in the third second, I am wanting to simulate the replay to take roughly 3 seconds, not as fast as possible (which would be sub-second). The tool should try to wait the remainder of a second before processing SQL statements in the incoming stream.

Does anybody know of a tool that currently provides this type of functionality? Any input appreciated before I create my own.

[caption id="attachment_209" align="alignright" width="240" caption="Photo by Stéfan under a CC by NC SA 2.0 license"][/caption]

If you have been reading Planet MySQL over April you will have seen many blog posts on the new features in the MySQL 5.6 (currently a development release).  I developed several patches that are in 5.6 including the 'Slave_last_heartbeat' status variable to show the time of the last replication heartbeat received.  One of the cool new features I developed which I am most proud of is the option to remotely backup …

If you have been reading Planet MySQL over April you will have seen many blog posts on the new features in the MySQL 5.6 (currently a development release). I developed several patches that are in 5.6 including the ‘Slave_last_heartbeat’ status variableto show the time of the last replication heartbeat received. One of the cool new features I developed which I am most proud of is the option to remotely backup your binary logs without a MySQL slave:

Remote Binlog Back-up

Enhances …

Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.

Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.

Let’s look at the following example output of a production system:

mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set …