Gentlemen,

The problem of how to generate a MySQL backup quickly via PHP quickly and efficiently has been stumping me for a while. I know the mysqldump trick, but for some reason under cPanel it generates needlessly bloated SQL I cannot even feed into phpMyAdmin. I have examined phpMyAdmin's code, but cannot figure out the exact part which enables it to export SQL so quickly. So I have been left with no choice to write code that generates insert statements one at a time. Very inefficient, and now my boss is getting maximum execution timeouts on one particularly large database.

Any insight that you might be able to offer would be much appreciated.

    Dont do inserts one at at time basically
    Mysql was designed to handle sets of data, which is a bit tricky for pragmatic programmers.
    In your php, generate the code of inserts into blocks of 100, 500, 1000, which ever speed is best and then execute each block with a single mysql statement, this will cut down on how many requests y ou send to the server.

      i hate cPanel (or any other sys control system). I know it's useful when you're managing dozens of sites but i still hate it.

      You could try writing your own php script to do a mysqldump. Mysqldump has lots of flags and parameters you can set in order to change the verbosity of the output.

      You could call mysqldump from within PHP using [man]exec[/man] or [man]passthru[/man] or something like that. If you choose the right flags, you can have dramatically different output.

      Another possibility is that your PHP script to export manually is slow because it writes a single line at a time. You could try creating a string variable to buffer your output and only writing the output file every 1000 lines or something. Fewer writes to disk should be significantly faster. Depending on your PHP setup, you may be allowed enough memory to dump the entire thing to a string before writing it out to a file.

        Some great ideas guys, I shall try them out. Thank you.

          Write a Reply...