How to export or import MySQL database structure and data_PHP tutorial

When using MySQL, sometimes you need to export data from the database for use on local or other database systems, or import existing data into the MySQL database. Next, let's analyze how to export and import the structure of the MySQL database and data.
The mysqldump tool of MySQL is used to export. The basic usage is:
shell> mysqldump [OPTIONS] database [tables]
If no table is given, the entire database will be exported .
If you want to see the various options and their meanings supported by the mysqldump version, you can execute this command:
shell> mysqldump --help
Let’s talk about the main options supported by mysqldump And meaning:
--add-locks: Add LOCK TABLES before each table is exported and UNLOCK TABLE afterwards.
--add-drop-table: Add a drop table before each create statement.
--allow-keywords: Allows the creation of column names that are keywords. This is done by prefixing the table name to each column name.
-c, --complete-insert: Use the complete insert statement (using column names).
-C, --compress: If both the client and the server support compression, compress all information between them.
--delayed: Use the INSERT DELAYED command to insert lines.
-e, --extended-insert: Use the new multi-line INSERT syntax. (Gives a more compact and faster insert statement)
-#, --debug[=option_string]: Track 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 have corresponding LOAD DATA INFILE clause has the same meaning as LOAD DATA INFILE syntax:

-F, --flush-logs: Flush the log files in the MySQL server before starting the export.
-f, --force : Even if we get a SQL error during a table export, continue.
-h, --host=.. : Export data from the MySQL server on the named host. The default host is localhost.
-l, --lock-tables: Lock all tables to start exporting.
-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 get the export of a structure with only one table.
--opt: Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables.
-pyur_pass, --password[=PASSWORD]: The password used when connecting to the server. If you do not specify the "="PASSWORD" part, mysqldump requires the password from the terminal.
-P port_num, --port=port_num: The TCP/IP port number used when connecting to a host. (This is used Useful for connecting to hosts other than localhost because it uses Unix sockets. )
-q, --quick: Do not buffer the query, export directly to stdout; 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: For each given table, create a table_name.sql file, which contains the SQL CREATE command, and a table_name.txt file, which contains the data. Note: This only works if mysqldump is running in the mysqld daemon. It only works when on the same machine. The format of the .txt file is determined by the --fields-xxx and --lines--xxx options.
-u user_name, --user=user_name: Connect to the server. The username used by MySQL. The default value is your Unix login name.
-O var=option, --set-variable var=option The possible variables are listed below. .
-v, --verbose: Print out more information about what the program does.
-w, --where=where-condition: Export only the selected records. ; Note that quotation marks are mandatory
"--where=user=jimf" "-wuserid＞1" "-wuserid＜1"
We can use mysqldump to make a backup of the entire database:
mysqldump --opt database ＞ backup-file.sql
Or we can fill information from one database into another MySQL 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