MySQL Backups: From strategy to Implementation

(1)

MySQL Backups:

From strategy to

Implementation

Mike Frank

(2)

Program Agenda



Introduction



The 5 Key Steps



Advanced Options

(3)

Backups are a DBAs Top Priority

Be Prepared

Automate

Test and Practice

(4)

Backups



Data loss will happen

–

OS crashes

–

Power Failures

–

Data center outages

–

Are you in a cloud?

–

File system crashes

–

Human error

–

Application Error



Just a reminder – of course - you know all that ….

(5)

MySQL Enterprise Backup



Highly efficient and Consistent



Performs the backup while the database is running



Necessary when

–

The database can’t be taken offline



Those are “Cold Backups”



The database is large – backups can take a long time

–

Export backups are too slow, lock tables, …

–

Also lets you perform “Warm Backups” for non-InnoDB tables.



Table level locks

(6)

What we’ll cover today



The goal today is to

get you running scheduled backups regularly

.

–

The first key step to protecting your data



Later you can explore more powerful work flows and features

–

Partial data moment

–

Compression

–

Task synchronization

–

Hot and Cold support

There’s more than we can cover this session

(7)

(8)

The 5 Steps

1. Download and Install

2. Setup Backup Directories

3. Creating Full and Incremental Backups

4. Scheduling

(9)

Step 1

(10)

Download



https://edelivery.oracle.com



Login



Pick MySQL Database and then OS platform

(11)

Find the download package



Your looking for MySQL Enterprise Backup …



Half way down the page – select Specific OS/Version and Format -

either TAR or RPM

Note for Windows -

MySQL Installer for Windows

includes

(12)

Install

Uncompress the downloaded file - then



ZIPs and Tars

–

Basically you can unzip, untar MEB where ever you want



RPM

–

sudo rpm -i package_name.rpm



Deb packages

–

sudo dpkg –i package_name.rpm



Windows

–

Run the MSI – installs the product in

(13)

(14)

(15)

Once installed set path or note location



Default Windows from MSI

–

C:\Program Files\MySQL\MySQL Enterprise Backup 3.8\mysqlbackup.exe

–

Used in 5 step example



Default Linux from RPM / DEB

–

/opt/mysql/meb-3.8



If tar or zip – will be where you unpacked the files

–

For linux 5 step example used

(16)

Step 2

(17)

Where do you want the backups to go



Make sure you have plenty of space



Monitor that space



Linux

–

mkdir /MySQLBackupHome

–

chown mysql –R /MySQLBackupHome

–

chgrp mysql –R /MySQLBackupHome

–

chmod 600 –R /MySQLBackupHome



Windows –

–

mkdir C:\mydatabasebackups\

–

Make sure only the user that has rights to mysql file can read and write to the directory

–

Right Click properties and Edit permissions for only that user (mysql) – Permissions - Modify, Read & Execute,

List Folders, Read, Write

(18)

Step 3

Backup

(19)

Backup Types



Full – all the database data

–

Note – but not the mysql programs and control files



Incremental – only the database changes –

–

Prerequisite – a full backup

–

Can be more than one incremental backups



The changes between last full or a prior incremental



Partial – just part of the database



Binary Logs – contains each event/transaction

http://dev.mysql.com/doc/refman/5.6/en/binary-log.html

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/meb-backup-overview.html

http://dev.mysql.com/doc/mysql-enterprise-backup/3.7/en/mysqlbackup.tasks.html

(20)

Keeping it simple



You’ll want to run backups on a schedule



For example – here we will show

–

1 full once week

(21)

Commands and Scripts



You need to have scripts that you schedule



In this case a minimum of 2

–

FULL

(22)

Create Backup User



For this example the backup user is – mysqlbackup

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/mysqlbackup.privileges.html

mysql -u root –p

Mysql> CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY '<password>';

mysql> GRANT RELOAD ON . TO 'mysqlbackup'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO

'mysqlbackup'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'mysqlbackup'@'localhost';

mysql> GRANT CREATE, INSERT, DROP ON mysql.backup_history TO 'mysqlbackup'@'localhost';

