Mysql – How to stream and record MySQL binlog remotely

MySQLmysql-replication

For data redundancy goals, I want to record the binlog of a few hundred mysql servers in a central location. To run a full replicator for each stream would require lots of resources (multiple servers) because the execution of the statements take a lot of cpu. My assumption is that just recording the binlog would take significant fewer resources.

How to do it? I found http://search.cpan.org/~alfie/MySQL-Replication-0.0.2/bin/MySQLReplicationClient.pl but that seems to use non-native replication (it requires an extra database) and seems to be in alpha state.

Perhaps another solution is to install a full mysql server as a replication slave, but somehow disable the execution of replicated statements and just log them.

Thanks!

Best Answer

Thanks to NickW!!

This is exactly what I need:

http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog-backup.html

--stop-never: Stay connected to the server after reaching the end of the last log file and continue to read new events.

I'm running 5.5 but I'll backport the 5.6 mysqlbinlog util.

Also of interest might be Percona Xtrabackup with xbstream continous backup feature: http://www.percona.com/doc/percona-xtrabackup/glossary.html#term-xbstream

Related Solutions

Mysql – Full MySQL BAckup strategy

You could actually use replication for this. Two options comes to mind.

Use MySQL "style" Time delayed replication (i.e. insert a fourth machine and let it do a rolling recovery).
You could also add a fourth machine as hot standby, take it out daily and then either back it up normally or snapshot it (fx. using ZSF on Solaris or LVM on Linux). Finally put it back in replication and let it catch up with the main servers.

Mysql – Sporadic crash of master-slave MySQL replication process

Double check that your character sets on the databases and connections are what you expect them to be - your replication choking on a statement with a non-ASCII character is immediately suspicious.

Look at the master's binary log to see how the statement it logged is different from the statement that the replica is trying to execute - that will offer clues to the problem.

Sadly, you should also make sure that the versions of the server for both the master and slave are the same. I recently encountered a weird instance where we had bad SQL trying to update a non-NULL numeric column to NULL. In the version that the master was running, MySQL silently converted it to 0, but in the version that the replica was running, it was an error.

Also, instead of doing CHANGE MASTER to fix the replica, you should be doing

STOP SLAVE;
[correct and execute whatever statement is the problem]
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

http://dev.mysql.com/doc/refman/5.1/en/set-global-sql-slave-skip-counter.html

Best Answer

Related Solutions

Mysql – Full MySQL BAckup strategy

Mysql – Sporadic crash of master-slave MySQL replication process

Related Topic