Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of Cassandra, MySQL and PostgreSQL …

  • Likes

    • 138,101
  • Archives

  • Categories

  • Subscribe

  • Advertisements
  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

MySQL – How to use LOAD DATA INFILE and INTO OUTFILE

Posted by FatDBA on December 20, 2017

Today i will discuss about the the useful but script/SQL based data export/import method in MySQL database that is – LOAD DATA INFILE and INTO OUTFILE.

Lets first create an export file/script for the table using SELECT … INTO OUTFILE, here you can specify the location of the export file.

mysql> select * from country into outfile 'countrycreate.sql';
Query OK, 109 rows affected (0.00 sec)

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql

As there is no table definition captured using SELECT INTO OUTFILE way, so you should always ensure that you have a copy of the table definition for restoration of the file.

bash-4.1$ mysqldump -u root -p --no-data dixit country > /var/lib/mysql/dixit/countryschemadef.sql
Enter password:

-rw-rw-rw-. 1 mysql mysql 3.6K Dec 20 01:07 countrycreate.sql
-rw-r--r--. 1 mysql mysql 1.6K Dec 20 01:10 countryschemadef.sql

Lets see the contents of this newly created file.

bash-4.1$ more countryschemadef.sql
-- MySQL dump 10.13  Distrib 5.7.20, for Linux (x86_64)
--
-- Host: localhost    Database: dixit
-- ------------------------------------------------------
-- Server version       5.7.20

/*!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 `country`
--

DROP TABLE IF EXISTS `country`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `country` (
  `country_id` int(11) DEFAULT NULL,
  `country` text,
  `last_update` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-12-20  1:10:20

Lets create the new user and load the table data to it.


bash-4.1$ mysqladmin -u root -p create dixit2
Enter password:


bash-4.1$ mysql -u root -p dixit2  load data infile '/var/lib/mysql/dixit/countrycreate.sql' into table country;
Query OK, 109 rows affected (0.01 sec)
Records: 109  Deleted: 0  Skipped: 0  Warnings: 0

mysql>
mysql>
mysql> select count(*) from country;
+----------+
| count(*) |
+----------+
|      109 |
+----------+
1 row in set (0.00 sec)

All set!

Hope It Helps!
Prashant Dixit

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

 
%d bloggers like this: