When using MySQL, you sometimes need to export data from the database for use locally or on other database systems, or import existing data into the MySQL database. Next we will analyze how to export and import the structure and data of the MySQL database .?? Export mysqldump job SyntaxHighlighter. al to use MySQL
When using MySQL, you sometimes need to export data from the database for use locally or on other database systems, or import existing data into the MySQL database. Next we will analyze how to export and import the structure and data of the MySQL database.
?? MySQL mysqldump is used for export. The basic usage is as follows:
?? Shell> mysqldump [OPTIONS] database [tables]
?? If no table is specified, the entire database is exported.
?? To view the various options and meanings supported by mysqldump, run the following command:
?? Shell> mysqldump -- help
?? The following describes the main options and meanings supported by mysqldump:
?? -- Add-locks: add lock tables and unlock table before each TABLE is exported.
?? -- Add-drop-table: add a drop table before each create statement.
?? -- Allow-keywords: Names of columns allowed to be created as keywords. This is done by the table name prefix on each column name.
?? -C, -- complete-insert: use the complete insert statement (with the column name ).
?? -C, -- compress: if both the client and server support compression, compress all information between the two.
?? -- Delayed: Use the insert delayed command to INSERT rows.
?? -E, -- extended-insert: use the new multi-row INSERT syntax. (A more compact and faster insert statement is provided)
?? -#, -- Debug [= option_string]: Trace program usage (for debugging ).
?? -- Fields-terminated-by =...
?? -- Fields-enclosed-by =...
?? -- Fields-optionally-enclosed-by =...
?? -- Fields-escaped-by =...
?? -- Fields-terminated-by =...
?? These options are used with the-T option, and the corresponding load data infile clause has the same meaning as the load data infile syntax:
?? -F, -- flush-logs: Wash the log files on the MySQL server before export.
?? -F, -- force: even if we get an 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: to lock all tables for export.
?? -T, -- no-create-info: Do not write TABLE creation information (create table statement)
?? -D, -- no-data: Do not write any row information of the table. With this parameter, we can export the structure of only one table.
?? -- Opt: same as -- quick -- add-drop-table -- add-locks -- extended-insert -- lock-tables.
?? -Pyour_pass, -- password [= PASSWORD]: password used to connect to the server. If you do not specify "=" PASSWORD ", mysqldump requires a PASSWORD from the terminal.
?? -P port_num, -- port = port_num: The TCP/IP port number used to connect to a host. (This is used to connect to a host other than localhost because it uses Unix sockets .)
?? -Q, -- quick: directly export the data to stdout without buffering the query; use mysql_use_result () to do it.
?? -S/path/to/socket, -- socket =/path/to/socket: socket file used when connecting to localhost (default host.
?? -T, -- tab = path-to-some-directory: CREATE a table_name. SQL file for each given table, which contains the SQL CREATE command and a table_name.txt file, which contains data. Note: This only works when mysqldump runs on the same machine where the mysqld daemon is running .. The format of the txt file is determined by the options -- fields-xxx and -- lines -- xxx.
?? -U user_name, -- user = user_name: username used by MySQL when connecting to the server. The default value is your Unix login name.
?? -O var = option, -- set-variable var = option to set the value of a variable. Possible variables are listed below.
?? -V, -- verbose: lengthy mode. Print out more information about the program.
?? -W, -- where = where-condition: only the selected records are exported. Note that the quotation marks are mandatory.
?? "-- Where = user = jimf" "-wuserid> 1" "-wuserid <1"
?? We can use mysqldump to back up the entire database:
?? Mysqldump -- opt database> backup-file. SQL
?? Or we can fill in the information of a database to another MySQL database:
???? Mysqldump -- opt database | mysql -- host = remote-host-C database because mysqldump exports a complete SQL statement, it is easy to use a mysql client program to import data:
???? Shell> mysqladmin create target_db_name
???? Shell> mysql target_db_name <backup-file. SQL
