Mysql import sql file, csv, database and dump example

Mysql import sql file, csv, database, dump
Mysql import sql file, csv, database, dump

In this tutorial we will  discuss and learn the  topics like mysql import sql file, mysql import csv etc. The topics are as follows.

a) Mysql import text file
b) Export data from Mysql table to text file
c) Mysql export to sql file
d) Mysql Import sql file

e) Import CSV into Mysql
f) Change maximum allowed packet size for import

Importing data to MySQL or vice versa is very important as these are the things that a System Admin may have to do as an everyday task. This tutorial will guide you through how to use Mysql import and mysql to do the task mentioned above.

Mysql import text file

Step 1:

Create a file "student.txt"  as given. Make sure to use a tab space between each field.

# cat > student.txt
201  Neha  700
202  John   650
203  Alan   600
204  Ravi   450

Step 2:

Now create a database. Login into the MySQL with the password.

# mysql -u root -p
Enter password:
When you are inside mysql prompt create a database 'university'
mysql> create database university;
Query OK, 1 row affected (0.16 sec)

Confirm weather the database has created or not by command 'show databases'

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rainloop |
| university |
+--------------------+
5 rows in set (0.02 sec)

 

Step 3:

Create a table called "student" inside MySQL database university. Enter into the database university by using the command 'use <database_name>'

mysql> use university;
Database changed

Now create a table 'student'  inside it. Table name must be the same as that of the file you created.

 

mysql> create table student ( roll_no int, Student_name varchar (20), marks int );
Query OK, 0 rows affected (0.35 sec)

 

Step 4:

As your database and table is now set. Its time to import file to MySQL with 'mysqlimport' command. See the example below.

# mysqlimport -u root -p --local university student.txt
Enter password: 
university.student: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

The above command will import text file to mysql.

 

Export data from Mysql table to text file

The below command is useful if you want to export data from Mysql table to a text file. 

mysql> select * from student into outfile '/tmp/student.txt';
Query OK, 4 rows affected (0.00 sec)

Mysql export to sql file

When the data in the database becomes old, you need to take backups of those old data. You can use the mysqldump command to do the task.

# mysqldump -u root -p --database university > /tmp/university.sql
Enter password:

The above will create a sql file 'university.sql' of database 'university'.

Mysql import sql file

To import mysql database database from  a '.sql dump'  file, you need to use the below command.

# mysql -u root -p --database university < /tmp/university.sql

Enter password:

Import CSV into Mysql

To import CSV to Mysql table do the following steps.

Step 1:
Create a database office.

mysql> create database office;

Step 2:
Change database.

mysql> use office;

Step 3:
Create table.

mysql> create table employee (emp_id int(5), name char(10), designation char(15));

Step 4:
Create a CSV file of the same name as table name. Insert data into the field. Don't create any header row.

import-csv-to-mysql

Step 5:
Get to mysql prompt.

# mysql -u root -p
Enter password:

Step 6:
select database office.

mysql> use office;
Database changed

Step 7:
Now run the given command.

mysql> LOAD DATA LOCAL INFILE '/home/techsakh/employee.csv' INTO TABLE employee FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 2 rows affected (0.26 sec)

Step 8:
Now verify the value inside the table;

mysql> select * from employee;
+--------+--------+-------------+
| emp_id | name | designation |
+--------+--------+-------------+
| 364 | Shawn | Manager |
| 365 | Kierra | HR |
+--------+--------+-------------+
2 rows in set (0.07 sec)

Mysqlimport options

There are many options you can use with Mysqlimport command to become more productive. Some of the most useful options are explained below.

--bind-address
With this option you can decide which network interface you want to connect to Mysql server. This is useful if you have several network interfaces in your computer.

--force
While taking backup if any sql error occurs like any table or field for the backup file not exist,  the '--force' option will continue to process ignoring the error.

--host
This option is used to connect Mysql server on particular host. If you don't use this option , you will be connected to localhost (default host).

--ignore-lines
You can use this option with mysqlimport command when taking backup from any csv or text file to ignore the first N lines from the file.

--local
This is useful if you want mysqlimport to connect and read file from client host. With this option you can tell mysqlimport to read files and take backup over the network.

--lock-tables
Lock all tables before dumping or importing, this will ensure that all tables are synchronized on server. If you don't lock tables while dumping you may get inconsistent data when dumping is over.

--delete
When you use '--delete' option with mysqlimport, it makes the table empty before importing file data into it.

--port
With '--port' option you can assign the port number to used for TCP/IP connection.

--silent
with the '--silent' option, mysqlimport won't produce any output on the terminal, output produce only if any error occurs.

--debug-check
This option identifies and also remove some errors when mysqlimport finished its execution. In other word it debug the program.

--ssl
When you use mysqlimport to take backup from the remote server, the connection is not secure.  With '--ssl' option you will instruct mysqlimport to connect server using ssl keys, and that makes it very secure.

--verbose
With '--verbose' mysqlimport works in verbose mode. It means producing more information about the process on the terminal while executing the task.

--version
To display what version of mysqlimport you are using, use this option.

--help
To see what more options you can use with mysqlimport, '--help' option is used.

Go to this official site of MySql to learn more about it.

Change max_allowed_packet size

When you import csv into mysql  or import mysql dump, you may experience errors of 'Maximum allowed packet size'. Don't be panic. You need to do some changes in my.cnf file under '/etc' directory. Do the following.

Step:1

# vi /etc/my.cnf

Under [Mysqld] section add the following line and change the size accordingly. I have given 600M the maximum allowed packet size.

max_allowed_packet=600M

Now restart the mysql service.

# service mysql restart

Step:2
Go to mysql query browser and type the following to confirm the max allowed packet size.

mysql> show variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 629145600 |
+--------------------+-----------+
1 row in set (0.00 sec)


Discover some extra about mysql import sql, mysql import csv and dump in itworld.com



This is it about the article Mysql import sql file, csv, database and dump example. If you like this article don't forget to comment and share.

Be the first to comment

Leave a Reply

Your email address will not be published.


*


shares