25

I am using Ubuntu 14.04. I want to import my database which is located on the server /var/www/backup.zip. Also I've SSH access using Putty. Noted that MySQL and phpMyadmin are installed on the server.

When I run this command

mysql -u root -p spy < /var/www/backup.zip

nothing happens. A newline shows which starts with ->.

What should I do now?

enter image description here

d a i s y
  • 5,411
  • 9
  • 41
  • 59
stack
  • 551
  • 3
  • 10
  • 14
  • You ran the `mysql` command inside `mysql`? – muru Aug 23 '17 at 05:46
  • @muru Not sure what you mean exactly, but yes, as you see, there is `mysql> ` at the beginning of the line. – stack Aug 23 '17 at 05:47
  • 2
    [How to import an SQL file using the command line in MySQL?](https://stackoverflow.com/questions/17666249/how-to-import-an-sql-file-using-the-command-line-in-mysql) – d a i s y Aug 23 '17 at 10:26

2 Answers2

37

I believe the format is:

mysql -u username -p database_name < /path/to/file.sql

From within mysql:

mysql> use db_name;
mysql> source backup-file.sql;
George Udosen
  • 35,970
  • 13
  • 99
  • 121
  • well what's the difference between yours *(`mysql -u username -p database_name < /path/to/file.sql`)* and mine *(`mysql -u root -p spy < /var/www/backup.zip`)*? – stack Aug 23 '17 at 06:04
  • 3
    @stack that George intends that command to be run in the shell, and not inside mysql. (That's why the rest of the answer is headed by "from **within** mysql".) – muru Aug 23 '17 at 06:08
  • @muru Ah I see. thank you guys. *(btw I think you could say so 30min ago `:)`)* – stack Aug 23 '17 at 06:11
  • in ubuntu you should add ./mysql to access file within xampp's bin folder otherwise it will search for global – Rj_Innocent_Coder Dec 05 '18 at 06:50
4

The main problem is that you're trying to run a bash command from within mysql. If you run that command at the regular terminal prompt the format of the command is correct.

The second issue is that you have a zip file and not a SQL file so you need to unzip it first.

How do I load a sql.gz file to my database? (on Server Fault) explains most of what you need. My answer there should work here too with a slight modification:

unzip -p /var/www/backup.zip | mysql -u root -p mydb

unzip -p unzips to a pipe, so that you can pipe that into your database command. Make sure the zip file only contains one sql file.

mysql -u root -p mydb is going to prompt you for a password (the -p without a value) and then attempt to pipe in the file data to mydb - change the mydb to your own database. You shouldn't specify the password in the command line as it then remains in your command history.

icc97
  • 645
  • 2
  • 8
  • 16