1. Overview
SQL dump files are a way to store database commands and data. In fact, a common task when managing a MySQL database is to import SQL dump files. This can be useful for restoring a backup or migrating data from one database to another.
In this tutorial, we’ll explore how to import multiple SQL dump files into a MySQL database from the command line.
2. Sample Task
Let’s suppose a user named sysadmin has access to two databases:
- db1 contains the employees table
- db2 contains the items table
We can inspect these tables using the SELECT statement in SQL. In particular, we issue an SQL statement for each table via the mysql -e command while supplying the necessary credentials:
$ mysql --user=sysadmin --password=mypassword -e 'SELECT * FROM db1.employees;'
+----+------------+-----------+------------+
| ID | FIRST_NAME | LAST_NAME | DEPARTMENT |
+----+------------+-----------+------------+
| 1 | Lin | Day | HR |
| 2 | Ron | Lee | IT |
| 3 | Mat | Ray | Finance |
+----+------------+-----------+------------+
$ mysql --user=sysadmin --password=mypassword -e 'SELECT * FROM db2.items;'
+----------+-------+-------+
| STOCK_ID | NAME | PRICE |
+----------+-------+-------+
| AB12CD | pen | 1 |
| EF34GH | paper | 2 |
| IJ56KL | book | 10 |
+----------+-------+-------+
Next, we can generate an SQL dump file for each database using the mysqldump command:
$ mysqldump --user=sysadmin --password=mypassword db1 > database1.sql
$ mysqldump --user=sysadmin --password=mypassword db2 > database2.sql
The generated SQL dump files are database1.sql and database2.sql. In general, we can use these dump files to restore the corresponding databases from a backup stage, or we could import them into another database in case we’re doing a migration.
Finally, for illustration, let’s create a third database named db3 into which we’ll import the SQL tables from the previous databases:
$ mysql --user=sysadmin --password=mypassword -e 'CREATE DATABASE db3;'
Here, we used the mysql -e command to issue a CREATE DATABASE statement in SQL for creating the db3 database.
Now, we’re ready to explore different ways of importing data into db3 via the SQL dump files generated earlier.
3. Importing a Single SQL Dump File
Importing a single SQL dump file into a database is straightforward:
$ mysql --user=sysadmin --password=mypassword db3 < database1.sql
In this case, we import the database1.sql dump file into the db3 database.
If we wish to import several SQL dump files instead of one, we can repeat the command for each dump file. However, this becomes tedious when the number of dump files is large.
4. Importing Multiple SQL Dump Files
It’s common to have many SQL dump files we wish to import into a MySQL database. We can import such files in an automated manner using several ways.
4.1. Using a for Loop
To import multiple dump files into a MySQL database, we can use a for loop to iterate over each .sql file for importing:
$ for i in *.sql; do mysql --user=sysadmin --password=mypassword db3 < "$i"; done
In this case, we iterate over both the database1.sql and database2.sql dump files, and import each, sequentially, into the db3 database.
To check that db3 is now populated with tables, we can issue the required SQL statements using mysql -e:
$ mysql --user=sysadmin --password=mypassword -e 'USE db3; SHOW TABLES;'
+---------------+
| Tables_in_db3 |
+---------------+
| employees |
| items |
+---------------+
The USE db3 statement selects the db3 database, while the SHOW TABLES statement lists the tables in this database.
We now see two tables listed in the previously empty db3. These tables were imported from the two SQL dump files.
4.2. Using Globbing With cat
Alternatively, we can use cat to concatenate the contents of all the dump files and pipe the result over to the mysql command:
$ cat *.sql | mysql --user=sysadmin --password=mypassword db3
Here, we used globbing to dynamically generate the names of our SQL dump files ending with the sql extension. Then, we piped the concatenated contents of those files to the mysql command with db3 preselected. The contents include SQL statements needed for generating the tables and populating them with data.
4.3. Sourcing the Dump Files
Another approach is to source the dump files in MySQL. To do so, we first use globbing with ls to list the names of the SQL dump files in our current directory. Then, we pipe the result to awk to prepend the word source before each file name:
$ ls *.sql | awk '{ print "source",$0 }'
source database1.sql
source database2.sql
Finally, we pipe the result to the mysql command:
$ ls *.sql | awk '{ print "source",$0 }' | mysql --user=sysadmin --password=mypassword db3
This way, we source each of the SQL dump files in the MySQL database.
5. Conclusion
In this article, we explored different ways to import multiple SQL dump files into a MySQL database.
In particular, one method involved using a for loop to import the dump files sequentially. Another approach was to concatenate the SQL dump files and pipe the result to the mysql command. Finally, a third approach was to arrange for sourcing the SQL dump files in MySQL.