MySQL has many ways to import data, but these are only half of the data transmission, and the other is generally exporting data from MySQL database. There are many reasons why we need to export data. An important reason is to use it to back up the database. Data is often expensive and requires careful processing. Backing up regularly can help prevent valuable data from being lost; another reason is that maybe you want to export data to share. In this growing world of information technology, sharing data is becoming more and more common.
Here we do not discuss various ways to export data in other databases. You will learn how to use MySQL to implement data export.
Using mysqldump:
(The mysqldump command is located in the mysql/bin/ directory)
MySQLdump tool is similar to the tool with opposite effects in many aspects. They have some of the same options. But mysqldump can do more. It can load the entire database into a separate text file. This file contains all the SQL commands required to rebuild your database. This command takes all the patterns (Schema, explained later) and converts them into DDL syntax (CREATE statement, i.e. database definition statement), obtains all the data, and creates an INSERT statement from this data. This tool reverses all designs in your database. Because everything is included in a text file. This text file can be imported back into MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never be any headache.
So, if you load the contents of the entire database Meet_A_Geek into a file like this, 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 table Orders in the 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 need to export to the file. To achieve this, you can use commands 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 of which are as follows:
Option/Option Performed
--add-drop-table
This option will add the DROP TABLE IF EXISTS statement before each table, which can ensure that there will be no errors when importing back to the MySQL database, because every time you import back, you will first check whether the table exists and delete it if it exists.
--add-locks
This option will bundle a LOCK TABLE and UNLOCK TABLE statement in the INSERT statement. This prevents other users from doing operations on the table when these records are re-imported into the database
-c or - complete_insert
This option causes the mysqldump command to add the name of a column to each INSERT statement. This option is useful when exporting data to another database.
--delayed-insert Add DELAY option to the INSERT command
-F or -flush-logs Use this option to refresh the MySQL server's log before executing.
-f or -force Use this option and continue to export even if an error occurs
--full This option also adds additional information to the CREATE TABLE statement
-l or -lock-tables Use this option, the server will lock the table when exporting the table.
-t or -no-create- info
This option makes the mysqldump command not create a CREATE TABLE statement, which is convenient when you only need data and not DDL (database definition statement).
-d or -no-data This option makes the mysqldump command not create an INSERT statement.
This option is available when you only need DDL statements.
Previous page123Next pageRead the full text