How to use the MySQL binary log to stream queries to another server

Introduction

If you want to use the MySQL binary log to stream queries to another MySQL instance, you can use the mysqlbinlog utility but it has the following drawbacks:

  • you need to specify the filename that currently contains the binary log
  • this file can rotate at any moment
  • older versions of mysqlbinlog (5.5) do not have the –stop-never option that allows you to « tail » the file

The following script is a simple workaround which will allow you to regularly dump the MySQL binary log to another server. Note that this does not really stream the queries, it merely simulates streaming, depending on the frequency of execution. In our example, the source database is a slave to another master database.

binlog streaming

When is this useful ? When for example, you can not modify the configuration of the master and slave servers to set up a slave of the existing slave, or when you do not have the possibility to modify the configuration of the destination database (eg. when it is a 3-node Galera cluster and you don’t want to make one of the nodes a receiving slave and thus break the high availability).

The scripts

/usr/local/bin/initbinlog.sh

The script that first copies the entire srcdb to dstdb:

#!/bin/bash

# This script initializes the destination DB with a dump from the source slave DB
# This should be executed only once at the beginning

# Stop the slave
/usr/bin/mysql -uuser -ppassword srcdb -e 'stop slave\G'

# Dump the database
/usr/bin/mysqldump -uuser -ppassword srcdb > /var/backups/srcdb.sql

# Restart the slave
# This automatically flushes the logs
/usr/bin/mysql -uuser -ppassword srcdb -e 'start slave\G'

# Import the dump into the destination database
# If there are stored procedures, the user must have SUPER privileges or the global log_bin_trust_function_creators variable must be set to 1
/usr/bin/mysql -h dstdb -uuser -ppassword dstdb < /var/backups/srcdb.sql

/usr/local/bin/streambinlog.sh

Then we need a script that periodically flushes the logs and streams the rotated binary log file to the destination DB:

#!/bin/bash

# Find out what the current relay binary log is
RELAYLOG=`/usr/bin/mysql -uuser -ppassword srcdb -e 'show slave status\G' | grep Relay_Log_File | awk '{ print $2; }'`

# Flush the logs
/usr/bin/mysql -uuser -ppassword srcdb -e 'flush logs\G'

# Pipe the flushed logs into the destination DB
/usr/bin/mysqlbinlog /var/lib/mysql/$RELAYLOG | /usr/bin/mysql -h dstdb -uuser -ppassword dstdb

/etc/cron.d/streambinlog

Finally, we need a cron entry that periodically executes /usr/local/bin/streambinlog.sh (in this case every 30 seconds):

## cron job for streambinlog#
MAILTO=root
# Launch every 30 seconds
* * * * * root /usr/local/bin/streambinlog.sh
* * * * * root sleep 30; /usr/local/bin/streambinlog.sh
0 réponses

Répondre

Se joindre à la discussion ?
Vous êtes libre de contribuer !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *