Backup and restore MySQL with mysql-zrm on Debian Sarge

This tutorial will describe how to back up and restore your MySQL database with mysql-zrm on a Debian Sarge system. mysql-zrm is part of MySQL's Zmanda Recovery Manager, it is a new tool that allows you to create full logic or raw database backup (regardless of your storage media and MySQL configuration ), create backup reports, verify the status of your database backup and restore . It can also send e-mail notifications to the backup status, from which you can add multiple backup measures (based on your application and time (eg daily or weekly .) ).

The first thing I want to say here is that this is not the only way to set up such a system. In fact there are many different ways to solve this problem and this is just one of those ways.

1, Notice the opening

mysql-zrm works on MySQL 4.1 and higher, so you must have a MySQL server installed on your Debian Sarge system or something like that:

apt-get install mysql-client-4.1 mysql-common-4.1 mysql-server-4.1

This section also installed the package libdbd-mysql-perl , which is an essential package for mysql-zrm when mysql-zrm is written in Perl.

2, Install

Zmanda has released an rpm package of mysql-zrm for rpm-based distribution like Fedora, RedHat, SuSE, CentOS, etc., however, there are no packages for Debian Sarge. So you have to download the mysql-zrm package from http://www.zmanda.com/downloads.html. At that time this version is 1.0.3 and download / tmp directory.

cd / tmp
wget http://www.zmanda.com/downloads/community/ZRM-MySQL/1.0.3/Source/MySQL-zrm-1.0.3.tar.gz

Then open the packages and go to the root directory:

tar xvfz MySQL-zrm-1.0.3.tar.gz
cd MySQL-zrm-1.0.3

Unfortunately, the INSTALL file instructions say that you can install the mysql-zrm rpm package if you are on a rpm-based branch. There are no installation instructions for the source package on the Zmanda website. Therefore I had to find a way to install mysql-zrm on my Debian Sarge system. Here is what I did:

chown root: root *
mv mysql-zrm / usr / bin
mv mysql-zrm-reporter / usr / bin
mv mysql-zrm-scheduler / usr / bin
gzip mysql-zrm.1
mv mysql-zrm.1.gz / usr / share / man / man1
gzip mysql-zrm.conf.5
mv mysql-zrm.conf.5.gz / usr / share / man / man5
gzip mysql-zrm-reporter.1
mysql-zrm-reporter.1.gz / usr / share / man / man1
gzip mysql-zrm-reporter.conf.5
mv mysql-zrm-reporter.conf.5.gz / usr / share / man / man5
gzip mysql-zrm-scheduler.1
mv mysql-zrm-scheduler.1.gz / usr / share / man / man1
mkdir / etc / mysql-zrm
mv * .conf / etc / mysql-zrm
mkdir -p / usr / lib / mysql-zrm / Data / Report / Plugin
mv Report.pm / usr / lib / mysql-zrm / Data
mv Base.pm / usr / lib / mysql-zrm / Data / Report
mv * .pm / usr / lib / mysql-zrm / Data / Report / Plugin
mkdir / var / log / mysql-zrm
gzip AUTHORS
gzip COPYING
gzip INSTALL
gzip README
mkdir / usr / share / doc / MySQL-zrm
mv * / usr / share / doc / MySQL-zrm
mkdir / var / lib / mysql-zrm
touch / etc / mysql-zrm / mysql-zrm-release

The executable files were moved to / usr / bin , the configuration files were placed in / etc / mysql-zrm and the executable pages ( mysql-zrm , mysql-zrm-reporter and mysql-zrm-scheduler ) so If you are unsure about how to use these implementations, you can run:

man mysql-zrm

man mysql-zrm-reporter

man mysql-zrm-scheduler

3, Basic configuration

The main configuration file is /etc/mysql-zrm/mysql-zrm.conf . In this file, it is necessary to specify the user and password of the user.

vi /etc/mysql-zrm/mysql-zrm.conf

user = "root"
password = "yourrootsqlpassword"