mysql> GRANT REPLICATION CLIENT ON . TO 'mysqlbackup'@'localhost';

mysql> GRANT SUPER ON . TO 'mysqlbackup'@'localhost';

mysql> GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'mysqlbackup'@'localhost';

mysql> FLUSH PRIVILEGES;

(23)

Step 3 a

(24)

FULL



Script will

–

Create a time stamped subdirectory in the “home”

backup area

–

Create the backup command

(25)

Linux FULL Bash Script

#!/bin/bash

dtstring=`date +%F`

new_dir="/MySQLBackupHome/mysqlfullweeklybackup_"$dtstring

mkdir $new_dir

cd /MySQLBackupHome

/home/mfrank/meb-3.8.1-linux2.6-x86-64bit/bin/mysqlbackup --defaults-file=mysqlback.cnf

--backup-dir=$new_dir/backup backup &> $new_dir/backup.log

(26)

Linux Configuration File -

[client]

port = 3306

protocol = TCP

user = <your backup user>

password = <your backup user password>

[mysqld]

datadir = /var/lib/mysql

[mysqlbackup]

datadir = /var/lib/mysql

innodb_data_home_dir =

incremental_base = history:last_backup

comments = My main backup - Full once a week, Incrementals daily

mysqlback.cnf

Note: MEB supports

MySQL External

Authentication

(27)

Windows FULL VBScript

Set shell = WScript.CreateObject("WScript.Shell")

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

new_dir = "C:\MySQLBackupHome\mysqlfullweeklybackup_" &

replace(FormatDateTime(now,2),"/","")

If Not fso.FolderExists(new_dir) Then

Set f = fso.CreateFolder( new_dir )

End If

backup_command = "cmd.exe /c ""C:\Program Files\MySQL\MySQL Enterprise Backup

3.8\mysqlbackup.exe"" --defaults-file=mysqlback.cnf --backup-dir=" & new_dir &

"\backup backup > " & new_dir & "\backup.log 2>&1"

Set oExec = shell.Exec(backup_command)

Do While oExec.Status = 0

WScript.Sleep 100

Loop

(28)

Windows Configuration File

[client]

port = 3306

socket = MySQL

user = <your backup user>

password = <your backup user password>

[mysqld]

datadir = C:\ProgramData\MySQL\MySQL Server 5.6\data\

[mysqlbackup]

datadir = C:\ProgramData\MySQL\MySQL Server 5.6\data\

innodb_data_home_dir =

c:\MySQLBackupHome\mysqlback.cnf

Note: MEB supports

MySQL External

Authentication

(29)

Step 3 a

(30)

Linux Incremental Bash Script

#!/bin/bash

dtstring=`date +%F`

new_dir="/MySQLBackupHome/mysqlincrementaldailybackup_"$dtstring

rm -Rf $new_dir

mkdir $new_dir

cd /MySQLBackupHome

/home/mfrank/meb-3.8.1-linux2.6-x86-64bit/bin/mysqlbackup --defaults-file=mysqlback.cnf

--incremental

--backup-dir=$new_dir/backup

--incremental-backup-dir

=$new_dir/backup/inc backup &> $new_dir/backup.log

(31)

Windows Incremental VBScript

Set shell = WScript.CreateObject("WScript.Shell")

Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")

new_dir = "C:\MySQLBackupHome\mysqlincrementaldailybackup_" &

replace(FormatDateTime(now,2),"/","")

If Not fso.FolderExists(new_dir) Then

Set f = fso.CreateFolder( new_dir )

End If

Dim backup_command

backup_command = "cmd.exe /c ""C:\Program Files\MySQL\MySQL Enterprise Backup

3.8\mysqlbackup.exe"" --defaults-file=mysqlback.cnf

--incremental

--backup-dir=" & new_dir &

"\backup

--incremental-backup-dir=

" & new_dir & "\backup\inc backup > " & new_dir &

"\backup.log 2>&1“

Set oExec = shell.Exec(backup_command)

Do While oExec.Status = 0

