MySQL dump from remote server to local windows box

[forrest]

I want to make a .sql copy on my local windows machine of some MySQL tables on a remote server. I can do this easily using phpMyAdmin (view dump schema & save as file) and end up with an .sql text file on my own harddrive (that way I have a text backup on my own computer of the table that I can restore if needed). What I want to do is figure out how to do this WITHOUT phpMyAdmin - but using SSH. I can SSH into the remote server no problem but I can't figure out the exact syntax for the mysqldump command. I have tried things like:

at the mysql> prompt ...

mysqldump -h localhost -u username -p password
databasename [tablename] > c:\sqlfiles\tablename.sql

but I get error - unknown command '/a'.

tried many other variations but can't seem to get it right.

Any help would be appreciated

JAF

[weirdbeardmt]

the problem is you are trying to dump to a directory that very likely doesn't exist or you don't have access to. What you will need to do is dump to your filespace, then FTP on to your local machine.

This is the code you will need:

mysqldump --opt -uyour_username -pyour_password database_name > /path/to/dump/file.sql

then to install it:

mysql -uyour_username -pyour_password database_name < /path/to/dump/file.sql

and to find out the path to dump the file to (i.e. the top level of your host type cwd at the bash prompt.

[forrest]

Thanks for the reply weirdbeardmt. I did manage to get the mysqldump to work using the following at the command prompt bash-2.05$:

mysqldump --opt -umyusername -pmypassword database_name > backup.sql

It worked and a complete backup .sql file is on the server which I can ftp to my local machine.

Next problem is I can't seem to get it dump it back up to a new database (test). At the bash prompt I type:

mysqldump -umyusername -pmypassword database_test < backup.sql

It seems to work and I get the following lines on my screen.

# MySQL dump 8.16
#
# Host: localhost Database: database_name
#--------------------------------------------------------
# Server version 3.23.45

bash-2.05$

But then it stops. I think I should see all the sql statements being executed here.

When I go into the new test database there's nothing there. (The test db is a blank db with nothing in it - set up to see if I can restore all the data from the original db dump sql file.)

The backup.sql does have all the info in it with 4 create tables statements and 4 insert statements with all the right data there.

Any tips?

Thanks

JAF

[weirdbeardmt]

mysqldump -umyusername -pmypassword database_test < backup.sql

Do you really mean that? You mean mysql right?

[forrest]

As a follow up - I can get the data loaded up to the new test db by just using mysql and cut & pasting the sql CREATE statements into the mysql> prompt and the INSERT statement - but its a bit cumbersome and it seems I should just be able to load the whole backup.sql file that contains all the sql statements and build the whole db automatically rather than copy & pasting each individual sql statements into mysql. Also one table has a long INSERT statement with 300 records - to get this to work I have to cut & paste 25 records at a time wiht the INSERT statement. Not a big deal (and it works) but I'm trying to learn a more efficient way of doing it.

Any help is appreciated

Thanks

JAF

[forrest]

mysqldump>

mysql>

Arghh - sorry - brain confused - stared at screen way to long - I'll punish myself all day...

(thanks - "mysql" works)

[DR_LaRRY_PEpPeR]

Originally posted by weirdbeardmt
and to find out the path to dump the file to (i.e. the top level of your host type cwd at the bash prompt.

hey weirdy. when i told you the cwd command (i assume you got it from me? ), i was wrong. it's pwd that will tell you the directory you're currently in. sorry about that!

[forrest]

Hey thanks Dr Larry Pepper for clarifying PWD (it works!)

I'm just beginning to use SSH as an alternative to phpmyadmin - do you know of a good resource site - ie ssh tutorial or source for commands etc?

thanks


J.

[forrest]

Just looked up on webmonkey and there are commands here:
webmonkey

j.