One of the common backups in MySQL databases, MySQLdump

The following articles mainly introduce one of the frequently used backup methods in MySQL Databases: MySQLdump and description of related matters that should be paid attention to during the actual operation of MySQLdump, the following is a description of the specific solution. I hope it will help you in your future study.

MySQLdump

Basic Syntax:

Shell> MySQLdump [OPTIONS] database [tables]> data_backup. SQL does not specify the MySQL database name to indicate all backups)

For example:

MySQLdump-uroot-p default-character-set = cp932 opt extended-insert = false hex-blob-R-x MySQL> E: \ MySQL. SQL

The beginning of the output file looks like this:

 
 

  
  
# MySQL Dump 6.0  
  
  
#   
  
  
# Host: localhost Database: samp_db  
  
  
#-  
  
  
# Server version 3.23.2-alpha-log  
  
  
#  
  
  
# Table structure for table 'absence'  
  
  
#  
  
  
CREATE TABLE absence(  
  
  
student_id int(10) unsigned DEFAULT '0' NOT NULL,  
  
  
date date DEFAULT '0000-00-00' NOT NULL,  
  
  
PRIMARY KEY (student_id,date)  
  
  
);  
  
  
#  
  
  
# Dumping data for table 'absence'  
  
  
#  
  
  
INSERT INTO absence VALUES (3,'1999-09-03');  
  
  
INSERT INTO absence VALUES (5,'1999-09-03');  
  
  
INSERT INTO absence VALUES (10,'1999-09-08');  
  
  
......　 
 
 

The rest of the file consists of more INSERT and create table statements. Example:

 
 

  
  
% MySQLdump samp_db>/opt/MySQLdatabak/samp_db.2006-5-15
  
  
% MySQLdump samp_db | gzip>/usr/archives/MySQL/samp_db.1999-10-02.gz # produce compressed backup
  
  
% MySQLdump samp_db student score event absence> grapbook. SQL # Back up some tables in the database
  
  
% MySQLadmin-h boa.snke.net create samp_db
  
  
% MySQLdump samp_db | MySQL-h boa.snke.net samp_db
 
 

Directly recover to another server and use the add-drop-table option to tell the server to write the drop table if exists statement to the backup file. In this way, IF the table already EXISTS when we use it to recover the MySQL database, you will not get an error.

% MySQLdump add-drop-table samp_db | MySQL-h boa.snke.net samp_db

Other useful options for MySQLdump include:

The combination of flush-logs and lock-tables will be helpful for your database checkpoints. Lock-tables locks all the tables you are dumping, and flush-logs closes and re-opens the Update log file. The new update log will only include queries for modifying the database from the backup point. This will set the backup time of your Update log check point. However, if you have a customer who needs to perform an update, it is not a good thing to lock all tables for customer access during backup .)

If you use flush-logs to set the Check Point to the backup point, it is best to dumped the entire database. If you dumped individual files, it is difficult to synchronize the Update log checkpoint with the backup file. During the recovery period, you usually extract the updates based on the database, but you have no choice to extract updates for a single table. Therefore, you must extract them by yourself.

By default, MySQLdump reads the entire content of a table into the memory before writing. This is usually unnecessary, and in fact, if you have a large table, it is almost a failure. You can use the quick option to tell MySQLdump to write each row as long as it retrieves a row. To further optimize the dumping process, use opt instead of quick. Select other options to accelerate data dumping and read them back.

Using opt for backup may be the most common method, because of the advantages of backup speed. However, we should warn you that the opt option does have a price. opt optimizes your backup process rather than other customers accessing the database. The opt option prevents anyone from updating any table you are dumping at a time by locking all tables. You can easily see the effect of common database access.

Dedayed is an option with the opposite effect of opt. This option causes MySQLdump to write the insert delayed statement instead of the INSERT statement. If you load data files into another database and want to minimize the impact of this operation on queries that may occur in the database, delayed is helpful.

The compress option is helpful when you copy a database to another machine because it reduces the number of bytes transmitted over the network. The following example shows that compress only provides programs for communicating with servers on the remote host, rather than programs connected to the local host:

% MySQLdump opt samp_db | MySQL compress-h boa.snke.net samp_db

MySQLdump has many other options, main parameters:

Compatible = name

It tells MySQLdump that the exported data will be compatible with the MySQL database or the old MySQL server. The values can be ansi, MySQL323, MySQL40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. Of course, it does not guarantee full compatibility, but is as compatible as possible.

Complete-insert,-c

The exported data adopts the complete INSERT method containing the field name, that is, all values are written in one row. This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Therefore, you need to use this parameter with caution. At least I do not recommend this parameter.

Extended-insert = true | false

By default, MySQLdump enables the complete-insert mode, so if you do not want to use it, use this option to set its value to false.

Default-character-set = charset

Specifies the character set used for data export. If the data table does not use the default latin1 character set, this option must be specified during data export. Otherwise, garbled characters will occur after data is imported again.

Disable-keys

Tell MySQLdump to add/* at the beginning and end of the INSERT statement /*! 40000 alter table table disable keys */; And /*! 40000 alter table table enable keys */; Statement, which greatly improves the speed of the insert statement because it re-creates the index after all data is inserted. This option is only applicable to MyISAM tables.

Hex-blob

Export binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB.

Lock-all-tables,-x

Before starting the export, submit a request to lock all tables in all databases to ensure data consistency. This is a global read lock and the single-transaction and lock-tables options are automatically disabled.

Lock-tables

It is similar to lock-all-tables, but instead of locking all tables in the database. This option is only applicable to MyISAM tables. For Innodb tables, you can use the single-transaction option.

No-create-info,-t

Only export data without adding the create table statement.

No-data,-d

Only the database table structure is exported without exporting any data.

Opt

This is just a shortcut option. It is equivalent to adding the add-drop-tables add-locking create-option disable-keys extended-insert lock-tables quick set-charset option at the same time. This option allows MySQLdump to export data quickly and export data back quickly. This option is enabled by default, but can be disabled with skip-opt. Note: If you run MySQLdump without specifying the quick or opt option, the entire result set is stored in the memory. If you export a large database, problems may occur.

Quick,-q

This option is useful when exporting large tables. It forces MySQLdump to retrieve records from the Server query and output them directly instead of caching all records into the memory.

Routines,-R

Export stored procedures and user-defined functions.

Single-transaction

This option submits a begin SQL statement before exporting data. BEGIN does not block any applications and ensures the consistency of the MySQL database during export. It is only applicable to transaction tables, such as InnoDB and BDB.

This option and the lock-tables option are mutually exclusive, Because lock tables will implicitly commit any pending transactions.

To export a large table, use the quick option.

Triggers

Export the trigger at the same time. This option is enabled by default. Use skip-triggers to disable it.