How to import an SQL file in MySQL
While working on web application, often you need to insert data from sql file. Also when changing your server, you need to export database from old server and import to new one.
There are so many ways you can import database from sql file. There are so many GUI applications like MySQL Workbench as well as web application like phpmyadmin available to work with Mysql server. We can also use command line to import file from remote desktop.
We will go through few ways to import database from SQL file in MySQL.
Import from MySQL Workbench software
MySQL Workbench is open-source as well as Community Edition visual database management software developed by Oracle Corporation. The Community Edition is a full featured product still we can do all basic stuff in Open-source software. We will use Open-source software to import sql file.
First create the connection on MySQL Workbench dashboard and lauch the server.
If you want to create new database, click Create New Schema button from menubar, write database name and apply the changes.
To import the SQL file from local system, click File > Run SQL Script.
Select the database name and default character set like utf8 and click Run button. This will execute the query.
Import from phpMyAdmin
phpMyAdmin is free and open-source web application written in PHP. It is easy to manage MySQL and MariaDB database through phpMyAdmin. Many web hosting providers provides phpMyAdmin tool to manage database. If they not provide phpMyAdmin, we can simply install from the official phpMyAdmin Link[https://docs.phpmyadmin.net/en/latest/setup.html].
This is simple way to import SQL file. First, from your browser login to server phpMyAdmin portal either through Cpanel menu or from your database server link.
Now from the left bar select the database where you want to import the sql file. Now click Import option from menubar. Select the file from file input and click Go button. This will run SQL file to database server.
Import through Command line
This is fast way to import SQL script but it is sometimes not easy and takes time to run proper command line. If you have enough knowledge about bash script and MySQL commands, this will be easiest way.
To import the SQL script file, run below command from your Terminal.
If this not work for you, first connect to ssh and append mysql command.
There is also one method to import sql file through command line. First upload the sql file to remote server through git or FTP application like FileZilla and you can directly import database.
So, this way sql file can be import to remote server. These are just few ways, there are many softwares available to import the sql file.
How to Import & Export MySQL Databases?
How to do a full database export and import SQL Dump file to your MySQL database.
Import an SQL Dump file into MySQL database
1. Using Import SQL Wizard
Previously, you can import and *.sql file to use with your database in TablePlus by dragging the file into the query editor and execute it. It works just fine for the small sized file, but for a huge file, it gives the app a hard time executing it.
Now TablePlus has already supported Import SQL Dump.
Note:
- You have to update the app to the latest version to make sure the app runs properly.
- Careful: Importing a *.sql file may overwrite your existing data!
To import an SQL dump file:
- Connect to your MySQL database
- Choose Import > From SQL Dump… from the File menu.
- This will bring up a dialog box, select the file on your file system that you would like to import, then click Import .
- Your database will now be updated. Click the Refresh button (Cmd + R) if needed.
2. Or using the Restore feature
It uses the binaries of the database for the job, and it’s compatible with the latest version of MySQL.
- If you are in the welcome screen, click on the restore icon.
- If you are connected to the database, Choose File > Restore… from the menu
- Choose connection and database to backup, or add any options if you want.
- Click Start restore…
- This will bring up a dialog box, select the file on your file system, then click Start Restore .
And your database now is updated.
Save the table structure/table content as an SQL Dump file?
1. Using the Export Wizard
To export an SQL dump file of the table structure or contents:
- Connect to your MySQL database.
- Select tables needed to export
- Choose Export… from the File menu. This will bring up a dialog box where you can choose output data.
- Choose the SQL tab
- Choose to include table content or structure, or drop table if exists command in the Dump file.
- Click ‘Export…’
- Choose the destination folder to store the exported file from the dialog box.
And now your tables are exported on your destination folder.
2. Using the Backup feature
- If you are in the welcome screen, click on the backup icon.
- If you are connected to the database, Choose Backup… from the File menu.
- Choose connection and database to backup, or add any options if you want.
- Click Start Backup…
- Choose the destination folder to store the backup file from the dialog box
And now you have a dump file of your entire database stored in your destination folder.
TablePlus is a modern, native GUI that allows you to simultaneously manage multiple databases such as MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server, CockroachDB… in a fast and easy way.
Import SQL file into mysql
I have a database called nitm . I haven’t created any tables there. But I have a SQL file which contains all the necessary data for the database. The file is nitm.sql which is in C:\ drive . This file has size of about 103 MB. I am using wamp server.
I have used the following syntax in MySQL console to import the file:
But this didn’t work.
18 Answers 18
From the mysql console:
mysql> use DATABASE_NAME;
mysql> source path/to/file.sql;
make sure there is no slash before path if you are referring to a relative path. it took me a while to realize that! lol
Finally, i solved the problem. I placed the `nitm.sql` file in `bin` file of the `mysql` folder and used the following syntax.
And this worked.
If you are using wamp you can try this. Just type use your_Database_name first.
Click your wamp server icon then look for MYSQL > MSQL Console then run it.
If you dont have password, just hit enter and type :
If you have password, you will promt to enter a password. Enter you password first then type:
location_of_your_file should look like C:\mydb.sql
so the commend is mysql>source C:\mydb.sql;
This kind of importing sql dump is very helpful for BIG SQL FILE.
I copied my file mydb.sq to directory C: .It should be capital C: in order to run
In windows, if the above suggestion gives you an error (file not found or unknown db) you may want to double the forward slashes:
In the mysql console:
Ok so, I’m using Linux but I think this holds true for Windows too. You can do this either directly from the command prompt
Or from within the mysql prompt, you can use:
But both these approaches have their own benefits in the results they display. In the first approach, the script exits as soon as it encounters an error. And the better part, is that it tells you the exact line number in the source file where the error occurred. However, it ONLY displays errors. If it didn’t encounter any errors, the scripts displays NOTHING. Which can be a little unnerving. Because you’re most often running a script with a whole pile of commands.
Now second approach (from within the mysql prompt) has the benefit that it displays a message for every different MySQL command in the script. If it encounters errors, it displays the mysql error message but continues on through the scripts. This can be good, because you can then go back and fix all the errors before you run the script again. The downside is that it does NOT display the line numbers in the script where the errors were encountered. This can be a bit of a pain. But the error messages are as descriptive so you could probably figure out where the problem is.