You are here

MySQL Tech-Feed (en)

Replication channel fail-over with Galera Cluster for MySQL

Shinguz - Thu, 2014-06-19 07:05
Taxonomy upgrade extras: channelgaleraclusterfail-overreplicationmasterslave

Sometimes it could be desirable to replicate from a Galera Cluster to a single MySQL slave or to an other Galera Cluster. Reasons for this measure could be:

  • An unstable network between two Galera Cluster locations.
  • A separation of a reporting slave and the Galera Cluster so that heavy reports on the slave do not affect the Galera Cluster performance.
  • Mixing different sources in a slave or a Galera Cluster (fan-in replication).

This article is based on earlier research work (see MySQL Cluster - Cluster circular replication with 2 replication channels) and uses the old MySQL replication style (without MySQL GTID).

  • Enable the binary logs on 2 nodes of a Galera Cluster (we call them channel masters) with the log_bin variable.
  • Set log_slave_updates = 1 on ALL Galera nodes.
  • It is recommended to have small binary logs and relay logs in such a situation to reduce overhead of scanning the files (max_binlog_size = 100M).


Let us assume that for some reason the actual channel master of channel 1 breaks. As a consequence the slave of channel 1 does not receive any replication events any more. But we have to keep the replication stream up and running. So we have to switch the replication channel to channel master 2.

Switching replication channel

First for security reasons we should stop the slave of replication channel 1 first:

mysql> STOP SLAVE;

Then we have to find the actual relay log on the slave:

mysql> pager grep Relay_Log_File mysql> SHOW SLAVE STATUS\G mysql> nopager Relay_Log_File: slave-relay-bin.000019

Next we have to find the last applied transaction on the slave:

mysql> SHOW RELAYLOG EVENTS IN 'slave-relay-bin.000019'; | slave-relay-bin.000019 | 3386717 | Query | 5201 | 53745015 | BEGIN | | slave-relay-bin.000019 | 3386794 | Table_map | 5201 | 53745067 | table_id: 72 (test.test) | | slave-relay-bin.000019 | 3386846 | Write_rows | 5201 | 53745142 | table_id: 72 flags: STMT_END_F | | slave-relay-bin.000019 | 3386921 | Xid | 5201 | 53745173 | COMMIT /* xid=1457451 */ | +------------------------+---------+-------------+-----------+-------------+--------------------------------+

This is transaction 1457451 which is the same on all Galera nodes.

On the new channel master of channel 2 we have to find now the matching binary log. This can be done best by matching times between the relay log and the binary log of master of channel 2.

On slave:

shell> ll *relay-bin* -rw-rw---- 1 mysql mysql 336 Mai 22 20:32 slave-relay-bin.000018 -rw-rw---- 1 mysql mysql 3387029 Mai 22 20:37 slave-relay-bin.000019

On master of channel 2:

shell> ll *bin-log* -rw-rw---- 1 mysql mysql 2518737 Mai 22 19:57 bin-log.000072 -rw-rw---- 1 mysql mysql 143 Mai 22 19:57 bin-log.000073 -rw-rw---- 1 mysql mysql 165 Mai 22 20:01 bin-log.000074 -rw-rw---- 1 mysql mysql 62953648 Mai 22 20:40 bin-log.000075

It looks like binary log 75 of master 2 matches to relay log of our slave.

Now we have to find the same transaction on the master of channel 2:

mysql> pager grep -B 6 1457451 mysql> SHOW BINLOG EVENTS IN 'bin-log.000075'; mysql> nopager | bin-log.000075 | 53744832 | Write_rows | 5201 | 53744907 | table_id: 72 flags: STMT_END_F | | bin-log.000075 | 53744907 | Xid | 5201 | 53744938 | COMMIT /* xid=1457450 */ | | bin-log.000075 | 53744938 | Query | 5201 | 53745015 | BEGIN | | bin-log.000075 | 53745015 | Table_map | 5201 | 53745067 | table_id: 72 (test.test) | | bin-log.000075 | 53745067 | Write_rows | 5201 | 53745142 | table_id: 72 flags: STMT_END_F | | bin-log.000075 | 53745142 | Xid | 5201 | 53745173 | COMMIT /* xid=1457451 */ | +----------------+----------+-------------+-----------+-------------+---------------------------------------+

