Use the MySQL mysqldump command correctly

MySQL mysqldump commands still occupy the majority of applications. If you are curious about this technology, the following articles will reveal its secrets. I hope it will help you in your future study or work.

1. Use MySQL mysqldump to back up and restore MySQL database data

Assume that you want to back up the tm database:

Shell> mysqldump-uroot-p123456 tm> tm_050519. SQL

In this case, you can use gzip to compress data. The command is as follows:

Shell> mysqldump-uroot-p123456 tm | gzip> tm_050519. SQL .gz

Recover data:

Shell> mysql-uroot-p123456 tm <tm_050519. SQL

Direct Recovery from compressed files:

Shell> gzip <tm_050519. SQL .gz | mysql-uroot-p123456 tm

2. Some Parameter options for using mysqldump

(1) directly use MySQL mysqldump-uroot-pxxxx tetratest> "d: \ data1. SQL"

When exporting data, there is no database creation statement. In this case, you can manually create a database and then use:

Mysql-uroot-pabcd mydatabase <"d: \ data1. SQL": Copies a database to a database with different names. In this case, stored procedures and functions are not transferred to the file.

(2) If you do not use the -- quick or -- opt option, mysqldump loads all the content into the memory before dumping the result. This will cause some problems when you dump a database with a large amount of data. This option is enabled by default, but you can use -- skip-opt to disable it.

(3) Use -- skip-comments to remove the comment statement from the exported file.

(4) You can use the -- compact option to output only the most important statements without comments or table deletion statements.

(5) The -- database or-B option can be used to dump multiple databases. The parameters after this option name are identified as database names.
Mysqldump-uroot-paaa -- database db1 db2> "d: \ mydata. SQL ". At the same time, this parameter is used to add the statement for creating a database in the exported file. If this option is not included, the second parameter is considered as the table name, namely, MySQL mysqldump-uroot-paaa my1 mytable1> "d: \ mydata. SQL to export the structure and data of the table mytable1.

(6) -- tables. parameters after this option are considered as table names.

(7) -- no-create-db

(8) -- no-create-info

(9) -- no-data

(10) -- routines,-R will also dump stored procedures and functions to the file.
(For more information, see the MySQL manual)

3. Temporary tables and HEAP tables in MySQL

Add the TEMPORARY keyword to the normal create table statement:

 
 

  
  
CREATE TEMPORARY TABLE tmp_table (  
  
  
name VARCHAR(10) NOT NULL,  
  
  
value INTEGER NOT NULL  
  
  
)  
 
 

Temporary tables will exist during your connection to MySQL. When you disconnect, MySQL automatically deletes the table and releases the space used. Of course, you can delete the table and release the space when it is still connected.

Drop table tmp_table

If you declare that the temporary table is a HEAP table, MySQL also allows you to specify to create it in memory:

 
 

  
  
CREATE TEMPORARY TABLE tmp_table (  
  
  
name VARCHAR(10) NOT NULL,  
  
  
value INTEGER NOT NULL  
  
  
) TYPE = HEAP 
 
 

HEAP tables use a hash index and are stored in the memory, which makes them faster, but if MySQL crashes, you will lose all the stored data. HEAP is available as a temporary table!

When using the HEAP table, here are some things you should consider:

You should always specify MAX_ROWS in the create statement to ensure that you intentionally do not use all the memory.

The index can only be used with = and <=> but soon ).

The HEAP table uses a fixed record length format.

HEAP does not support BLOB/TEXT columns.

HEAP does not support the AUTO_INCREMENT column.

HEAP does not support indexes on a NULL column.

You can have a non-unique key in a HEAP table (the hash table is generally not like this ).

HEAP tables are shared among all customers (just like any other table ).

Data in the HEAP table is allocated in small blocks. The table is 100% dynamic (during insertion), with no overflow zone or extra key space required. The deleted row is placed in a chain table and used again when you insert new data into the table.

To release the memory, you should execute delete from heap_table or drop table heap_table.

To ensure that you do not accidentally do stupid things, you cannot create a HEAP table larger than max_heap_table_size.

The above content is the description of the MySQL mysqldump command, hoping to help you in this regard.