MySQL: backup, restoration, and recovery: mysqldump-a standard free universal backup tool

I. Overview

Mysqldump is a standard, free, universal, and powerful MySQL tool that supports logical, hot, local, or remote, full, or Incremental backup.

The following describes how to use mysqldump ver 10.13 distrib 5.1.34 and for Win32 (ia32!

 

2. Option file. Remember: you can control the file in the command line without relying on the option file.ProgramAction

The program reads standard configuration file path order: C: \ WINDOWS \ My. INI c: \ windows \ My. CNF c: \ My. INI c: \ My. cnf d: \ Program Files \ mysql \ MySQL Server 5.1 master \ My. ini d: \ Program Files \ mysql \ MySQL Server 5.1 master \ My. CNF: the default configuration options in the following sections: [mysqldump] and [client.

This behavior can be changed through the first option of the standard MySQL program:

1. -- Print-defaults print the program argument list and exit -- print the default configuration options of the program and immediately exit the program execution. Used to understand the default option Configuration
2. -- No-defaults don't read default options from any options file -- do not read the configuration in any default option configuration file. You must provide command line options in the command line. Used for Clean control configuration options, must be configured with command line options
3. -- Defaults-file = # Only read default options from the given file # -- read only the specified option configuration file instead of the configuration in any default option configuration file. Used for Clean control configuration options
4. -- Defaults-extra-file = # Read this file after the global files are read -- read the configuration in any default option configuration file and then read the specified option configuration file. Used to supplement or replace the default configuration!

 

3. Understand default program behavior characteristics through program variable values

 

Default program behavior feature Variables

  1   Variables (-- Variable-name  =  Value)
  2   And Boolean options {false | true} value (after reading
  3   ------------------------------------------------------
  4   All true
  5  All-databases false
  6   All-tablespaces false
  7   No-tablespaces false
  8   Add-drop-database false
  9     Add-drop-Table true
  10   Add-locks true
  11  Allow-keywords false
  12   Character-sets-Dir (no default value)
  13   Comments true
  14   Compatible (no default value)
  15   Compact false
  16   Complete-insert false
  17   Compress false
 18   Create-options true
  19   Databases false
  20   Debug-check false
  21   Debug-Info false
  22   Default-character-set utf8
  23   Delayed-insert false
  24  Delete-master-logs false
  25   Disable-keys true
  26   Events false
  27   Extended-insert true
  28   Fields-terminated-by (no default value)
  29   Fields-enclosed-by (no default value)
  30  Fields-optionally-enclosed-by (no default value)
  31   Fields-escaped-by (no default value)
  32   First-slave false
  33   Flush-logs false
  34   Flush-Privileges false
  35   Force false
  36   Hex-blob false
 37   Host (no default value)
  38   Insert-ignore false
  39   Lines-terminated-by (no default value)
  40   Lock-all-tables false
  41   Lock-tables true
  42   Log-error (no default value)
  43  Master-Data  0  
  44   Max_allowed_packet  25165824  
  45   Net_buffer_length  1046528  
  46   No-autocommit false
  47   No-create-DB false
 48   No-create-Info false
  49   No-data false
  50   Order-by-Primary false
  51   Port  3306  
  52   Quick true
  53   Quote-names true
 54   Replace false
  55   Routines false
  56   Set-charset true
  57   Shared-memory-base-Name (no default value)
  58   Single-transaction false
  59   Dump-date true
  60  Socket (no default value)
  61   SSL false
  62   SSL-Ca (NO default value)
  63   SSL-capath (no default value)
  64   SSL-Cert (no default value)
  65   SSL-Cipher (no default value)
  66  SSL-Key (no default value)
  67   SSL-verify-server-Cert false
  68   Tab (no default value)
  69   Triggers true
  70   TZ-UTC true
  71   User (no default value)
  72  Verbose false
  73   Where (no default value) 

 

 

Four common configuration options

The value of these feature variables can be changed by setting options.

Note:

• -- Opt = -- add-drop-table, -- add-locks, -- create-options, -- quick, -- extented-insert, -- lock-tables, -- Set-charset, -- disable -Keys
• -- Skip-OPT

 

Configuration options related to database objects:

• -- Databases
• -- All-Databases
• -- All-tablespaces
• -- No-tablespaces
• -- Events
• -- Routines
• -- Triggers
• -- Tables can block the -- databases Option
• -- Ignore-table = Name

 

Configuration options related to script format:

• -- All
• -- Create-options # equivalent to -- all
• -- Replace
• -- Complete-insert
• -- Extented-insert
• -- Delayed-insert
• -- Disable-keys
• -- No-create-DB
• -- Add-drop-Database
• -- No-create-Info
• -- Add-drop-table
• -- Quote-name
• -- No-autocommit
• -- Skip-set-charset # equivalent to -- no-set-names
• -- Set-charset
• -- TZ-UTC
• -- Dump-Date
• -- XML

 

 

Options related to the number of data rows:

• -- No-Data
• -- Where = Name
• -- Order-by-primary

 

 

Configuration options related to data type and encoding:

• -- Default-character-Set
• -- Hex-blob

 

 

Configuration options related to data integrity:

• -- Lock-tables
• -- Lock-all-tables
• -- Single-transaction

 

 

Configuration options related to export performance:

• -- Quick
• -- Order-by-primary
• -- Default-character-Set
• -- Delayed-insert
  -- Disable-keys
• -- No-autocommit
• -- Net-buffer-length = #
• -- Max-allowed-packet = #

 

Configuration options related to error control:

• -- Force
• -- Log-error = Name

 

 

File-related configuration options:

• -- Log-error = Name
• -- Result-file = Name