MySQL database structure and data export and import using LAMP strategy (1)

 

Figure-LAMP

LAMPInsideMySQLThe database structure and data export and import operations are shown to you!

MySQL mysqldump is used for export. The basic usage is as follows:

Copy content to clipboard

PHP code:

 
 

  
  
shell> mysqldump [OPTIONS] database [tables]  
  
  
 
 
 

If you do not specify any tables, the entire database will be exported.

Run mysqldump -- help to obtain the option table supported by your mysqldump version.

Note: If you run mysqldump without the -- quick or -- opt option, mysqldump loads the entire result set to the memory before the export result. If you are exporting a large database, this may be a problem.

Mysqldump supports the following options:

Copy content to clipboard

PHP code:

 
 

  
  
--add-locks  
  
  
 
 
 

Add lock tables and unlock table before each TABLE is exported. (To make it faster to insert data to MySQL ).

Copy content to clipboard

PHP code:

 
 

  
  
--add-drop-table  
  
  
 
 
 

Add a drop table before each create statement.

Copy content to clipboard

PHP code:

 
 

  
  
--allow-keywords  
  
  
 
 
 

Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.

Copy content to clipboard

PHP code:

 
 

  
  
-c, --complete-insert  
  
  
 
 
 

Use the complete insert Statement (with the column name ).

Copy content to clipboard

PHP code:

 
 

  
  
-C, --compress  
  
  
 
 
 

If both the client and server support compression, all information is compressed between the two.

Copy content to clipboard

PHP code:

 
 

  
  
--delayed  
  
  
 
 
 

Use the insert delayed command to INSERT rows.

Copy content to clipboard

PHP code:

 
 

  
  
-e, --extended-insert  
  
  
 
 
 

Use the new multiline INSERT syntax. (A more compact and faster insert statement is provided)

Copy content to clipboard

PHP code:

 
 

  
  
-#, --debug[=option_string]  
  
  
 
 
 

Tracking Program usage (for debugging ).

Copy content to clipboard

PHP code:

 
 

  
  
--help  
  
  
 
 
 

Displays a help message and exits.

Copy content to clipboard

PHP code:

 
 

  
  
--fields-terminated-by=...  
  
  
 
  
  
--fields-enclosed-by=...  
  
  
 
  
  
--fields-optionally-enclosed-by=...  
  
  
 
  
  
--fields-escaped-by=...  
  
  
 
  
  
--fields-terminated-by=...  
  
  
 
 
 

These options are used with-T options and have the same meaning as the load data infile clause.

Copy content to clipboard

PHP code:

Load data infile syntax

 
 

  
  
-F, --flush-logs  
  
  
 
 
 

Wash Out the log files on the MySQL server before starting the export.

Copy content to clipboard

PHP code:

 
 

  
  
-f, --force,  
  
  
 
 
 

Even if we get an SQL error while exporting a table, continue.

Copy content to clipboard

PHP code:

 
 

  
  
-h, --host=..  
  
  
 
 
 

Export data from the MySQL server on the named host. The default host is localhost.

Copy content to clipboard

PHP code:

 
 

  
  
-l, --lock-tables.  
  
  
 
 
 

Lock all tables for start export.

Copy content to clipboard

PHP code:

 
 

  
  
-t, --no-create-info  
  
  
 
 
 

Create table statement)

Copy content to clipboard

PHP code:

 
 

  
  
-d, --no-data