Friday, 28 December 2012

MySQL Enterprise Backup Fast, Consistent, Online Backups.

The MySQL Enterprise Backup product performs hot backup operations for MySQL databases. The product is architected for efficient and reliable backups of tables created by the InnoDB storage engine. For completeness, it can also back up tables from MyISAM and other storage engines.
Hot backups are performed while the database is running. This type of backup does not block normal database operations, and it captures even changes that occur while the backup is happening. For these reasons, hot backups are desirable when your database “grows up” -- when the data is large enough that the backup takes significant time, and when your data is important enough to your business so that you must capture every last change, without taking your application, web site, or web service offline.
MySQL Enterprise Backup does a hot backup of all tables that use the InnoDB storage engine. For tables using MyISAM or other non-InnoDB storage engines, it does a “warm” backup, where the database continues to run, but those tables cannot be modified while being backed up. For efficient backup operations, you can designate InnoDB as the default storage engine for new tables, or convert existing tables to use the InnoDB storage engine.

Backing up MySQL data

When do you need backups?
What needs to be backed up?
When should backups be performed?
Where will the backups be stored?
How can backups be performed?

When Do You Need Backups?

Hardware failure
A system crash may cause some of the data in the databases to be lost
A hard¬disk failure will most certainly lead to lost data

User/Application failure
Accidental DROP TABLE or malformed DELETE FROM statements
Editing the table files with text editors, usually leading to corrupt tables.

What needs to be backed up? When?

Database content
for full backups
logical or physical backup

Log files
for incremental backups
point-in-time recovery

On a regular basis
Not during high usage peaks (off hours)
Static data can be backed up less frequently

The Data Directory

Databases and most log and status files are stored in the data directory by default

Default directory compiled into the server
/usr/local/mysql/data/ (tarball installation)
/var/lib/mysql (RPM packages)
Data directory location can be specified during server startup with

Find out the location by asking the server

mysql> SHOW VARIABLES like 'data%';

The Binary Log

Contains all SQL commands that change data
Also contains additional information about each query (e.g. query execution time)
Binary log is stored in an efficient binary format
Use mysqlbinlog to decipher the log contents
Log turned on with ¬¬log¬bin[=file_name]

Update logs are created in sequence
e.g. file_name¬bin.001, file_name¬bin.002, etc.

Binary log is transaction-compatible
mysqld creates binary log index file which contains names of the binary log files used

Managing the Binary Log

Purpose of the Binary Log:
Enable replication
Ease crash recovery

SHOW MASTER LOGS shows all binary log files residing on the server
FLUSH LOGS or restarting the server creates a new file
RESET MASTER deletes all binary log files
PURGE MASTER deletes all binary log files up to a certain point
Don't delete logs that slaves still need


•Dumps table structure and data into SQL statements
$ mysqldump mydb > mydb.20090413.sql

•Dumps individual tables or whole databases
•Default output from mysqldump consists of SQL
– CREATE TABLE statements for table structure
– INSERT statements for the data
•Can also be used directly as input into another mysqld server (without creating any files)
$ mysqldump opt world | mysql world

mysqldump hints
Use –single-transaction when backing up InnoDB tables
--lock-all-tables is useful for performing consistent MyISAM backups
But locks all DML statements until backup is done
--flush-logs flushes the binary log file

Recovering from Backups
Restoring tables to the state before a crash requires both the backup files and the binary log
> Restore the tables to the state they were at the time of the backup from the backup files
> Extract the queries issued between the backup and now from synchronized binary logs
If you are recovering data lost due to unwise queries, remember not to issue them again
DB Recovery = Last full backup & binlogs

MySQL Enterprise Backup--Features & Benefits

MySQL Backup Concerns

MySQL Enterprise Backup

Online Backup for InnoDB
Support for MyISAM (Read-only)
High Performance Backup & Restore
Compressed Backup
Full Backup
Incremental Backup
Partial Backups
Point in Time Recovery
Unlimited Database Size
Windows, Linux, Unix

