SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL dump from remote server to local windows box

    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

  2. #2
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    mysqldump --opt -uyour_username -pyour_password database_name > /path/to/dump/file.sql
    then to install it:

    Code:
    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.
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    mysqldump -umyusername -pmypassword database_test < backup.sql
    Do you really mean that? You mean mysql right?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysqldump>

    mysql>

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

    (thanks - "mysql" works)

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2001
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just looked up on webmonkey and there are commands here:
    webmonkey

    j.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •