Chapter 16. Backup

Table of Contents

16.1. Introduction
16.2. Backup Project
16.3. Advanced Options
16.3.1. Backup Execution Method
16.3.2. Output File Options
16.4. Schedule

16.1. Introduction

This section shows you how to create backup projects and to perform backups. This section will produce backup files that are plain text and composed of multiple SQL statements that can be executed to restore your database, similar to the output generated by the mysqldump utility. For more information on mysqldump, see mysqldump, in the reference manual.

Existing backup projects are listed in the lower left area of the sidebar. If there are many backup projects, the field with the magnifying glass icon can be used to filter the projects you are looking for. Typing in t or T, for example, will set the filter to projects whose names begin with a t. The filter works in a case-insensitive fashion.

You can update the list of backup projects by right clicking on a project and choosing the Refresh Backup Projects option from the drop-down menu. You can delete a backup project by right clicking on the project and choosing the Delete Backup Project(s) option from the drop-down menu.

If you are not working from a previously saved backup project, click the New Project button to create a new backup project. You cannot begin configuring a backup operation without either loading an existing project or creating a new project.

Once you have configured your backup project, you can click the Save Project button to save your project for future use. To start your backup, click the Execute Backup Now button. You will be prompted for a path and file name for the backup file, after which the backup operation will begin.

16.2. Backup Project

The Backup Project tab allows you to set the name of your backup project and also determine what data will be backed up.

You can name your project by filling out a name in the Project Name field. If you have enabled the Add Date/Time to Backup Files option in the Administrator Options dialog then the date and time of the backup will be appended to the project name automatically. See Section 23.6.3, “Backup” for more information on setting this option.

Specify a name that clearly identifies the project, such as Full Backup or World Only. The project name may contain arbitrary characters, including spaces.

To choose the databases and tables that will be backed up, select the database from the schemata list and click the > button. Once the database is in the Backup Content list, you can uncheck specific tables that you do not wish to back up.

16.3. Advanced Options

The Advanced Options tab allows you to configure advanced options that affect the way the backup operation behaves. These include concurrency controls, backup type, and output formatting.

16.3.1. Backup Execution Method

The Backup Execution Method affects concurrency within the backup itself.

InnoDb Online Backup will begin a transaction before reading from the tables. Because of the InnoDB versioning system this will allow all tables to be read in a consistent state. This method is recommended when backing up InnoDB tables.

Lock all tables will cause all tables within the backup to be locked simultaneously to prevent writing. This will prevent users from writing to the tables during the backup operation and will ensure that the tables are in a consistent state. This option is recommended for MyISAM tables.

Normal Backup will lock each table individually when performing the backup. While this can have less impact on the actions of users during the backup operation, these is a potential for tables to be backed up in an inconsistent state in relation to one another. This method is not recommended unless the risk of inconsistency is minimal.

Online with binlog pos is the same as InnoDb Online Backup but also logs the current position within the binary log.

Complete Backup overrides the table selection made in the Backup Project tab and backs up all tables in the selected schema. This option can be useful when scheduling the backup of a schema that changes regularly.

16.3.2. Output File Options

The Output File Options section controls the formatting of the backup file. At present there is only one Backup Type available, the SQL Files format, which outputs a file similar to the output of the mysqldump utility.

No CREATEs will suppress CREATE DATABASE statements. This can be useful if you need to restore the data to a different database than it was backed up from.

No EXTENDED INSERTS will suppress the default behavior of combining multiple inserts into a single statement (for example INSERT INTO test VALUES ('1',Some data',2234),('2','Some More Data',23444)). While the EXTENDED INSERT syntax is more efficient both in terms of storage and execution, it is not compatible with most RDBMSes.

Add DROP Statements adds DROP TABLE IF EXISTS statements to the backup file.

Note

If your backup project includes views then you must choose this option. Restoration of a view is a two step process that involves creating a dummy table with the same name as the view. This table must be dropped before the view can be recreated.

Complete INSERTs will produce full INSERT statements that include a column list (for example, INSERT INTO tablename(columnA, columnB) VALUES(valueA, valueB)). Without this option the column list will be omitted.

Comment writes additional backup information to the backup file in the form of SQL comments.

Fully Qualified Identifiers causes MySQL Administrator to write statements in the format, schema_name.table_name, instead of writing only the object name. If you enable fully qualified identifiers you will not be able to restore your backup to a different schema. Selecting this option will also display a warning dialog requesting acknowledgement of this.

Note

Under Linux this check box is labeled Don't write full path. In this case you need to check this box if you later want to restore to another database schema.

Compatibility mode creates backup files that are compatible with older versions of MySQL Administrator.

ANSI Quotes will cause all table and database names to be quoted with ANSI style double quotes instead of backticks.

Disable keys will add an ALTER TABLE ... DISABLE KEYS statement to the backup file before the INSERT statements that populate the tables. This allows for faster recovery by loading all data before rebuilding the index information.

16.4. Schedule

The Schedule tab. is used to execute backup operations on a daily, weekly, or monthly basis.

In order to run a scheduled backup, you must be using a stored connection. Without one you will see the following warning:

A scheduled backup requires a stored connection. You are currently
connected without using such a stored connection. Please use the connection
manager to create one and use it then for the login.

For instructions on creating a stored connection, see Chapter 7, Connection Dialog.

To enable scheduling for your backup project, check the Schedule this backup project checkbox. You will need to choose a target directory for the backup files and also a name for them. You can browse for a target directory by clicking the button.

If you have enabled the Add Date/Time to Backup Files option in the Administrator Options dialog, then the date and time of the backup will be appended to the file name automatically. See Section 23.6.3, “Backup” for more information on setting this option.

Once you have decided on a file name and target directory, you can set the interval of the backup operation. Regardless of your choice of daily, weekly, or monthly backups, you can set the time that the backup will occur by setting the Time option. Set the backup time in a 24 hour format (for example, 23:30).

When you select the Execute Backup Weekly option, a set of check boxes representing the days of the week will be displayed. Check the boxes corresponding to the days when you want to perform backups.

When you select the Execute backup monthly option, a list of dates will appear, allowing you to choose the day of the month for backups.

Click the Save Project button to save your preferences. After your project is saved it will be executed at the next scheduled interval. To run the backup operation immediately click the Execute Backup Now button.