MySQL programming on linux (4): Data Import and Export and backup, mysql Import and Export

MySQL programming on linux (4): Data Import and Export and backup, mysql Import and Export
[Copyright statement: respect originality. Reprinted and reprinted. Retained for Source: blog.csdn.net/shallnet. this document only serves for learning and communication. Do not use commercial use cases.] MySQL provides multiple data import methods, such as mysqlinport, SQL statement import, and special. Generally, the data import basic step section is divided into three steps: 1. Determine the imported data source, text files or SQL files stored in a fixed format.
2. Determine the target data table according to the imported file format. If the data table does not exist, you can create a corresponding data table according to the imported text file format.
3. Run the import command to import data to the data table.

The following describes various data import methods provided by MySQL. A table is designed here, and the data to be imported is stored in text format.
1. Data Source
Create the following text file separated by the tab key:

# cat myuser.txt 
zhao    25      8       2015-1-1
qian    22      4       2014-5-6
sun     31      1       2013-12-7
li      40      6       2014-12-12
zhou    45      3       2015-2-8
wu      18      1       2014-9-12
zheng   44      9       2012-10-12
wang    29      12      2015-3-6

2. Create a target database table. The data structure of the table must correspond to the text file one by one, as shown below:
[Root @ localhost db_bak] # mysql-u root-p
Enter password:

mysql> use db_users;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table user_import ( name varchar(32) not null primary key, age int, level int, login_date date );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user_import;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| name       | varchar(32) | NO   | PRI | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
| level      | int(11)     | YES  |     | NULL    |       |
| login_date | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

3. import data. mysqlimport is an import tool provided by MySQL that can import text files to user-specified data tables.
The format of mysqlimport is mysqlimport [-d/f...] tablename data.txt.
[-D/f...] is an optional parameter. Tablename indicates the database name. Data.txt is used to indicate the recorded text file. The name of the data table to be imported is the same as that of the text file by default. For example, run the mysqlimport command to import text data to MySQL:

# cp /home/allen/user_import.txt /var/lib/mysql/db_users/
# mysqlimport -uroot -pxxx  db_users user_import.txt
db_users.user_import: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
# mysqlimport -uroot -p  db_users user_import.txt        
Enter password: 
mysqlimport: Error: 1062, Duplicate entry 'zhao' for key 'PRIMARY', when using table: user_import
# mysqlimport -d -uroot -p  db_users user_import.txt
Enter password: 
db_users.user_import: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

Then, view the data imported as follows:

# mysql -u root -p
Enter password:
......
mysql> use db_users;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user_import;
+-------+------+-------+------------+
| name  | age  | level | login_date |
+-------+------+-------+------------+
| zhao  |   25 |     8 | 2015-01-01 |
| qian  |   22 |     4 | 2014-05-06 |
| sun   |   31 |     1 | 2013-12-07 |
| li    |   40 |     6 | 2014-12-12 |
| zhou  |   45 |     3 | 2015-02-08 |
| wu    |   18 |     1 | 2014-09-12 |
| zheng |   44 |     9 | 2012-10-12 |
| wang  |   29 |    12 | 2015-03-06 |
+-------+------+-------+------------+
8 rows in set (0.00 sec)

Sometimes the data source delimiter may not be the default tab key, but may be a comma. In this case, you can add the -- field-terminatied-by = str parameter. The import command is:

# mysqlimport -uroot -pxxx --fields-terminated-by=, db_users user_import2.txt
db_users.user_import2: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

Data export is to export existing data in the database to a fixed text record. mysqldump is a MySQL-based tool that exports data only to a database or table, set the SQL statement of Stored Procedure 1 to export data. This tool is also used in data backup. The format is mysqldump [-r/...] databsesname <data. SQL [-r;
Databsesname is the database name;
Data. SQL indicates the SQL script of the sub-export.

# mysqldump -u root -p db_users  > db_userr.sql        
Enter password: 
# ls -al db_userr.sql
-rw-r--r--. 1 root root 6366 Jun 10 22:52 db_userr.sql

# vim db_userr.sql
-- MySQL dump 10.13  Distrib 5.1.66, for redhat-linux-gnu (i386)
--
-- Host: localhost    Database: db_users
-- ------------------------------------------------------
-- Server version       5.1.66
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `password`
--
DROP TABLE IF EXISTS `password`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
"db_userr.sql" 163L, 6366C                                                1,1           Top
-- MySQL dump 10.13  Distrib 5.1.66, for redhat-linux-gnu (i386)
--
-- Host: localhost    Database: db_users
-- ------------------------------------------------------
-- Server version       5.1.66
......

The data is exported successfully. The exported database file can be restored to the database in the following ways: 1. Use the mysql command to restore data:

# mysql -u root db_users2 < db_userr.sql -p        
Enter password: 
[root@localhost allen]# mysql -u root -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_users           |
| db_users2          |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> 

2. Import the SQL statement source.

mysql> create database db_users3;
Query OK, 1 row affected (0.00 sec)
mysql> use db_users3;
Database changed
mysql> source db_userr.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

For a single data backup, you can use select into... outfile... to export data and use load data... to import data. Perform the following steps to back up a data table:

mysql> select  * into outfile 'tbbk_users' from tb_users;        
Query OK, 13 rows affected (0.00 sec)

Perform the following operations on the data table:

mysql> load data infile 'tbbk_users' into table tb_users;                                  
Query OK, 13 rows affected, 13 warnings (0.00 sec)
Records: 13  Deleted: 0  Skipped: 0  Warnings: 13