Using commands is a commonly used mysqldump to operate (mysqldump commands are located in the mysql/bin/directory)
The Mysqldump tool has a number of options, some of which are listed below:
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 be returned without error, because each time it is returned, the table is first checked for existence and deleted.
--add-locks
This option bundles the previous lock table and unlock table statement in the INSERT statement. This prevents other users from working on the table when these records are imported again into the database
-C Or-complete_insert
This option causes the mysqldump command to add a column (field) name to each generated INSERT statement. This option is useful when exporting data to another database.
--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 is handy when you need only data without the need for DDL (database definition statements).
-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 to the guide file when read.
-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. The directory 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.
1. Export Data and table structure:
Mysqldump-u User name-p password database name > database name. sql
#/usr/local/mysql/bin/mysqldump-uroot-p ABC > Abc.sql
Prompt for password after knocking in return
2. Export table structure only
Mysqldump-u User name-p password-d database name > database name. sql
#/usr/local/mysql/bin/mysqldump-uroot-p-D ABC > Abc.sql
NOTE:/usr/local/mysql/bin/---> MySQL data directory
Second, import the database
1, first to build a blank database
Mysql>create database ABC;
2. Import Database
Method One:
(1) Select database
Mysql>use ABC;
(2) Setting up the database encoding
Mysql>set names UTF8;
(3) Import data (note the path to the SQL file)
mysql>source/home/abc/abc.sql;
Method Two:
Mysql-u User name-p password database name < database name. sql
#mysql-UABC_F-P ABC < Abc.sql
The above command means:
Log on to the MySQL server
Select the database to import (use new_db)
Set character encoding, don't ask why, this is to prevent garbled
The code is best consistent, or there will be inexplicable mistakes
For example: GBK, gb2312, UTF8 ...
The final step is to import the source Db_bak.sql
Db_bak.sql this file in the current directory. You can use an absolute path to indicate
It is recommended that you import using the second method.
Note: command line mode with SQL command