By Raveendra Reddy on Sep 15, 2014
With MySQL Enterprise Backup(MEB) 3.9.0 we had introduced full instance backup feature for cloning the MySQL server. Now with MEB 3.11.0 we have enhanced the feature by copying all the master-slave setup files like MySQL server binary logs(will be referred as 'binlogs'), binary log index files, relay logs of slave, relay log index files, master info of slave, slave info files. As part of full instance backup, copying of binlog files is default behavior MEB-3.11.0 onwards. DBA should be aware of the fact that current full instance backup is bigger than the backups with old MEB's.
As every event on MySQL production database goes as a entry to binlog files in particular format, binlog files could be huge. Backing of huge binlog and/or relaylog files should not impact the performance of MySQL server. Hence, all the binlog files, except the current binlog used by server, are copied just like the innodb .ibd files without locking tables. Binlog files currently being used by server and added after backup started, are copied during read the lock which is acquired by MEB for copying meta files and redo logs.
DBA gets the following benefits:
1) Direct cloning of master and slave possible from backup
Earlier DBA had to copy binlog files manually in order to setup master/slave. Now, MEB 3.11 by default copies all the files including the global variables needed for setting up master-slave. Hence DBA can clone master or slave with the same state of backed-up server.
Now, DBA need not to use --slave-info option to copy the binlog info for setting up the slave after restore. By copying master and slave info files, DBA can fetch the information of up to which master binlog position, slave SQL thread has executed and IO threads has read etc. With this information along with relay logs, binlogs, DBA can easily setup slave from backed-up slave content
2) Backup of binary logs helps in Point In Time Recovery (PITR)
First let us understand what is PITR by above example. Consider DBA has taken full backup on Sunday(assume date as 14-09-2014), and incremental backups on Tuesday(date as 16-09-2014), Thursday(date as 18-09-2014). It means DBA can only restore database up to full backup or incremental backups in other words database can be restored either up to Sunday or up to Tuesday, Thursday, but not in between let say Monday or Wednesday. Because backup is just a snapshot of data when it was taken. Hence backup taken once can't be restored in between without change log. That's where binlog helps in restoring to a certain point of time, which is called Point-In-Time-Recovery(PITR). As binlogs captures all the events of a server with timestamps. Therefore to restore in between DBA need to have base data i.e. full backup and incremental binlogs.
Let's look at our example, below are the points to recover server to Wednesday 12 PM(assume date as 17-09-2014)
a) Restore the backup up to latest backup before PITR time(Here, restore Tuesday's incremental)
b) Get the SQL statements using below mysqlbinlog command up to PITR from the immediate next incremental binlogs(Here get SQL statements up to Wednesday from Thursday's incremental binlogs of binlog.000005, binlog.000006, binlog.000007)
mysqlbinlog --start-datetime=<latest backup time before PITR time> \
--stop-datetime=<PITR point> \
<incremental binlogs from immediate next backup> > <SQL file>
For our above example, the command is
mysqlbinlog --start-datetime="2014-09-16 12:00:00" \
--stop-datetime="2014-09-17 12:00:00" \
binlog.000005 binlog.000006 binlog.000007 > mysql_PITR_restore.sql
Read Point-in-Time (Incremental) Recovery Using the Binary Log for more details about PITR using Binary logs.
c) Execute the SQL statements obtained on the restored server, server is restored to PITR point
3) Backing up relay-logs from slave server helps avoiding unnecessary pull of logs from master once it is restored
Let us understand this by an example
Slave has 1 relay log with master binlog positions from 1 to 100
SQL thread at slave reads from relaylog and apply events on slave. Now assume SQL thread currently executed statements 1 to 20 and 21 to 100 are yet to be executed.
If DBA takes backup without copying relay log, when he/she restores the backup as slave, it asks master from the binlog position 21. So restored slave need to pull the logs of binlog position 21 to 100 from master. More network I/O needed as usually slave is on different machine.
As MEB takes backup of relay log, slave can avoid pulling the logs for binlog positions 21 to 100. Now restored slave asks master from binlog positions 101 onwards. This way slave don't pull logs from master which are present in slave backup, there by reducing network I/O which is costly than disk I/O.
Unlike binary logs, relaylogs are mostly deleted automatically once applied by SQL thread, as a result few relay logs exist at any point of time. So all the relay logs are copied for all the backup types full, incremental, partial without major impact on backup size and time.
4) Copied binary logs remains consistent with the backup data
Earlier DBA had to copy binlog files manually in order to setup master/slave. Data files are copied by MEB and binlogs are copied by DBA at two different times, so there is a possibility of binlog files not consistent with the backed-up data.
Lets consider following example:
1. MEB takes backup of the server without binlogs at 1 PM
2. DBA has copied binlogs from the server at 1:30 PM
From 1 PM to 1:30, lets say 100 events logged in binlogs
Now to use these binlog files, DBA has to either execute 100 events on server or have to remove 100 events from binlog files.
Consider another example:
1. DBA has copied binlogs from the server at 1:30 PM
2. MEB takes backup of the server without binlogs at 2 PM
From 1:30 PM to 2 PM, lets say 100 events went into backup data
Now DBA has to copy the missing binlog files again from the running server.
With MEB 3.11.0 onwards, binlogs and the data are copied at the same time, so they are consistent with each other.
Options to avoid binlogs/relay logs:
If DBA is not concerned about backing up binlog files then he/she can use --skip-binlog and --skip-relaylog to skip relay log files in backup. It is advisable to use these options if he/she don't plan to clone server or want PITR.
For Master, to skip only binlogs:
./mysqlbackup --skip-binlog --backup-dir=back_dir --socket=server_sock backup
For Slave, to skip relay-logs
./mysqlbackup --skip-relaylog --backup_dir=back_dir --socket=server_sock backup
For Slave which is also a master, to skip both binlogs and relay logs
./mysqlbackup --skip-binlog --skip-relaylog backup_dir=back_dir --socket=server_sock
Options for offline backup:
MEB also supports offline backup. In order to copy binlog and/or relaylog, MEB searches for default values of log-bin-index(default: host_name-bin.index), relay-log-index(default: host_name-relay-bin.index), relaylog-info-file(default: relay-log.info), master-info-file(default: master.info) at default location that is in server's 'datadir'. And if MEB finds those files then it successfully backs up those files. In case those files are configured with different values, DBA need to provide --log-bin-index = PATH, --relay-log-index = PATH, --relaylog-info-file = PATH, --master-info-file=PATH options to MEB in order to copy them.
To enrich the full instance backups that MySQL Enterprise Backup has been performing since release 3.9.0, all the replication setup files are included as part of all the backups in 3.11.0. With these files as well as all the global variables, plugin details, MEB now takes the responsibility of giving all the details to DBA for cloning any server. Read MEB 3.11.0 documentation for more details and many other great features.