Restore a database: mysql-h localhost-u root-p123456 Www<c:/www.sql
Backing up a database: mysqldump-h localhost-u root-p123456 www > d:/www2008-2-26.sql
The following is a test in the program
$command = "Mysqldump--opt-h $dbhost-u $dbuser-P $dbpass $dbname | gzip > $backupFile ";
$command = "Mysqldump-h localhost-u root-p123456 guestbook > Guestbook2-29.sql";
System ($command);
echo "Success";
************************************************
Commands to back up the MySQL database
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql
backing up the MySQL database to a format with a delete table
Backing up the MySQL database is a format with a delete table that allows the backup to overwrite the existing database without having to manually delete the existing database.
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql
Compress the MySQL database directly backup
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz
Back up a MySQL database (some) table
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql
Back up multiple MySQL databases at the same time
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql
Backing up the database structure only
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql
Back up all databases on the server
Mysqldump–all-databases > Allbackupfile.sql
Commands to restore MySQL database
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql
Restore a compacted MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName
To transfer a database to a new server
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName
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 the database
Common source Commands
Go to MySQL Database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source D:/wcnc_db.sql
Mysqldump supports the following options:
–add-locks
Add lock tables before each table is exported and then unlock table. (To make it faster to insert into MySQL).
–add-drop-table
Add a drop table before each create statement.
–allow-keywords
Allows you to create column names that are keywords. This is done by prefixing the table name with each column name.
-c,–complete-insert
Use the full INSERT statement (with the column name).
-c,–compress
If both the client and the server support compression, compress all the information between the two.
–delayed
Insert a row with the insert delayed command.
-e,–extended-insert
Use the new multi-line insert syntax. (Give a more condensed and faster INSERT statement)
-#,–debug[=option_string]
The use of the tracker (for debugging purposes).
Help
Displays a help message and exits.
–fields-terminated-by= ...
–fields-enclosed-by= ...
–fields-optionally-enclosed-by= ...
–fields-escaped-by= ...
–fields-terminated-by= ...
These options are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
LOAD DATA infile syntax.
-f,–flush-logs
Before starting the export, wash down the log files in the MySQL server.
-f,–force,
Even if we get a SQL error during the export of a table, continue.
-h,–host=.
Export data from a MySQL server on a named host. The default host is localhost.
-l,–lock-tables.
Locks all tables for starting export.
-t,–no-create-info
Do not write table creation information (CREATE TABLE statement)
-d,–no-data
No row information is written to the table. This is useful if you only want to export the structure of a table!
–opt
With –quick–add-drop-table–add-locks–extended-insert–lock-tables.
Should give you the fastest possible export for reading into a MySQL server.
-pyour_pass,–password[=your_pass]
The password to use when connecting to the server. If you do not specify the "=your_pass" section, Mysqldump requires a password from the terminal.
-P Port_num,–port=port_num
The TCP/IP port number to use when connecting to a single host. (This is used to connect to a host other than localhost because it uses a UNIX socket.) )
-q,–quick
Do not buffer queries, export directly to stdout; use Mysql_use_result () to do it.
-s/path/to/socket,–socket=/path/to/socket
A socket file that is used with localhost when it is connected (it is the default host).
-t,–tab=path-to-some-directory
For each given table, create a Table_name.sql file that contains the SQL create command, and a table_name.txt file that contains the data. Note: This only works when the mysqldump is running on the same machine as the mysqld daemon. The format of the. txt file is determined by the –fields-xxx and –lines–xxx options.
-U user_name,–user=user_name
The user name that MySQL uses when connecting to the server. The default value is your UNIX login name.
-O var=option,–set-variable var=option
Sets the value of a variable. The possible variables are listed below.
-v,–verbose
Verbose mode. Print out more information that the program has done.
-v,–version
Print the version information and exit.
-w,–where= ' Where-condition '
Only the selected records are exported; Note that the quotation marks are mandatory!
"–where=user= ' JIMF '" "-wuserid>1″"-wuserid<1″
The most common use of mysqldump may be to make a backup of the entire database:
Mysqldump–opt Database > Backup-file.sql
But it is also useful to enrich another MySQL database with information from one database:
Mysqldump–opt Database | Mysql–host=remote-host-c Database
Since mysqldump exports a complete SQL statement, it is easy to import the data using the MySQL client program:
Shell> mysqladmin Create Target_db_name
shell> MySQL Target_db_name < backup-file.sql
It is
shell> MySQL Library name < file name
Transferred from: http://blog.csdn.net/bxbx258/article/details/2945832
MySQL database backup and restore command Mysqldump,source usage