資料庫 & ERP

(From zmanda.com)How to setup and verify a backup solution for MySQL in 15 minutes(open source)

詳細內容
分類: MySQL
發佈: 2008-12-02, 週二 10:40
作者 Super User
點擊數: 19190

- By Shailen Patel

The Problem

The value of data stored in MySQL grows exponentially. For companies who are using MySQL in production, loss of data can be catastrophic, resulting in lost revenues, lost customers and lost reputations. Setting up MySQL backup and restore processes typically takes up a lot of a DBA's time and attention.

The Challenge

For our 15-minute challenge, we will backup a MySQL 5.0 database on Linux. We will only use freely downloadable open source software for the solution.

Our scenario is as follows:

We have a fairly representative MySQL database being managed by a DBA. The DBA doesn't have the time to write backup/recovery scripts nor does the DBA want to manually backup the database every 12 hours. We are running one MySQL database using the InnoDB Storage Engine. We will be performing a logical backup of our database. A logical backup 1) contains SQL statements that can reconstruct the database table schema and contents, 2) can be performed while the database is still running (hot backup),  and 3) can be restored to another platform or another database.

For this test we are using the following:
Server OS: Fedora Core 5
MySQL Version: MySQL 5.0.22 (http://www.mysql.com)

For this example, we use a database that was recently provided by Netflix as part of the community project to improve Netflix movie recommendation algorithm. In this example, all letters in green are commands that needs to be typed in.

The Solution: Zmanda Recovery Manager [ZRM] for MySQL

Zmanda Recovery Manager [ZRM] for MySQL simplifies the life of a Database Administrator who needs an easy-to-use yet flexible and robust backup and recovery solution for MySQL server. 
 
This article will show you how, in about 15 minutes, you can:

  1. Install ZRM for MySQL locally, on a MySQL Database Server.
  2. Configure ZRM for MySQL locally, on a MySQL Database Server.
  3. Perform a backup.
  4. Setup a automated backup process which adheres to your retention policies.
  5. Backup reports.
  6. Verification of backup images.
  7. Perform a full restoration.
Note:Letters in green are commands that needs to be typed in.

Prerequisites

  • MySQL Server installed and configured.
  • At least one running database.
  • MySQL user with appropriate permissions (backup-user).
    • Grant access to 'backup-user' in mysql with password 'pass123'
      • mysql>  grant select, insert, update, create, drop, reload, shutdown, alter, super, lock tables, replication client on *.* to 'backup-user'@'localhost' identified by 'pass123';
      • mysql> show grants for 'backup-user'@'localhost';
      • Grants for backup-user@localhost
      • | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, RELOAD, SHUTDOWN, ALTER, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup-user'@'localhost' IDENTIFIED BY PASSWORD '591051bb593d2f5b' WITH GRANT OPTION |
  • ZRM 1.1 for MySQL requires perl-DBI as well as perl-XML-parser. These perl modules must be installed before ZRM 1.1 for MySQL installation. These perl modules are available in all Linux distributions (might be already installed). Packages are available for these perl modules in the following locations (choose the package based on your distribution) perl-DBI & perl-XML-parser.
TIP: You can copy and paste all of the examples here, making appropriate modifications for your environment.

Install ZRM for MySQL.

1. Log in as root on your Linux Server. Download ZRM for MySQL:

ZRM for MySQL Version: MySQL-zrm-1.1-1 (http://www.zmanda.com/download-zrm.php)

2. Install the ZRM for MySQL 1.1 community edition. Installation of the package should be done as root.

-bash-3.1# ls -lh MySQL-zrm-1*
-rw-rw-r-- 1 root root 101K Oct 16 17:29 MySQL-zrm-1.1-1.noarch.rpm

-bash-3.1# rpm -ivh MySQL-zrm-1.1-1.noarch.rpm
Preparing...                ########################################### [100%]
   1:MySQL-zrm              ########################################### [100%]


3. Verify ZRM for MySQL Installation.

ZRM for MySQL executables are located in /usr/bin:
-bash-3.1# ls -lh /usr/bin/mysql-zrm*
-rwxr-x--- 1 root root 86K Oct 16 15:04 /usr/bin/mysql-zrm
-rwxr-x--- 1 root root 26K Oct 16 15:04 /usr/bin/mysql-zrm-reporter
-rwxr-x--- 1 root root 13K Oct 16 15:04 /usr/bin/mysql-zrm-scheduler

ZRM for MySQL configuration files are located in /etc/mysql-zrm:
-bash-3.1# ls -lh /etc/mysql-zrm/
-rwxr-x--- 1 root root 9.1K Oct 16 15:04 mysql-zrm.conf
-rwxr-x--- 1 root root   46 Oct 16 15:04 mysql-zrm-release
-rwxr-x--- 1 root root 2.8K Oct 16 15:04 mysql-zrm-reporter.conf
drwxr-x--- 2 root root 4.0K Oct 16 15:04 plugins
drwxr-x--- 4 root root 4.0K Oct 17 11:53 plugin-templates

Additional ZRM for MySQL files are located here:
Man pages    /usr/share/man/{man1,man5}
Libraries    /usr/lib/mysql-zrm
Log files    /var/log/mysql-zrm
Documentation    /usr/share/doc/MySQL-zrm-1.1

Configure ZRM for MySQL to backup your database

For this example, we will refer to a database called Movies with a size of 340mb. The Movies Database has two tables: MovieID and MovieRatings.

-bash-3.1# mysql -u backup-user -p

mysql> use movies;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables ;
+-------------------------------+
| Tables_in_movies    |
+-------------------------------+
| MovieID                   |
| MovieRatings           |
+-------------------------------+
2 rows in set (0.00 sec)

1. Log in as root on your Linux Server.

2. Configure a daily full backup of the Movies Database.

For the purpose of this test, we will create a directory called “dailyrun”, and copy the sample mysql-zrm.conf configuration file into there. This allows more flexibility to create individual backup sets for individual databases.

-bash-3.1# cd /etc/mysql-zrm
-bash-3.1# mkdir dailyrun
-bash-3.1# cp mysql-zrm.conf dailyrun/
-bash-3.1# cd dailyrun/
-bash-3.1# ls -lh
-rwxr-x--- 1 root root 8.8k Oct 17 15:58 mysql-zrm.conf

3. Edit /etc/mysql-zrm/dailyrun/mysql-zrm.conf to change the following parameters. Please be sure to protect this file with the proper permissions, as it stores the password for the MySQL 'backup-user' in clear text.

For the purpose of this test we are demonstrating a full backup:
Backup Level:
# Backup level. It can be full or incremental
# Use 0 for full and 1 for incremental backups
# This parameter is optional and default value is full backup.
backup-level=0

We will be performing logical backups:
Backup Method:
# Backup method
# Values can be "raw" or "logical". Logical backup are backups using
# mysqldump(1) tool
# This parameter is optional and default value is "raw".
backup-mode=logical

For the purpose of this test we chose to retain our backups for 10 days:
Retention Policy:
# Specifies how long the backup should be retained. The value can be
# specified in days (suffix D), weeks (suffix: W), months (suffix: M) or
# years (suffix Y). 30 days in a month and 365 days in a year are assumed
# This parameter is optional and the default is the backups are retained
# forever.
retention-policy=10D

We have chosen to compress our backups to save disk space:

# This parameter should be set to 1 if backups should be compressed. If this
# parameter is set, gzip(1) command is used by default. If different
# compression algorithm should be used, it must be set in "compress-plugin"
# parameter. Default: There is no data compression.
compress=1

We are choosing to backup only the 'Movies' database:
Database(s) to backup:
# List of databases that are part of this backup set. Multiple database
# names are separated by space character. This parameter is ignored if
# "all-databases" is set 1.
databases=movies

MySQL username/password that was created above:
(the password below is stored in clear text)
MySQL Server Parameters:
# MySQL database user used for backup and recovery of the backup set.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file.
user="backup-user"

# MySQL database user password.
# This parameter is optional. If this parameter is not specified, values from
# my.cnf configuration file or no password is used.
password="pass123"

This can be turned off, if you like, once MySQL is configured and running:
Verbosity of ZRM for MySQL Logging:
# This parameter controls the verbosity of MySQL ZRM logging. The MySQL ZRM logs
# are available at /var/log/mysql-zrm/mysql-zrm.log. This parameter is optional
# default value is 0 (less verbose).
# The valid values are 0 and 1
verbose=1

Emailing Backup Report:
(requires your Linux server to be able to send mail and a valid email address)
# After a backup run the backup report is emailed to the mailto address
# This parameter is optional and default behavior is not to send mail notifications.
mailto="Email住址會使用灌水程式保護機制。你需要啟動Javascript才能觀看它"

Save and close the /etc/mysql-zrm/dailyrun/mysql-zrm.conf file.

Perform a Backup

1. On your MySQL Server, as root run ZRM for MySQL to start the dailyrun Backup on the Movies Database.

-bash-3.1# mysql-zrm-scheduler --now --backup-set dailyrun

2. ZRM for MySQL will output the following report to screen. 

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Input Parameters Used {
INFO:   quiet=0
INFO:   verbose=1
INFO:   retention-policy=1W
INFO:   backup-level=0
INFO:   destination=/var/lib/mysql-zrm
INFO:   databases=movies
INFO:   html-reports=backup-status-info
INFO:   backup-mode=logical
INFO:   password=******
INFO:   compress=
INFO:   user=backup-user
INFO: Getting mysql variables
INFO: mysqladmin --user=backup-user --password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: backup set being used is dailyrun
INFO: backup-set=dailyrun
INFO: backup-date=20061025161624
INFO: host=localhost
INFO: backup-date-epoch=1161818184
INFO: retention-policy=1W
INFO: mysql-zrm-version=ZRM for MySQL Community Edition - version 1.1
INFO: mysql-version=5.0.22-log
INFO: backup-directory=/var/lib/mysql-zrm/dailyrun/20061025161624
INFO: Executing pre-backup-plugin
INFO: Flushing the logs
INFO:  flush-logs
INFO: Getting master logname using command mysql --user=backup-user --password=***** -e "show master status"
INFO: backup-level=0
INFO: Command used for logical backup is mysqldump --opt --extended-insert --single-transaction --default-character-set=utf8 --create-options --user=backup-user --password=***** --databases movies > "/var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql"
INFO: Logical backup done for the following database(s)
        movies
INFO: logical-databases=movies
INFO: next-binlog=mysql-bin.000030
INFO: last-backup=/var/lib/mysql-zrm/dailyrun/20061025160303
INFO: /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql=497892557fd97f4f8f4102ab866293a6
INFO: backup-size=306.20 MB
INFO: Compressing  backup
INFO: Command used is 'tar  --same-owner -cpsC  "/var/lib/mysql-zrm/dailyrun/20061025161624"  --exclude=backup-data  --exclude=index  . 2>/tmp/ZQr1wiV0Oi  | gzip 2>/tmp/LqilmZDG20 > "/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data" 2>/tmp/NDX91Ym36X'
INFO: compress=
INFO: backup-size-compressed=69.72 MB
INFO: Removing all of the uncompressed/unencrypted data
INFO: Executing post-backup-plugin
INFO: read-locks-time=00:00:32
INFO: flush-logs-time=00:00:00
INFO: backup-time=00:01:08
INFO: backup-status=Backup succeeded
INFO: Backup succeeded
 
Note:
In this example, we are using the ZRM for MySQL scheduler to do backup now. The schedule can be used to set up a backup schedule, as shown below.
Schedules a daily full at 1am everyday.
-bash-3.1# mysql-zrm-scheduler --add --interval daily --start 01:00 --backup-level 0 --backup-set dailyrun
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log DONE
Verification that scheduler has been configured
-bash-3.1# mysql-zrm-scheduler --query
Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log 0 1 * * * /usr/bin/zrm-pre-scheduler --action backup --backup-set dailyrun --destination /var/lib/mysql-zrm --backup-level 0 --interval daily

Backup Reports

You can run the following mysql-zrm-reporter commands for a quick summary of your backup job(s). 

-bash-3.1# mysql-zrm-reporter --where backup-set=dailyrun --show backup-status-info

backup_set  backup_date                                backup_level     backup_status       comment
-------------------------------------------------------------------------------------------------------------------------------------------
dailyrun  Wed 25 Oct 2006 04:16:24 PM PDT              0           Backup succeeded      ----

The first report shows us the status of backup(s) for backup-set 'dailyrun', including backup level (full [0] or incremental [1]) & backup status (success or failure).

-bash-3.1# mysql-zrm-reporter --where backup-set=dailyrun --show backup-performance-info

backup_set     backup_date                            backup_level   backup_size  backup_size_compressed     backup_time
---------------------------------------------------------------------------------------------------------------------------------------------------------------
dailyrun  Wed 25 Oct 2006 04:16:24 PM PDT               0           306.20 MB             69.72 MB                       00:01:09

The second report shows the total time the backup(s) ran, as well as the compressed & uncompressed size of the backup(s).

Verification of Backup Images

You can run the following mysql-zrm command to quickly verify the integrity of your last backup.

-bash-3.1# mysql-zrm --action verify-backup --backup-set dailyrun

ZRM for MySQL Community Edition - version 1.1
INFO: Input Parameters Used {
INFO:   verbose=1
INFO:   retention-policy=1W
INFO:   backup-level=0
INFO:   databases=movies
INFO:   html-reports=backup-status-info
INFO:   backup-mode=logical
INFO:   password=******
INFO:   compress=
INFO:   user=backup-user
INFO: Uncompressing  backup
INFO: Command used is 'cat "/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data"  |  gzip -d  | tar  --same-owner -xpsC  "/var/lib/mysql-zrm/dailyrun/20061025161624"  2>/tmp/bY1jIvcElN'
INFO: checksum for file /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql is correct
INFO: Verification successful
INFO: Removing all of the uncompressed/unencrypted data

Perform a full restoration

First we'll drop the movies database.  In order to this, we will have to login MySQL with a user that has root privileges. In this example, we have renamed the 'root' user to 'admin' (for security reasons), within MySQL.

-bash-3.1# mysql -u admin -p
mysql> show databases;
+----------------------------------+
| Database                    |
+----------------------------------+
| information_schema   |
| movies                        |
| mysql                          |
| test                              |
+-----------------------------------+
4 rows in set (0.00 sec)

mysql> drop database movies;
Query OK, 2 rows affected (0.67 sec)

mysql> show databases;
+----------------------------------+
| Database                    |
+----------------------------------+
| information_schema   |
| mysql                          |
| test                              |
+-----------------------------------+
3 rows in set (0.00 sec)

1. Determine which backup to restore from. 

-bash-3.1# mysql-zrm-reporter --show restore-info --where backup-set=dailyrun

backup_set  backup_date                                backup_level                  backup_directory
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
dailyrun  Wed 25 Oct 2006 04:16:24 PM PDT                 0       /var/lib/mysql-zrm/dailyrun/20061025161624
                                                                  
2. Kick off restore job. We'll restore from the last full backup, from 4:16pm on October 25th.

-bash-3.1# mysql-zrm -restore --backup-set dailyrun --source-directory \
/var/lib/mysql-zrm/dailyrun/20061025161624/
INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Input Parameters Used {
INFO:   verbose=1
INFO:   retention-policy=1W
INFO:   backup-level=0
INFO:   databases=movies
INFO:   source-directory=/var/lib/mysql-zrm/dailyrun/20061025161624
INFO:   html-reports=backup-status-info
INFO:   backup-mode=logical
INFO:   password=******
INFO:   compress=
INFO:   user=backup-user
INFO: Getting mysql variables
INFO: mysqladmin --user=backup-user --password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: Uncompressing  backup
INFO: Command used is 'cat "/var/lib/mysql-zrm/dailyrun/20061025161624/backup-data"  |  gzip -d  | tar  --same-owner -xpsC  "/var/lib/mysql-zrm/dailyrun/20061025161624"  2>/tmp/1wNYPfSl01'
INFO: restoring using command mysql --user=backup-user --password=***** -e "set character_set_client=utf8;set character_set_connection=utf8;set character_set_database=utf8;set character_set_results=utf8;set character_set_server=utf8;source /var/lib/mysql-zrm/dailyrun/20061025161624/backup.sql;"
INFO: Restored database(s) from logical backup: movies
INFO: Shutting down MySQL
INFO: Removing all of the uncompressed/unencrypted data
INFO: Restore done in 140 seconds.
MySQL server has been shutdown. Please restart after verification.

After starting the MySQL service (as root type: /sbin/service mysqld start) Verify the database was restored.

-bash-3.1# mysql -u backup-user -p
mysql> show databases;
+----------------------------------+
| Database                    |
+----------------------------------+
| information_schema   |
| movies                        |
| mysql                          |
| test                              |
+-----------------------------------+
4 rows in set (0.00 sec)
mysql> use movies;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------------------+
| Tables_in_movies    |
+-------------------------------+
| MovieID                   |
| MovieRatings           |
+-------------------------------+
2 rows in set (0.00 sec)


Success!

In just about 15 minutes, we installed and configured a fast and reliable MySQL backup solution, performed a backup, verified our backup and performed a restoration. We did it with freely downloadable open source software that you can install from binaries or compile for your unique needs. As a DBA, you now have a reliable and easy to implement backup solution to protect your database using ZRM for MySQL.