How Do I Load a Sql. Gz File to My Database? (Importing)

Use the following command:

gunzip < databasefile.sql.gz | mysql -u root -p dbname

• You must not use the password directly in the terminal, use without it like follows

zcat YOUR_FILE.sql.gz | mysql -u YOUR_DB_USERNAME -p YOUR_DATABASE_NAME

• Hit enter and when terminal asked for your password, type your password and hope everything will work fine.

Straight and clear:

gunzip -c myfile.sql.gz | mysql -uroot -ppassword mydb

-c option for gunzip writes to stdout, keeps original files

NOTE: You shouldn't put the password directly in the command. It's better to provide just -p and than enter the password interactively.

For Generating dbName.sql.gz

mysqldump -u <YOUR USERNAME> -p<YOUR PASSWORD> <YOUR DBNAME> | gzip > ~/mysqlBackup/dbName_`date +%Y%m%d%H%M`.sql.gz

For Loading dbName.sql.gz

zcat ~/mysqlBackup/<.SQL.GZ file> | mysql -u <YOUR USERNAME> -p<YOUR PASSWORD> <DATABASE NAME IN WHICH YOU WANT TO LOAD>

I have myfile.tar.gz in mysql_daly_backup so it run above like:

mysqldump -u <YOUR USERNAME> -p<YOUR PASSWORD> <YOUR DBNAME> | gzip > ~/mysqlBackup/dbName_`date +%Y%m%d%H%M`.tar.gz

If you are a windows user, I recommend you follow these steps:

1. The first step is to install gzip, I recommend you do it using Chocolatey. You can install it via the following link:

2. After cholocatey installed, now just install gzip:

   choco install gzip -y

3. Once installed, you can now unzip and import your sql.gz files directly into the MySQL prompt with the following command:

   gzip -cd backup.sql.gz > mysql -uUSER -pPASSWORD -hLOCALHOST DATABASE

Notes and useful:

• If you want to dump and compress it directly using gzip, just do it using the command below:

  mysqldump -uUSER -pPASSWORD -hHOSTNAME DATABASE_NAME | gzip -a9 > PATH_TO_SAVE_FILE_SQL_GZ

• The -h parameter in mysqldump does not require for using in localhost. Use this for remote MySQL server

• Use in gzip the -9 parameter to the best compression level or -1 parameter to fast compression.

You have to follow below steps:

• First check Mysql service should be running.

• Then if you have compressed file, decompress it first.

• Then you will find .sql file after decompressing.
• Then find import data in left corner in Mysql.
• Select option import from self-contained file and select your .sql file and specify a new schema name.
• Then click on import data.
• After importing you will see your new schema in available schema list.