Mysql import sql file , csv , database and dump example Introduction
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) Mysqlimport options
g) 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. We will also see 'mysqlimport' command in details.
Mysql import text file
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
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)
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)
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.
Create a database office.
mysql> create database office;
mysql> use office;
mysql> create table employee (emp_id int(5), name char(10), designation char(15));
Create a CSV file of the same name as table name. Insert data into the field. Don't create any header row.
Get to mysql prompt.
# mysql -u root -p Enter password:
select database office.
mysql> use office; Database changed
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)
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)
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>
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.
# mysqlimport -u root -p --bind-address=192.168.0.2
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.
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).
# mysqlimport -u root -p --host=techsakh
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.
The below command will ignore the first 4 line of the file while importing.
# mysqlimport -u root -p --ignore-lines=4
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 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.
When you use '--delete' option with mysqlimport, it makes the table empty before importing file data into it.
With '--port' option you can assign the port number to used for TCP/IP connection.
with the '--silent' option, mysqlimport won't produce any output on the terminal, output produce only if any error occurs.
This option identifies and also remove some errors when mysqlimport finished its execution. In other word it debug the program.
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.
With '--verbose' mysqlimport works in verbose mode. It means producing more information about the process on the terminal while executing the task.
To display what version of mysqlimport you are using, use this option.
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.
# 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.
Now restart the mysql service.
# service mysql restart
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.