Mysql Backup tool mysqldump the basics of using command summary _mysql

MySQL database backup tools are many, but here is a small and easy to use the Mysqldump tool, located in the database directory Bin/mysqldump.exe. These days with phpMyAdmin back up the database when there are garbled, Repeated in the local and remote DreamHost space to test the database, but still export the database will appear garbled, should be the problem of phpMyAdmin, the database itself is not a problem. Pull away, hehe. I google about the Mysqldump tools related usage.
If the host host support Shell, you can SSH landing host, the following command to implement the backup and restore, suitable for dreamhost,bluehost and other space.

To export a database:

Mysqldump-h hostname-uusername-p dbname > Exported_db.sql

To import a database:

Mysql-h Hostname-uusername-p dbname < Exported_db.sql

HOSTNAME is the database hostname, USERNAME and dbname are login IDs and database names, and the exported databases are stored in Exported_db.sql files. Chocolate Factory (BETA5): The introduction of Wordpress Super Large database.

Use mysqldump:
(mysqldump command in the mysql/bin/directory)//To be used exclusively in the mysql/bin/directory, direct cmd Run command window is not used, specifically to the database in the mysql/bin/directory used.

The basic usage is:

Shell> mysqldump [OPTIONS] database [tables]

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

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

Note that if you run mysqldump without –quick or –opt option, mysqldump will load the entire result set into memory before exporting the results, if you are exporting a large database, this could be a problem.

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

This time will prompt you to enter the root username password, enter the password after the Dataname database is successfully backed up in the mysql/bin/directory.

2. 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 add a drop table before each CREATE statement

4. Import Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p

If you like to load 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.

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

--opt This option opens all options that will increase the speed of file exports 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 then perform the export, but write to the guide file when read.

-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another 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 the data that will be placed in the exported file.
　　
Suppose you need to create a file for the account that you want to use in a form, the manager wants to see all orders for this year (2004), they are not interested in DDL, and require a comma-delimited file because it's easy to import 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=, Me Et_a_geek Orders 

This will get 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 a data definition language that fully describes the structure of the database.
　　
SELECT into outfile:

If you think the mysqldump tool is not cool enough to use SELECT into OutFile, MySQL also provides a command that has the opposite effect to 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 status of Mysqld, the is Alive description is running, and an error may require a username and password.

4. Start the MySQL listener program.

5. bin/mysql–p meet_a_geek;//Enter 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_d Ate >= ' 2000-01-01 ' 

　　
After you press RETURN (enter), the file is created. This sentence is like a regular SELECT statement that redirects the output of the screen to the file. This means that you can use joins 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, and try this:
　　
To create a text file named Report_g.rpt in the MySQL directory, 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, and this file will contain the last name of all the customers you entered in the Customers table. As you can see, you'll be able to use the import/export (Import/export) method you learned today to help you get a report.