Database Backup (MySQL)

From DBArtisan
Jump to: navigation, search

Go Up to Database Backup

The MySQL Database Backup WIzard lets you build and submit a mysqldump call, backing up databases to results files.

To back up a MySQL database

  1. Connect to a MySQL datasource and ensure that it is the currently selected datasource. For details, see Connected/Selected Datasource options.
  2. Select Utilities > Database Backup to open the Database Backup WIzard.
  3. Use the following topics as a guide to setting properties and performing tasks as you pass through the wizard panels:
  4. 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

  1. 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

  1. From the Select tables from list, select the database containing the tables you want to back up.
  2. 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:

To dump only specific records

  1. 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

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.

Retrieved from "http://docwiki.embarcadero.com/DBArtisan/170/e/index.php?title=Database_Backup_(MySQL)&oldid=4526"