Take care of MySQL programming on Linux (v): Data import Export and backup

"Copyright notice: respect for the original, reproduced please retain the source: blog.csdn.net/shallnet, the article only for learning Exchange, do not use for commercial purposes" A variety of data import methods are available in MySQL, such as mysqlinport, SQL statement Import, and writing specialized import programs. Typically, the data import basic Steps section is divided into 3 steps:1. Determine the imported data source, a text file stored in a fixed format, or a SQL file.
2. According to the imported file format, determine the target data table, if not, you can follow the imported text file format, create a corresponding data table.
3. Execute the Import command to import the data into the data table.

The following describes the various import data methods provided by MySQL, where a table is designed, a data to be imported, stored in text format.
1. Data sources
Create a text file as follows, each field is tab-separated:

# cat Myuser.txt Zhao      8       2015-1-1qian      4       2014-5-6sun      1       2013-12-7li      6       2014-12-12zhou      3       2015-2-8wu      1       2014-9-12zheng   44      9       2012-10-12wang      2015-3-6

2. Then create a target database table, the data structure of the table corresponding to the text file one by one, as follows:
[[email protected]db_bak]# Mysql-u Root-p
Enter Password:

mysql> use db_users; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> CREATE TABLE User_import (name varchar) NOT NULL primary key, age int., Level int, Logi N_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 |     YES  |     | NULL    |       | | level      | int |     YES  |     | NULL    |       | | login_date | date        | YES  |     | NULL    |       

3. Import data, Mysqlimport is a MySQL-provided import tool that can import text files into a user-specified data table.
Mysqlimport Use format: Mysqlimport[-d/f ...] tablename data.txt
where [-d/f ...]is an optional parameter. The tablename is used to represent the database name. data.txt is used to represent the text file of a record, and the name of the data table to be imported is usually the same as the text file. For example, run the Mysqlimport command below to import the text data into MySQL:

# cp/home/allen/user_import.txt/var/lib/mysql/db_users/# mysqlimport-uroot-pxxx  db_users user_import.txtdb_ 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.txtenter password:db_users.user_import:records:8  deleted:0  skipped:0  warnings:0

Then look at the following data import scenarios:

# mysql-u Root-penter password:......mysql> use db_users; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> SELECT * from user_import;+-------+------+-------+------------+| Name  | Age  | level | login_date |+-------+------+-------+------------+| Zhao  |   |     8 | 2015-01-01 | | Qian  |   |     4 | 2014-05-06 | | Sun   |   |     1 | 2013-12-07 | | Li    |   |     6 | 2014-12-12 | | Zhou  |   |     3 | 2015-02-08 | | Wu    |   |     1 | 2014-09-12 | | Zheng |   |     9 | 2012-10-12 | | Wang  |   |    12 | 2015-03-06 |+-------+------+-------+------------+8 rows in Set (0.00 sec)

Sometimes the data source's spacer may not be the default tab key, there may be a comma, you can add the parameter--field-terminatied-by=str, the imported command is:

# mysqlimport-uroot-pxxx--fields-terminated-by=, db_users user_import2.txtdb_users.user_import2:records:8  deleted:0  skipped:0  warnings:0

Data export is to export the data stored in the database to a fixed text record, MySQLDump is a tool for data export-only services in MySQL that can export a database, table, and Setup stored procedure as an SQL statement, as well as use the tool in data backup. It is used in the following format:mysqldump [-r/...] databsesname < Data.sql[-r/...] As optional parameters;
Databsesname is the database name;
Data.sql represents a script for sub-export SQL.

# mysqldump-u Root-p db_users > Db_userr.sql Enter Password: # ls-al db_userr.sql-rw-r--r--. 1 root root 6366 June 22:52 db_userr.sql# vim db_userr.sql--MySQL dump 10.13 distrib 5.1.66, for Redhat-linux-gnu (i38       6)----Host:localhost database:db_users----------------------------------------------------------Server version 5.1.66/*!40101 set @[email protected] @CHARACTER_SET_CLIENT */;/*!40101 set @[email protected] @CHARACTER_ Set_results */;/*!40101 set @[email protected] @COLLATION_CONNECTION */;/*!40101 set NAMES UTF8 */;/*!40103 set @[ Email protected] @TIME_ZONE */;/*!40103 set time_zone= ' +00:00 ' */;/*!40014 set @[email protected] @UNIQUE_ CHECKS, unique_checks=0 */;/*!40014 SET @[email protected] @FOREIGN_KEY_CHECKS, foreign_key_checks=0 */;/*!40101 Set @[email protected] @SQL_MODE, sql_mode= ' No_auto_value_on_zero ' */;/*!40111 SET @[email protected] @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 top--MySQL dump 10.13 distrib 5.1 . REDHAT-LINUX-GNU (i386)----Host:localhost database:db_users---------------------------------------------- ------------Server version 5.1.66 ...

The data export was successful. The database export file can be restored to the database in the following ways: 1 use the MySQL command to perform recovery operations on the data:

# mysql-u Root Db_users2 < db_userr.sql-p        Enter password: [[email protected] allen]# mysql-u root-penter passwo Rd:mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | db_users           | | db_users2          | | mysql              | | test               

2. SQL statement source to import.

mysql> CREATE DATABASE Db_users3; Query OK, 1 row Affected (0.00 sec) mysql> use db_users3;database changedmysql> source Db_userr.sqlquery OK, 0 rows a Ffected (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 representation of a data backup you can take the SELECT INTO ... outfile ... Data export, using the load ... Data import in the same way. The data table backup is performed as follows:

Mysql> Select  * into outfile ' tbbk_users ' from tb_users;        Query OK, Rows Affected (0.00 sec)

The data table is executed as follows:

mysql> load Data infile ' tbbk_users ' into table tb_users;                                  Query OK, rows affected, Warnings (0.00 sec) records:13 deleted:0 skipped:0  warnings:13

Take care of MySQL programming on Linux (v): Data import Export and backup