Mysqldump command

Mysqldump is similar to the opposite tool in many aspects. They have the same options. However, mysqldump can do more. It can load the entire database into a separate text file. This package contains all the SQL commands required to recreate your database. This command gets all the modes (Schema, which is explained later) and converts them into DDL syntax (CREATE statement, that is, database definition statement) to get all the data, create an INSERT statement from the data. This tool reverses all the designs in your database. Because everything is contained in a text file. This text file can be imported back to MySQL using a simple batch processing and a suitable SQL statement. This tool is incredibly simple and fast. There will never be a headache.


Mysqldump is a utility used by mysql to store databases. It mainly generates an SQL script, including the CREATE TABLE INSERT command required to recreate the database from scratch. Let's learn it together!

1. mysqldump: database backup program

There are three methods to call mysqldump:

Mysqldump [options] db_name [tables]
Mysqldump [options] --- database DB1 [DB2 DB3. ..]
Mysqldump [options] -- all -- database

If no table is specified or the --- database or -- all -- database option is used, the whole database is dumped.

1. Back up a database.

Mysqldump-uroot-p123456 mysql> mysql_backup. SQL

The structure and data of database mysql are backed up here, and the generated SQL file does not contain statements for creating database mysql.

2. You can use one command to back up mysql and test multiple databases:

Mysqldump-u root-p123456 -- database mysql test> my_databases. SQL

The generated SQL file contains the statement for creating database mysql and test.

3. Back up all databases:

Mysqldump-u root-p123456 -- all-databases> all_databases. SQL

4. Export the structure of the mysql database

Mysqldump-u root-p123456-d -- add-drop-table mysql> mysql_define. SQL

5. Export all data of one data and compress it with gz

Mysqldump-u root-p123456 mysql | gzip> mysql. SQL .gz

The following code reads the dump file back to the server:

Mysql db_name <backup-file. SQL
Mysql-e "source/path-to -- backup/backup-file. SQL" db_name

Or restore from the gz file.

Gunzip-f <mysql. SQL .gz | mysql-u root-p123456 test

II. SELECT... INTO OUTFILE

SELECT... into outfile 'File _ name' can write the selected row INTO a file, which is created on the server host.

SELECT... into outfile is the complement of load data infile. The syntax used for the exort_options part of the statement includes some FIELDS and LINES clauses, which are used together with the load data infile statement.

In the following example, a file is generated, and each value is separated by a comma. This format can be used by many programs.

SELECT * into outfile '/tmp/result.txt'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n'
FROM mysql. user;

Export data from the user table of the mysql database to/tmp/result.txt

SELECT... into outfile can only export data, but cannot export the structure. It is generally used together with load data.

III. LOAD DATA INFILE

The load data infile statement is used to quickly read rows from a text file and LOAD a table. The file name must be a text string.

The character set indicated by character_set_database system variables is used to interpret information in the file.

Load data local infile '/tmp/result.txt' into table test. user
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n'

Import/tmp/result.txt data to the user table of the test database.

IV. Import and export formats

Fields terminated by ',' delimiter used between FIELDS, number
Optionally enclosed by '"' enclose fields with ', which is invalid for numeric type
Lines terminated by '\ n' record delimiter \ n line break




Import and Export MYSQL database instances under the CMD command

1. Import the database (local)

For example, if you install xampp on the local D disk (the steps are the same, pay attention to the location), follow these steps to import the database:

D:
Cd xampp \ mysql \ bin \
Xampp \ mysql \ bin \ mysql-u database name-p password //

Mysql> use net2003; // find the database to be imported

Mysql> source D: \ database location

Bytes --------------------------------------------------------------------------------------

2. Export the database (local)

D:
Cd xampp \ mysql \ bin \

Mysql-h server-u user-p password name of the database to be imported <backup file. SQL
Mysqldump-u database name-p password database> net2003. SQL database

Run explorer to open the exported database and find the exported database.

This is all done.

Bytes --------------------------------------------------------------------------------------

The foreign key associated table problem occurs during import. Solution:

Run the following command before running source file. SQL:
Use databasename;
SET FOREIGN_KEY_CHECKS = 0;
Run the following command after the import is successful:
SET FOREIGN_KEY_CHECKS = 1;