Mysql database backup and restore command Mysqldump,source usage _mysql

Restore a database: mysql-h localhost-u root-p123456 Www<c:\www.sql

Back up a database: mysqldump-h localhost-u root-p123456 www > d:\www2008-2-26.sql

The following is a test in a 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 for backing up the MySQL database
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

Backing up the MySQL database for a deleted table format
Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Directly compress MySQL database to backup
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Backing up a MySQL database (some) tables
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases at the same time
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Just back up the database structure
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server
Mysqldump–all-databases > Allbackupfile.sql

command to restore MySQL database
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restoring a compressed MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Transferring 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 Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p

Mysql>use Database

Then use the source command, followed by the script file (such as 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. (in order to allow faster insertion into MySQL).

–add-drop-table
Add a drop table before each create statement.

–allow-keywords
Allow creation of column names that are keywords. This is done by the table name prefix in 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
Inserts a row with the insert delayed command.

-e,–extended-insert
Use the new multiple-line insert syntax. (Gives a tighter and faster insert statement)

-#,–debug[=option_string]
Trace the use of the program (for debugging).

–help
Displays a help message and exits.

–fields-terminated-by= ...

–fields-enclosed-by= ...

–fields-optionally-enclosed-by= ...

–fields-escaped-by= ...

–fields-terminated-by= ...

These selections are used with the-T selection and have the same meaning as the corresponding load DATA infile clause.
The LOAD DATA infile syntax.

-f,–flush-logs
Before you start exporting, wash 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 the MySQL server on the named host. The default host is localhost.

-l,–lock-tables.
Lock all tables for starting export.

-t,–no-create-info
Do not write table creation information (CREATE TABLE statement)

-d,–no-data
Any row information that does not write to the table. This is useful if you want to export only 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, the mysqldump requires a password from the terminal.

-P Port_num,–port=port_num
The TCP/IP port number to use when connecting to a 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
The socket file used by the 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 works only when Mysqldump is running on the same machine that the mysqld daemon is running. The format of the. txt file is based on 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 about what the program is doing.

-v,–version
Print 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 mysqldump uses a backup that might make the entire database:
Mysqldump–opt Database > Backup-file.sql

But it's also useful for enriching another MySQL database with information from one database:
Mysqldump–opt Database | Mysql–host=remote-host-c Database
Since mysqldump is exporting complete SQL statements, it is easy to import data into the MySQL client program:

Shell> mysqladmin Create Target_db_name
shell> MySQL Target_db_name < backup-file.sql
It is
shell> MySQL Library name < filename