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 23

Displaying posts with tag: binlog (reset)

Trawling the binlog with FlexCDC and new FlexCDC plugins for MySQL
+0 Vote Up -0Vote Down

Swanhart-Tools includes FlexCDC, a change data capture tool for MySQL. FlexCDC follows a server’s binary log and usually writes “changelogs” that track the changes to tables in the database. I say usually because the latest version of Swanhart-Tools (only in github for now) supports FlexCDC plugins, which allow you to send the updates to a remote data source, or to any other place of your liking.  You can find out more about FlexCDC basics in a previous blog post.

Please note that FlexCDC still needs to have source and destination

  [Read more...]
Trawling the binlog with FlexCDC and new FlexCDC plugins for MySQL
+1 Vote Up -0Vote Down

Swanhart-Tools includes FlexCDC, a change data capture tool for MySQL. FlexCDC follows a server’s binary log and usually writes “changelogs” that track the changes to tables in the database. I say usually because the latest version of Swanhart-Tools (only in github for now) supports FlexCDC plugins, which allow you to send the updates to a remote data source, or to any other place of your liking.  You can find out more about FlexCDC basics in a previous blog post.

Please note that FlexCDC still needs to have source and destination instances defined in

  [Read more...]
MySQL Slave Scaling and more
+3 Vote Up -0Vote Down

Jean-François talks about binlog servers. Take a look here: http://blog.booking.com/mysql_slave_scaling_and_more.html

MySQL 5.7: mysqlbinlog now supports SSL
Employee +2 Vote Up -0Vote Down
Starting in version 5.7.3 MySQL added SSL support to mysqlbinlog client program. This feature allows system administrators to perform remote binlog queries (using --read-from-remote-server option) over secure connections.
So, the behavior of mysqlbinlog client program using SSL options is now the same as other MySQL client tools, with same SSL options and same SSL defaults. See the References section if you want more information about MySQL SSL options.


The remote administration of MySQL servers is a very common task as many MySQL servers are deployed in remote hosting facilities or in remotely located data centers.
There are many problems with remote administration of servers. With respect to security, the major concerns are:
  • If the traffic between

  [Read more...]
Why Does MySQL Replication Delays?
+0 Vote Up -0Vote Down
Send to Kindle

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen:

See the bellow image, it represents asynchronous replication on MySQL, I highly recommend

  [Read more...]
How Does MySQL Replication Works?
+0 Vote Up -0Vote Down
Send to Kindle

See the bellow image, it represents how asynchronous replication works on MySQL

Bearing in mind the number on the image, let’s see how the process works:

  • Item 1 in the image represents the clients executing queries on master, note that the master is capable to handle multiple simultaneous connections (it can be configurable by max_connections variable). 
  • Master process these

  [Read more...]
Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format = STATEMENT | MIXED
+0 Vote Up -0Vote Down
Send to Kindle

Got a packet bigger than ‘slave_max_allowed_packet’ bytes and binlog_format=STATEMENT|MIXED

Since version 5.1.64 MySQL introduces a new variable named slave_max_allowed_packet, which was introduced to allow large updates using row-based replication do not cause replication to fail when exceeded max_allowed_packet.

The problem is if you have you replication using binlog_format=STATEMENT or binlog_format=MIXED it ignores this option and use as

  [Read more...]
MySQL 5.6 Replication with GTID – Global Transaction ID
+0 Vote Up -0Vote Down
Send to Kindle

Hi guys, Early February Oracle released the new version of MySQL named 5.6, one of the enhancements is the GTID (Global Transaction ID)

GTID is an unique identifier which will be added at each transaction, and will be very useful on the slave. remember before we needed to set MASTER_LOG_FILE and MASTER_LOG_POS, now we don’t need them anymore.

