1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

phpMyAdmin exports empty sql files

Discussion in 'Databases' started by 1959, Dec 15, 2008.

  1. #1
    I am managing my first server and learning the ropes. This question probably has some overlap into the server administration forum.

    There is a big database on my site, about 900mb, that I would like to back up by exporting from phpMyAdmin. The sql file that is exported downloads instantly, is empty, and the file size is 0 bytes.

    I am guessing that large size of the database means that it can't be downloaded without changing some settings on the server. I have root access but I'm not too experienced :)
    SEMrush
    The server details:
    Plesk version psa v8.3.0_build83080131.20 os_FedoraCore 7
    Operating system Linux 2.6.23.15-80.fc7

    I also tried to back up the databases using SSH commands, everything goes fine until it's time to write the database, when it tells me "can't write to folder".

    Thanks for any guidance while I get the hang of this and learn how to fend for myself :)
     
    1959, Dec 15, 2008 IP
    SEMrush
  2. QiSoftware

    QiSoftware Well-Known Member

    Messages:
    791
    Likes Received:
    10
    Best Answers:
    0
    Trophy Points:
    158
    #2
    you have the right permissions set on the folder, right? That said-- I was trying to load a 1.9MB sql file via phymyadmin yesterday and it kept hanging. How many tables does the database have and can you export it table by table?

    Q...
     
    QiSoftware, Dec 15, 2008 IP
  3. ilajas

    ilajas Peon

    Messages:
    183
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Perhaps you forgot to select all tables when exporting the database using phpmyadmin.

    If you have root access, try using this command

    mysqldump -u root -p thepassword --all-databases | gzip > backup.sql.gz

    The backup file will be on your root folder.
     
    ilajas, Dec 15, 2008 IP
  4. Optiplex Line

    Optiplex Line Peon

    Messages:
    204
    Likes Received:
    5
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Try doing it in ssh.
     
    Optiplex Line, Dec 15, 2008 IP
  5. 1959

    1959 Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Guys, thanks for trying to help me with my problem.

    phpMyAdmin wasn't working with my database, and GoDaddy (don't hate me, I didn't choose them!) said the database is too large to be backed up by phpMyAdmin (6 million lines, about 900mb).

    I ran the ssh commands to dump the sql file in the root. It worked! But some things happened that I do not totally understand.

    1) The mysqldump file is only 225mb when the original database is close to 1GB in size.
    2) I did not specify a path, I just put the filename so the sql file would be created in the root. When I log in to the server by SFTP to find the file and download it, it's actually written to a directory called /home/myusername/

    :)
     
    1959, Dec 16, 2008 IP
  6. kailash

    kailash Well-Known Member

    Messages:
    1,251
    Likes Received:
    42
    Best Answers:
    0
    Trophy Points:
    100
    #6
    It seems you have taken compress backup hence it is displaying small size then actual database size. Not sure about your second question, why it is happened.

    Kailash
     
    kailash, Dec 16, 2008 IP
  7. StevePro

    StevePro Peon

    Messages:
    106
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #7
    This sounds normal. the /home/username would be your 'home' directory and is probably the default location you are routed to when connecting with SSH.
     
    StevePro, Dec 17, 2008 IP
  8. israelibadamm

    israelibadamm Greenhorn

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #8
    Hey everybody,
    I seem to have the same problem, i am also on a godaddy server,
    my file is not that big, only around 15MB but i can't seem to download it via phpmyadmin, i click export with all the the fields selected but i get a blank SQL,

    I tried running the commands via SSH but i get errors, maybe one of you guys could help me. and write the SSH command i need to run with the following details
    I currently have a few databases installed on the server, the one i need is "bizbooz", the user name for the server is "bizbooz", the user name for the sql admin is "admin" and lets say the password is 123456. Please any help would awesome!

    Thanks!
     
    israelibadamm, Dec 17, 2008 IP
  9. 1959

    1959 Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    I didn't compress the database at all.

    It does appear to be complete with all tables present, I'm just at a loss to explain the size difference.
     
    1959, Dec 17, 2008 IP
  10. 1959

    1959 Peon

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    I found this link helpful.

    http://www.kirupa.com/web/mysql_backup_restore2.htm

     
    1959, Dec 17, 2008 IP
  11. stevesd34

    stevesd34 Peon

    Messages:
    11
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #11
    that link doesnt work
     
    stevesd34, Dec 20, 2008 IP
  12. igor.icsoft.ru

    igor.icsoft.ru Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    I had the same issue. It works fine, but at some point it just stops and always returns empty file (either .sql or .zip/.gz format selected).

    First solution I found was to reset PHP-FPM (PHP server used in NGINX or apache depending on your setup). That worked well. But at some point it again stops to export.
    The most recent solution I discovered is to select Texy! text format, make a quick export and then it works again for .sql.

    I hope that helps you guys,
    Enjoy.
     
    igor.icsoft.ru, Nov 7, 2011 IP
  13. proxyblaze

    proxyblaze Peon

    Messages:
    17
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #13
    corrupted file i guess??
     
    proxyblaze, Dec 7, 2011 IP
  14. ezprint2008

    ezprint2008 Well-Known Member

    Messages:
    612
    Likes Received:
    15
    Best Answers:
    2
    Trophy Points:
    140
    Digital Goods:
    4
    #14
    You might want to change the username/password at your database if that's your real login/pass because there's lots of bad stuff blackhat and others could do with that :D
     
    ezprint2008, Feb 5, 2012 IP
  15. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #15
    In phpMyAdmin you have to select the tables to back up and you have to tell it to include the data (otherwise it just saves "create table" statements). You have to do both - doing one doesn't set the other. Selecting no tables and not selecting data will result in an empty backup file.
     
    Rukbat, Feb 25, 2012 IP