If no user and password are specified, mysql-zrm assumes that the values ​​are default to all other settings. With this configuration, mysql-zrm will perform a backup of all databases in raw format and it will keep these backups forever without sending email notifications.

The raw format means that it backs up the database as binary files that can be copied back to the database in case of data loss, but it can cause problems if you copy these files. between different MySQL versions.

A copy of the raw format is a logical format to create a text file with a simple SQL Server output in your database. These SQL dumps can be saved in almost all MySQL versions and you can do it manually.

If you want to get e-mail notifications to your email address example@example.com then you must add /etc/mysql-zrm/mysql-zrm.conf :

vi /etc/mysql-zrm/mysql-zrm.conf

mailto=example@example.com

If you want to create backups in logical format and want to keep them for a week (instead of forever), add the following to /etc/mysql-zrm/mysql-zrm.conf :

vi /etc/mysql-zrm/mysql-zrm.conf

# giá trị được phép cho backup-mode "raw" and "logical"
backup-mode = logical
retention-policy = 7D

If you want to create a backup for a secondary MySQL, add the following line to /etc/mysql-zrm/mysql-zrm.conf :

vi /etc/mysql-zrm/mysql-zrm.conf

replication = 1

This way they will make backups of important files for MySQL secondary.

If you just want to back up the database exampledb and anotherexampledb instead of all databases, add them to /etc/mysql-zrm/mysql-zrm.conf :

vi /etc/mysql-zrm/mysql-zrm.conf

databases = exampledb anotherexampledb

If you just need to backup the text tables, users and pages from the exampledb database, add them to /etc/mysql-zrm/mysql-zrm.conf :

vi /etc/mysql-zrm/mysql-zrm.conf

tables = text user page
database = "exampledb"

Note : all-databases settings , databases , and tables / databases are able to go back and forth.

The default /etc/mysql-zrm/mysql-zrm.conf for most comments to explain all configuration options. I am currently using those settings:

vi /etc/mysql-zrm/mysql-zrm.conf

user = "root"
password = "yourrootsqlpassword"
mailto = "example@example.com"
backup-mode = logical
retention-policy = 7D
# all-databases = 1 is optional, as it is the default setting
all-databases = 1

4, Examples

With mysql-zrm, you can maintain multiple backups, called a backup sets collection, such as daily, weekly backup and backup of your commercial operating system (osCommerce) database, backup for your organization. vBulletin database .

Now let's create your first backup (we created in the dailyrun backup setup):

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

It will immediately back up all databases to the / var / lib / mysql-zrm / dailyrun directory and will also create the / etc / mysql-zrm / dailyrun directory.--backup-level 0 means: perform a full backup (if you can perform incremental backups ( --backup-level 1 ) but this problem will be said later and now we will do backups)

If you allow email notifications, you will receive an email with the backup status at that time. But you can also create a backup status report with the following command:

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

Result of executing the command line:

backup_set backup_date backup_level backup_status comment
-------------------------------------------------- --------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 Backup succeeded ----

You can also create some other reports with the following statements:

mysql-zrm-reporter --where backup-set = dailyrun --show backup-method-info
mysql-zrm-reporter --where backup-set = dailyrun --show backup-retention-info
mysql-zrm-reporter --where backup-set = --show dailyrun backup-performance-info
mysql-zrm-reporter --where backup-set = --show dailyrun restore-full-info
mysql-zrm-reporter --where backup-set = --show dailyrun restore-incr-info
mysql-zrm-reporter --where backup-set = dailyrun --show replication-info

Try them to see what information they give you.

Now we want to create a daily and weekly backup to run automatically by the system (no interaction required). Set up these backups as follows:

mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --backup-level 0
mysql-zrm-scheduler --add --interval weekly --backup-set weeklyrun --backup-level 0

(With the second command, we create a new backup called weeklyrun .)

Now check the backups recorded in the program:

mysql-zrm-scheduler --query

Get out

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
0 3 * * * / usr / bin / mysql-zrm --action backup --destination / var / lib / mysql-zrm --backup-set dailyrun --backup-level 0
0 4 * * * / usr / bin / mysql-zrm --action --destination / var / lib / mysql-zrm
0 * * 0 / usr / bin / mysql-zrm --action backup --destination / var / lib / mysql-zrm --backup-set weeklyrun --backup-level 0

