{"id":6309,"date":"2016-10-06T08:30:32","date_gmt":"2016-10-06T00:30:32","guid":{"rendered":"http:\/\/rmohan.com\/?p=6309"},"modified":"2016-10-06T08:30:32","modified_gmt":"2016-10-06T00:30:32","slug":"full-backup-using-mysql-enterprise-backup","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6309","title":{"rendered":"Full Backup using MySQL Enterprise Backup"},"content":{"rendered":"<header>\n<h2><a title=\"Full Backup using MySQL Enterprise Backup\" href=\"https:\/\/www.krenger.ch\/blog\/full-backup-using-mysql-enterprise-backup\/\" rel=\"bookmark\">Full Backup using MySQL Enterprise Backup<\/a><\/h2>\n<div class=\"blog-post-meta\"><time datetime=\"2014-01-27 07:00\">\u00a0<\/time><\/div>\n<\/header>\n<div>\n<p>As you might know, I am primarily an Oracle guy. This means that for all my backup needs, I am using Oracle Recovery Manager (RMAN). I recently had the task to implement <strong>MySQL Enterprise Backup<\/strong> for a MySQL database (mysql-advanced-5.6.15-linux-glibc2.5-x86_64). My most important resource for this task was the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/\">MySQL Enterprise Backup User\u2019s Guide<\/a>.<span id=\"more-3316\"><\/span><\/p>\n<h3>Install the software<\/h3>\n<p>Before we can configure backups and the like, you\u2019ll need to install the MySQL Enterprise Backup software. Do so by following <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/installing.html\">this guide<\/a>:<\/p>\n<pre><code>$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz \r\nmeb-3.9.0-linux2.6-x86-64bit\/\r\nmeb-3.9.0-linux2.6-x86-64bit\/bin\/\r\nmeb-3.9.0-linux2.6-x86-64bit\/bin\/mysqlbackup\r\nmeb-3.9.0-linux2.6-x86-64bit\/README.txt\r\nmeb-3.9.0-linux2.6-x86-64bit\/LICENSE.mysql\r\nmeb-3.9.0-linux2.6-x86-64bit\/manual.html\r\nmeb-3.9.0-linux2.6-x86-64bit\/mysql-html.css<\/code><\/pre>\n<p>I then placed the <code>mysqlbackup<\/code> binary in my MySQL \u201cbin\u201d directory (typically <code>\/usr\/local\/mysql\/bin<\/code> if you installed MySQL as described in the <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/5.6\/en\/binary-installation.html\">documentation<\/a>):<\/p>\n<pre><code>$ cp meb-3.9.0-linux2.6-x86-64bit\/bin\/mysqlbackup \/usr\/local\/mysql\/bin\/\r\n$ which mysqlbackup \r\n\/usr\/local\/mysql\/bin\/mysqlbackup<\/code><\/pre>\n<p>Now we\u2019ve installed the software, we can go on and prepare our database for backup.<\/p>\n<h3>Gather information, create backup user<\/h3>\n<p>Use <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/backup-prep-gather.html\">this guide<\/a> to review your configuration and figure out where you want to place your backup. In this example, I needed the following information:<\/p>\n<ul>\n<li>MySQL port (often \u201c<em>3306<\/em>\u201c)<\/li>\n<li>Backup user and password (\u201c<em>backup\/mysupersecret<\/em>\u201d in this example)<\/li>\n<li>Location for backup data (\u201c<em>\/var\/backups\/mysql\/backups<\/em>\u201d in this example)<\/li>\n<\/ul>\n<p>Since I did not yet have a backup user for the database, I had to create one. Log into the database as <em>root<\/em> and create the backup user (named <em>backup<\/em> in my case) and <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/mysqlbackup.privileges.html\">grant the necessary privileges<\/a>:<\/p>\n<pre><code>$ mysql -u root -p\r\nmysql&gt; CREATE USER 'backup'@'localhost' IDENTIFIED BY 'mysupersecret';\r\nmysql&gt; GRANT RELOAD ON *.* TO 'backup'@'localhost';\r\nmysql&gt; GRANT CREATE, INSERT, DROP, UPDATE ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';\r\nmysql&gt; GRANT CREATE, INSERT, DROP, UPDATE ON mysql.backup_progress TO 'backup'@'localhost';\r\nmysql&gt; GRANT CREATE, INSERT, SELECT, DROP, UPDATE ON mysql.backup_history TO 'backup'@'localhost';\r\nmysql&gt; GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';\r\nmysql&gt; GRANT SUPER ON *.* TO 'backup'@'localhost';\r\nmysql&gt; GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';\r\nmysql&gt; FLUSH PRIVILEGES;<\/code><\/pre>\n<p>With these steps complete, we can now create a shell script to automate our backups.<\/p>\n<h3>Shell script for full backup<\/h3>\n<p>Based on your backup concept (you have one, right?), you might want to schedule the following shell script to run multiple times per day, daily or weekly. The most important command in the script is the following:<\/p>\n<pre><code>\/usr\/local\/mysql\/bin\/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log<\/code><\/pre>\n<p>This command calls the <code>mysqlbackup<\/code> binary with the following arguments (also see <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/backup-prep-gather.html\">here<\/a>):<\/p>\n<table>\n<tbody>\n<tr>\n<th>Argument<\/th>\n<th>Description<\/th>\n<\/tr>\n<tr>\n<td><code>--port<\/code><\/td>\n<td>Port used to connect to the database instance during backup operations.<\/td>\n<\/tr>\n<tr>\n<td><code>--protocol<\/code><\/td>\n<td>Protocol used to connect to the database.<\/td>\n<\/tr>\n<tr>\n<td><code>--user<\/code>, <code>--password<\/code><\/td>\n<td>ID and password of privileged MySQL user.<\/td>\n<\/tr>\n<tr>\n<td><code>--with-timestamp<\/code><\/td>\n<td>Creates a subdirectory underneath the backup directory, with a name formed from the timestamp of the backup operation. Useful to maintain a single backup directory containing many backup snapshots.<\/td>\n<\/tr>\n<tr>\n<td><code>--backup-dir<\/code><\/td>\n<td>The directory under which to store the backup data. This is a crucial parameter required for most kinds of backup operations.<\/td>\n<\/tr>\n<tr>\n<td><code>backup-and-apply-log<\/code><\/td>\n<td>This option performs an extra stage after the initial backup, to bring all InnoDB tables up-to-date with any changes that occurred during the backup operation, so that the backup is immediately ready to be restored.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Make sure to educate yourself on the <a href=\"https:\/\/dev.mysql.com\/doc\/mysql-enterprise-backup\/3.9\/en\/backup-subcommands-backup.html\">backup operations supported by MySQL Enterprise Backup<\/a>. The full script looks something like this:<\/p>\n<pre><code>#!\/bin\/bash\r\n\r\nBACKUP_DIR=\/var\/backups\/mysql\/backups\r\nBACKUP_PASS=mysupersecret\r\nBACKUP_USER=backup\r\n\r\nDATE_DAY=$(date +\"%Y-%m-%d\")\r\nDATE_HOUR=$(date +\"%H\")\r\n\r\nEMAIL_RECIPIENT=simon@krenger.ch\r\n\r\n\/usr\/local\/mysql\/bin\/mysqlbackup --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$BACKUP_DIR backup-and-apply-log\r\n\r\nNO_OF_COMPLETE_OK_MESSAGES=$(cat $BACKUP_DIR\/${DATE_DAY}_${DATE_HOUR}*\/meta\/MEB_${DATE_DAY}.${DATE_HOUR}*.log | grep \"mysqlbackup completed OK\" | wc -l)\r\n\r\n# Note that the string \"mysqlbackup completed OK\" must occur 2 times in the log in order for the backup to be OK\r\nif [ $NO_OF_COMPLETE_OK_MESSAGES -eq 2 ]; then\r\n        # Backup successful, find backup directory\r\n        echo \"Backup succeeded\"\r\n        exit 0\r\nelse\r\n        echo \"MySQL backup failed, see attached logfile\" | mail -s \"ERROR: MySQL Backup Failed!\" ${EMAIL_RECIPIENT}\r\n        exit 1\r\nfi<\/code><\/pre>\n<p>You can then schedule it to run daily (<code>crontab -e<\/code>) at 04:00 in the morning for example:<\/p>\n<pre><code>0 4 * * * \/var\/backups\/mysql\/make-mysql-backup.sh<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p> Full Backup using MySQL Enterprise Backup <\/p>\n<p>As you might know, I am primarily an Oracle guy. This means that for all my backup needs, I am using Oracle Recovery Manager (RMAN). I recently had the task to implement MySQL Enterprise Backup for a MySQL database (mysql-advanced-5.6.15-linux-glibc2.5-x86_64). My most important resource for this task was [&#8230;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6309"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6309"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6309\/revisions"}],"predecessor-version":[{"id":6310,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6309\/revisions\/6310"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6309"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6309"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6309"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}