Mysqldump database table

A) Data import and export between data tables on the same database server:

1. If the structure of the table tb1 and TB2 is identical, the data in table TB1 can be imported into the table TB2 using the following command:

INSERT INTO DB2.TB2 select * from DB1.TB1

2. If the table tb1 and TB2 only have partial fields that are identical, to implement importing some of the fields from tb1 into the same field in TB2, use the following command:

Insert into DB2.TB2 (field 1, Field 2, Field 3 ...) Select field 1 ', Field 2 ', Field 3 ', .... from DB1.TB1;

b) But to implement the data from the data table TB1 in the remote database into the local database data table TB2, there is no good way to use SQL statements locally as shown in the example above. You will use the export to the database (table), import. Export the remote database (table) to a local file, and then import (restore) the database (table) into the local database. Many of these methods are used in backup and restore of databases

1. Remote database (table) export to local Database (table) file

(1) Exporting a database

mysqldump-h192.168.1.1-uroot-p123456--databases mydb > Mydb.bak; Export the MyDB database on the 192.168.1.1 Host to a local Mydb.bak file

(mysqldump-uroot-p123456--databases mydb1 > Mydb1.bak; Export the MYDB1 database on the local MySQL server to a local Mydb1.bak file)

(2) Export Data sheet

mysqldump-h192.168.1.1-uroot-p123456 mydb tb1 > Tb1.bak; Export the TB1 data table for the MyDB database on the 192.168.1.1 Host to a local Tb1.bak file

(mysqldump-uroot-p123456 mydb1 tb2 > Tb2.bak; Export the TB2 data table of the MYDB1 database on the local host to a local Tb2.bak file)

2. Import Database (table)

(1) Import Database

In the local database, create a database mydb the same name as the database that should be exported:

mysql> CREATE DATABASE MyDB;

Then exit the database and use the following command to import the database file Mydb.bak to the local database mydb:

mysql-uroot-p123456 MyDB </root/data/mydb.bak;

Or into MySQL, use the SOURC command to complete the database import, as follows:

Mysql> Source/root/data/mydb.bak; /root/data/mydb.bak is the local storage location for the remote database mydb export File

(2) Import data Sheet

In the local database, create a database mydb the same name as the database that should be exported:

mysql> CREATE DATABASE MyDB;

Then use the source command in MySQL to complete the import of the data table, as follows:

Mysql> Source/root/data/tb1.bak; /root/data/tb1.bak is the local storage location for Remote data table TB1 export files

III) Export table record to file

mysql-hxxx.xxx.xxx.xxx-uroot-pxxxxxx-d whois-e "Select domain from domaininfo where queryflag = ' 2 ' or Queryflag = ' 3 ' >/data/dump/overdue

Note: In the database (table) in the import, export process, depending on the size of the database (table), it takes a certain amount of time overhead, please wait patiently ...

The following command-line specific usage is as follows: Mysqldump-u username-p password-D is the name of the script in the database name table;

1, export the database as DBNAME table structure (where the username is root, the password is DBPASSWD, the generated script is named Db.sql)
Mysqldump-uroot-pdbpasswd-d dbname >db.sql;

2. Export the database as dbname a table (test) structure
Mysqldump-uroot-pdbpasswd-d dbname test>db.sql;

3. Export the database to dbname all table structures and table numbers (without-D)
MYSQLDUMP-UROOT-PDBPASSWD dbname >db.sql;

4. Export the database as dbname a table (test) structure and table numbers (without-D)
MYSQLDUMP-UROOT-PDBPASSWD dbname test>db.sql;

Mysqldump database table