Skip to content

Instantly share code, notes, and snippets.

@lefred
Last active June 27, 2023 09:37
Show Gist options
  • Save lefred/fd223701c76742ddff8ba8077d5d4ac9 to your computer and use it in GitHub Desktop.
Save lefred/fd223701c76742ddff8ba8077d5d4ac9 to your computer and use it in GitHub Desktop.
binlog streaming
[Unit]
Description=Streaming MySQL binary logs to local filesystem using %i
After=network.target
[Service]
Type=simple
User=root
Restart=on-failure
ExecStart=/root/binlog_streaming/bin/binlog_to_local.sh /root/binlog_streaming/conf/%i.conf
[Install]
WantedBy=multi-user.target
#!/bin/bash
if [ $# -eq 0 ]
then
echo "A configuration file is required"
exit 1
fi
CONF_FILE=$1
if [ ! -f "$CONF_FILE" ]
then
echo "Configuration file [$CONF_FILE] doesn't exist"
exit 2
fi
# default config
MYSQLCONFIGED=/bin/mysql_config_editor
MYSQLBINLOG=/bin/mysqlbinlog
RESPAWN=10 # time to wait before reconnecting after failure
source $CONF_FILE
# check for some files
for i in $MYSQLCONFIGED $MYSQLBINLOG
do
which $i >/dev/null 2>&1
if [[ $? -ne 0 ]]
then
echo "$i is missing, please install"
exit 6
fi
done
if [ ! -d "$TARGET_DIR" ]
then
echo "[$TARGET_DIR] doesn't exist... creating it"
mkdir $TARGET_DIR
if [[ $? -ne 0 ]]
then
echo "Error creating [$TARGET_DIR], aborting !"
exit 4
fi
fi
# check is credentials are available
$MYSQLCONFIGED print --login-path=$MYSQL_LOGIN_PATH | grep $MYSQL_LOGIN_PATH >/dev/null
if [[ $? -ne 0 ]]
then
echo "please use $MYSQLCONFIGED to add the credentials for login-path=$MYSQL_LOGIN_PATH"
exit 7
fi
cd $TARGET_DIR
echo "Streaming binary logs to $TARGET_DIR"
while true
do
MYSQL_HOST=$(mysql --login-path=$MYSQL_LOGIN_PATH -BN -e "select @@hostname")
echo "MySQL Host Name is ${MYSQL_HOST}"
LASTFILE=`ls -1 $TARGET_DIR/${MYSQL_HOST}* 2>/dev/null|grep -v orig|tail -n 1|xargs -n1 basename 2>/dev/null`
TIMESTAMP=`date +%s`
if [ -z "$LASTFILE" ]
then
if [ $# -eq 2 ]
then
LASTFILE_SERVER=$2
else
LASTFILE_SERVER=$(mysql --login-path=$MYSQL_LOGIN_PATH -BN -e "select SUBSTRING_INDEX(FILE_NAME,'/', -1) binlog from performance_schema.file_instances WHERE EVENT_NAME='wait/io/file/sql/binlog' ORDER BY 1 limit 1;")
fi
else
LASTFILE_SERVER=${LASTFILE#"${MYSQL_HOST}-"}
FILESIZE=$(stat -c%s "$LASTFILE")
if [ $FILESIZE -gt 0 ]; then
echo "Backing up last binlog"
mv $LASTFILE $LASTFILE.orig$TIMESTAMP
fi
touch $LASTFILE
fi
echo "Starting live binlog streaming from $LASTFILE_SERVER"
$MYSQLBINLOG --login-path=$MYSQL_LOGIN_PATH --raw --result-file="${MYSQL_HOST}-" --read-from-remote-server --stop-never $LASTFILE_SERVER
echo "mysqlbinlog exited with $? trying to reconnect in $RESPAWN seconds."
sleep $RESPAWN
done
MYSQL_LOGIN_PATH=localhost
TARGET_DIR=/root/binlog_streaming/data
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment