- How to run or import sql file in MySQL
- Import sql file in MySQL on Linux:
- Import sql file in MySQL on Windows:
- MySQL — How to run SQL file or script from the terminal
- Run SQL file while connected to the server
- Run SQL file while not connected to the server
- Take your skills to the next level ⚡️
- About
- Search
- Tags
- How to Run an SQL File in MySQL (or MariaDB) on Linux/Ubuntu
- From the MySQL Command Line
- From the Shell/Command Line
- Saving the Output of the SQL Script
- Using phpMyAdmin
- Run sql file in database from terminal
- 4 Answers 4
- How to run SQL script in MySQL?
- 21 Answers 21
How to run or import sql file in MySQL
In this article, we will learn how to run or import sql file in MySQL on Linux Windows.
We can import sql file in MySQL by two methods:
1. From Linux shell
2. By connecting to MySQL
1. From Command Prompt
2. By connecting to MySQL.
Import sql file in MySQL on Linux:
From Linux shell(terminal):
cat > createdatabase.sql create database test; use test; create table emp(empno int, empname varchar(40));
press ctrl+d to save and quit this sql file.
2. Now import(run) sql file by using below command.
Import sql file in MySQL on Windows:
1. Create sql file and paste below commands and save the file.
create database test; use test; create table emp(empno int, empname varchar(40));
2. Open command command prompt.
3. Now, import sql file in MySQL on Windows
By connecting to MySQL
1. Create anothr sql file with name createtable.sql
CREATE TABLE students(SNO int PRIMARY KEY, SNAME varchar(50),DOB date, class i nt, gender varchar(5));
2. Now connect to MySQL on Windows.
3. Switch to the database in which database to be imported sql file.
4. Now, import the sql file in MySQL using below command.
source C:\Test\createtable.sql
5. Lets verify by running show tables; comamnd.
So, we have successfully imported sql file in MySQL on Windows with different methods.
Finally we have successfully imported sql file in both Linux and Windows environments.
MySQL — How to run SQL file or script from the terminal
When you need to run a saved .sql file directly from the terminal, you can use the mysql command line client.
You can run SQL scripts with or without opening a connection to the MySQL server.
First, let’s see how to run SQL files while connected to a MySQL server
Run SQL file while connected to the server
For example, suppose you have a file named main.sql with the following content:
The script will select a database named school_db and retrieve all rows from the students table.
To run SQL files from the terminal, you can use the source or the backslash and dot command ( \. )
First, you need to connect to your MySQL database server using the mysql command. Here’s an example of connecting with the root user:
Next, enter the password for your root user.
Once inside, use the source or \. command followed by the absolute path to the SQL file as shown below:
The path /Users/nsebhastian/Desktop/test/main.sql above needs to be changed to the SQL file path on your computer.
MySQL will print the output in the command line if any. Here’s an example of running the main.sql file in my terminal:
And that’s how you run SQL files from the terminal while being connected to MySQL database server.
Let’s see how you can run SQL files without having to connect to the server next.
Run SQL file while not connected to the server
The mysql command line client has the ability to run SQL scripts without needing to connect to MySQL database server.
You only need to provide the database_name option followed by the smaller than ( < ) operator as follows:
For example, here’s how to run the same main.sql script without connecting to the server:
Once again, the command line client will ask for a password to run the operation.
Here’s an example of the output in my terminal:
As you can see from the output above, the SELECT statement result set is a bit messy compared to when you run the script while being connected to the server.
But you won’t see any difference if your SQL script contains INSERT , UPDATE , or DELETE statements.
Also, if you have a USE database_name statement in your SQL file, you can omit the database_name from the command line as shown below:
The above example works because the main.sql file contains the USE school_db; statement.
Without the USE statement, MySQL will throw the No database selected error.
You have learned how to run SQL files or scripts from the terminal. Great job! 👍
Take your skills to the next level ⚡️
I’m sending out an occasional email with the latest tutorials on programming, web development, and statistics. Drop your email in the box below and I’ll send new stuff straight into your inbox!
About
Hello! This website is dedicated to help you learn tech and data science skills with its step-by-step, beginner-friendly tutorials.
Learn statistics, JavaScript and other programming languages using clear examples written for people.
Search
Type the keyword below and hit enter
Tags
Click to see all tutorials tagged with:
How to Run an SQL File in MySQL (or MariaDB) on Linux/Ubuntu
Whether you’re installing a package, following a tutorial, or restoring a backup – it’s useful to be able to execute an SQL script from a file and have it do all of the work for you, rather than having to type it all out.
Most GUI database managers have a simple import option prominently displayed in the menu bar – so here’s how to do it from the command line (and PHPMyAdmin if you’re command-line averse).
From the MySQL Command Line
If you’re logged into the MySQL command line, you can use MySQL statements to execute the contents of a script/file using the source command:
source full/path/to/sql/file.sql
- You should provide the full absolute path to the file you wish to read as it may not be clear what working directory will be from within MySQL
- The file will need to be located on the same system on which the MySQL server is running or on a filesystem available to it.
- The path should not be wrapped in quotes!
From the Shell/Command Line
You can also execute an SQL file without logging into MySQL from the Linux shell – this is especially useful if you wish to make your command part of a Bash/Shell script:
mysql --host="mysql_server" --user="user_name" --database="database_name" --password="user_password" < "path/to/sql/file.sql"
Above, the database login details are provided to the MySQL command, which is then fed commands via standard redirection from the given SQL file. The command directs the contents of the SQL file to the MySQL command.
The path to the SQL file can be relative, and the file should exist on your local computer – it doesn’t have to exist on the remote MySQL server, so it’s perfect for running local SQL scripts on remote servers.
Saving the Output of the SQL Script
If you wish to save the output generated while the script is running rather than just having it output to the screen, you can again use standard redirection to redirect the output into a file:
mysql -u yourusername -p yourpassword yourdatabase < query_file >results_file mysql --host="mysql_server" --user="user_name" --database="database_name" --password="user_password" < "path/to/sql/file.sql" >"path/to/sql/results.txt"
The > command directs the output of the command into the given results file. If the file exists, it is overwritten.
Using phpMyAdmin
If you use phpMyAdmin to administrate your MySQL server, you can also execute MySQL files from there. It’s a good tool for those less comfortable working on the command line.
Navigate to the database you wish to execute the SQL commands within, then go to the import tab and browse for the file you wish to execute.
Press the go button when you’re ready to go. Pretty easy.
Be warned, if you are executing a particularly large file that may take some time to complete, the command line is the best way to go, as phpMyAdmin is limited by the file upload size and is limited in how long it is allowed to execute tasks depending on the configuration of the web server it is running from. If you are having issues uploading a file via phpMyAdmin, then consider checking your php.ini file for an upload limit that might be limiting the file size.
Run sql file in database from terminal
I have a server and within that server I have an sql script in the /var/www/ directory. That script is to create tables and databases. I need to know how to execute this sql script from terminal or some client while keeping the file on the server. PS- I don't know much of the terminology for this topic so I will look into other suggestions for my approach.
4 Answers 4
I presume that it is MYSQL. To run it from Unix / Linux environment you must do this:
$ mysql -h "server-name" -u "your_username" -p "your_password" "database-name" < "filename.sql" mysql --host=localhost --user=your_username --password=your_password -e "filename.sql" What if my sql script needs to create a database? How, then, would I have a database name to use for that command line?
Second way does not work for me, the argument to -e is a valid SQL statement, not a path. E.g.: mysql --host=127.0.0.1 --port=3306 --user=root --password=your_password -e "select 1;"
My MySQL doesn't respond to $ mysql -h "server-name" -u "your_username" -p "your_password" "database-name" < "filename.sql" , do you have any ideas?
@Tomislav I agree with your answer but writing password in the command line together is a bad idea. If you leave just -p and not writing the password then the system will ask you to enter the password.
mysql -u your_username -p your_password use db_name source /file.sql I selected a databse before "source" command: " use
If you want to run an SQL file for a specific database, then select the database first:
mysql -u your_username -p your_password use db_name source /file.sql The above answer of @Tomislav is correct but I do not like to write the password with the command-line option.
- If you did not create the new database and want to create it with a .SQL file then use the below statement. mysql -h -u -p
then MySQL CLI will ask for the password (The best way to prevent the password from anyone). Write your password and hit Enter
- If you have already created a database then use the below command. mysql -h -u -p
then MySQL CLI will ask for the password. Write your password and hit Enter
Usually, the .SQL file doesn't have any print statements. So you have to enter and check with your MySQL CLI.
How to run SQL script in MySQL?
I want to execute a text file containing SQL queries, in MySQL. I tried to run source /Desktop/test.sql and received the error:
mysql> . \home\sivakumar\Desktop\test.sql ERROR: Failed to open file '\home\sivakumar\Desktop\test.sql', error: 2
21 Answers 21
If you’re at the MySQL command line mysql> you have to declare the SQL file as source .
mysql> source \home\user\Desktop\test.sql; for windows, using '/' instead of '\' worked correctly for me. I got errors when I originally used '/'. This is what worked for me. source C:/Users/macombers/Downloads/midcoast_db.sql;
@kapil I know this post is pretty old, but if you are in the folder your script is in before you start mysql, you don't need to type the full path.
You have quite a lot of options:
@AmirKatz While the password can be given with the -p option, this is not recommended: Other users on the same host can use system tools like ps to read it in this case.
you can execute mysql statements that have been written in a text file using the following command:
mysql -u yourusername -p yourpassword yourdatabase < text_file if your database has not been created yet, log into your mysql first using:
mysql -u yourusername -p yourpassword mysql>CREATE DATABASE a_new_database_name mysql -u yourusername -p yourpassword a_new_database_name < text_file on Windows: for the password I had to use quotation marks and NO space to make it work (the password itself did not contain any spaces or special chars): mysql -u yourusername -p"yourpassword"
If you don't want your password to be on the prompt I believe the method outlined here will work: stackoverflow.com/questions/9293042/…
My favorite option to do that will be:
mysql --user="username" --database="databasename" --password="yourpassword" < "filepath" I use it this way because when you string it with "" you avoiding wrong path and mistakes with spaces and - and probably more problems with chars that I did not encounter with.
With @elcuco comment I suggest using this command with [space] before so it tell bash to ignore saving it in history, this will work out of the box in most bash.
in case it still saving your command in history please view the following solutions:
Just in case you want to be extra safe you can use the following command and enter the password in the command line input:
mysql --user="username" --database="databasename" -p < "filepath"