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 filename 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 19.5.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.
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.
Single transaction
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.
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.
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
control 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 supress 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 supress 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.
Complete INSERTs
will produce full
INSERT
statements that include a column list
(i.e. 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.
Don't write full path
causes MySQL Administrator
to write only the table name in statements, instead of
schema_name.table_name
.
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.
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
dialogue, then the date and time of the backup
will be appended to the file name automatically. See
Section 19.5.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 (i.e. 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 immediatly click the button.