As you can see, daily backup is run daily at 03:00 and weekly on Sunday at 2:00 PM.
(BTW instead

mysql-zrm-scheduler --query

You can run

crontab -l

to get such information)

If you want to start backing up daily at another time, for example, 13:35, you can do it as follows:

mysql-zrm-scheduler --add --interval daily --backup-set dailyrun --start 13:35 --backup-level 0

To remove a backup that was recorded in the program, you can do the following:

mysql-zrm-scheduler --delete --interval weekly

It will remove the weekly backup that is run at 2:00 pm on Sundays. If you want to backup daily recorded in the program at 13: 35h, do the following:

mysql-zrm-scheduler --delete --interval daily --start 13:35

But you can also run

crontab -e

to make it easier by mysql-zrm-scheduler .

5, Custom reports and HTML

In section 4, I mentioned what types of reports are available. But you can also create custom reports, for example, you can specify which columns or information you want to see. For example:

mysql-zrm-reporter --fields backup-set, backup-date, backup-level, backup-status - where backup-set = dailyrun

Create a report for setting up backup dailyrun shown in backup-set , backup-date , backup-level and backup-status columns:

backup_set backup_date backup_level backup_status
-------------------------------------------------- --------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 Backup succeeded
dailyrun Tue 26 Sep 2006 07:58:08 PM CEST 0 Backup succeeded
dailyrun Tue 26 Sep 2006 07:58:31 PM CEST 0 Backup succeeded
dailyrun Tue 26 Sep 2006 08:24:04 PM CEST 0 Backup succeeded

List of all available columns:
http://mysqlbackup.zmanda.com/index.php/What_information_can_be_obtained_from_a_backup_report%3F

mysql-zrm lets you create HTML reports. Admit that you have a web server (eg Apache) installed on your system with the root / var / www link . Now run

mysql-zrm-reporter -show backup-status-info -where backup-set = dailyrun --type html --output /var/www/backup-status-dailyrun.html

