MySQL database backup and recovery

MySQL database backup and recovery

Restore a database: mysql-h localhost-u root-p123456 www

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

Where www is the database name

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
is that
shell> MySQL Library name < file name

The last two days, because the database was designed to not notice when the table is to be built on another server (the test server database is distributed, different tables may be on different servers). Now there are a lot of test data in it, and do not want to add it again. So I want to be able to back up, and then restore to the target machine up. Then yesterday's words toss a bit, consult colleagues, online search information. Here is the usage record.
mysqldump command: MySQL database backup restore

First, the common operation:
Backing up the entire database
Format:
MYSQLDUMP-H host name-P Port-u user name-p password (–database) database name > file name. sql
Mysqldump-h{hostname}-p{port}-u{username}-p{password} {DatabaseName} > {backupfile.sql}
For example:

The code is as follows:
1
mysqldump-hlocalhost-p3306-uzhuchao-p123456 db_test > Backfile1.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.
Format: Mysqldump-–add-drop-table-u{username}-p{password} {DatabaseName} > {backfile.sql}
For example:

The code is as follows:
1
Mysqldump-–add-drop-table–uzhuchao-p123456 db_test > Backfile2.sql
Compress the MySQL database directly backup
Format: Mysqldump-h{hostname}-u{username}-p{password} {databasename} | gzip > {backfile.sql.gz}
For example:

The code is as follows:
1
mysqldump–hlocalhost–uzhuchao–p123456 Db_test1 | gzip > backfile3.sql.gz
Back up a MySQL database (some) table
Format: MYSQLDUMP-H host name-P Port-u user name-p password (–tables |–quick) database Name Table name 1 (table Name 2 ...) > file name. SQL (default in parentheses).
Mysqldump-h{hostname} (-p{port})-u{user}-p{password} (–tables |–quick) {databasename} {table1} {table2} > {backfile . SQL}
For example:

The code is as follows:
1
2
3
4
mysqldump-hlocalhost-uzhuchao-p123456 db_test tbl_test > Backfile4-1.sql
mysqldump-hlocalhost-p3306-uzhuchao-p123456 db_test tbl_test > Backfile4-2.sql
mysqldump-hlocalhost-p3306-uzhuchao-p123456--quick db_test tbl_test > Backfile4-3.sql
mysqldump-hlocalhost-p3306-uzhuchao-p123456--tables db_test tbl_test1 tbl_test2 > Backfile4-4.sql
Back up multiple MySQL databases at the same time
Format: Mysqldump-h{hostname} (-p{port})-u{username}-p{password}–databases {databasename1} {databasename2} { Databasename3} > Multibackfile.sql
For example:

The code is as follows:
1
Mysqldump-hlocalhost-uzhuchao-p123456–databases db_test1 db_test2 db_test3 > Multibackfile.sql
Backing up the database structure only
Format: mysqldump–no-data–databases {databasename1} {databasename2} > {structurebackfile.sql}
For example:

The code is as follows:
1
Mysqldump–no-data–databases Db_test1 db_test2 > Structurebackfile.sql
Back up all databases on the server
Format: mysqldump–all-databases > Allbackupfile.sql

========================================================================

Commands to restore MySQL database
Format: Mysql-h{hostname}-u{username}-p{password} {DatabaseName} < {backfile.sql}
For example:

The code is as follows:
1
mysql-hlocalhost-uroot-p123456 Db_test4 < Back_file1.sql
Restore a compacted MySQL database
Format: Gunzip < {backfile.sql.gz} | Mysql–u{username}–p{password} {DatabaseName}
For example:

The code is as follows:
1
Gunzip < backfile.sql.gz | mysql–uzhuchao–p123456 DB_TEST5
To transfer a database to a new server
Mysqldump–u{username}–p{password} {DatabaseName} | mysql–host=*.*.*.*–c {DatabaseName}

Second, other:
1, if the port is the default of 3306, you can omit the-p {port number} this item.

2. The contents of {} in the command line format are variables
Host name: {hostname}
Port: {port} (typically default 3306, default)
User name: {user} {username} (such as root)
Password: {password}
Database name: {databasename}
Table name: {table} {table1} {table2}
File name: {backfile.sql}

MySQL database backup and recovery