Database Backup (MySQL)
Go Up to Database Backup
The MySQL Database Backup WIzard lets you build and submit a mysqldump
call, backing up databases to results files.
Contents
- 1 To back up a MySQL database
- 2 Playback panel - Database Backup Wizard
- 3 Backup Type panel - Database Backup Wizard
- 4 Databases panel - Database Backup Wizard
- 5 Tables panel - Database Backup Wizard
- 6 Where Clause panel - Database Backup Wizard
- 7 Options panel - Database Backup Wizard
- 8 Output File Type panel - Database Backup Wizard
- 9 Normal Output File panel - Database Backup Wizard
- 10 Datafile Page panel - Database Backup Wizard
- 11 Summary panel - Database Backup Wizard
To back up a MySQL database
- Connect to a MySQL datasource and ensure that it is the currently selected datasource. For details, see Connected/Selected Datasource options.
- Select Utilities > Database Backup to open the Database Backup WIzard.
- Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
- Playback panel - Database Backup Wizard
- Backup Type panel - Database Backup Wizard
- Databases panel - Database Backup Wizard
- Tables panel - Database Backup Wizard
- Where Clause panel - Database Backup Wizard
- Options panel - Database Backup Wizard
- Output File Type panel - Database Backup Wizard
- Normal Output File panel - Database Backup Wizard
- Datafile Page panel - Database Backup Wizard
- Summary panel - Database Backup Wizard
- Finally, use the Finish button to perform the backup.
For information on restoring a database from results files, see Database Restore Wizard (MySQL).
Playback panel - Database Backup Wizard
When backing up a MySQL database, this panel offers the following options:
Required Information | Description |
---|---|
New Backup/Dump Operation |
This initiates the process of creating a new backup or dump operation. |
Enter the views SQL definition |
If you want to replay a backup operation, when you select the radio button, the browse function is enabled so you can find the .DBDump file you want to reuse. |
Backup Type panel - Database Backup Wizard
When backing up a MySQL database, this panel offers the following options:
Setting | Description |
---|---|
Backup databases |
Lets you backup one or more entire databases. |
Backup tables within a single database |
Lets you backup one or more tables within a single database. |
Databases panel - Database Backup Wizard
When backing up a MySQL database, this panel is only available if you selected the Backup databases option. For details, see Backup Type panel - Database Backup Wizard.
To specify the databases that are to be backed up
- Select each check box corresponding to the name of a database you want to back up.
Tables panel - Database Backup Wizard
When backing up a MySQL database, this panel is only available if you selected the Backup tables within a single database option. For details, see Backup Type panel - Database Backup Wizard.
To specify the tables that are to be backed up
- From the Select tables from list, select the database containing the tables you want to back up.
- In the tables list, select each check box corresponding to the name of a table you want to back up.
Where Clause panel - Database Backup Wizard
When backing up a MySQL database, this panel is only available if:
- You selected the Backup tables within a single database option. For details, see Backup Type panel - Database Backup Wizard.
- You selected a single table from the Tables panel. For details, see Tables panel - Database Backup Wizard.
To dump only specific records
- In the WHERE clause box, type a WHERE clause qualifying those records you want to dump.
Options panel - Database Backup Wizard
When backing up a MySQL database, this panel lets you set or select the mysqldump
options dictating how the backup is to be performed.
- Note: Before working with these options, consult MySQL documentation for information on the mysqldump program. For more information, see Accessing Third Party Documentation.
The following table shows the Database Backup Wizard settings on this panel, their mysqldump
option equivalents, and a description of their effect.
Wizard Option | mysqldump Option Equivalent | Description |
---|---|---|
No Data |
--no-data |
Table contents are not dumped |
Continue if Errors Occur |
--force |
Continues the dump even if SQL errors occur. |
Flush Server Logs |
--flush-logs |
Flushes server logs before starting the dump. |
Drop Table Statements |
--add-drop-table |
Adds a DROP TABLE statement before each CREATE TABLE statement. |
Lock/Unlock Statements |
--add-locks |
LOCK TABLE and UNLOCK TABLE statements are generated for each table dump. |
Lock All Tables |
--lock-tables |
Locks all tables before dumping them. |
Allow Keyword Column Names |
--allow-keywords |
Allows creation of column names that contain keywords. |
Complete Insert Statements |
--complete-insert |
Uses complete INSERT statements, including column names. |
Use MySQL Table Options |
--create-options |
All MySQL-specific table options are included in CREATE TABLE statements. |
Use Insert Delayed Options |
--delayed-insert |
Writes INSERT DELAYED statements instead of INSERT statements. |
Disable Keys |
--disable-keys |
Each INSERT statement is surrounded by disable/enable key statements. |
Use Extended Inserts |
--extended insert |
Generates multiple-row INSERT syntax that includes several VALUES lists. |
Use Create Table Statements |
--no-create-info |
Scripts CREATE TABLE statements for each table. |
Suppress Information |
--skip-comments |
Additional information such as program version, server version, and host, is not written to the dump file. |
Exclude Create Database Statements |
--no-create-db |
Suppresses CREATE DATABASE statements. |
Retrieve Single Rows |
--quick |
Rows are retrieved one at a time. |
Use Quotes |
--quote-names |
Identifiers such as database, table, and column names are generated within quote characters. |
Use Begin Statements |
--single-transaction |
Issues a BEGIN statement before dumping data from the server. |
Use First-Slave Option |
--first-slave |
Locks all tables across all databases. |
Use Master-Data Option |
--master-data |
Deselected, a CHANGE MASTER statement is written as an SQL comment. |
Delete Binary Logs |
--delete-master-logs |
Deletes binary logs after the dump operation on a master replication server. |
Suppress Set Names Statement |
--skip-set-charset |
Suppresses SET NAMES default_character_set statements. |
Default Character Set |
--default-character-set=charset |
Uses the specified charset as the default character set. |
Protocol |
--protocol= |
The connection protocol (TCP, SOCKET, PIPE, or MEMORY ) to use. |
Max Allowed Packet |
max_allowed_packet= |
The maximum size of the client/server communication buffer. |
Net Buffer Length |
net_buffer_length= |
The initial size of the client/server communication buffer. |
Compress |
--compress |
Compresses information sent between client and server if compression support is available. |
ANSI |
--compatible=ansi |
These settings enable dump output compatibility modes. |
MySQL 3.23 |
--compatible=mysql323 | |
MySQL 4.0 |
--compatible=mysql40 | |
PostgresSQL |
--compatible=postrgreql | |
Oracle |
--compatible=oracle | |
MSSQL |
--compatible=mssql | |
DB2 |
--compatible=db2 | |
MaxDB |
--compatible=maxdb | |
No Key Options |
--compatible=no_key_options | |
No Table Options |
--compatible=no_table_options | |
No Field Options |
--compatible=no_field_options |
Output File Type panel - Database Backup Wizard
When backing up a MySQL database, this panel lets you select between standard output or use of the --tab option. It offers the following options:
Setting | Description |
---|---|
Create a normal output file |
Produces standard output. |
Create a tab-separated output file |
For each dumped table, mysqldump creates a ‘tbl_name.sql’ file that contains the CREATE TABLE statement that creates the table, and a ‘tbl_name.txt’ that contains its data. |
For more general information, such as how to open this Wizard, see Database Restore Wizard (MySQL).
Normal Output File panel - Database Backup Wizard
When backing up a MySQL database, this panel lets you provide results file options (--result-file=file and --xml). It offers the following options:
Setting | Description |
---|---|
Enter the path and filename of the results file |
Lets you locate and provide/select a path and filename for the results file. |
Prevent newline “\n” characters from being converted to “\r\n” carriage return/newline sequences (recommended for Windows systems) | |
Write dump output as well-formed XML |
Datafile Page panel - Database Backup Wizard
When backing up a MySQL database, this panel is only available if
- You selected the Backup tables within a single database option. For details, see Backup Type panel - Database Backup Wizard.
- You selected the Create a tab-separated tab file option. For details, see Output File Type panel - Database Backup Wizard.
It offers the following options:
Setting | Description |
---|---|
Enter the directory where the file(s) will be stored |
Lets you provide a --tab=path option value. |
Fields Terminated By, Fields Enclosed By, Fields Optionally Enclosed By, Fields Escaped By, and Lines Terminated By |
Lets you provide --fields-xxx and --lines--xxx option values for line and column separators. |
Summary panel - Database Backup Wizard
When backing up a MySQL database, this panel summarizes the choices you made in previous steps setting up the operation.