Mysql import sql file , csv , database and dump example

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


Mysql import sql file , csv , database and dump example Introduction

In this tutorial we will  be discussing and learning about the  topics like mysql import sql file, mysql import csv etc in Linux. A system administrator or a database administrator should also know how to import sql file into mysql in Windows platform. Therefore, I will also be covering topic 'how to import  in Windows'. The summary of the topics are as follows.

a) Import sql file into mysql in Windows.
b) Mysql import text file
in Linux
c) Export data from Mysql table to text file
d) Mysql export to sql file
e) Mysql Import sql file

f) Import CSV into Mysql
g) Mysqlimport options
h) Change maximum allowed packet size for import

Importing data to MySQL or exporting the database to a sql file is key technique that any System Admin must know by heart. This tutorial will guide you through how to use Mysql import and mysql to do the tasks mentioned above. We will also see the use of 'mysqlimport' command in details.

Import sql file into mysql in Windows

Here, I assume you have wamp or xampp server installed on you PC. Download xampp and wamp for Windows.
We will use command line (CMD) to import database from sql file.
The general syntax is.

mysql -u <user_name> -p <password> database < file.sql

Go to the given path and import database if you have wamp server.

C:\wamp\bin\mysql\mysql5.5.50\bin> mysql -u sqluser -p pass mydatabase < E:\mydb.sql

For xampp server, use the following path. Do not use password while executing the command. It will ask for password once it's executed.

C:\xampp\mysql\bin\mysql -u sql -p yourdatabase < D:/dbfile.sql

 

Mysql import text file in Linux

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));
Mysql import sql file , csv , database and dump example
Mysql import sql file , csv , database and dump example

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



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.

General syntax of the command is

$ mysqlimport -u root -p <source-path> <destination-path>

--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.

--bind-address=IP address

# mysqlimport -u root -p --bind-address=192.168.0.2

--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).

--host=host name

# mysqlimport -u root -p --host=techsakh

--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.

--ignore-lines=N

The below command will ignore the first 4 line of the file while importing.

# mysqlimport -u root -p --ignore-lines=4 

--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.

--port=port number

--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.

Check out this official site of MySql .

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.

Download Our Free eBook now

Linux and UNIX Shell scripting ebook

Be the first to comment

Leave a Reply

Your email address will not be published.


*


shares