Shulou(Shulou.com)05/31 Report--

This article mainly introduces the basic commands of the MySQL backup tool mysqldump, which have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

There are many backup tools for MySQL database, but here is a compact and easy-to-use mysqldump tool located in the database directory bin/mysqldump.exe. These days, there is garbled code when backing up the database with phpMyAdmin, and the database has been tested repeatedly in the local and remote Dreamhost space, but there will be garbled code in the exported database, which should be the problem of phpMyAdmin, and there is no problem with the database itself. It's too far, hehe. I Google to about the use of mysqldump tools.

If the host supports Shell, you can log in to the host with SSH and execute the following command to achieve backup and recovery, which is suitable for space such as Dreamhost,Bluehost.

Export the database:

Mysqldump-h HOSTNAME-uUSERNAME-p DBNAME > exported_db.sql

Import the database:

Mysql-h HOSTNAME-uUSERNAME-p DBNAME

< exported_db.sql 　　HOSTNAME 是数据库主机名，USERNAME 和 DBNAME 分别是登陆 ID 和数据库名，导出的数据库存放在 exported_db.sql 文件里。 巧克力工厂(Beta5):Wordpress 超大数据库的导入. 　　使用mysqldump: 　　(mysqldump命令位于mysql/bin/目录中) //要专到mysql/bin/目录中才能使用,直接cmd运行命令窗口使用不了,专到数据库所在的mysql/bin/目录中使用. 　　基本用法是： 　　shell>

Mysqldump [OPTIONS] database [tables]

If you do not specify any tables, the entire database will be exported.

By executing mysqldump-help, you can get a list of options supported by your version of mysqldump.

Note that if you run mysqldump without the-quick or-opt options, mysqldump will load the entire result set into memory before exporting the results, which may be a problem if you are exporting a large database.

Several common use cases:

1. Export the entire database

Mysqldump-u user name-p database name > exported file name

Mysqldump-u root-p dataname > dataname.sql

At this time, you will be prompted to enter the password of the root user name. After entering the password, the dataname database will be successfully backed up in the mysql/bin/ directory.

two。 Export a table

Mysqldump-u user name-p database name table name > exported file name

Mysqldump-u root-p dataname users > dataname_users.sql

3. Export a database structure

Mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc > d:/wcnc_db.sql

-d No data-add-drop-table adds a drop table before each create statement

4. Import database

Common source commands

Go to the mysql database console

Such as mysql-u root-p

If you want 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 just 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 so flexible that you can even use the WHERE clause to select the records you want to export to a file. To do this, 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 of which are shown in the table below:

Option / Option role / Action Performed

-- add-drop-table

This option will precede each table with a DROP TABLE IF EXISTS statement, which ensures that there will be no error when importing back to the MySQL database, because each time it is imported, it will first check whether the table exists and then delete it.

-- add-locks

This option binds a LOCK TABLE and UNLOCK TABLE statement to the INSERT statement. This prevents other users from operating 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 the column (field) to each generating INSERT statement. This option is useful when exporting data to another database.

-- delayed-insert adds the DELAY option to the INSERT command

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

-for-force uses 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 uses this option, and the server will lock the table when it is exported.

-t or-no-create- info

This option prevents the mysqldump command from creating CREATE TABLE statements, which is convenient when you only need data and not DDL (database definition statements).

The-d or-no-data option causes the mysqldump command not to create INSERT statements.

You can use this option when you only need DDL statements.

-- opt this option opens all options that increase file export speed and create a file that can be imported faster.

The-Q or-quick option allows MySQL not to read the entire export into memory and then perform the export, but to write it to the import file when it is read.

The option-T path or-tab = path creates two files, one containing DDL statements or table creation statements, and the other containing data. The DDL file is named table_name.sql and the data file is named table_name.txt. The pathname is the directory where the two files are stored. The directory must already exist and the user of the command has privileges on the file.

-w "WHERE Clause" or-where = "Where clause"

As mentioned earlier, you can use this option to filter the data that will be placed in the exported file.

Suppose you need to create a file for the account to be used in a form, and the manager wants to look at all the orders (Orders) for this year (2004), which are not interested in DDL and need to be comma-delimited because they can be easily imported into Excel. 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 get the results you want.

Schema: mode

The set of statements, expressed in data definition language, that completely describe the structure of a data base.

A set of statements expressed in a data definition language that fully describes the structure of the database.

SELECT INTO OUTFILE:

If you don't think the mysqldump tool is cool, use SELECT INTO OUTFILE. MySQL also provides a command that is the opposite of the LOAD DATA INFILE command, which is the SELECT INTO OUTFILE command, and there are many similarities between the two commands. First of all, they have almost the same options. Now that you need to complete the previous functions done with mysqldump, you can follow these steps:

1. Make sure the mysqld process (service) is already running

two。 Cd / usr/local/mysql

3. Bin/mysqladmin ping; / / if this sentence fails, you can use this: mysqladmin-u root-p ping

Mysqladmin ping is used to detect the status of mysqld, is alive indicates that it is running, and a user name and password may be required for errors.

4. Start the MySQL listener.

5. Bin/mysql-p Meet_A_Geek;// enters the mysql command line and opens the database Meet_A_Geek. You need to enter a password

6. On the command line, enter the following command:

SELECT * INTO OUTFILE'/ home/mark/Orders.txt'

FIELDS

TERMINATED BY =','

FROM Orders

WHERE Order_Date > = '2000-01-01'

After you press Return, the file is created. This sentence is like a regular SELECT statement, only redirecting 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 called 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 verify that the mysql process is running and that you enter the following command in the mysql directory:

Bin/mysql < Report_G.rpt checks the file you named as output, which will contain the last names of all customers you enter in the Customers table.

Thank you for reading this article carefully. I hope the article "what are the basic commands for the use of MySQL backup tool mysqldump" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.