MySQL backup and recovery

Logical backup:
1. mysqldump (data export tool)
Mysqldump options db_name [table_name] // back up a single database
Mysqldump option -- database database-name1 [databases-name2]... // back up one or more of the specified database
Mysqldump option -- all-database // back up all databases
Link options:
-U: Specifies the user name.
-P: Password
-H: Specify the Server IP address or domain name
-P (uppercase): Specifies the port
Eg:/usr/bin/mysqldump-u root-H 202.194.132.237-P 3306-p bbs user>/home/wuxiaoxiao/user.txt
Output content options:
-- Add-drop-Database: add the drop database statement before each database creation statement.
-- Add-drop-table: add the drop TABLE statement before each table creation statement.
-N: does not contain database creation statements.
-T: Create statement that does not contain data tables
-D: does not contain data
Output format options:
-- Compact: make the output result concise
-C -- compact-insert: Make the insert statement in the output file contain the field name
-T: Back up the data in the database table into two files: simple data text and table creation SQL.
-- Fields-terminated-by = Name (domain delimiter)
-- Fields-enclosed-by = Name (domain quote)
-- Fields-optionally-enclosed-by = Name (optional referenced in the field)
-- Fields-escaped-by = Name (Transfer Character)
E. g:/usr/bin/mysqldump-u root-H 202.194.132.237-P 3306-p bbs user-T./bak
Character Set options:
-- Default-character-set = Name: sets the exported client character set.
Eg: mysql-u root-p -- compact -- default-character-set = utf8 BBS user> test.txt
Other options:
-F: refresh logs before backup
-L: Apply a read lock to all tables (used during backup to ensure consistency of backup data)

Backup:
Back up all databases:
Mysqldump-u root-p -- all-database> test. SQL
Backup database Test
Mysqldump-u root-P test> test. SQL
Backup the temp table under database test:
Mysqldump-u root-P test demp> test. SQL
All tables in the backup database are separated by commas (,) and backed up to/temp.
Mysqldump-u root-P test-T/temp -- fields-terminated-','
Full recovery:
Msyql-u root-P <bakfile
Note: After the backup is restored, the data is incomplete. You also need to redo the logs executed after the backup.
Mysqlbinlog BINLOG-file | mysql-u root-P ***
A complete example of mysqldump backup and recovery:
Nine o'clock A.M. back up database
Mysqldump-u root-P-l-f test> test. dmp
After half past nine, I want to insert data into the database.
The database suddenly fails at and the data cannot be accessed. The backup must be restored.
Mysql-u root-P test <test. dmp
The recovered data is incomplete. The data inserted at half past nine is not recovered.
Use mysqlbinlog to restore the BINLOG since mysqldump backup
Mysqlbinlog binlogfilename | mysql-u root-P Test
Restore based on time points:
If a misoperation occurs at ten o'clock A.M., you can use the following statement to back up and BINLOG to restore the database to the fault:
Mysqlbinlog -- stop-date = "2005-04-20 9:59:59" binlogfile | mysql-u root-P Test
Skip the time point of the fault, continue executing the subsequent BINLOG, and complete the recovery
Mysqlbinlog -- start-date = "2005-04-20 9:59:59" binlogfile | mysql-u root-P Test
Location-based recovery:
Mysqlbinlog -- start-date = "9:55:59" -- stop-date = "10:05:00" binlogfile> test. SQL
Check this file to find the location number before and after the error statement, for example
Mysqlbinlog -- stop-position = "368312" binlogfile | mysql-u root-P Test
Mysqlbinlog -- start-position = "368315" binlogfile | mysql-u root-P Test

Table import and export:
Export:
Mysqldump-u username-p-t target_dir dbname tablename [Options]
Options:
-- Fields-terminated-by = Name (domain delimiter)
-- Fields-enclosed-by = Name (domain quote)
-- Fields-optionally-enclosed-by = Name (optional referenced in the field)
-- Fields-escaped-by = Name (Transfer Character)
All tables in the backup database are separated by commas (,) and backed up to/temp.
Mysqldump-u root-p-t/temp test -- fields-terminated-by ',' -- fields-optionally-enclosed-'"'
Import:
Msyqlimport-u root-P [local] dbname order_tab.txt [Options]
Options:
-- Fields-terminated-by = Name (domain delimiter)
-- Fields-enclosed-by = Name (domain quote)
-- Fields-optionally-enclosed-by = Name (optional referenced in the field)
-- Fields-escaped-by = Name (Transfer Character)
Eg: mysqlimport-u root-P test order.txt -- fields-terminated-by = ',' -- fields-enclosed-by = '"'

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/feng_sundy/archive/2008/12/11/3496744.aspx