We successfully found the transaction and want the position of the next transaction 53745173 where we should continue replicating.

As a last step we have to set the slave to the master of replication channel 2:

mysql> CHANGE MASTER TO master_host='master2', master_port=3306, master_log_file='bin-log.000075', master_log_pos=53745173; mysql> START SLAVE;

After a while the slave has caught up and is ready for the next fail-over back.


We found during our experiments that an IST of a channel master does not lead to a gap or loss of events in the replication stream. So restarting a channel master does not require a channel fail-over as long as an IST can be used for resyncing the channel master with the Galera Cluster.

The increase of wsrep_cluster_conf_id is NOT an indication that a channel fail-over is required.

A SST resets the binary logs so after the SST a slave will not replicate any more. So using this method should be safe to use. If you find any situation where you experience troubles with channel fail-over please let us know.

Backup Manager for MySQL, MariaDB and Percona Server (mysql_bman)

Shinguz - Tue, 2014-05-06 17:28
Taxonomy upgrade extras: mysqlBackupRestoreRecoverymysql_bmanpitrAbout

The MySQL Backup Manager (mysql_bman) is a wrapper script for standard MySQL backup tools. The Problem with MySQL backup tools is, that they have many options and thus are overcomplicated and errors are easy made.

mysql_bman has the intention to make backups for MySQL easier and technically correct. This means it should per default not allow non-consistent backups or complain if some functions or parameters are used in the wrong way to guarantee proper backups.

In addition it has added some nice features which are missing in standard MySQL backup tools or which are only known from Enterprise backup solutions.

Where can mysql_bman help you

The intention of mysql_bman is to assist you in bigger MySQL set-ups where you have to follow some backup policies and where you need a serious backup concept.

Backup types

To achieve this we have defined different backup types:

TypeDescriptionfullfull logical backup (mysqldump) of all schemasbinlogbinary-log backupconfigconfiguration file backup (my.cnf)structurestructure backupcleanupclean-up of backup pieces older than n daysschemabackup of one or more schemas

A backup type is specified with the option --type=<backup_type>.

Backup modes

A backup can either be logical or physical. A logical backup is typically what you do with mysqldump. A physical backup is typically a physical file copy without looking into the data. That is what for example xtrabackup does.

The backup mode is specified with the option --mode=<backup_mode>. The following backup modes are available:

ModeDescriptionlogicaldo a logical backup (mysqldump).physicaldo a physical backup (mysqlbackup/innobackup/xtrabackup)Backup policies

Further we have introduced different backup policies. Policies are there to distinguish how different backups should be treated.

The following backup policies exist:

PolicyDescriptiondailydirectory to store daily backupsweeklydirectory to store weekly backupsmonthlydirectory to store monthly backupsquarterlydirectory to store quarterly backupsyearlydirectory to store yearly backups

For example you could plan to do a daily MySQL backup with binary logs with a retention policy of 7 days. But once a week you want to do a weekly backup consisting of a full backup, a configuration backup and a structure dump. But this weekly backup you want to keep for 6 months. And because of legal reasons you want to do a yearly backup with a retention policy of 10 years.

A backup policy is specified with the --policy=<backup_policy> option. This leads us to the retention time:


The retention time which should be applied to a specific backup policy you can specify with the option --retention=<period_in_days>. The retention option means that a backup is not deleted before this amount of days when you run a clean-up job with mysql_bman.

Let us do an example:

shell> mysql_bman --type=cleanup --policy=daily --retention=30

This means that all backups in the daily policy should be deleted when they are older than 30 days.


