Table of Contents
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
option from the drop-down menu. You can delete a backup project by right clicking on the project and choosing the option from the drop-down menu.If you are not working from a previously saved backup project, click the
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
button to save your project for future use. To start your backup, click the button. You will be prompted for a path and file name for the backup file, after which the backup operation will begin.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
Backup Content
list, you can uncheck specific
tables that you do not wish to back up.
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.
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.
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.
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.
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.
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
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 button.