1. Import Export
MySQL frequently used to import data commands:
1.mysql command
The code is as follows |
Copy Code |
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql |
2.source command
The code is as follows |
Copy Code |
Mysql>source Backupfile.sql |
Specific methods
1.mysql Export the entire database
The code is as follows |
Copy Code |
Mysqldump-u user name-p database name > exported file name Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql |
2.mysql Export Database A table
The code is as follows |
Copy Code |
Mysqldump-hhostname-uusername-ppassword database tablename> exported file name Mysqldump-hlocalhost-uroot hqgr t_ug_user> User.sql |
2-1. If you need to export the MySQL inside the function or stored procedures
The code is as follows |
Copy Code |
Mysqldump-hhostname-uusername-ppassword-ntd-r databasename > Backupflie.sql Mysqldump-hlocalhost-uroot-ntd-r hqgr > Hqgr.sql |
The-NTD is to represent the exported stored procedure;-R is to represent the exported function
3.mysql Export a database structure
The code is as follows |
Copy Code |
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.mysql Import Database
Common source Commands
Enter the MySQL database console,
The code is as follows |
Copy Code |
such as Mysql-u root-p |
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
The code is as follows |
Copy Code |
Mysql>source D:wcnc_db.sql |
2. Set session Character set encoding
Set names Utf-8;
Common options in Mysqldump
Option/option action/action performed
--add-drop-table
This option will precede each table with the drop table IF exists statement, which guarantees that the MySQL database will not be returned
Wrong, because each time the guide back, will first check whether the table exists, the existence of the delete
--add-locks
This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents the records from being imported into the database again
Other user actions on the table
-C Or-complete_insert
This option causes the mysqldump command to add a column (field) name to each generated INSERT statement. When data is exported to another database
This option is useful.
--delayed-insert Add delay option to insert command
-F or-flush-logs Use this option to flush the MySQL server log before performing the export.
-F Or-force Use this option to continue exporting even if errors occur
--full This option adds additional information to the statement in the CREATE table
-L or-lock-tables using this option, the server will lock the table when the table is exported.
-T Or-no-create-info
This option causes the mysqldump command to not create a creating table statement, which requires that you only need data without DDL (database definition statement
) is very convenient.
-D or-no-data This option causes the mysqldump command to not create INSERT statements.
You can use this option when you need only DDL statements.
--opt This option opens all options that will increase the speed of file exports and create a file that can be imported faster.
-Q or-quick This option allows MySQL to not read the entire exported content into memory and then perform the export, but write the guide file when read
In
-T Path or-tab = Path This option will create two files, one file contains DDL statements or table creation statements, and another file contains
Data. The DDL file is named Table_name.sql, and the data file is named Table_name.txt. The path name is the directory where the two files are stored. Directory must
Must already exist, and the user of the command has privileges to the file.
-W "where Clause" Or-where = "where Clause"
As mentioned earlier, you can use this option to filter the data that will be placed in the exported file.
Suppose you need to create a file for the account that you want to use in a form, and the manager wants to see all orders for this year (2004), and they
Not interested in DDL, and requires a comma-delimited file because it's easy to import into Excel. To complete this character, you can use the following
The sentence:
Bin/mysqldump–p–where "order_date >= ' 2000-01-01 '"
–tab =/home/mark–no-create-info–fields-terminated-by=, Meet_a_geek Orders
This will get the results you want.
Schema: Schema the set of statements, expressed in data definition language, that completely describe
The structure of a data base.
A set of statements expressed in a data definition language that fully describes the structure of the database