Benefits: ==
Online “Hot” Backup (Non-blocking)
Reads and Writes to InnoDB
Reads for MyISAM tables
High Performance
Backup: >3x faster than mysqldump (export)
Restore: >10x than mysqldump recovery
Consistent Backups
Point in Time Recovery
Multi-level compression
Save 70% or more of the storage required.
Proven for 7+ Years
Scalable for Large Databases
No Database Size Limitations
Easy to automate
Easily integrate within various scheduling systems
Examples: cron, OSB scheduler, others

MySQL Enterprise Backup 3.5: New Features

Incremental backup
Support of InnoDB Barracuda file format 
Backup of compressed tables
Backup of partition files
Backup of in-memory database
with --exec-when-locked option
Adds mysql system tables to keep backup status, progress, and history

Database Backup Types-- Advantages & Disadvantages 

Hot Backup (online)
MySQL Enterprise Backup
Export/Import (portable copies – a logical backup)
Standby Copy (hot swap)
MySQL Replication
Cold Backup (offline)
Simple File Copies when server is shutdown
File System Volume Managers (snapshots)
LVM for example - create snapshot copy


Good for small databases or tables
Good assurance that database files are not corrupt 
Logical Backup – thus flexible and portable
Very slow restore times
Uses database processing cycles and resources
Not Online (requires Transaction or Locks on Tables in the database)
Not Incremental (requires a Full Backup every time)
Not Consistent (unless transaction is used)

MySQL Replication

Rolling “snapshot”
Quick Recovery - via failover
Works well in conjunction with other backup options

Only latest “Point in Time” (point it time keeps moving forward)
Not historical
Not for archival purposes
Doesn’t protect from “oops”   

LVM Snapshots

Feature of Linux
Good to use in conjunction with backups

It’s a snapshot 
Still need to make a backup copy – which is “full” in size
Performance degrades with each concurrent snapshot
Snapshots need to be released
Cross File System Limitations 

MySQL Enterprise Backup

Physical Backup so Fast – esp. restores
Flexible - many options 

Requires some planning

MySQL Enterprise Backup--How it Works

MySQL Enterprise Backup: Terms

•mysqlbackup : backup executable which includes InnoDB, MyISAM and other MySQL Data. mysqlbackup is a compatible replacement for the innobackup post 3.5.1 and includes additional features and capabilites
•ibbackup: finer grained raw innodb backup executable for innodb files alone
•binlog: contains database changes – eg DDL and DML
•LSN: Log Sequence Number – the unique monotonically increasing id for each change in the binlog
•Ibdata: system tablespace files
•.ibd: single table space file

mysqlbackup – Usage Syntax

mysqlbackup [--sleep=MS] [--compress[=LEVEL]] [--include=REGEXP]
            [--user=WORD][--password=WORD] [--port=PORT]
            [--socket=SOCKET] [--no-timestamp]
            [--ibbackup=IBBACKUP-BINARY] [--slave-info]
            [--backup-and-apply-log] [--databases=LIST]
            [--exec-when-locked="utility arg1 arg2 ..."]
            [--incremental --lsn=LSN]
mysqlbackup --apply-log [--use-memory=MB] [--uncompress]
            [--ibbackup=IBBACKUP-BINARY]  MY.CNF BACKUP-DIR
mysqlbackup --apply-log --incremental [--use-memory=MB] [--uncompress]
mysqlbackup --copy-back MY.CNF BACKUP-DIR

mysqlbackup (innobackup) Examples

Full Backup
mysqlbackup --user=dba --password=xyz --compress /etc/my.cnf /backups
Incremental Backup
The backup only contains changed data
mysqlbackup --incremental --lsn  /etc/my.cnf /incr-backup
The backup contains tables in test database that match the .ib.* regular expression.
mysqlbackup --include 'test\.ib.*' /etc/my.cnf /backups

Tips: InnoDB and MyISAM Backup

InnoDB tables are fully accessible during backup
Insert, Update & Delete
MyISAM tables cannot be updated during backup
Uses FLUSH TABLES WITH READ LOCK near the end of the backup
Works best if …
Wait for insert/update/delete transactions during MyISAM backup
Do not run long SELECT queries during the backup
MyISAM tables are small, thus copied quickly

ibbackup – Usage Syntax – strictly innodb

