MySQL data import and export methods and tools introduced in the third

You can see that MySQL has a lot of ways to import data, but that's just half of what it does. In addition, data is typically exported from the MySQL database. There are a number of reasons why we need to export data. One important reason is to back up the database. The cost of data is often expensive and needs to be handled with care. Regular backups can help prevent the loss of valuable data, and another reason is that you might want to export data to share. In a world of growing information technology, sharing data is becoming more common.

Let's say Macmillan USA maintains a large database of books that will be published. The database is shared among many bookstores, so they know which books will be published soon. Hospitals are increasingly moving towards paperless medical records so that they can follow 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 importing data. Because, after all, these are just a way of being 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 for data export.

Using mysqldump:

(The mysqldump command is located in the mysql/bin/directory)
The Mysqldump tool Mysqlimport Many aspects of the tool similar to the opposite effect. They have some of the same options. But mysqldump can do more things. It can load the entire database into a single text file. This file contains all the SQL commands needed to rebuild your database. This command obtains all the schemas (schema, which are explained later) and converts them into DDL syntax (create statement, i.e. database definition statement), obtains all the data, and creates insert statements 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 directed back to MySQL with a simple batch and a suitable SQL statement. This tool is incredibly simple and fast. There will never be a place where people get a headache.

Therefore, if you like to load 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 only want to export the entire contents of the 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 and you can even use the WHERE clause to select the records you need to export to a 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 will ensure that the MySQL database is returned without error, because each time it is returned, it will first check if the table exists, and it will be deleted.
--add-locks
This option binds the lock table and the Unlock table statement in the INSERT statement. This prevents other users from manipulating the table when these records are re-imported to the database

-C Or-complete_insert
This option allows the mysqldump command to give each INSERT statement plus (field)

                      name. This option is useful when exporting data to another database.  
--delayed-insert      Add the delay option to the Insert command
-F or-flush-logs       with this option, the log of the MySQL server will be flushed before the export is performed.
-F or-force             Use this option to continue exporting even if an error occurs
--full                       This option adds additional information to the CREATE TABLE statement
-L or-lock-tables      With this option, the server will lock the table when the table is exported.
-T or-no-create-info 
                 This option causes the mysqldump command not to create a make table statement, which is handy when you only need the data without the DDL (database definition statement).

-D or-no-data This option causes the mysqldump command not to create an INSERT statement.
You can use this option when you only need DDL statements.
--opt This option will open all the options that will increase the file export speed and create a file that can be imported faster.
-Q or-quick This option allows MySQL to not read the entire exported content into memory and perform the export, but writes it to the guide file when it is read.
-T Path or-tab = Path This option creates two files, one file contains DDL statements or table creation statements, and the other file contains data. The DDL file is named Table_name.sql, and the data file is named Table_name.txt. The path name is the directory where the two files are stored. The directory must already exist, and the user of the command has privileges to the file.

-W "where Clause" Or-where = "where Clause"
As mentioned earlier, you can use this option to filter what will be placed in
The data for the exported file.

Suppose you need to create a file for the account you want to use in a form, and the manager will look at all orders for this year (2004), they are not interested in DDL, and need a comma-delimited file because it is easy to import into Excel. In order to complete this character, you can use the following sentence:
Bin/mysqldump–p–where "Order_date >=2000-01-01"
–tab =/home/mark–no-create-info–fields-terminated-by=, Meet_a_geek Orders
This will give you the results you want.

Schema: Schema
The set of statements, expressed in data definition language, that completely describe the structure of a data base.
A set of statements expressed in the data definition language that fully describes the structure of the database.

SELECT into OUTFILE:
If you think the mysqldump tool is not cool enough, use SELECT INTO OUTFILE, and MySQL also provides a command that has the opposite effect on the load DATA infile command, which is the SELECT INTO OUTFILE command, There are many similarities between the two commands. First, they have almost the same options. Now you need to complete the previous function with Mysqldump, you can follow the steps below:

1. Ensure that the MYSQLD process (service) is already running
2. Cd/usr/local/mysql
3. Bin/mysqladmin Ping;//If this sentence does not pass, you can use this: mysqladmin-u root-p ping
Mysqladmin Ping is used to detect the state of mysqld, the is Alive description is running, and an error may require a user name and password.
4. Start the MySQL listener.
5. Bin/mysql–p meet_a_geek;//into the MySQL command line, and open the database Meet_a_geek, you need to enter a password
6. At the command line, enter the command:
SELECT * into Outfile/home/mark/orders.txt
Fields
TERMINATED by =,
From Orders
WHERE order_date >= 2000-01-01

After you press RETURN (enter), the file is created. This sentence is like a rule of the SELECT statement, just to redirect the output of the screen to the file. This means that you can use join to implement advanced queries for multiple tables. This feature can also be used as a report generator.
For example, you can combine the methods discussed in this chapter to produce a very interesting query, try this:

Create a text file named Report_g.rpt in the MySQL directory, and add the following line:
Use Meet_a_geek;
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Kinnard", "Vicky");
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Kinnard", "Steven");
INSERT into Customers (customer_id, last_name, first_name)
VALUES (NULL, "Brown", "Sam");
SELECT last_name into Outfile/home/mark/report.rpt
From Customers WHERE customer_id > 1;
Then confirm that the MySQL process is running and that you are in the MySQL directory, enter the following command:
Bin/mysql < report_g.rpt Check the file you named as output, this file will contain all the last names of the customers you entered in the Customers table. As you can see, you could use the Import/export (Import/export) method you learned today to help you get the report.

Transfer from http://www.2cto.com/database/200411/1800.html

MySQL data import and export methods and tools introduced in the third