(32)

Step 4

(33)

Step 4 a

(34)

Linux CRON

Weekly Full – Sun at 2:00 am

crontab –e

0 2 * * 0 /MySQLBackupHome/MyFullBackup.sh

Daily Incremental Mon-Sat at 2:00 am

crontab –e

(35)

Step 4 b

(36)

Windows Scheduler

(37)

Weekly for Full

Daily for Incremental

(38)

(39)

(40)

(41)

Step 5

(42)

Recovery - Preparing



Determine time to recover to

–

If the time is for an incremental



Applying an Incremental Backup changes reflected in incremental backup files

bring the backup up-to-date



Apply those changes for each - oldest to newest



For example

mysqlbackup

--backup-dir=/MySQLBackup/mysqlfullweeklyfullbackup_2013-04-22/backup

--incremental-backup-dir

=/MySQLBackup/mysqlfulldailyincrementalbackup_2013-04-23/backup/inc apply-incremental-backup

(43)

Recovery – Restore the data



Shutdown the database to be restored

–

Stop the service or run a mysqladmin shutdown



Run Copy-Back

–

Copies the datafiles, logs, and other backed up files into place



For example

mysqlbackup --defaults-file=/etc/my.cnf

--backup-dir =/MySQLBackup/mysqlfullweeklyfullbackup_2013-04-22/backup copy-back

(44)

Advanced - Backup



Schedule the mysqlbinlog utility to dump all the SQL activity that

happened after the binlog position of the last backup



Direct the output of the mysqlbinlog command to an output file

$ mysqlbinlog --start-position=27183537 /sqldata/binlog.000005 >partial_binlog

$ mysqlbinlog /sqldata/binlog.000006 >> partial_binlog

$ mysqlbinlog /sqldata/binlog.000007 >> partial_binlog ...

$ mysqlbinlog /sqldata/binlog.00000N >> partial_binlog

(45)

Advanced - Recovery



To recover the database to the latest possible time,

–

Pipe the output from mysqlbinlog --start-position=...



Directly to mysql



This replays all the SQL statements after the last backup.



Or to get to a point in time say be before an “oops” happened

–

Edit the bin log and remove the “oops” and all statements after it.



Then

$ mysql < partial_binlog

Getting finer grained for an exact Point in Time

(46)

Advanced – Single File



–

Has been seen to be faster than directory backup



Gives you an additional facility to verify the backup



Does take a little longer to restore



BUT - Can be prepared for restore

–

Extract

–

Apply log

–

Compress back again

(47)

Advanced – Backup Direct to Tape



Meb Is a complete tape solution - Direct to Tape using SBT

–

SBT is Oracle’s API for backing up and restoring data via media

management products

–

use SBT for integration with Oracle Secure Backup

–

use SBT for integration with Symantec Net Backup

–

use SBT for integration with Tivoli Storage Manager

(48)

(49)

Advanced - Compression



The compression value needs to be determined

–

1 is normally a good value

–

Unless you have an idle CPU

–

And a very strict disk limitation



Compression will save disk but eat up more CPU and will take longer

(50)

Advanced – Internal Table



MySQL Enterprise backup writes status to internal backup table



Useful to see detailed history of backups



Provide an audit trail to managers and auditors

(51)

Finally



You also need to decide on

–

Retention



What to save and for how long



Schedule and automate this process



Its often nice to keep recent backups “close” for fast recovery

–

Archiving



Whether to go to tape

–

MEB also supports direct to tape backups

–

Move files to archive storage – tape, HDFS, Cloud - S3 ….

(52)

Resources



Product Page

–

http://www.mysql.com/products/enterprise/backup.html



Latest Release

–

https://blogs.oracle.com/mysqlenterprisebackup/entry/mysql_enterprise_backup_3_8



User Guide

–

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/



Team Blog

–

https://blogs.oracle.com/mysqlenterprisebackup/



Questions, Ideas, …

–

http://www.mysql.com/about/contact/

–

Phone: USA=+1-866-221-0634; Outside USA = +1-208-338-8100