ibbackup [--incremental lsn]
         [--sleep ms] [--suspend-at-end] [--compress [level]]
         [--include regexp] my.cnf backup-my.cnf
ibbackup --apply-log
         [--use-memory mb] [--uncompress]
ibbackup --apply-log --incremental
         [--use-memory mb] [--uncompress]
         incremental-backup-my.cnf full-backup-my.cnf

Typical ibbackup Backup/Restore Steps

Take  Backup'
ibbackup  my.cnf  backup.cnf
Prepare backup for restore
ibbackup –apply-log  backup.cnf
Copy innodb backup files to mysqld datadir
Start mysqld
Performs recovery during startup

Examples : Take Backup details

Example my.cnf
  datadir = /production/var
  innodb_data_home_dir = /production/var
  innodb_log_group_home_dir = /production/var
  innodb_data_file_path =ibdata1:32M;ibdata2:32M:autoextend
  innodb_log_files_in_group = 3
  innodb_log_file_size = 32M
Example backup.cnf
   datadir = /backup
   innodb_log_group_home_dir = /backup
   innodb_data_home_dir = /backup
   innodb_data_file_path = ibdata1:32M;ibdata2:32M:autoextend
   innodb_log_files_in_group = 3
   innodb_log_file_size = 32M

Wednesday, 19 December 2012

How to Install MySQL from Generic Binaries on Unix/Linux

Oracle provides a set of binary distributions of MySQL.
These include binary distributions in the form of compressed tar files (files with a .tar.gz      
extension) for a number of platforms, as well as binaries in platform-specific package formats for selected platforms.

This section covers the installation of MySQL from a compressed tar file binary distribution.
For other platform-specific package formats.

1>> "How to Get MySQL."
MySQL compressed tar file binary distributions have names of the form mysql-VERSION-OS.tar.gz, 
where VERSION is a number (for example, 5.5.21), and OS indicates the type of operating system
for which the distribution is intended (for example, pc-linux-i686 or winx64).

2>> Gunzip & Uncompress mysql-VERSION-OS.tar.gz

To install MySQL from a compressed tar file binary distribution, your system must have GNU gunzip to uncompress the distribution and a reasonable tar to unpack it. If your tar program supports the z option, it can both uncompress and unpack the file.

GNU tar is known to work. The standard tar provided with some operating systems is not able to unpack the long file names in the MySQL distribution. You should download and install GNU tar, or if available, use a preinstalled version of GNU tar. 

Usually this is available as gnutar, gtar, or as tar within a GNU or Free Software directory, such as /usr/sfw/bin or /usr/local/bin. GNU tar is available from 



If you have previously installed MySQL using your operating system native package management system, such as yum or apt-get, you may experience problems installing using a native binary. Make sure your previous MySQL previous installation has been removed
entirely (using your package management system), and that any additional files, such as old versions of your data files, have also been removed. You should also check the existence of
configuration files such as /etc/my.cnf or the /etc/mysql directory have been deleted.

On Unix, to install a compressed tar file binary distribution, unpack it at the installation location you choose (typically /usr/local/mysql). This creates the directories shown in the
following table.

3>> “MySQL Installation Layout for Generic Unix/Linux Binary Package Directory Contents of Directory”

Debug versions of the mysqld binary are available as mysqld-debug.
To compile your own debug version of MySQL from a source distribution, 
use the appropriate configuration options to enable debugging support.

 4>> “To install and use a MySQL binary distribution, the basic command   sequence looks like this:” 

5>> A more detailed version of the preceding description for  installing a binary distribution follows.


This procedure assumes that you have root (administrator) access to your system. Alternatively, you can prefix each command using the sudo (Linux) or pfexec (OpenSolaris) command. The procedure does not set up any passwords for MySQL accounts.

5.1>> Create a mysql User and Group

If your system does not already have a user and group for mysqld to run as, you may need to create one. The following commands add the mysql group and the mysql user. You might want to call the user and group something else instead of mysql. If so, substitute the appropriate name in the following instructions. The syntax for useradd and groupadd may differ slightly on different versions of Unix, or they may have different names such as adduser and addgroup.

shell> groupadd mysql
shell> useradd -r -g mysql mysql

