/0 Commentaires/dans /par /Étiquettes : ,

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 slaveG'

# 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 slaveG'

# 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 statusG' | grep Relay_Log_File | awk '{ print $2; }'`

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

# 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

Laisser un commentaire

Participez-vous à la discussion?
N'hésitez pas à contribuer!

Laisser un commentaire

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

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.