How to Import a .sql.gz File into MySQL Database on Ubuntu

by

When working with MySQL, you may find yourself needing to import a database backup that comes in a compressed .sql.gz format. This can be done with a few simple commands on your Ubuntu server. In this guide, we’ll walk you through the process of importing a .sql.gz file into a MySQL database.

Table of Contents Hide
1) Prerequisites
2) Step 1: Log in to MySQL
3) Step 2: Create the Database (If Needed)
4) Step 3: Create a MySQL User and Grant Permissions
5) Step 4: Import the .sql.gz File into MySQL
6) Step 5: Verify the Import
7) Step 6: Login as the New User (Optional)
8) Conclusion

Prerequisites

Before we begin, ensure you have:

  • A running MySQL server on your Ubuntu machine.
  • Access to the MySQL root user or a user with appropriate privileges to import data.
  • The .sql.gz file that contains your backup data.

For this guide, we’ll use the following sample details:

  • Database Name: sample_db
  • MySQL User: db_user
  • User Password: S3cur3P@ssw0rd!
  • SQL Backup File: /home/user/backup_sample.sql.gz

Step 1: Log in to MySQL

Start by logging in to MySQL as the root user (or another user with sufficient privileges) through the terminal:

mysql -u root -p

You will be prompted to enter the MySQL root password. Enter it, and you should now be inside the MySQL shell.

Step 2: Create the Database (If Needed)

If the database does not already exist, you need to create it:

CREATE DATABASE sample_db;

If the database already exists, you can skip this step.

Step 3: Create a MySQL User and Grant Permissions

If you haven’t already set up a MySQL user for this database, create one and grant them privileges:

CREATE USER 'db_user'@'%' IDENTIFIED BY 'S3cur3P@ssw0rd!';
GRANT ALL PRIVILEGES ON sample_db.* TO 'db_user'@'%';
FLUSH PRIVILEGES;

This creates a user db_user with full access to sample_db.

Now, exit the MySQL shell by running:

EXIT;

Step 4: Import the .sql.gz File into MySQL

Now that you’re back in the terminal, import the .sql.gz file using gunzip and mysql:

gunzip < /home/user/backup_sample.sql.gz | mysql -u root -p sample_db

Explanation:

  • gunzip < /home/user/backup_sample.sql.gz: Decompresses the .sql.gz file.
  • | mysql -u root -p sample_db: Pipes the decompressed SQL data into MySQL for the sample_db database.

Enter your MySQL root password when prompted. The import process should now begin.

Step 5: Verify the Import

After the import is complete, log back into MySQL:

mysql -u root -p

Then, select the database:

USE sample_db;
SHOW TABLES;

If the tables are listed, the import was successful!

Step 6: Login as the New User (Optional)

To test if the new MySQL user (db_user) can access the database, log in using their credentials:

mysql -u db_user -p

Enter the password (S3cur3P@ssw0rd!), then try:

USE sample_db;
SHOW TABLES;

If you see a list of tables, the new user has access.

Conclusion

Congratulations! 🎉 You have successfully imported a .sql.gz file into a MySQL database on Ubuntu. This method allows for quick and efficient restoration of database backups without needing to manually extract the .sql file beforehand.

Leave a Comment