How to easily import multiple sql files into a MySQL database?
MysqlImportMysql Problem Overview
I have several sql
files and I want to import all of them at once into a MySQL database.
I go to PHPMyAdmin
, access the database, click import
, select a file and import it. When I have more than a couple of files it takes a long time.
I would like to know if there is a better way to import multiple files, something like one file which will import the other files or similar.
I'm using WAMP
and I would like a solution that does not require installing additional programs on my computer.
Mysql Solutions
Solution 1 - Mysql
In Windows, open a terminal, go to the content folder and write:
copy /b *.sql all_files.sql
This concate all files in only one, making it really quick to import with PhpMyAdmin.
In Linux and macOS, as @BlackCharly pointed out, this will do the trick:
cat *.sql > .all_files.sql
Important Note: Doing it directly should go well, but it could end up with you stuck in a loop with a massive output file getting bigger and bigger due to the system adding the file to itself. To avoid it, two possible solutions.
A) Put the result in a separate directory to be safe (Thanks @mosh):
mkdir concatSql
cat *.sql > ./concatSql/all_files.sql
B) Concat them in a file with a different extension and then change it the name. (Thanks @William Turrell)
cat *.sql > all_files.sql1
mv all_files.sql1 all_files.sql
Solution 2 - Mysql
This is the easiest way that I have found.
In Windows (powershell):
cat *.sql | C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe -u user -p database
You will need to insert the path to your WAMP - MySQL
above, I have used my systems path.
In Linux (Bash):
cat *.sql | mysql -u user -p database
Solution 3 - Mysql
-
Goto cmd
-
Type in command prompt C:\users\Usersname>cd [.sql tables folder path ]
Press Enter
Ex: C:\users\Usersname>cd E:\project\database -
Type command prompt
C:\users\Usersname>[.sql folder's drive (directory)name]
Press Enter
Ex: C:\users\Usersname>E: -
Type command prompt for marge all .sql file(table) in a single file
copy /b *.sql newdatabase.sql
Press Enter
EX: *E:\project\database>copy /b .sql newdatabase.sql -
You can see Merge Multiple .sql(file) tables Files Into A Single File in your directory folder
Ex: E:\project\database
Solution 4 - Mysql
I know it's been a little over two years... but I was looking for a way to do this, and wasn't overly happy with the solution posted (it works fine, but I wanted a little more information as the import happens). When combining all the SQL files in to one, you don't get any sort of progress updates.
So I kept digging for an answer and thought this might be a good place to post what I found for future people looking for the same answer. Here's a command line in Windows that will import multiple SQL files from a folder. You run this from the command line while in the directory where mysql.exe is located.
for /f %f in ('dir /b <dir>\<mask>') do mysql --user=<user> --password=<password> <dbname> < <dir>\%f
With some assumed values (as an example):
for /f %f in ('dir /b c:\sqlbackup\*.sql') do mysql --user=mylogin --password=mypass mydb < c:\sqlbackup\%f
If you had two sets of SQL backups in the folder, you could change the *.sql to something more specific (like mydb_*.sql).
Solution 5 - Mysql
just type:
cat *.sql |mysql -uroot -p
and mysql will import all the sql file in sequence
Solution 6 - Mysql
Enter the mysql shell like this. > mysql --host=localhost --user=username --password --database=db
Then use the source command and a semicolon to seperate the commands. >source file1.sql; source file2; source file3;
Solution 7 - Mysql
You could also a for loop to do so:
#!/bin/bash
for i in *.sql
do
echo "Importing: $i"
mysql your_db_name < $i
wait
done
Solution 8 - Mysql
Save this file as .bat and run it , change variables inside parenthesis ...
@echo off
title Mysql Import Script
cd (Folder Name)
for %%a in (*) do (
echo Importing File : %%a
mysql -u(username) -p(password) %%~na < %%a
)
pause
if it's only one database modify (%%~na) with the database name .
Solution 9 - Mysql
The easiest solution is to copy/paste every sql files in one.
You can't add some sql markup for file importation (the imported files will be in your computer, not in the server, and I don't think MySQL manage some import markup for external sql files).
Solution 10 - Mysql
in windows open windows powershell and go to the folder where sql files are then run this command
cat *.sql | C:\xampp\mysql\bin\mysql.exe -u username -p databasename
Solution 11 - Mysql
Just type below command on your command prompt & it will bind all sql file into single sql file,
c:/xampp/mysql/bin/sql/ type *.sql > OneFile.sql;
Solution 12 - Mysql
Import From multiple SQL files into one Database.
Step 1: Goto to the folder and create file 'import-script.sh' with execute permission
(give Permission to file is chmod u+x import-script.sh
)
#!/bin/bash
for i in *.sql
do
echo "Importing: $i"
mysql -u USERNAME -pPASSWORD DBNAME < $i
wait
done
The main thing is -p and PASSWORD didn't add any space.
Step 2: then in your terminal run this command ./import-script.sh