Because the user is required only for ownership purposes, not login purposes, the useradd command uses the -r option to create a user that does not have login permissions to your server host. Omit this option to permit logins for the user (or if your useradd does not support the option).

Pick the directory under which you want to unpack the distribution and change location into it. The example here unpacks the distribution under /usr/local. The instructions, therefore, assume that you have permission to create files and directories in /usr/local. If that directory is protected, you must perform the installation as root.

shell> cd /usr/local

Unpack the distribution, which creates the installation directory. Then create a symbolic link to that directory. tar can uncompress and unpack the distribution if it has z option support:

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql

The tar command creates a directory named mysql-VERSION-OS. The ln command makes a symbolic link to that directory. This enables you to refer more easily to the installation directory as /usr/local/mysql.

if your tar does not have z option support, use gunzip to unpack the distribution and tar to unpack it. Replace the preceding tar command with the following alternative command to uncompress and extract the distribution:

shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -

6>> “Perform Post installation Setup”

The remainder of the installation process involves setting up the configuration file, creating the core databases, and starting the MySQL server "Post installation Setup and Testing."
The accounts that are listed in the MySQL grant tables initially have no passwords. After starting the server, you should set up passwords.

After installing MySQL, there are some issues that you should address. For example, on Unix, you should initialize the data directory and create the MySQL grant tables. On all platforms, an important security concern is that the initial accounts in the grant tables have no passwords. You should assign passwords to prevent unauthorized access to the MySQL server. Optionally, you can create time zone tables to enable recognition of named time zones.

7>> “Unix Post installation Procedure”

After installing MySQL on Unix, you must initialize the grant tables, start the server, and make sure that the server works satisfactorily. You may also wish to arrange for the server to be started and stopped automatically when your system starts and stops. You should also assign passwords to the accounts in the grant tables.

On Unix, the grant tables are set up by the mysql_install_db program. For some installation methods, this program is run for you automatically if an existing database cannot be found.
If you install MySQL on Linux using RPM distributions, the server RPM runs mysql_install_db.
Using the native packaging system on many platforms, including Debian Linux, Ubuntu Linux, Gentoo Linux and others, the mysql_install_db command is run for you.
If you install MySQL on Mac OS X using a PKG distribution, the installer runs mysql_install_db.
For other platforms and installation types, including generic binary and source installs, you will need to runmysql_install_db yourself.

The following procedure describes how to initialize the grant tables (if that has not previously been done) and start the server. It also suggests some commands that you can use to test whether the server is accessible and working properly.

7.1>> “Starting and Stopping MySQL Automatically”

After you complete the procedure and have the server running, you should assign passwords to the accounts created by mysql_install_db and perhaps restrict access to test databases.

7.2>> "Securing the Initial MySQL Accounts"

In the examples shown here, the server runs under the user ID of the mysql login account. This assumes that such an account exists. Either create the account if it does not exist, or substitute the name of a different existing login account that you plan to use for running the server. For information about creating the account.
Change location into the top-level directory of your MySQL installation, represented here by BASEDIR:

shell> cd BASEDIR

BASEDIR is the installation directory for your MySQL instance. It is likely to be something like /usr/local/mysqlor /usr/local. The following steps assume that you have changed location to this directory.

You will find several files and subdirectories in the BASEDIR directory. The most important for installation purposes are the bin and scripts subdirectories:

The bin directory contains client programs and the server. You should add the full path name of this directory to your PATH environment variable so that your shell finds the MySQL programs properly.

For some distribution types, mysqld is installed in the libexec directory.
The scripts directory contains the mysql_install_db script used to initialize the mysql database containing the grant tables that store the server access permissions.
For some distribution types, mysql_install_db is installed in the bin directory.

If necessary, ensure that the distribution contents are accessible to mysql. If you installed the distribution asmysql, no further action is required. If you installed the distribution as root, its   

contents will be owned by root. Change its ownership to mysql by executing the following commands as root in the installation directory. The first command changes the owner attribute of the files to the mysql user. The second changes the group attribute to the mysql group.

shell> chown -R mysql .
shell> chgrp -R mysql .

