Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 19

Displaying posts with tag: binary log (reset)

Multi-master data conflicts - Part 2: dealing with conflicts
+2 Vote Up -0Vote Down

In the first part of this article we examined the types of conflicts and their causes. In this part, we will analyse some of the methods available to deal with conflicts.

Pessimistic locking (or: conflicts won't happen)

Applicability: synchronous clusters with 2pc

We've covered this topic in the previous article, but it's worth repeating. If you use a synchronous cluster, you don't have conflicts. For example, MySQL Cluster ensures consistent data with updates coming from different nodes. However, MySQL Cluster is not a replacement for a MySQL server, and it has severe limitations.

Optimistic locking

Applicability: synchronous clusters without 2pc (Galera)

Conflicting transactions proceed on different

  [Read more...]
Binary Log Group Commit in MySQL 5.6
Employee +9 Vote Up -0Vote Down
With the release of MySQL 5.6 binary log group commit is included, which is a feature focused on improving performance of a server when the binary log is enabled. In short, binary log group commit improve performance by grouping several writes to the binary log instead of writing them one by one, but let me digress a little on how transactions are logged to the binary log before going into the details. Before going into details about the problem and the implementation, let look at what you do to turn it on.


Well... we actually have a few options to tweak it, but nothing required to turn it on. It even works for existing engines since we did not have to extend the handlerton interface to implement the binary log group commit. However, InnoDB has some optimizations to take advantage of the binary log group commit implementation.

  [Read more...]
Binary Log Replayer
+2 Vote Up -0Vote Down

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.

Temporary files, binlog_cache_size, and row-based binary logging
+20 Vote Up -0Vote Down

Even when the output of EXPLAIN doesn’t show “using temporary”, a temporary file may still be used in certain cases.

That’s not to say the query needs the temporary file to actually resolve the query (like what you’d see from the need for a derived table). But rather, the temporary file I’m speaking of is due to binary logging.

In particular, you can see this easily if using InnoDB, (most commonly) row-based binary logging, and you issue a large transaction, say a large UPDATE (large meaning something larger than the size of

  [Read more...]
Looking for a hack - Passing comment-like info through the binary log
+6 Vote Up -0Vote Down
I am facing an interesting problem. I need to mark somehow a statement in such a way that the comment is preserved through the binary log.
I don't have control on how the statement is generated or using which client software. For the sake of example, let's say that I need to mark a CREATE PROCEDURE statement in such a way that, if I extract the query from the binary log and apply it to another server, the information is still available.


Normally, I would use a comment. The first thing I would think is
CREATE PROCEDURE p1(i int) select "hello" /* This is my text */
But most client libraries will strip it.
There was

  [Read more...]
Filtering binary logs with MySQL Sandbox and replication
+10 Vote Up -0Vote Down
A few days ago, a friend of mine asked me if I knew of a way of filtering a bunch of binary logs, to extract only statements related to a single table. The task was about filtering a few hundred binary log files.

It's a tricky problem. Even with my experience with regular expressions, I knew that using a script to extract statements related to a single table was going to be a nasty business.
However, I know of an entity that can filter by table name efficiently, and that's the MySQL replication system. So I suggested using replication to a sandbox with a replicate-wild-do-table statement to get the job done.
My friend was skeptical and did not want to go that way. I was busy writing an article for an Italian magazine and did not follow up immediately. But today, with the article safely in the editor's hands, I did a quick test, and guess what? It works!

  [Read more...]
MySQL Limitations Part 2: The Binary Log
+2 Vote Up -0Vote Down

This is the second in a series on what's seriously limiting MySQL in certain circumstances (links: part 1). In the first part, I wrote about single-threaded replication. Upstream from the replicas is the primary, which enables replication by writing a so-called "binary log" of events that modify data in the server. The binary log is a real limitation in MySQL.

The binary log is necessary not only for replication, but for point-in-time recovery, too. Given a backup and the corresponding binary log position, you can replay the binary log and roll forward the state of your server to a desired point in time.

But enabling the binary log reduces MySQL's performance dramatically. It is not the logging itself that's the problem -- writing

  [Read more...]
Do you use MySQL replication? Do you use “FLUSH LOGS”? If yes you might want to read this.
+3 Vote Up -0Vote Down

Scenario: Master-Master replication
Description: Master A is the active db server whilst Master B is a read only swappable db server hence both are creating binary logs. During backup I run “FLUSH LOGS” in order to have a simpler point in time recovery procedure if that case arises.
Problem: Flush logs is mean mean command :) …. it rotates not only my binary logs but my error log too (since I user error-log=blahblahblah in my my.cnf). Well given I flush logs every night my error log is cycled through every night, but unlike binary logs which have an incrimental number attached to the fine, error logs only have a `-log` attached to the filename and a second “FLUSH LOG” would just clear all error logs permanently. That is really not fun believe me!

So what is the solution?

  [Read more...]
Going to the O'Reilly MySQL Conference & Expo
Employee +0 Vote Up -0Vote Down

As I've been doing the last couple of years, I will be going to the O'Reilly MySQL Conference & Expo. In addition to the tutorial and the replication sessions that I will be holding together with Lars, I will be holding a session about the binary log together with Chuck from the Backup team which the Replication team normally works very close with.

This year, O'Reilly also have a Friend of the Speaker discount of 25% that you can use when you register using the code mys10fsp.

The sessions that we are going to hold are listed below. Note that I am using Microformats[Read more...]
Applying binary logs without adding to the binary log
+3 Vote Up -4Vote Down

Applying binary logs to a MySQL instance is not particularly difficult, using the mysqlbinlog command line utility:

$> mysqlbinlog mysql-bin.000003 > 03.sql
$> mysql < 03.sql

Turning off binary logging for a session is not difficult, from the MySQL commandline, if you authenticate as a user with the SUPER privilege:

mysql> SET SESSION sql_log_bin=0;

However, sometimes you want to apply binary logs to a MySQL instance, without having those changes applied to the binary logs themselves. One option is to restart the server binary logging disabled, and after the load is finished, restart the server with binary logging re-enabled. This is not always possible nor desirable, so there’s a better way, that works in at least versions 4.1 and up:

The mysqlbinlog utility

  [Read more...]
Never let your binlog directory fill up
+6 Vote Up -0Vote Down

Recently with a client while running a number of disaster recovery tests I came across a nasty situation which was not part of the original plan and provided a far worse disaster situation then expected.

I should preface this condition with some of the environment conditions.

  • MySQL 5.0 Enterprise 5.0.54
  • RHEL 5 64bit
  • Master and 2 Slaves
  • MySQL Data and MySQL Binary Logs/MySQL Error Logs are on separate disk partitions

While running stress tests under high load, we tested the filling of partition containing the logs. This partition included the binary log and MySQL error log.

The observed output was.

  • An error message was written to the MySQL error log. See below.
  • Application throughput dropped, but did not stop.
  • Binary logs stopped occuring.
  • MySQL proactively
  [Read more...]
MySQL 5.4 performance with logging
+8 Vote Up -0Vote Down

About a month ago, I published the results of MySQL 5.x performance with logging. The results covered several versions, from 5.0.45 to 5.1.33. Among the conclusions of the post was the consideration that MySQL 5.0.x is faster than MySQL 5.1 in read only operations. I hinted that better results may come for MySQL 5.1. When I wrote that post I had, in fact, an ace up my sleeve, because I had already benchmarked the performance of MySQL 5.4, using the same criteria shown in my previous post. The results, as you can

  [Read more...]
MySQL 5.x performance with logging
+0 Vote Up -0Vote Down
There has been much talking about MySQL performance related to logging. Since MySQL 5.1.21, when Bug #30414 was reported (Slowdown (related to logging) in 5.1.21 vs. 5.1.20) I have been monitoring the performance of the server, both on 5.0 and 5.1.
Recently, I got a very powerful server, which makes these measurements meaningful.
Thus, I measured the performance of the server, using all publicly available sources, because I want this benchmark to be repeatable by everyone.
I will first describe the method used for the benchmarks, and then I report the results.

The server

The server is a Linux Red Hat Enterprise 5.2, running on a 8core processor, with 32 GB RAM and 1.5 TB storage.

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

  [Read more...]
mysql_install_db, mysqld --bootstrap, binary log, cPanel
+0 Vote Up -0Vote Down
Warning... what follows is a murky mess.
It's filed as MySQL bug#43398 (verified!) but it's triggered by cPanel doing evil.

Start a mysql server, as normal
Then run mysql_install_db (as root, like you would when you first install MySQL)
See a new binlog file get created, with ownership/group root!
Of course you generally wouldn't run mysql_install_db while a server is running, but there's nothing to prevent you (or something else) from doing so!
--bootstrap just shouldn't initialise binlog, then there wouldn't be a issue.

cPanel runs mysql_install_db in its automatic upgrade scripts (dangerous already, automatically upgrading MySQL Server on a system!), it's run every night on cPanel systems even if no upgrade is done, and it behaves exactly as described above. It then chowns the binlog

  [Read more...]
mysqlbinlog --server-id before MySQL 5.1? awk to the rescue!
+0 Vote Up -0Vote Down

Recently I had an interesting issue crop up. Due to an unfortunate migration incident in which involved master/master replication and not checking to see if replication was caught up, we ended up with an infinite replication loop of a number of SQL statements. awk helped immensely in the aftermath cleanup.

The basics of the replication infinite loop were (more…)

Binary Logs and saving data
Employee +0 Vote Up -0Vote Down
Back when I started working with computers, you actually carved each character with a machine into oddly shaped pieces of paper. My FORTRAN class required up to two dozen of these carefully manicured pieces of paper for each program. The folks in the RPG class had litterally stacks of data one or two feet deep and my mind boggled at the amount of data these poor folks had to carry around with them. My best guess now is that they may have had up to two thousand records of up to 80 characters. My lowly FORTAN class saw the RPG programming class as demi-gods as they trudged along with trays of cards under their arms.

And then one day it rained. Data cards

  [Read more...]
Decoding binlog entries with row-based replication
+0 Vote Up -0Vote Down

If you have tried using row based replication, you may have noticed two things: (1) it fixes many inconsistencies of statement-based replication, but (2) the binlog is unfit for humans. Inspecting it after a problem occurs won't provide any useful information.
The instructions look like line noise, and when you don't see the result you were expecting you wonder if that's the case.

For example, after executing this code:
create table t1 (id int, c char(10), d date);
insert into t1 values (1, 'abc', '2008-01-01');
insert into t1 values (2, 'def', '2008-08-19');
insert into t1 values (3, 'ghi', current_date());

  [Read more...]
The missing pieces in the protobuf binary log
Employee +0 Vote Up -0Vote Down
Protobuf comes with a minor problem: it does not have support for handling "type tagged structures", that is, something reminiscent of objects in OOP lingo, so if one is going to have a heterogeneous sequences of messages, you have to roll it yourself. For that reason, I added a transport frame for the messages in the binary log that wraps each with some extra information. In addition to allowing the binary log to be a sequence of messages, it also adds some integrity-checking data and simplifies some administrative tasks.

Transport frame with message Length Type Tag Message Checksum The format of each message in the sequences is given in the table in the margin. where the length is a specially encoded length that we will go through

  [Read more...]
Using protobuf for designing and implementing replication in Drizzle
Employee +0 Vote Up -0Vote Down
So, following the lead of Brian, I spent a few hours of the weekend to create a very simple replication scheme for Drizzle using protobuf for specifying the binary log events.

Since we are developing a replication for a cloud, there are a few things we have to consider:

  • Servers are unreliable. We shall not trust server, but we shall expect them to crash at the worst possible time (Murphy is a very good friend of mine, you know. He must be, since he visits me very often.) This means that we need to have support to allow statements to be sent to the slaves before the transaction is complete, which means that we need to support

  [Read more...]
Showing entries 1 to 19

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.