Backup and Restore Mysql with Mysql-zrm on Debian Sarge

Understanding backup and restore mysql with mysql-zrm on debian sarge is easier when the main points are organized clearly. This guide brings together the essential details and practical considerations.

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:

How Backup and Restore Mysql with Mysql-zrm on Debian Sarge Works

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. You have now 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 users and password of users.

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 If 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. When needed 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

When needed 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

When needed 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) When needed 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. when needed 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)

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. when needed 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

When needed 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. You have now 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

FAQ

What is the main benefit of backup and restore mysql with mysql-zrm on debian sarge?

The main benefit is a clearer understanding of the topic and a practical way to apply the information covered in this guide.

What should I check before using backup and restore mysql with mysql-zrm on debian sarge?

Confirm compatibility, review the required settings, protect important data, and use the latest supported version whenever possible.

What should I do if the result is different?

Repeat the steps carefully, verify permissions and version differences, and consult the product's official support documentation for changes not shown in the article.