Remote tar.gz with sql run local server

Hi:

I have a tar.gz with a full mysql database update that I can access via ftp.

This tar.gz updates daily and I would like to create a php and/or mysql connection to that ftp account that would allow me to run that large sql query on my local mysql server.

Any thoughts?

From stackoverflow

• First of all PHP supports reading from FTP out of the box, just use fopen with specified ftp protocol like so

  <?php
  $handle = fopen("ftp://user:password@example.com/somefile.txt", "w");
  ?>
  

  then you will need to unzip the archive. Here File_Archive from Pear repository will come very handy

  Hugo Gameiro : Thats a start. But is there a way to run the full sql without extracting?

  Sergej Andrejev : I don't think so. Anyway go with Sean's solution I haven't realized that you will be using cron

• (I'm going to go out on a limb and assume you want to do this from a unix box of some kind...)

  Throw this:

  #!/bin/bash
  
  wget                        \
      -O -                    \
      --ftp-user=username     \
      --ftp-password=password \
      "ftp://wherever/whatever.tar.gz" | tar xzOf - | mysql --user=whoever --password=secret dbname
  

  in a file (update-db.sh for example) then chmod it:

  chmod 755 update-db.sh
  

  then you can just:

  ./update-db.sh
  

  whenever you want to download the update. Or you can throw it into a cron job (man crontab).

  Edit: Added the O flag to tar so that it extracts to stdout.

  Hugo Gameiro : and could I run the tar.gz sql source into my mysql server at the same time? how?

  Hugo Gameiro : sorry I didnt read the full script... thanks a lot gona try it

  Hugo Gameiro : what does ": bad interpreter: No such file or directoryin/bash" mean??? any ideas?

  Sean Bright : Most likely you have carriage returns (\r) in the file instead of just newlines (\n)

  strager : @Bright, The problem with your script is that you're running mysql on the output of tar. tar extracts files, so you need to make a temporary directory and run mysql on all e.g. *.sql files.

  Hugo Gameiro : got it... line endings in windows

  Hugo Gameiro : harder then I thought

  Sean Bright : @strager: ah yes. good call. updated.

  Sean Bright : @Hugo: see my minor update (change xzf to xzOf in the call to tar)

  Hugo Gameiro : changed it still get an error Resolving ... failed: Name or service not known. line 4: -O: command not found

  Hugo Gameiro : my bad. copy paste didnt work that good and had to solve the line endings to linux and the extra spaces into tabs... now I only have a problem with permissions for the database... but I think I am on the right track...

  Hugo Gameiro : MANY MANY MANY thanks... I would never get there... It works like magic... If you were a woman I would kiss you right now :)