Let’s see some new variables which we need to add to our cnf file:
gtid-mode : It will enable GTID, in order to this function work, we need to turn on log-bin and log-slave-updates
enforce-gtid-consistency : It will guarantee that only allowed command will be executed ( more information

  [Read more...]
MySQL 5.6 Replication Performance
Employee_Team +10 Vote Up -1Vote Down

With data volumes and user populations growing, its no wonder that database performance is a hot topic in developer and DBA circles.  

Its also no surprise that continued performance improvements were one of the top design goals of the new MySQL 5.6 release which was declared GA on February 5th (note: GA means “Generally Available”, not “Gypsy Approved” @mysqlborat)

And the performance gains haven’t disappointed:

- Dimitri Kravtchuk’s Sysbench tests showed MySQL delivering up to 4x higher performance than the previous 5.5 release.

- Mikael Ronstrom’s testing (http://www.mysql.com/why-mysql/benchmarks/) showed up to 4x better scalability as thread counts rose to

  [Read more...]
MySQL 5.6 Replication – New Early Access Features
Employee_Team +2 Vote Up -0Vote Down
0 0 1 1037 5914 Homework 49 13 6938 14.0 Normal 0 false false false EN-GB JA X-NONE

0 0 1 204 1168 Homework 9 2 1370 14.0 Normal 0 false false false EN-GB JA X-NONE

  [Read more...]
On Hot Backups and Restore using XtraBackup
+3 Vote Up -0Vote Down
Backups are an integral and very important part of any system. Backups allow you to recover your data and be up and running again, in the advent of problems such as system crashes, hardware failures or users deleting data by mistake. I had been evaluating backup solution for a while but to be honest I really wasn't satisfied with the solutions available until I came across XtraBackup and I am loving it since. In this post I intend on showing how to do backups and restores using XtraBackup.
Could not find first log file name in binary log index file
+2 Vote Up -4Vote Down

Scenario Master – Master replication

MasterA is a client facing server
MasterB is a warm standby server (read only)

MasterB restarted abruptly and when instances were braught back up MasterA (it’s slave) was showing the following error:

MasterA has the following error in show slave status:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file’


Slave: stop slave;

Master: flush logs
Master: show master status; — take note of the master log file and master log position

Slave: start slave;

How analysing your binlogs can be quite informative
+1 Vote Up -0Vote Down

If you have used MySQL for some time you know that mysqld can write binlogs. This is usually used for backup purposes and JITR or for replication purposes so a slave can collect the changes made on the master and apply them locally.

Most of the time apart from configuring how long you keep these binlogs they are pretty much ignored.

Recently I came across an issue.  I have a slave server which is NOT configured read only and which has an additional database used to collect statistics from the replicated database and provided aggregation and business information. The typical sales per country, per product, per day, week, month, year, per whatever etc.  This is the usual datawarehouse type functionality.  It’s done on a slave and not the

  [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_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...]
Overloading BINARY
+0 Vote Up -0Vote Down

“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

  [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...]
mysqlbinlog Tips and Tricks
+0 Vote Up -0Vote Down

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
  [Read more...]
Data inconsistency issues – binlog to rescue
+0 Vote Up -0Vote Down

One of our peer group developers contacted me today stating that they were inserting a value of 10 in one of the columns in their tables. But after the insert, when they query the table they were getting the value as 0.

Logged into the box to check what was happening. Luckily binlog was turned on that mysql instance. Ran the mysqlbinlog utility on the current binlog and greped for the particular primary key value.

It listed an insert statement and an update statement. There was the culprit, the value of 10 was getting inserted but soon the application was following it up with an update to value 0. Yeah it is always not the database

  [Read more...]
When SHOW SLAVE STATUS and the error log Disagree
+0 Vote Up -0Vote Down

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
  [Read more...]
Faster way to find the next valid position in a relay log?
+0 Vote Up -0Vote Down

I'm working on an implementation of a mysql slave precache tool as described by Paul Tuckfield at last year's User conference.

I can easily tell where the SQL Thread is currently is reading from in the local relay log, but it's a harder problem to solve when I want to scan ahead in that log some number of binlog entries and find a valid position. I can simply add to the position some number of bytes, but then I have to stop and loop through calls to 'mysqlbinlog' until I get a position that doesn't throw an error.

This turns out to be a pretty expensive operation, and I'm having a problem where by the time I find a good position and can start my precacher, the slave is already past me!

Does anyone have any clever ways to find good binary log positions besides this (this is in

  [Read more...]
Stopping the slave exactly at a specified binlog position
Employee +0 Vote Up -0Vote Down
Catching up on some articles on the Planet MySQL feed, I just read the post by Dathan on how to promote a slave to be master by using MASTER_POS_WAIT(). The MASTER_POS_WAIT() is an excellent function that allows you to wait until the slave reaches a point at or after the given binlog position. Observe that after the statement issuing a MASTER_POS_WAIT() returns, the slave threads are still running, so this means that even if a STOP SLAVE is issued immediately after the statement with MASTER_POS_WAIT(), it is bound to move a little more before actually stopping. For Dathan's situation, this is not necessary, but wouldn't it be great if you could stop a slave at  [Read more...]
Showing entries 1 to 23

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.