MySQL Backup and Recovery for LiveCycle ES

This article outlines one method for performing a backup and recovery on a MySQL database. This article is not intended as a comprehensive technical guide of specific database backup and recovery features, but is meant to be an outline of basic MySQL commands needed for a database administrator to create a reliable database backup strategy for LiveCycle ES.

Caution: As with any other aspect of your LiveCycle ES implementation, your backup and recovery strategy needs to be developed and tested in a development or staging environment before being used in production in order to ensure that the entire solution is working as expected with no data loss.

Use MySQLAdmin, or modify the ini files in Windows, to configure your MySQL database to run in binary log mode. (See MySQL binary logging.) A hot backup tool for MySQL is also available from InnoBase software. For more information about hot backup by Innobase, see Innobase Hot Backup.

You can use the mysqldump utility to obtain the full database backup. Full backups are needed but they are not always convenient. They produce big backup files and take time to generate. To do an incremental backup, ensure you start the server with the log-bin option as described in the previous section. Each time the MySQL server restarts, it stops writing to the current binary log, creates a new one and from then on, and the new one will become the current one. You can force a switch manually with the FLUSH LOGS SQL command. After the first full backup, subsequent incremental backups are done by using mysqladmin with the flush-logs command which will create the next log file.

For more information about MySQL database backup and recovery, see Backup Strategies.

The information provided in this article is a summary of the steps required to perform the tasks described. For detailed information, consult the manufacturer's documentation provided with your database.

The MySQLDump is located in the MySQL\bin bin directory, for example, on Windows it is located in the C:\Program Files\MySQL\MySQL Server 5.0 directory. To get a list of the options your version of mysqldump supports, type:

mysqldump --help.

Consult the MySQL Reference manual for more information on MySQLDump.

The following example illustrates how to obtain a fullbackup backup of a MySQL adobe database.

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysqldump -u root -p --single-transaction --flush-logs -q -e --master-data=2 adobe > C:\lcbackup\mysql\mysqldump.2008-01-30-16.17.sql

Where:

• -u root - Specifies the user name as root when connecting to the server.
• -p - requires mysql to prompt for the password of the user specified in the user parameter.
• --single-transaction - This option issues a BEGIN SQL statement before dumping data from the server. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail. These commands will generally not be executed in normal LiveCycle ES processing.
• -q - This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.
• -e - This option forces mysqldump to use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
• --flush-logs This option is used to flush the server log files before starting the dump
• --master-data=2 - This option writes the binary log filename and position to the output as a SQL comment. An example of this information is shown below.

-- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='adobe.000001', MASTER_LOG_POS=77414;

This means that all data changes logged in mySQL binary logs older than adobe.000001 are present in the .sql file, and all data changes logged in adobe.000001 or newer are not present in the dump .sql file.

Full backups are needed but they are not always convenient. They produce large backup files, and take time to generate. To do incremental backup, make sure that you start the server with the log-bin option as described in the previous section. Every time MySQL server restarts, it stops writing to the current binary log, creates a new one and from then on, the new one will become the current one. Such a switch can be forced manually with the FLUSH LOGS SQL command. After the first full backup, subsequent incremental backup is done by using mysqladmin with the flush-logs command which will create the next log file:

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysqladmin -u root -p flush-logs

Continue with the full backup example, all changes between the first full backup and the next incremental backup are now in the adobe.000002 file. You must store this incremental backup file together with your full backup file in order to restore properly. You will need all incremental log files from the time of the full backup to restore properly.

To restore the last full backup we have, you can import the sql file using mysql as shown below. You must first create the adobe database if it no longer exists:

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user root -p adobe < c:\lcbackup\mysql\mysqldump.2008-01-30-16.17.sql

If you receive the following error: ERROR 2006 (HY000) at line 3160: MySQL server has gone away. It may be because your network packet size is not set large enough to handle all of the LiveCycle data. If this is the case, you will need to set your MySQL max packet length to a higher value. To do this, modify the following value in the MySQL my.ini or my.cnf file: max_alllowed_packet=25M

To apply incremental backups, you need to restore the log file that you have backed up and use mysqlbinlog utility to create readable output for mysql to consume as shown below:

D:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog c:\lcbackup\mysql\adobe.000002 | mysql --user root -p adobe

There is a bug in MySQL version 5.0.18 (InnoDB Engine) that writes an incorrect sql_mode value to the binary file. This has been fixed in later versions of MySQL. A workaround to this problem follows:

1. Convert the binary log file to a text .sql file by executing the following command on the command line: D:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog C:\LCBackup\MySQL\adobe.000002 > C:\LCBackup\MySQL\adobe.000002.sql

2. Change the line that sets the session sql mode from: SET @@session.sql_mode=1344274432; to SET @@session.sql_mode="NO_AUTO_VALUE_ON_ZERO";

It may be necessary to change the mode to another appropriate value for your specific MySQL environment. See the following MySQL documenation for more information on sql_modes: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

3. Execute sql file: D:\Program Files\MySQL\MySQL Server 5.0\bin>mysql --user root -p adobe < C:\LCBackup\MySQL\adobe.000002.sql

The file should now run correctly without error.

(MySQL 5.1.x only) If you see the following error message during recovery of your MySQL 5.1.x database, you must add the --force parameter to your command line.

"ERROR 1064 (42000) at line xx: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1"

Refer to the MySQL database documentation for more information about using the --force parameter.

This section provides references to articles relevant to database administration and back up tasks.

Note: The external web sites referenced here were valid at the time this article was published.

There are several good reference guides available from the MySQL developer's web site located at http://dev.mysql.com. You can search for the following documents or articles from this site:

• MySQL 5.1 Reference Manual
• MySQL Point-in-Time Recovery
• "Recovering from Crashes" article

In addition, the following documents provide valuable information:

• InnoDB Hot Backup Manual available from www.innodb.com
• "How to backup and import a MySQL InnoDB database" at http://blog.spikesource.com

• Ntbackup command reference for Windows Server 2003
• Utilizing the Built-in Windows Backup (ntbackup.exe) for Windows at www.fishbrains.com