SQL synchronization remote Database (table) to local

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 ...

IV) Import and export data from a data table

1. Export the table data to the file:
mysql-uroot-p123456--default-character-set=utf8
Use Guanjia;
SELECT * from driver into outfile '/tmp/a.txt ';

(SELECT * from Crawldocs ORDER BY doccrawltime desc limit + to outfile '/tmp/weixindata ' fields TERMINATED by ', ';)
It is generally best to export to the/tmp directory, because the MySQL user can also write.




2. The table data is imported into the data table:
mysql-uroot-p123456--default-character-set=utf8
Use Guanjia;
Load data infile '/tmp/a.txt ' into table test CHARACTER SET UTF8;

(Load Data infile '/tmp/weidata ' into table crawldocs CHARACTER SET UTF8 fields TERMINATED by ', ';)

If you import an error like HY000: File '/tmp/a.txt ' not Found (errcode:13), it is most likely because the MySQL user does not have permission to access the file.
Use Chown mysql:mysql/tmp/a.txt to set the file's owning as a MySQL user, and executing the above command again will generally complete the import.

SQL synchronization remote Database (table) to local