MySQL mysqldump command usage details

MySQL has many data import methods, but these are only half of the data transmission, and the other is generally exporting data from the MySQL database. There are many reasons for exporting data. One important reason is that it is used to back up the database. Data is often expensive and must be processed with caution. Regular backup can help prevent valuable data loss. Another reason is that you may want to export data for sharing. Sharing data becomes more and more common in the ever-growing world of information technology.

For example, Macmillan USA maintains a large database that protects the books to be published. This database is shared among many bookstores so that they know which books will be published soon. Hospitals are increasingly adopting paperless medical records so that these records can follow you at any time. The world is getting smaller and smaller, and information is shared more and more. There are many methods for exporting data, which are similar to importing data. Because, after all, these are just a method of pivoting. The data exported from the database is the data imported from the other end. We will not discuss various data export methods in other databases. You will learn how to use MySQL to export data.

Use mysqldump:

(The mysqldump command is located in the mysql/bin/directory)

Mysqldump is similar to the opposite tool in many aspects. They have the same options. However, mysqldump can do more. It can load the entire database into a separate text file. This package contains all the SQL commands required to recreate your database. This command gets all the modes (Schema, which is explained later) and converts them into DDL syntax (CREATE statement, that is, database Definition Statement) to get all the data, create an INSERT statement from the data. This tool reverses all the designs in your database. Because everything is contained in a text file. This text file can be imported back to MySQL using a simple batch processing and a suitable SQL statement. This tool is incredibly simple and fast. There will never be a headache.

Therefore, if you load the contents of the entire database Meet_A_Geek to a file, you can use the following command:

Bin/mysqldump-p Meet_A_Geek> MeetAGeek_Dump_File.txt

This statement also allows you to specify a table for dump (backup/export/load ?). If you want to export the entire content of the table Orders in the database Meet_A_Geek to a file, run the following command:

Bin/mysqldump-p Meet_A_Geek Orders> MeetAGeek_Orders.txt

This is very flexible. You can even use the WHERE clause to select the records you need to export to the file. To achieve this purpose, you can use a command similar to the following:

Bin/mysqldump-p-where = "Order_ID> 2000" Meet_A_Geek Orders> Special_Dump.txt

The mysqldump tool has a large number of options. Some options are shown in the following table:

Option/Option Action/Action completed MED

-- Add-drop-table

This option will add the drop table if exists statement before each TABLE. This ensures that errors will not occur when importing data back to the MySQL database, check whether the table exists and delete the table if it exists.

-- Add-locks

This option binds a lock table and an unlock table statement in the INSERT statement. This prevents operations performed on tables by other users when these records are re-imported to the database.

-C or-complete_insert

This option causes the mysqldump command to add the column (field) name to each INSERT statement. This option is useful when exporting data to another database.

-- Delayed-insert: add the DELAY option to the INSERT command.

-F or-flush-logs uses this option to refresh the log of the MySQL server before executing the export.

-F or-force: use this option to continue exporting even if an error occurs.

-- Full: This option adds the additional information to the create table statement.

-L or-lock-tables: When you export a table, the server locks the table.

-T or-no-create-info

This option does not allow the mysqldump command to CREATE a create table statement. This option is convenient when you only need data and do not need DDL statements.

-D or-no-data does not create an INSERT statement for the mysqldump command.