MySQL database (table) import/export (backup and restoration)-PHP source code

MySQL database (table) Import and Export (backup and restore) 1) import and export data between data tables on the same database server:

1. if the table tb1 and tb2 have the same structure, you can use the following command to import data from table tb1 to table tb2:

Insert into db2.tb2 select * from db1.tb1

2. if only some fields in the tb1 and tb2 tables are the same, to import some fields in tb1 to the same fields corresponding to tb2, run the following command:

Insert into db2.tb2 (Field 1, field 2, Field 3 ......) Select field 1', field 2', field 3 ',...... From db1.tb1;

2) However, you must import the tb1 data in the remote database to the tb2 data table in the local database, there is no good way to directly use SQL statements locally, as shown in the preceding example. In this case, you need to use the database (table) for export and import. Export a remote database (table) to a local file, and then import (restore) the database (table) to the local database. These methods are mostly used in database backup and restoration.

1. export a remote database (table) to a local database (table) file

(1) export the database

Mysqldump-h192.168.1.1-uroot-p123456 -- databases mydb> mydb. bak; // export the mydb database on host 192.168.1.1 to the local mydb. bak file.

(Mysqldump-uroot-p123456 -- databases mydb1> mydb1.bak; // export the mydb1 database on the local mysql server to the local mydb1.bak file)

(2) export data tables

Mysqldump-h192.168.1.1-uroot-p123456 mydb tb1> tb1.bak; // export the tb1 data table of mydb database on host 192.168.1.1 to the local tb1.bak file.

(Mysqldump-uroot-p123456 mydb1 tb2> tb2.bak; // export the tb2 data table of mydb1 database on the local host to the local tb2.bak file)

2. import the database (table)

(1) import database

Create a database with the same name as the exported database mydb in the local database:

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 enter mysql and use the sourc command to import the database as follows:

Mysql> source/root/data/mydb. bak; // root/data/mydb. bak is the local storage location of the exported files in the remote database mydb.

(2) import data tables

Create a database with the same name as the exported database mydb in the local database:

Mysql> create database mydb;

Then, use the source Command in mysql to import data tables, as shown below:

Mysql> source/root/data/tb1.bak; // root/data/tb1.bak is the local storage location of the exported files in the remote data table tb1.

3) export table records to files

Mysql-hxxx. xxx. xxx. xxx-uroot-pxxxxxx-d whois-e "select domain from domainInfo where queryFlag = '2' or queryFlag = '3'">/data/dump/overdue

Note: During the import and export of databases (tables), depending on the size of the database (tables), it takes a certain amount of time. please wait patiently ......

4) import and export data in a data table

1. export table data to a 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 2000 into outfile '/tmp/weixinData 'Fields TERMINATED ',';)
It is generally better to export data to the/tmp Directory, because mysql users can also write data.



2. import table data to 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 crawler ldocs character set utf8 fields terminated ',';)

If an ERROR similar to ERROR 29 (HY000): File '/tmp/a.txt' not found (Errcode: 13) occurs during import, it is probably because the mysql User has no permission to access the File,
Then, use chown mysql: mysql/tmp/a.txt to set the file to a mysql User. if you execute the preceding command again, the import is generally completed.