With the --target option you specify the connect string to the database to backup. This database can be located either local (all backup types can be used) or remote (only client/server backup types can be used).

A target looks as follows: user/password@host:port (similar to URI specification) whereas you can omit password and port.

Backup location, archiving, compressing and clean-up

The --backupdir option is to control location of the backup files. The policy folders are automatically created under this --backupdir location.
If you have a second layer of backup stores (e.g. tapes or slow backup drives or deduplicated drives or NFS drives) you can use the --archive option to copy your backup files to this second layer storage which is specified with the --archivedir option. For restore performance reasons it is recommended to always keep one or two generations of backups on you fast local drive. If you want to remove the backuped files from the --backupdir destination after the archive job use the --cleanup option.
If you want to omit to compress backups, either to safe time or because your location uses deduplicated drives you can use the --no-compress option.

Per schema backup

Especially for hosting companies a full database backup is typically not the right backup strategy because a restore of one specific customer (= schema) is very complicated. For this case we have the --per-schema option. mysql_bman will do a backup of the whole database schema by schema. Keep in mind: This breaks consistency among schemas!

Sometimes you want to do a schema backup only for some specific schemas for this you can use the --schema option. This option allows you to specify schemas to backup or not to backup. --schema=+a,+b means backup schema a and b. --schema=-a,-b means backup all schemas except a and b.
The second variant is less error prone because you do not forget to backup a new database.

Instance name

MySQL does not know the concept of naming an instance (mysqld). But for bigger environments it could be useful to uniquely name each instance. For this purpose we have introduced the option --instance-name=<give_it_a_name>. This instance name should be unique within your whole company. But we do not enforce it atm. The instance name is used to name backup files and later to identify the backup history of an instance in our backup catalog and to allow us to track the files for restore.

mysql_bman configuration file

Specifying everything on the command line is cumbersome. Thus mysql_bman considers a configuration file specified with the --config=<config_file> option.
A mysql_bman configuration file looks for example as follows:

policy = daily target = root/secret@ type = schema schema = -mysql policy = daily archive = on archivedir = /mnt/tape per-schema = on no-compress = on
Simulate what happens

For the Sissies among us (as for example me) we have the --simulate option. This option simulates nearly all steps as far as possible without executing really anything. This option is either for testing some features or for debugging purposes.


If you want to track your backup history you can specify with the --log option where your mysql_bman log file should be located.

More help

A little more help you can get with the following command:

shell> mysql_bman --help

Do a full (logical = default) backup and store it in the daily policy folder:

shell> mysql_bman --target=root/secret@ --type=full --policy=daily

Do a full physical backup and store it in the weekly policy folder (I think this feature is not implemented yet...):

shell> mysql_bman --target=root/secret@ --type=full --mode=physical --policy=weekly

Do a binary-log backup omitting the password in the target and store it in the daily policy folder:

shell> mysql_bman --target=bman@ --type=binlog --policy=daily

Do a MySQL configuration backup and store it in the weekly policy folder:

shell> mysql_bman --target=root/secret@ --type=config --policy=weekly

Do a structure backup and store it in the monthly policy folder and name the file with the instance name:

shell> mysql_bman --target=root/secret@ --type=structure --policy=monthly --instance-name=prod-db

Do a weekly structure backup and archive it to an other backup location:

shell> mysql_bman --target=root/secret@ --type=structure --policy=weekly --archive --archivedir=/mnt/tape

Do a schema backup omitting the mysql schema:

shell> mysql_bman --target=root/secret@ --type=schema --schema=-mysql --policy=daily --archive --archivedir=/mnt/tape

Do a schema backup only of foodmart and world and write it to their own files. Omit compressing these backups because they are located for example on deduplicated drives:

shell> mysql_bman --target=root/secret@ --type=schema --schema=+foodmart,+world --per-schema --policy=daily --no-compress
Where to get mysql_bman

mysql_bman is a part of the MyEnv utilities.

Subscribe to FromDual aggregator - MySQL Tech-Feed (en)