48

is this right?

mysql -uroot -ppassword mydb < myfile.sql.gz
TIMEX
  • 259,804
  • 351
  • 777
  • 1,080

8 Answers8

115

No, it isn't. The right way would be

zcat myfile.sql.gz | mysql -u root -ppassword mydb

Note there can be no space between the -p and password if using the -p syntax, refer http://dev.mysql.com/doc/refman/5.5/en/mysql-command-options.html#option_mysql_password

Sepster
  • 4,800
  • 20
  • 38
Artyom
  • 31,019
  • 21
  • 127
  • 215
  • 5
    NOTE: the space between -p and password must be removed – Onyx Mar 31 '13 at 23:01
  • 11
    In case you run a limited bash shell (like "git bash", where zcat is missing) you might want to use instead: `gzip -dc myfile.sql.gz | mysql -u root -ppassword mydb` – pkk Mar 23 '14 at 22:38
  • 13
    NOTE: please don't put the mysql password in the -p argument as shown before. It may be captured by bash history and pose a security risk on your server. – Ben Rowe Jul 09 '14 at 03:35
  • 1
    Use `mysql mydb -u root -p` to avoid confusion between db name and password – zdepablo Feb 04 '15 at 12:36
  • 1
    Also, mydb needs to be created before importing. This doesn't create the db for you. – Siddhartha Apr 20 '15 at 23:00
  • thats why I prefer the `--password=blahblah` syntax. – Tuncay Göncüoğlu Aug 16 '17 at 11:10
  • 5
    -1 for writing the password in the command line. Use `mysql -u root -p mydb` which asks for passwords interactively. So that the password is not saved in bash history. – Елин Й. Dec 08 '17 at 07:15
58

Use the following command:

gunzip < databasefile.sql.gz | mysql -u root -p dbname
Елин Й.
  • 953
  • 10
  • 25
Alok Jain
  • 821
  • 1
  • 7
  • 12
7
  • 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.
Shahrukh Anwar
  • 2,544
  • 1
  • 24
  • 24
6

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.

Sergey Alexeev
  • 61
  • 1
  • 1
0

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>
Renish Gotecha
  • 2,232
  • 22
  • 21
0

On windows you can do this:

First step! Install gzip for windows. I recommend using chocolatey to do it: (https://chocolatey.org/install)

choco install gzip -y

Now you can descompress your gz file and send it to mysql:

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

Good luck!

Cristiano Mozena
  • 21
  • 4
0

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
4b0
  • 21,981
  • 30
  • 95
  • 142
chetan chetan
  • 1
-1

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.
Aman Goel
  • 3,351
  • 1
  • 21
  • 17