Will create the HTML file backup-status-dailyrun.html in your / var / www directory (you can access the browser key, for example http://server1.example.com/backup-status-dailyrun.html)

Backup and restore MySQL with mysql-zrm on Debian Sarge Picture 1

6, Validate your backups

Now we will check the status of the dailyrun backup setup. Do as follows:

mysql-zrm --action verify-backup --backup-set dailyrun

Get out:

Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: Verification successful

(You can skip the first line warning, it's not serious at all)

Next we check a specific backup inside the backup dailyrun file. First run

mysql-zrm-reporter -show restore-full-info -where backup-set = dailyrun

to find out which backups are available:

backup_set backup_date backup_level backup_directory
-------------------------------------------------- ---------------------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195747
dailyrun Tue 26 Sep 2006 07:58:08 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195808
dailyrun Tue 26 Sep 2006 07:58:31 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195831
dailyrun Tue 26 Sep 2006 08:24:04 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926202404

As you can see, there are four backups in our dailyrun backup setup. If you want to check the last backup in the directory / var / lib / mysql-zrm / dailyrun / 20060926202404 . Do the following:

mysql-zrm --action verify-backup --backup-set --no-quiet dailyrun --source-directory / var / lib / mysql-zrm / dailyrun / 20060926202404

The results are as follows:

Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: Verification successful

7, Restore data

Let's assume our database is disputed and we have lost data. And how to recover data from MySQL backups. First we run:

mysql-zrm-reporter -show restore-full-info -where backup-set = dailyrun

to find available backups:

backup_set backup_date backup_level backup_directory
-------------------------------------------------- ---------------------------------------
dailyrun Tue 26 Sep 2006 07:57:47 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195747
dailyrun Tue 26 Sep 2006 07:58:08 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195808
dailyrun Tue 26 Sep 2006 07:58:31 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926195831
dailyrun Tue 26 Sep 2006 08:24:04 PM CEST 0 / var / lib / mysql-zrm / dailyrun / 20
060926202404

If you want to restore from the latest backup in / var / lib / mysql-zrm / dailyrun / 20060926202404 :

mysql-zrm --action restore --backup-set dailyrun --source-directory / var / lib / mysql-zrm / dailyrun / 20060926202404

The results are as follows:

Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: Restored database from raw backup: egroupware
INFO: Restored database from raw backup: mysql
INFO: Restore done in 14 seconds.
MySQL server has been shutdown.Hãy chạy sau khi kiểm tra.

The data has been restored but the MySQL server has been disabled. So we have to start it with:

/etc/init.d/mysql start

8, Backup measures

So far, we have a global configuration in /etc/mysql-zrm/mysql-zrm.conf that applies to all backup files. Now suppose that we have a set of osCommerce backups for the osCommerce database, the vBulletin backup set is for the vBulletin database.

It is completely unlike the backup of all our data because the backup only includes the osCommerce database with the osCommerce backup set and is similar to the vBulletin backup file. We can solve this problem as follows:

Each backup set has its subdirectory in the / etc / mysql-zrm directory, for example: backup file osCommerce has the directory / etc / mysql-zrm / osCommerce . Now, whenever mysql-zrm is run, it first checks the global configuration in /etc/mysql-zrm/mysql-zrm.conf and then checks the current backup file directory for the mysql- file. zrm.conf whose settings override the global settings in /etc/mysql-zrm/mysql-zrm.conf , for example: if the current backup set is osCommerce then mysql-zrm will read the configuration first from /etc/mysql-zrm/mysql-zrm.conf and then configure from /etc/mysql-zrm/osCommerce/mysql-zrm.conf .

If you only want to copy the osCommerce MySQL database in the osCommerce backup set, we put it in /etc/mysql-zrm/osCommerce/mysql-zrm.conf :

vi /etc/mysql-zrm/osCommerce/mysql-zrm.conf

databases = osCommerce

9, Remove the old Backup

If you do not have a retention-policy set in the mysql-zrm configuration, the MySQL backups are kept forever meaning that your hard disk will be full after a while. However, you can simply remove old backups simply by deleting the backup folder. For example, if you have a backup in / var / lib / mysql-zrm / dailyrun / 20060926195831 and don't need it anymore, you can delete it as follows:

rm -fr / var / lib / mysql-zrm / dailyrun / 20060926195831

10, Record

mysql-zrm writes the .log file like /var/log/mysql-zrm/mysql-zrm.log .

11, Record backup increases

mysql-zrm can perform incremental write-up despite some problems with it. First I have to configure MySQL to write its bin-logs to / var / lib / mysql (by editing /etc/mysql/my.cnf ) because that's where mysql-zrm expects them. Later

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1

seems to work, but

mysql-zrm --action parse-binlogs --source-directory = / var / lib / mysql --backup-set dailyrun

generate an error:

Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
ERROR: không thể mở tập tin chỉ mục / var / lib / mysql / chỉ Không có tập tin hay directory

That's why I think it would be better to fully backup backups at this time instead of incremental backups.

12, Remote backups

Mysql-zrm allows you to perform backups from remote MySQL servers on the network. However this may also cause some problems for me.

12.1, First try

In the first step, I want to make a backup (in raw format) of a MySQL server on a remote SuSE 10.0 system. I compose /etc/mysql-zrm/mysql-zrm.conf and set user, password, server name. Then run

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

The following results:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
WARNING: Binary logging is off. Incremental và Backup logical sẽ không làm việc.
INFO: backup-set = dailyrun
INFO: backup-date = 20060927095528
INFO: backup-date-epoch = 1159343728
INFO: mysql-version = 4.1.13
INFO: backup-directory = / var / lib / mysql-zrm / dailyrun / 20060927095528
INFO: backup-level = 0
WARNING: Tập tin cơ sở dữ liệu là trống và đó sẽ không được lưuup
WARNING: Database tmp is empty and thì sẽ không được lưuup
ERROR: Output của câu lệnh: 'mysqlhotcopy' is
DBI connect ('; host = 192.168.0.163; mysql_read_default_group = mysqlhotcopy', 'root', .) failed: Client does not support giao thức xác thực đã yêu cầu của máy chủ Consider upgrading MySQL client at / usr / bin / mysqlhotcopy line 182
ERROR: mysqlhotcopy command did not succeed.
- used = root --password = ***** --host = 192.168.0.163 db_ispconfig mysql "/ var / lib / mysql-zrm / dailyrun / 20060927095528"> / tmp / 4Z75iIAeo5 2 > & 1
Return value is 65280
INFO: backup-status = Backup failed
INFO: Backup failed
ERROR: / usr / bin / mysql-zrm did not finish successfully

I guess this happened because MySQL versions are different on both systems.

12.2, Second attempt

On the second try, I wanted to create a backup (like in raw format) of a MySQL database on a remote Debian Sarge server. Both local and remote systems have the same MySQL version. I run

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

and there are errors below:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
INFO: backup-set = dailyrun
INFO: backup-date = 20060927100653
INFO: backup-date-epoch = 1159344413
INFO: mysql-version = 4.1.11-Debian_4sarge7-log
INFO: backup-directory = / var / lib / mysql-zrm / dailyrun / 20060927100653
INFO: backup-level = 0
WARNING: Tập tin cơ sở dữ liệu là trống và đó sẽ không được lưuup
ERROR: Output của câu lệnh: 'mysqlhotcopy' is
Không thể mở thư mục / / var / lib / mysql / web34_db1 ': No such file or directory at / usr / bin / mysqlhotcopy line 293.
ERROR: mysqlhotcopy command did not succeed.
Command used is mysqlhotcopy - user = root --password = ***** --host = 192.168.0.110 mysql web34_db1 "/ var / lib / mysql-zrm / dailyrun / 20060927100653"> / tmp / yxFsViAlbm 2 > & 1
Return value is 512
INFO: backup-status = Backup failed
INFO: Backup failed
ERROR: / usr / bin / mysql-zrm did not finish successfully

It seems to have a directory error / var / lib / mysql / web34_db1 ( web34_db1 is one of the databases on the remote system) in the current system. So I created it:

mkdir / var / lib / mysql / web34_db1

and run

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

this time it worked, but I doubt that this is how it is supposed to be .

12.3, Third attempt

At this point, I tried to create a backup from the same remote Debian Sarge system as before but in logic instead of raw format. I run

mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 0

and has the following error:

Logging to /var/log/mysql-zrm/mysql-zrm-scheduler.log
Sử dụng một số nguyên không được xác định trong tập tin concatenation (.) Hoặc chuỗi tại / usr / bin / mysql-zrm line 1305.
INFO: mysql-zrm-version
WARNING: Binary logging is off. Incremental và Backup logical sẽ không làm việc.
INFO: backup-set = dailyrun
INFO: backup-date = 20060927095501
INFO: backup-date-epoch = 1159343701
INFO: mysql-version = 4.1.13
INFO: backup-directory = / var / lib / mysql-zrm / dailyrun / 20060927095501
INFO: backup-level = 0
ERROR: Binary logging is off. Logical backup cannot be done
INFO: backup-status = Backup failed
INFO: Backup failed
ERROR: / usr / bin / mysql-zrm did not finish successfully

But now it works normally because with MySQL remote logical backups, it needs to be configured with SSL as described above

mysqld --ssl --help

060927 12:26:09 [ERROR] mysqld: unknown option '--ssl'

Write in the MySQL utility

mysql -u root -p

and run

SHOW VARIABLES LIKE 'have_openssl';

and have the same result:

+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| have_openssl | NO |
+ --------------- + ------- +
1 row trong đặt (0.01 sec)
Does not support SSL .

13, Links

• mysql-zrm : http://mysqlbackup.zmanda.com
• mysql-zrm Manual : http://mysqlbackup.zmanda.com/index.php/Zmanda_Recovery_Manager_for_MySQL
• Zmanda : http://www.zmanda.com
• MySQL : http://www.mysql.com
• Debian : http://www.debian.org