Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 10

Displaying posts with tag: mysqlbinlog (reset)

MySQL 5.7 Replication: mysqlbinlog tool idempotent mode while applying row events
Employee +3 Vote Up -0Vote Down

Introduction

MySQL 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.

Rationale

Prior 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






  [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.

My contribution to MySQL 5.6
+0 Vote Up -0Vote Down
[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

  [Read more...]
My contribution to MySQL 5.6
+6 Vote Up -0Vote Down

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

  [Read more...]
Part 2 – Simple lessons in improving scalability
+3 Vote Up -0Vote Down

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
  [Read more...]
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...]
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...]
Replicating from MySQL to *
+3 Vote Up -0Vote Down
Recently I needed to replicate between MySQL and another database technology. You might say, why on earth would you want to do something like that, but believe me there are reasons and definitely not (to go away from MySQL to some other DB technology like Oracle or SQL server). Unsurprisingly there are quite a few different tools to do it from any platform towards MySQL but very few which do it the other way round, just to name a couple: Golden Gate and DSCallards.
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…)

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...]
Showing entries 1 to 10 of 10

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.