MySQL Database backup restore

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

MySQL Database backup restore