MySQL mysqldump command to use the detailed

MySQL has a lot of ways to import data, but these are only half of that data transfer, and the other thing is to export it from the MySQL database. There are many reasons why we need to export the data. One important reason is to back up the database. The cost of data is often expensive and needs to be handled with care. Backing up frequently can help prevent the loss of valuable data, and another reason is that you might want to export the data to share. In this growing world of information technology, shared data is becoming more common.

For example, Macmillan USA maintains a large database of books that will be published. The database is shared among many bookstores so that they know which books will be published soon. Hospitals are increasingly moving to paperless records so that they can be followed by you at any time. The world is getting smaller, and information is being shared more and more. There are many ways to export data, which are similar to the imported data. Because, after all, these are just a way of perspective. The data that is exported from the database is the data that is imported from the other end. Here we do not discuss the various methods of exporting data from other databases, and you will learn how to use MySQL to implement data export.

Use mysqldump:

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

Mysqldump tools in many ways are similar to the opposite effect of tool mysqlimport. They have some of the same options. But mysqldump can do more things. It can load the entire database into a separate text file. This file contains all the SQL commands needed to rebuild your database. This command gets all the schemas (schemas, followed by explanations) and converts them to the DDL syntax (create statement, which is the database definition statement), gets all the data, and creates the INSERT statement from the data. This tool reverses all the designs in your database. Because all things are included in a text file. This text file can be used in a simple batch process and a suitable SQL statement to guide back to MySQL. The tool was incredibly simple and fast. There's no place to be a pain in the neck.

Therefore, if you are loading the contents of the entire database meet_a_geek into 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 just want to export the entire contents of the table orders in database Meet_a_geek to a file, you can use 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 want to export to the file. To achieve this, you can use a command similar to the following:

Bin/mysqldump–p–where= "order_id > Meet_a_geek" Orders > Special_dump.txt

The Mysqldump tool has a number of options, some of which are listed below:

Option/option action/action performed

--add-drop-table

This option will precede each table with the drop table if exists statement, which guarantees that the MySQL database will be returned without error, because each time it is returned, the table is first checked for existence and deleted.

--add-locks

This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents other users from working on the table when these records are imported again into the database

-C Or-complete_insert

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

--delayed-insert Add delay option to insert command

-F or-flush-logs Use this option to flush the MySQL server log before performing the export.

-F Or-force Use this option to continue exporting even if errors occur

--full This option adds additional information to the statement in the CREATE table

-L or-lock-tables using this option, the server will lock the table when the table is exported.

-T Or-no-create-info

This option causes the mysqldump command to not create a creating table statement, which is handy when you need only data without the need for DDL (database definition statements).

-D or-no-data This option causes the mysqldump command to not create INSERT statements.