SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Kevin Yank's book: mysqldump doesn't work

    In his book "Database Driven Website" Kevin Yank explains how to backup a database. In Mac OS X's terminal I entered:

    cd /Library/MySQL/bin

    The ls command then shows lots of files, including:
    mysqladmin
    mysqldump

    The command line says:
    mycomp:/Library/MySQL/bin userone$

    I enter, as Yank suggests:
    mysqldump -h localhost -u root -pmypassword dbname > dbname_backup.sql

    Result:
    -bash: dbname_backup.sql: Permission denied

    Since I can create a backup using phpMyAdmin I know I should be able to do the same thing using terminal.

    Who knows how to do it right?

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That isn't necessarily so. Do you run phpMyadmin with the same user and password as the one you are using the command line mysqldump?

    Check that you actually have file permissions with the user you are trying the mysqldump with.

    go to mysql as your root user, change to the mysql database and select file_priv from the user table for that particular user. If it isn't a 'Y' in that field then you can't dump to a file until you update that permission.

  3. #3
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    file_priv = Y, so what next?

    Thanks, David, but:

    mysql> SELECT user, file_priv FROM user WHERE user='root';
    +------+-----------+
    | user | file_priv |
    +------+-----------+
    | root | Y |
    +------+-----------+

    So what next?

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Isn't that a shell error?
    Try:

    mysqldump -h localhost -u root -pmypassword dbname > /usr/myname/dbname_backup.sql

    or

    mysqldump -h localhost -u root -pmypassword dbname > /change/this/path/to/somewhere/you/have/write/privs/dbname_backup.sql
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Shell error? Not one I can solve

    Thanks Samsm, but there is no "myname" equivalent inside "usr" and I cannot find any "privs" or equivalent, let alone a path to that place.

    I wonder where mysql puts a dumped file anyway. If I use phpMyAdmin I must choose a download folder.

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha!

    do this:
    mysqldump -h localhost -u root -pmypassword dbname > ~/dbname_backup.sql

    THe file will end up in your user's home folder, which should be accessible as ~

    That last part of the command is where the file is placed.

    If you did this:
    mysqldump -h localhost -u root -pmypassword dbname > /foo/bar
    ... the output would go to a file named "bar" in the "foo" folder. This is how you kind of choose a download folder.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Samsm, this worked indeed:

    mysqldump -h localhost -u root -pmypassword dbname > ~/dbname_backup.sql

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    so then the error was not mysql related but file permissions related. You didn't have permission with the user you were logged in as, to write the file to the directory you were attempting to write to. simple as that.

  9. #9
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Dave L,

    The question is - which directory am I writing to if I enter:

    mysqldump -h localhost -u root -pmypassword dbname > dbname_backup.sql ?

    In case of > /dbname_backup.sql it's my startup disk, in case of ~/dbname_backup.sql it's my user's home folder, but without / or ~/ where would it go provided I had mentioned permissions? And how do I get those permissions?

  10. #10
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Prist
    Hi Dave L,

    The question is - which directory am I writing to if I enter:

    mysqldump -h localhost -u root -pmypassword dbname > dbname_backup.sql ?
    Oh, that would be the directory you are presently in.
    Use "pwd" to see what that path is. Just type pwd and press enter.

    If it is /Library/MySQL/bin, that's not a place you want to save a MySQL dump, don't screw with the permissions there.

    But since you asked, you can change permissions with chmod.
    I'd suggest reading up a little on unix in general, that might be a help to you.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?


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
  •