mySQL / MariaDB - Import .sql file on Docker using the mysqldump command

Before importing a .sql file into a mySQL or MariaDB database, you will most likely have used the mysqldump command to create the .sql file.

Let's say the foo.sql file resides at /usr/local/foo.sql on your Docker system. The docker cp command can be used to copy foo.sql to the MariaDB container. In this example, the foo.sql file will be copied to /tmp in the container.

docker cp /usr/local/foo.sql mariadb:/tmp

 

The docker exec command can be used to verify that foo.sql now exists at /tmp in the container.

~]# docker exec mariadb ls -l /tmp
-rw-r--r-- 1 root root 2037015 Oct 31 09:15 foo.sql

 

Before importing a .sql file into a mySQL or MariaDB database, you may want to determine if the file contains CREATE DATABASE IF NOT EXISTS command. If not, you will want to create the database before importing the .sql file.

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `myDatabase` /*!40100 DEFAULT CHARACTER SET latin1 */;

 

Use the docker exec command to get an interactive shell in the container.

docker exec -it mariadb bash

 

If the database does not exist, and the .sql file contains CREATE DATABASE IF NOT EXISTS, there is no need to include the name of the database on the command line.

mysql -u root -p < foo.sql

 

If the database does already exist, or the .sql file does not contains CREATE DATABASE IF NOT EXISTS, you will need to include the name of the database on the command line. Be aware that this will append data to the database.

mysql -u root -p databasename < foo.sql

 

If the file is gzip compressed, use this syntax.

zcat the_file.sql.gz | mysql -u root -p databasename

 

In this scenario, while the zcat and mysql commands are running, the ps command should return the following.

~]# ps
  PID TTY          TIME CMD
 2905 pts/0    00:00:00 su
 2910 pts/0    00:00:00 bash
 3105 pts/0    00:00:00 zcat
 3106 pts/0    00:00:00 mysql
 3109 pts/0    00:00:00 ps

 

To verify that the data was imported, connect to your MySQL or MariaDB, and use the show databases, use database, show tables and select * from tablename commands.




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee

Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.





Please enter c3299 in the box below so that we can be sure you are a human.