Here is an article outlining several methods for running SQL files in MySQL 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
Note that:
- 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.
Here’s why you should wrap paths and values in shell commands in double-quotes.
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.