If necessary, run the mysql_install_db program to set up the initial MySQL grant tables containing the privileges that determine how users are permitted to connect to the server. You will need to do this if you used a distribution type for which the installation procedure does not run the program for you.

Typically, mysql_install_db needs to be run only the first time you install MySQL, so you can skip this step if you are upgrading an existing installation, However, mysql_install_db does not overwrite any existing privilege tables, so it should be safe to run in any circumstances.
The exact location of mysql_install_db depends on the layout for your given installation. To initialize the grant tables, use one of the following commands, depending on whether mysql_install_db is located in the bin or scripts directory:

shell> scripts/mysql_install_db --user=mysql
shell> bin/mysql_install_db --user=mysql

It might be necessary to specify other options such as --basedir or --datadir if mysql_install_db does not identify the correct locations for the installation directory or data directory. For example:

shell> scripts/mysql_install_db --user=mysql \
         --basedir=/opt/mysql/mysql \

The mysql_install_db script creates the server's data directory with mysql as the owner. Under the data directory, it creates directories for the mysql database that holds the grant tables and the test database that you can use to test MySQL. The script also creates privilege table entries for root and anonymous-user accounts. The accounts have no passwords initially.
Describes the initial privileges. Briefly, these privileges permit the MySQL root user to do anything, and permit anybody to create or use databases with a name of test or starting with test.

It is important to make sure that the database directories and files are owned by the mysql login account so that the server has read and write access to them when you run it later. To ensure this if you run mysql_install_dbas root, include the --user option as shown. Otherwise, you should execute the script while logged in as mysql, in which case you can omit the --user option from the command.

If you do not want to have the test database, you can remove it after starting the server, using the instructions.

Most of the MySQL installation can be owned by root if you like. The exception is that the data directory must be owned by mysql. To accomplish this, run the following commands as root in the 

shell> chown -R root .
shell> chown -R mysql data

If the plugin directory (the directory named by the plugin_dir system variable) is writable by the server, it may be possible for a user to write executable code to a file in the directory using SELECT ... INTO DUMPFILE. This can be prevented by making plugin_dir read only to the server or by setting --secure-file-priv to a directory where SELECT writes can be made safely.

If you installed MySQL using a source distribution, you may want to optionally copy one of the provided configuration files from the support-files directory into your /etc directory. There are different sample configuration files for different use cases, server types, and CPU and RAM configurations. If you want to use one of these standard files, you should copy it to /etc/my.cnf, or /etc/mysql/my.cnf and edit and check the configuration before starting your MySQL server for the first time.

If you do not copy one of the standard configuration files, the MySQL server will be started with the default settings.

If you want MySQL to start automatically when you boot your machine, you can copy support-files/mysql.server to the location where your system has its startup files. More information can be found in the mysql.server script itself.

8>> “Start the MySQL server”:

shell> bin/mysqld_safe --user=mysql &

It is important that the MySQL server be run using an unprivileged (non-root) login account. To ensure this if you run mysqld_safe as root, include the --user option as shown. Otherwise, you should execute the script while logged in as mysql, in which case you can omit the --user option from the command.

For further instructions for running MySQL as an unprivileged user.

If the command fails immediately and prints mysqld ended, look for information in the error log (which by default is the host_name.err file in the data directory).
If you neglected to create the grant tables by running mysql_install_db before proceeding to this step, the following message appears in the error log file when you start the server:

mysqld: Can't find file: 'host.frm'

This error also occurs if you run mysql_install_db as root without the --user option. Remove the data directory and run mysql_install_db with the --user option as described previously.

Use mysqladmin to verify that the server is running. The following commands provide simple tests to check whether the server is up and responding to connections:

shell> bin/mysqladmin version
shell> bin/mysqladmin variables

The output from mysqladmin version varies slightly depending on your platform and version of MySQL, but should be similar to that shown here:

To see what else you can do with mysqladmin  invoke it with the --help option.
Verify that you can shut down the server:

shell> bin/mysqladmin -u root shutdown

Verify that you can start the server again. Do this by using mysqld_safe or by invoking mysqld directly. For example:

shell> bin/mysqld_safe --user=mysql 

If mysqld_safe fails:=
Run some simple tests to verify that you can retrieve information from the server. The output should be similar to what is shown here: