Transfer mysql database file to dedicated server via SSH+FTP


I want transfer a 60mb (.gz) file to mysql  on dedicated server and know that first must send it to root of site via FTP but don't know how transfer it to db via SSH.

Please let me know the codes.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zephyr ICTCloud ArchitectCommented:
What is the full extension of the file? Is it only .gz or .tar.gz?

If it's only .gz, one of the following commands should work to unpack the file:

$ gunzip db.gz

Open in new window

$ gzip -d db.gz

Open in new window

If  the file extension is .tar.gz, use something like this:

$ tar -zxvf db.tar.gz

Open in new window

Once you have unpacked the file you should have a file that is something like "db.sql".
You can then import this file into mysql with following command:

mysql -u username -p password db-name < db.sql

Open in new window

Where username and password is from the user that has the necessary rights to create a new database (e.g. root), replace "db-name" with the name you want to give this database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MOSTAGHASSIAuthor Commented:
Thanks, during that i was wait for reply from expert i found this command that you have send:

mysql -u username -p password db-name < db.sql

But now i can't reach to file on ssh,i login in ssh and i'm in this command:
[root@Server ~]#

on Directadmin i have created one user  'jamshid'
and 'jamshid' can manage and have created mysql db so when i FTP as 'jamshid' i reach to  /domains/  and my db file is in this dir,but in SSH cannot recognize these dirs.

So first let me know that how can to place of my db file from SSH?
Zephyr ICTCloud ArchitectCommented:
You are logged in as root.

It's possible the file you're looking for is under the user's (jamshid) home folder, check it like this:

# ls /home/jamshid/domains/

Open in new window

If it's there you can still use the command, just use the whole path like this:

#  mysql -u username -p password db-name < /home/jamshid/domains/

Open in new window

Or, move the file to the root folder, for example:

# cp /home/jamshid/domains/ /root

Open in new window

Then try:

# mysql -u username -p password db-name < db.sql 

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Gerwin Jansen, EE MVETopic Advisor Commented:
You can use scp to copy the file over (scp=secure copy).

Use the same credentials to copy the file over like this:

scp yourfile.gz root@server:/home/jamshid/

Open in new window

The above command will setup a secure connection and ask you for the (remote) root password (prompt).
MOSTAGHASSIAuthor Commented:
Yes the db file is there under jamshid user.

But this command below does not work:

mysql -u jamshid_music -p 74LfQhHJ7PhZGFjZ67 jamshid_musicarchive < /home/jamshid/domains/
MOSTAGHASSIAuthor Commented:
the responce:

--- ----------------------------------------
auto-rehash                       TRUE
auto-vertical-output              FALSE
character-sets-dir                (No default value)
column-type-info                  FALSE
comments                          FALSE
compress                          FALSE
debug-check                       FALSE
debug-info                        FALSE
database                          (No default value)
default-character-set             auto
delimiter                         ;
enable-cleartext-plugin           FALSE
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
ignore-spaces                     FALSE
init-command                      (No default value)
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              0
prompt                            mysql>
quick                             FALSE
raw                               FALSE
reconnect                         FALSE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
user                              jamshid_music
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect-timeout                   0
max-allowed-packet                16777216
net-buffer-length                 16384
select-limit                      1000
max-join-size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE
plugin-dir                        (No default value)
default-auth                      (No default value)
Zephyr ICTCloud ArchitectCommented:
Then move the file to the root folder ... And you might need to change the user rights.

# mv  /home/jamshid/domains/ /root

Open in new window

Now change user rights and owner:
chown root:root jamshid_musicarchive.gz

Open in new window

Now you can try to import the db again ...
Zephyr ICTCloud ArchitectCommented:
So the command worked but not everything was imported ... That's something else ...

Try the command to import with the other user, the one that is admin, jamshid.

#  mysql -u jamshid -p password db-name < /home/jamshid/domains/

Open in new window

MOSTAGHASSIAuthor Commented:
in last command password is for jamshid or for db?
Zephyr ICTCloud ArchitectCommented:
the password is for the user that has access to the db, or for the user that has rights to create databases.
MOSTAGHASSIAuthor Commented:
the response is:

-bash: /home/jamshid/ No such file or directory
Zephyr ICTCloud ArchitectCommented:
Did you move the file ?? Did you use the move command (mv) I posted above?
MOSTAGHASSIAuthor Commented:
i used this command and db file is there:

# ls /home/jamshid/domains/
MOSTAGHASSIAuthor Commented:
yes ,but in ftp filezila i cannot see the file in root please send me a command to test root.
MOSTAGHASSIAuthor Commented:
your command for move is:

mv  /home/jamshid/domains/ /root

but i think in last there is space after.gz
MOSTAGHASSIAuthor Commented:
do i correct it?
Zephyr ICTCloud ArchitectCommented:
First type:

Open in new window

This will tell us what directory your in now.

If it says /root then type:

ls -a

Open in new window

See if the file is listed
MOSTAGHASSIAuthor Commented:
the response is:

.              .bash_profile  DALOG                        .lesshst  .tcshrc
..             .bashrc        epel-release-6-8.noarch.rpm  .pki
.bash_history  .cshrc             .rnd
.bash_logout   .custombuild   jamshid_musicarchive.gz      .ssh
MOSTAGHASSIAuthor Commented:
please see my comment about move just above your comment about command pwd
Zephyr ICTCloud ArchitectCommented:
Ok, so the file is there so it seems....

You can use the command like this then:

 # mysql -u jamshid -p password db-name < jamshid_musicarchive.sql

Open in new window

MOSTAGHASSIAuthor Commented:
no it doesn't work.

please see my comment about move just above your comment about command pwd
Zephyr ICTCloud ArchitectCommented:
Don't worry about the move command ... The file is clearly in the directory your at, so it must be something else...

Type following:

# ls -la

Open in new window

Give me the result
MOSTAGHASSIAuthor Commented:
the result:

dr-xr-x---.  5 root    root    4.0K Jun 28 19:00 .
drwxr-xr-x  24 root    root    4.0K Jun 24 17:37 ..
-rw-------   1 root    root    1.7K Jun 28 18:43 .bash_history
-rw-r--r--.  1 root    root      18 May 20  2009 .bash_logout
-rw-r--r--.  1 root    root     195 Jun 24 17:35 .bash_profile
-rw-r--r--.  1 root    root     305 Jul 11  2011 .bashrc
-rw-r--r--.  1 root    root     100 Sep 23  2004 .cshrc
-rw-r--r--   1 root    root       4 Jun 24 17:01 .custombuild
drwxr-xr-x   2 root    root    4.0K Jun 24 17:36 DALOG
-rw-r--r--   1 root    root     15K Nov  5  2012 epel-release-6-8.noarch.rpm
-rw-r--r--   1 root    root    2.8K Nov  9  2014
-rw-r--r--   1 jamshid jamshid 230K Jun 28 15:55 jamshid_musicarchive.gz
-rw-------.  1 root    root      53 Jul 10  2014 .lesshst
drwxr-----   3 root    root    4.0K Jun 25 08:03 .pki
-rw-------   1 root    root    1.0K Jun 24 18:10 .rnd
drwx------.  2 root    root    4.0K Jun 24 17:02 .ssh
-rw-r--r--.  1 root    root     129 Dec  4  2004 .tcshrc
Zephyr ICTCloud ArchitectCommented:
Ok, do this:

chown root:root jamshid_musicarchive.gz

Open in new window

Try the mysql command again
MOSTAGHASSIAuthor Commented:
no it doesn't work.

if the db file (jamshid_musicarchive.gz) is in root i must not see it in  filezila?
Zephyr ICTCloud ArchitectCommented:
sorry ..

First unpack the file again ... It's still in gz.
Zephyr ICTCloud ArchitectCommented:
In my first answer are the command to unpack the gz file... The db is inside.
MOSTAGHASSIAuthor Commented:
i have .sql file on my laptop can i transfer it with filezila?
Zephyr ICTCloud ArchitectCommented:
Yes you can ... But why? Is the unpacking not working?
MOSTAGHASSIAuthor Commented:
I have confused are you agree that start from first,i don't know  start with which command?
MOSTAGHASSIAuthor Commented:
it is simple for me that first i upload db file via filezila to where that you want.
Zephyr ICTCloud ArchitectCommented:
Well, you have the file in /root now.

1. unzip the file with one of the 2 commands in my first reply
   1a. if this somehow fails you might need to run the chmod command first.
2. now run the mysql command on the jamshid_musicarchive.sql file

That should be it...
MOSTAGHASSIAuthor Commented:
Sorry and sorry because i didn't see your command for unzip and went to mysql  command so i start unzip.
Zephyr ICTCloud ArchitectCommented:
No problem ... Try the unzip
MOSTAGHASSIAuthor Commented:
i used 2 command:

$ gunzip jamshid_musicarchive.gz

$ gzip -d jamshid_musicarchive.gz

and the response is:

-bash: $: command not found

and the for chmod command the result is:

chmod: missing operand
Try `chmod --help' for more information.
Zephyr ICTCloud ArchitectCommented:
Is the response the same for both the commands? You might need to install gzip first then, or upload the sql file instead...

What is the distro you are using? CentOS, Ubuntu?
MOSTAGHASSIAuthor Commented:
yes it is response the same for both the commands.

it is CentOS
Zephyr ICTCloud ArchitectCommented:
Try to install gzip

# yum install gzip

Open in new window

MOSTAGHASSIAuthor Commented:
but  spravtek; this db file is very small,and i can upload the .sql file and try the command of mysql are you agrre?
Zephyr ICTCloud ArchitectCommented:
Yes, I mentioned it earlier, upload the sql file if you can ...
MOSTAGHASSIAuthor Commented:
i did your command but there was no message has it installed?
Zephyr ICTCloud ArchitectCommented:
Try the command to unzip again ... If it shows an error then probably not.

Or try :  yum list installed gzip
MOSTAGHASSIAuthor Commented:
the result for command   yum list installed gzip  is:

Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
epel/metalink                                            |  23 kB     00:00
 * base:
 * epel:
 * extras:
 * updates:
base                                                     | 3.7 kB     00:00
epel                                                     | 4.4 kB     00:00
epel/primary_db                                          | 6.6 MB     00:00
extras                                                   | 3.4 kB     00:00
updates                                                  | 3.4 kB     00:00
updates/primary_db                                       | 3.9 MB     00:00
Installed Packages
gzip.x86_64                         1.3.12-22.el6                          @base

is it ok?
Zephyr ICTCloud ArchitectCommented:
Looks like it ... Should work now.

If not, post the error.
MOSTAGHASSIAuthor Commented:
for both command the error is:

-bash: $: command not found
Zephyr ICTCloud ArchitectCommented:
did you run the following command yet:

chown root:root jamshid_musicarchive.gz

Open in new window

What is the result of just typing "gzip" followed by enter.
MOSTAGHASSIAuthor Commented:
i did again the command :
chown root:root jamshid_musicarchive.gz

there was nothing as respose.

for  gzip+enter the result is:

gzip: compressed data not written to a terminal. Use -f to force compression.
For help, type: gzip -h
Zephyr ICTCloud ArchitectCommented:
so that means it's installed and should be working ...

So this is not working?

gzip -d jamshid_musicarchive.gz

Open in new window

If not, give me the results of "ls -la" again please.
MOSTAGHASSIAuthor Commented:
before i test the command please read this comment:

but there is one thing ,in Direcadmin it says that the file for upload must be(.gz) and it works good ,i have tested it ,because the phpmyadmin resote it,why do you want that unpack the file?

I have send this db file via Directadmin very good.

But i have another big file that cannot send via direcadmin so must send via ftp+ssh  for this reason i want learn it because its zip is around 60mb.
MOSTAGHASSIAuthor Commented:
i did your command(gzip -d jamshid_musicarchive.gz) without error and nothing for any message is it good?
MOSTAGHASSIAuthor Commented:
the result for 'ls -la'   is:

total 1.4M
dr-xr-x---.  5 root root 4.0K Jun 28 21:35 .
drwxr-xr-x  24 root root 4.0K Jun 24 17:37 ..
-rw-------   1 root root 2.9K Jun 28 21:34 .bash_history
-rw-r--r--.  1 root root   18 May 20  2009 .bash_logout
-rw-r--r--.  1 root root  195 Jun 24 17:35 .bash_profile
-rw-r--r--.  1 root root  305 Jul 11  2011 .bashrc
-rw-r--r--.  1 root root  100 Sep 23  2004 .cshrc
-rw-r--r--   1 root root    4 Jun 24 17:01 .custombuild
drwxr-xr-x   2 root root 4.0K Jun 24 17:36 DALOG
-rw-r--r--   1 root root  15K Nov  5  2012 epel-release-6-8.noarch.rpm
-rw-r--r--   1 root root 2.8K Nov  9  2014
-rw-r--r--   1 root root 1.3M Jun 28 15:55 jamshid_musicarchive
-rw-------.  1 root root   53 Jul 10  2014 .lesshst
drwxr-----   3 root root 4.0K Jun 25 08:03 .pki
-rw-------   1 root root 1.0K Jun 24 18:10 .rnd
drwx------.  2 root root 4.0K Jun 24 17:02 .ssh
-rw-r--r--.  1 root root  129 Dec  4  2004 .tcshrc
MOSTAGHASSIAuthor Commented:
it seems it is unzip now yes?
Zephyr ICTCloud ArchitectCommented:
Yes, but it doesn't have an extension so it seems

Try the mysql import command with the name as you see it in the list you posted ... Let's see if that works.
MOSTAGHASSIAuthor Commented:
it want password ,the password of db?
MOSTAGHASSIAuthor Commented:
do enter db password?
Zephyr ICTCloud ArchitectCommented:
Probably yes, if the user has access to the db and the db already exists ... Otherwise a user that has the right to create or alter the database.
MOSTAGHASSIAuthor Commented:
or password of 'jamshid'
MOSTAGHASSIAuthor Commented:
ERROR 1045 (28000): Access denied for user 'jamshid_music'@'localhost' (using password: YES)
Zephyr ICTCloud ArchitectCommented:
Did you create the database beforehand? Or did you create it earlier when you tried to import the file into MySQL?
MOSTAGHASSIAuthor Commented:
i had created database in Directadmin before ,in Directadmin for uploading the db(.gz) file there are 2 options for access the db :

1- 'Your account login information'(this is jamshid account and i'm inside it so i have selected this without any password can upload)

2- define a username  and password
MOSTAGHASSIAuthor Commented:
from inside Directadmin it is very simple that we upload db file but for files more that 10mb it is not possible.
Zephyr ICTCloud ArchitectCommented:
What's the name of the database? Did you try to run the command with the root user?
MOSTAGHASSIAuthor Commented:

Did you try to run the command with the root user?

All of command that you want i entered as root user to ssh.
MOSTAGHASSIAuthor Commented:
i transferred the .sql file in root via ftp and the enetered this command  ls -a

then the result is:

.              .bash_profile  DALOG                        .lesshst  .tcshrc
..             .bashrc        epel-release-6-8.noarch.rpm  .pki
.bash_history  .cshrc             .rnd
.bash_logout   .custombuild   jamshid_musicarchive         .ssh
Zephyr ICTCloud ArchitectCommented:
Ok, but I meant like this

 mysql -u root -p password jamshid_music < jamshid_musicarchive.sql

Open in new window

If root has the necessary right of course.

If all else fails you could delete the database and try the import from the command line again ... After you can add/change the users anyway.
MOSTAGHASSIAuthor Commented:
mysql -u root -p password jamshid_music < jamshid_musicarchive.sql

no never used root in my commands as your guide ,this is first command that i see has root.

my mean was  entering in ssh as root user.

what does mean root in this command (root for ssh?)
Zephyr ICTCloud ArchitectCommented:
Yes, it's the same user, it can work, but it depends on the fact if the root user has all the necessary rights in MySQL of course.

You normally have an administrator who is some sort of super user of MySQL, this user can create/delete/adjust all the databases ... But again, everything depends on the config of MySQL.
MOSTAGHASSIAuthor Commented:
My server support send this command and i tested it is ok,

mysql -u dbusername -p databasename < backupname.sql

mysql -u jamshid_music -p jamshid_musicarchive < jamshid_musicarchive.sql

after entering it request password and i entered db pass.

Thanks a lot for your help,you spent lot of times and as you know i'm beginner but learnt lot of things from you.
Zephyr ICTCloud ArchitectCommented:
Yes, that's the command I gave you somewhere in the beginning ... But the most important thing is, you got there in the end ;-)

Glad to be of help!
MOSTAGHASSIAuthor Commented:
spravtek,i realy sorry and sorry because i see that in your first comment i had forgotten to enter dbname,and spent lot of times from you,once again thanks.
Zephyr ICTCloud ArchitectCommented:
No problem ... You learned that is the most important part!
MOSTAGHASSIAuthor Commented:
I hope that you forgive me ,please let me know that you have forgiven me,my mistake takes lot of time from you.
Zephyr ICTCloud ArchitectCommented:
Not worry MOSTAGHASSI, I forgive you! :-)
MOSTAGHASSIAuthor Commented:
Thanks a lot for your help and time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.