ZZEE Active SQL Backup v 0.9.2
ZZEE Active SQL Backup v 0.9.2© Copyright 2001 ZZEE. http://www.zzee.com
This program is a shareware. See license.txt for the license agreement. By using this software you accept all the terms of the license agreement.
This guide is written in assumption that you have a telnet access to the host where you are going to use Active SQL Backup. By convention "shell>" means your telnet shell prompt. Also a little knowledge of Perl is required.
Active SQL Backup can do incremental or full backups of MySQL database as often as needed. It is suitable both for the clients of web hosting companies who can not get access to MySQL update logs and for the host administrators. Backup syntax is:
shell> ./zmdb.pl -b
It stores backup files in BACKUP_DIR, specified in user_settings.txt, named like YYYY_MM_DD_hh_mm_ss.sql[.gz]. The backup files consist of series of SQL INSERT statements and may contain DELETE statements. The database scheme is stored in file database_name.create.sql. If gzip is available, then Active SQL Backup automatically uses it to compress stored data. The program can automatically send backed up files by email or upload to the other computer by FTP.
If the system fails, Active SQL Backup can restore the database. Restore syntax for incremental backups is:
shell> ./zmdb.pl -r
If you make full backups, restore syntax is the following:
shell> ./zmdb.pl -e < BACKUP_DIR/database_name.create.sql shell> ./zmdb.pl -e < BACKUP_DIR/YYYY_MM_DD_hh_mm_ss.sql &
The first line is needed to restore database structure, and the second is the *most recent* data file. You may need to "gunzip" it before supplying to restore.
You can use the program to dump database structure
shell> ./zmdb.pl -s > structure_dump_file
or to process queries. The latter feature is useful if you need to process queries regularly, for example, to update membership status of the users of your online service. The syntax to execute SQL queries is:
shell> ./zmdb.pl -e < some_sql_file
Where format of some_sql_file is the same as with Mysql utility: SQL statements are separated by semicolon. Note that if you pass SELECT queries to Active SQL Backup, then no result will be returned.
Syntax summary is:
zmdb.pl [SINGLE_OPTION] -a --advice suggest database modifications and tables_config.txt -b --backup backup database structure and data -e --execute execute SQL queries [from STDIN] -h --help print this screen -r --restore restore database structure and data -s --structure dump database structure [to STDOUT]
Consider an internet exam practicing service which must keep a log of all students' exams. If you perform *daily* backups, then in a year full backup files are on average 365 times bigger than incremental backup files and full backup takes 100-365 times longer. When there are much data, backup may take even an hour to complete. Can you afford to shut down your system for an hour every day? Even if you perform *weekly* backups, incremental one will be up to 52 times faster than a full one in a year. And it would take about 1-2 minutes, where full backup would take one hour. Thus with incremental backup you can make backups more frequently, and if the system will crash, the less data you will lose.
There is also another consideration to take into account. If your web site is on a shared server, then a web hosting company policy may not allow a task to consume much system resources. And your program may be simply terminated by an admin if it takes too much. With incremental backup there are usually much less data to save, so it takes less resources.
There are several things that you should be aware. Almost all of them are described elsewhere in this manual.
Active SQL Backup is focused on incremental backup. Mysqldump is a very good utility, but primarily aimed at full database backup. Therefore incremental backup can not be done using *only* Mysqldump, merely because storage of "mark-up" values is needed. Also certain things like "incremental backup with update" can't be done using Mysqldump, because index duplication on insert may occur. And on some systems Mysqldump might be missing. Active SQL Backup is not a direct replacement of Mysqldump, because it lacks certain features of Mysqldump, but can do full backups as Mysqldump.
Mysql is a client utility that can connect to MySQL database and perform SQL queries. Active SQL Backup can also perform SQL queries by itself, but lacks many features of mysql utility, such as interactive mode. Mysql utility might be missing on some computers. If it is available, Active SQL Backup uses mysql utility for restore.
To use Active SQL Backup as a query processor, use -e option like:
shell> ./zmdb.pl -e < some_sql_file
Any table that is backed up incrementally should have a description in tables_config.txt file. Also it should have a field which values might be used to separate records that are already have written into backup files from those that are needed to be written. By convention, we call such field as "mark" or "mark-up" field.
TIMESTAMP field is suitable to serve as mark-up field almost in any situation. Its advantage is that MySQL fills this field with current time even if it is not mentioned in SQL INSERT or UPDATE query. In particular it means that SQL queries in your application should not be changed to support incremental backup, provided that you use full SQL syntax, i.e. avoid constructions like "select * from ..." or "insert into table values ...".
Field of the other type can work too, depending on your application, the only restriction that its values should support relation ">". Please see "WHERE clause for incremental backups".
For incremental backups Active SQL Backup creates WHERE clause as follows:
WHERE $tables_mark_field > '$tables_mark_value'
Where tables_mark_field and tables_mark_value are taken from "tables_config.txt" file. This clause is used on making SELECT query to choose the records that are to be backed up. tables_mark_value is overwritten each time the program does the backup.
Active SQL Backup uses 2 modes of incremental backup, which are supported at table level: incremental_backup_just_insert and incremental_backup_with_update. Also you can perform a full backup of any table in the database. And any table can be omitted from backup. These types are described below.
Note that for *any* table in the database, despite if it is not skipped from backup or not, its structure is backed up.
Table structure is backed up. Data is backed up as a series of INSERT statements. This is suitable for data that become read-only after they have been added to a table. A good example of such data is a web site log or any other log. A table should have a mark-up field, TIMESTAMP or AUTO_INCREMENT should work.
Table structure is backed up. Data is backed up as a one DELETE and one INSERT statement per a record. This allows the data to be edited afterwards and ensures that if the data will be updated, the updates will not be lost due to index duplication. A table should have a PRIMARY KEY (to update target record) and a mark-up field, TIMESTAMP is the best choice.
Table structure is backed up. Data is backed up as a series of INSERT statements. Before performing INSERT statements, one DELETE statement to clear all previous records is issued.
Table structure is backed up anyway. Data is not backed up. This is good for temporarily tables.
Note that if the records might be deleted from the table, either perform its full backup, or if you really want to do the incremental backup, then re-design your application to prevent records from deletion, eg. by introducing 'deleted' flag (do you remember DBFs?). The other way is to log delete queries, but in this case logging all queries that modify data is needed, like it is done in mysqld.
For *each* table that needs to be backed up incrementally, you need to provide two lines of code describing how to process incremental backup. Those tables that are to be backed up in full, may be omitted from tables_config.txt, they will be processed automatically. The other way is to explicitly set 'whole_table_backup'. If you need to exclude certain table from backup, you need to specify its name in @tables_to_skip of user_settings.txt file. If the table name is specified there it will be excluded from backup despite if it is specified in tables_config.txt below or not. When you are done with this file, make its backup copy *before* you process any backups. The syntax of table description is the following (2 lines per a table):
my @table_name = ($increment_mode, $field_name, $field_value);
$tables{'table_name'} = \@table_name;
Note: table name in $tables{'table_name'} is case sensitive, just like filenames in UNIX.
$increment_mode takes one of three values: 'incremental_backup_just_insert', 'incremental_backup_with_update' or 'whole_table_backup'.
$field_name is the name of a "mark-up" field from the table, which will serve as increment indicator.
$field_value is the value of the $field_name, from which the next backup will starts. '0' is the best choice in most situations as initial value of $field_value. When the program is successfully run, it overwrites the value of $field_value [and this file accordingly], so the next backup starts from a new value.
Note that Active SQL Backup can generate file "tables_config.txt" for you. Use "-a" option.
Consider a table named "phone_book" with a field named "last_modified" of type timestamp, defined as follows: " last_modified timestamp(14) default '0' ". Because information could be updated at any time, then "incremental backup with update" is the choice in this situation. Description for 'tables_config.txt' will be like:
my @phone_book= ('incremental_backup_with_update', 'last_modified', '0');
$tables{'phone_book'} = \@phone_book;
Consider a table named "payments" which tracks customer payments with a field named "payment_id" of type INT UNSIGNED, defined as follows: " payment_id int unsigned not null default '0' auto_increment...primary key (payment_id) ". This field is AUTO_INCREMENT and PRIMARY KEY of its table. When a record has been inserted into this table, it becomes read-only and is never updated. "Incremental backup just insert" mode is the best choice for such a table. Its description for 'tables_config.txt' will be like:
my @payments = ('incremental_backup_just_insert', 'payment_id', '0');
$tables{'payments'} = \@payments;
If you need to perform only full backups then no special skills are required to complete an installation.
The distribution of Active SQL Backup is a file named "zmdbVERSION.tgz", where VERSION is replaced by a version number of the program. First you need to create a directory on your host where you will place the Active SQL Backup files.
shell> mkdir some_dir
Then place a distribution file there ("zmdbVERSION.tgz"). To unpack the distribution you need "gunzip" and "tar" utilities. They can be called in a single command like this:
shell> gunzip < zmdbVERSION.tgz | tar xvf -
After that you need to change the rights of the files to make sure that nobody except you can read or execute them. This is made with "chmod" command.
shell> chmod 0600 * shell> chmod 0700 zmdb.pl
To make Active SQL Backup work you need to specify the database name, database login, a password, folder where your backup data will go, etc. All these settings are defined in "user_settings.txt" file. This file contains in-place help and comments for each setting.
It is reasonable to leave $send_data_by_email disabled ('0'). After you perform the first backup, then you are welcome to turn $send_data_by_email on. Path for every utility might be found using "whereis" command. If you are not sure if any path is correct, then simply set an empty string ("") to it.
Zmdb.pl is a Perl file, and Perl is required to execute this script. Shell finds the path to the executable file via the first line of the zmdb.pl file, so the first line must point to the correct path of Perl. You should adjust this line. You can find Perl path by using "whereis perl" command.
If you are going to perform a full backup, then you don't have to set anything more. Now you can do backups by calling zmdb.pl from shell or from cron. To call it from shell use the following syntax.
shell> ./zmdb.pl -b &
To call it from cron you don't have to put an ampersand ("&") at the end. Calling from cron is the preferred way of doing backups. Also by default cron will send the output of the program to your email address.
If you need to perform an incremental backup and your database needs to be restructured (see below), then you need to make one full backup anyway.
These steps require certain level of SQL knowledge and should be performed by qualified persons only. If you are not sure if you can complete these steps by yourselves, you can hire us for the installation. More information is available at http://www.zzee.com/zmdb/ .
To do incremental backup, each table in a database should have fields of certain types. The program can suggest the database restructure needed and generate tables_config.txt file for you. This operation is safe, as nothing is changed at this step, and all files generated are just for reference.
shell> ./zmdb.pl -a
Active SQL Backup makes 2 files: "structure_change.sql" and "structure_rollback.sql", they go to the backup folder. Both are SQL statements needed to perform structure change of the database and restore the original structure.
The program also makes tables_config.txt.suggested file that you can use to replace tables_config.txt, which is needed for incremental backup.
When structure_change.sql file is generated, it needs to be manually checked and corrected, because it is built in a straightforward way in accordance with the current structure only. You know the details of your application better and can fine tune the changes needed. If you modify structure_change.sql, then modify structure_rollback.sql accordingly. To assign incremental mode for tables, please edit tables_config.txt.suggested file, not tables_config.txt.
Observe SQL queries where tables, which need to be restructured, are involved. And skip the queries where only the tables, which don't need a restructure, are used.
If you have SQL constructs like "select * from ..." or "insert into table values ...", which use field names implicitly, then adding new field to a table may cause MySQL or application errors. And you will have to rewrite these queries in your application if you want to enable incremental backup. Don't take any further steps unless your SQL queries are changed.
However, if you use full SQL statements like "select field1, field2 from ..." or "insert into table (field1, field2) values ('value1', 'value2')", that is, use field names explicitly, then adding a new field to a table won't cause any harm to your application. In this case you are ready to enable incremental backup.
shell> ./zmdb.pl -e < BACKUP_DIR/structure_change.sql
Where BACKUP_DIR is the backup directory which you set in user_settings.txt.
shell> cp -f tables_config.txt.suggested tables_config.txt
Editing tables_config.txt.suggested instead of tables_config.txt keeps a copy of your original file, as tables_config.txt is overwritten each time the backup is performed.
shell> ./zmdb.pl -b &
You can do it either from shell or from cron. Note that the first incremental backup will be again the full backup of your database, as values of mark fields in tables_config.txt are set initially to '0'.
Usually, the largest files occur on the first backup which is full backup anyway. So maybe it is better to disable this feature on first run, and enable if needed later.
Both incremental and full backups are performed using the same command line syntax:
shell> ./zmdb.pl -b
The difference between modes is specified in user_settings.txt file, and incremental backup require tables tables_config.txt to be set. It is possible that some tables of the database are backed up in full, while others are backed up incrementally. All tables that are omitted from tables_config.txt are automatically backed up in full.
Please see backup modes and how to pick up backup mode for a table.
Each time you are running backup, the program creates two files in BACKUP_DIR folder:
The program can be used as a query processor. This is useful if you need to modify data or structure of your database or perform data replication or restore. The syntax is:
shell> ./zmdb.pl -e < some_sql_file
This feature is also useful if you need to process queries regularly, for example, to update membership status of the users of your online service everyday.
Format of some_sql_file is the same as with Mysql utility: SQL statements are separated by semicolon.
Note that if you pass SELECT queries to Active SQL Backup, then no result will be returned.
Because the program can do incremental backup you can use it to perform incremental data replication. That is you can synchronize data between two databases on a regular basis. This is useful in the following situations:
To perform data replication do incremental backup on a main server and query processing on a remote server, where an input of the remote server is an output of the main server:
main shell> ./zmdb.pl -b remote shell> ./zmdb.pl -e < YYYY_MM_DD_hh_mm_ss.sql
To facilitate replication the program can transfer data via FTP or email.
There are situation when you require database structure dump, e.g. when you need to generate queries or forms based on database structure. Of course, structure is needed to perform restore and replication.
shell> ./zmdb.pl -s > structure_dump_file
Structure_dump_file consists of SQL CREATE TABLE statements.
Active SQL Backup can upload backed up files via FTP to any other computer. To enable this feature you should have Perl Net::FTP module installed. You can find this module at CPAN (http://www.perl.com/CPAN), it is a part of "libnet" module bundle. If this module is not available on your host, and if you specify $send_data_by_ftp, then nothing happens, but warning message is released. You also should specify remote host name, login, etc. All these settings are set in user_settings.txt.
The files are uploaded right after they are backed, when you call "zmdb.pl -b".
Note that FTP communication *is not secure* and could be intercepted by the 3rd party.
Note that if you need to upload data by FTP, then enable "gzip compression", because the network data transfer will be significantly less.
Active SQL Backup can send backed up files via email to any other computer. To enable this feature you should specify sendmail path, email address to send to, etc. All these settings are set in user_settings.txt.
The files are emailed right after they are backed, when you call "zmdb.pl -b".
Note that email communication *is not secure* and could be intercepted by the 3rd party.
Note that if you need to send data by email, then enable " gzip compression", because the network data transfer will be significantly less.
Note that sometimes the data might be too long, so be careful with setting $send_data_by_email flag to '1'. In fact, the largest files occur on the first backup, which is full backup anyway. So we suggest that you do the first backup with $send_data_by_email set to 0 (zero) and manually transfer the first backup data. Later you can change $send_data_by_email to 1 (one).
While MySQL allows dollar sign '$' in table names, in Active SQL Backup it won't work if you need to backup a table incrementally, because '$' is a special symbol in Perl. Such tables could be backed up just in full.
For efficiency Active SQL Backup uses multiple value lists with INSERT statements for dumped data for MySQL servers from 3.22.5. While this is good for backup/restore, however if you want to use backed up data to transfer data to other computer with MySQL server older than 3.22.5, this won't work. To work around set $use_fast_mode to 0 (zero).
Active SQL Backup uses "IF EXISTS" on DROP TABLE for MySQL servers from 3.22. While this is good for backup/restore, however if you want to use backed up data to transfer data to other computer with MySQL server older than 3.22.5, this won't work. To work around set $use_if_exists to 0 (zero).
If you use the program beyond the first 30 evaluation days, you must register your copy, i.e. pay a registration fee. Registered users get email tech support within 3 months and can order the installation services from us. Please read license.txt file for more information.
Please register here:
http://www.zzee.com/zmdb/
Please read FAQ at our web site: