MEB Backup & Restore in MySQL

MySQL Enterprise Backup provides enterprise-grade backup and recovery for MySQL. It delivers hot, online, non-blocking backups on multiple platforms including Linux, Windows, Mac & Solaris.

MySQL supports the following backup types:

Logical backup
Logical backup outputs a database structure in a .sql file by generating the CREATE or INSERT statements. Later, this file can be restored with the help of the mysqldump utility. This type backs up only data without indexes, thus, has a small size. However, its recovery option is slower compared with its alternatives, because it should execute all statements one by one.

Physical backup
Physical backup copies database files in the same format, in which they are stored on the disk. It is faster than the logical type but can be restored only to the MySQL server from the same database engine.

Consistent backup
Consistent backup copies the files at the exact moment of time – only after the server stops running or is locked.

Cold backup 
Cold backup blocks access to data during the backup and does not allow making any changes to data. It is simple, fast, and does not impact performance.

Hot backup 
Hot backup copies files while the database is running. Users can read and manipulate data.

Whether data should be fully or partially copied, backups can be classified into:

• Full backup copies all the data from the database. It can be logical or physical. The full backup can be restored on another server.
• Differential backup copies all the changes that were made since the latest full backup. The differential backup can be restored only after the full backup was restored.

• Incremental backup copies all the data changes made since the previous backup (either full or differential).

Back up a MySQL database using the mysqldump command

mysqldump is a command-line utility used to generate a MySQL logical database backup. It creates a single .sql file that contains a set of SQL statements. This file can be used to create tables, objects, and/or insert data that were copied from the database. 

With the utility, you can dump tables, one or several databases, or the entire server with all databases and tables along with their objects or migrate them to another server.

In addition, the mysqldump utility can output the dump to the CSV or XML file formats. It should be noted that mysqldump cannot dump a database or data to separate .sql files.

mysqldump -u [user name] –p [password] -h [host name] [options] [database_name] [tablename] > [dumpfilename.sql]


-u (or --user=[username]) is a username to connect to a MySQL server.

-p is a password for the username you use to connect to the server.

-h ( --host=[hostname] ) is the name of the server you want to connect to dump data from.

options are additional parameters to configure the backup.

database_name is the name of the database you want to back up.

tablename is the table name that you want to back up.

< or > refers to parameters indicating the process of database backup (>) or restore (<).

dumpfilename.sql is a path to a dump file containing the database backup.

Perform replication recovery using Mysql Enterprise Backup (MEB)

Mysql Enterprise Backup (MEB) performs much faster in case of large databases. Here is comparison in backup and restore times for 73GB database:

DB SIZE CHECK
-----------------
 SELECT table_schema "DB Name",
         ROUND(SUM(data_length + index_length) / 1024 / 1024/1024, 1) "DB Size in GB"
 FROM information_schema.tables
 GROUP BY table_schema; 

Node will be referred here as GOOD server from where backup will be taken. Another node to which backup will be load will be referred from here as BAD server.

Login to GOOD server and Stop replication: 

mysql> stop slave;

Create temporary backup dir:

# mkdir /root/backup

Create backup using MEB:

# /opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-image=/opt/app/asrblg/backup/075v.img --backup-dir=/root/backup --socket=/var/lib/mysql/mysql.sock --user=root --password=asrblg@123  backup-to-image

NOTE:

Backup-image is set to /opt/app/asrblg which is NFS share.

Choosing NFS for backup will cause backup to be slower, however since NFS is shared between 2 DB nodes, no transfer of backup image will be needed for restore procedure.

Login to BAD server and Stop DB 

# systemctl stop myslqd

Rename DB data directory

# cd /mysql-data
# mv mysql mysql.orig

Create new DB directory and set ownership to mysql user and group:

# mkdir mysql
# chown mysql:mysql mysql

Create temporary backup dir:

# mkdir /root/backup

Perform DB Restore using MEB command:

/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf -uroot --backup-image=/opt/app/asrblg/backup/075v.img --backup-dir=/root/backup/ --datadir=/mysql-data/mysql/ copy-back-and-apply-log

Copy old DB UUID to restored dir:

# cp mysql.orig/auto.cnf mysql

Change permissions on datadir:

# chown -R mysql:mysql /mysql-data/mysql
# find /mysql-data/mysql -type d -exec chmod  g+rx {}\; 

Edit the my.cnf file of the new slave and put skip-slave-start and event_scheduler=off under the [mysqld] section.

Start mysql server:

# systemctl start mysqld

Connect to BAD server using mysql client and execute:

mysql> reset master;
mysql> set sql_log_bin=0;

When a server using the GTID feature is backed up, mysqlbackup produces a file named backup_gtid_executed.sql, which can be found in the restored data directory of the new slave server. The file contains a SQL statement that sets the GTID_PURGED configuration option on the slave.

Edit backup_gtid_executed.sql and append info to “CHANGE MASTER” line. For example:

CHANGE MASTER TO MASTER_HOST='192.168.0.250', MASTER_USER='muser', MASTER_PASSWORD='mpass', MASTER_PORT=18675, MASTER_AUTO_POSITION = 1;

Execute file from mysql client:

mysql> source /path/to/backup_gtid_executed.sql

Set the statuses of any events that were copied from the master to SLAVESIDE_DISABLED. For example:

mysql> UPDATE mysql.event SET status = 'SLAVESIDE_DISABLED';

Flush the logs and reset slave:

mysql> flush logs;

mysql> reset slave;

mysql> start slave;

4.	Login to GOOD server perform below steps:

mysql> stop slave;

mysql> reset slave;

mysql> start slave;

From GOOD and BAD server check replication status:

mysql> show slave status\G

Hope it worked for you !! 🙂