#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bucharest, Romania
    Posts
    10
    Rep Power
    0
    Hello !!!

    My provider is using a firewall to protect the database server (MySQL). The connections on the 3306 port are not allowed.

    The only way for me is to upload the database file to the remote machine, and then sending queries to MySQL about that file.

    I created the file using the following statement:

    mysqldump --quick db_name > db_name.data

    Then i've uploaded the "db_name.data" file using FTP...

    And now i need a php3-program to create the tables and fill them with data... Does anybody know how can i do that ?

    Thank you in advance...


    P.S. I cannot do telnet or ssh on the remote machine
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    You're almost there.

    Dumping the schema results in individual lines of mysql commands. All you have to do is read in the file line by line using the various PHP filesystem commands and send each to mysql using mysql_query().

    Depending on the size of the dump file, you might have to up the max execution time of the script.
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Actually, if you want a simple, quick and dirty way, just write a small shell script (you might have to place it in your CGI directory) and then execute it using the exec() function of PHP.

    If you dumped a complete schema of your database, all you have to do is execute the mysql client and pipe that schema in (it will include all the SQL commands to create tables, and populate them with your data):

    #/usr/bin/sh
    mysql -u username -h hostname -ppassword < /{path to file}/db_name.data
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bucharest, Romania
    Posts
    10
    Rep Power
    0
    Aha! both ideas are really good. Thanx !!
    Unfortunately the dump file has about 2 MB and grows .5 mb / month, so i'll try using that short cgi-program.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bucharest, Romania
    Posts
    10
    Rep Power
    0
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rycamor:
    If you dumped a complete schema of your database, all you have to do is execute the mysql client and pipe that schema in (it will include all the SQL commands to create tables, and populate them with your data):

    #/usr/bin/sh
    mysql -u username -h hostname -ppassword < /{path to file}/db_name.data
    [/quote]

    The service provider allows me only to run perl scripts. (I never used perl.) Conform with his instructions, i've created a directory db, where i placed the "database.data" file, a show.php3 file that shows the database content, and last the create.pl file.

    The create.pl file is
    #!/usr/bin/sh
    mysql -u username -h xx.yyyyyy.zz -ptttt < database.data

    Then I've set the rights, rwxr-xr-x.

    The create.pl file is executed from a php3 file, using passthru("create.pl");

    I think i shoud rewrite the program for perl. Can you please give me a perl version for that shell script ? Thank you....
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bucharest, Romania
    Posts
    10
    Rep Power
    0
    I'm using now the following sequence:


    mysql_connect("xx.yy.zz","user","pass") or die ("Error1");
    mysql_select_db("db") or die("Error2");

    $db_file="database.data";
    $db_file_size=filesize($db_file);
    $lines=file($db_file);
    $nr=count($lines);

    for ($i=0; $i < $nr ; $i++)
    {
    $command=$lines[$i];
    $query = $command;
    $result = mysql_db_query("db",$query);
    }

    But no effect
    In fact it appears that no records were inserted...
  12. #7
  13. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Maybe an even simpler operation:

    Check out http://www.php.net/manual/language.o....execution.php

    Use the PHP backtick operator, and just include the whole command inside there.

    <?
    $output = `mysql -u username -h hostname -ppassword < /{path to file}/db_name.data`;

    echo "<pre>$output</pre>";

    ?>

    Echoing the $output variable will return whatever error message the console would give.

    I don't know Perl myself, but if this doesn't work, I should be able to slap together a little script. On the other hand, you could research Perl a bit (right here on Devshed) and probably figure it out.
  14. #8
  15. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2000
    Location
    Bucharest, Romania
    Posts
    10
    Rep Power
    0
    AAAAArgh..... It has absolutely no effect. I'll make a 1-2 days pause, then try again, and finally if it does not work, write a mail to the sysadmin... Maybe he knows what's happening on his machine...

    Anyway, thank you.

Similar Threads

  1. maysqladmin drop database syntax?
    By Slurm in forum MySQL Help
    Replies: 1
    Last Post: February 17th, 2004, 05:07 AM
  2. Replies: 1
    Last Post: February 8th, 2004, 01:59 PM
  3. PHP file upload script trouble
    By casbboy in forum PHP Development
    Replies: 9
    Last Post: February 5th, 2004, 07:42 PM
  4. creating database & tables using ASP
    By pdxlooie in forum ASP Programming
    Replies: 2
    Last Post: December 26th, 2003, 05:27 PM
  5. distributing database reads among db slaves
    By JunkCookie in forum PHP Development
    Replies: 3
    Last Post: December 17th, 2003, 11:45 AM

IMN logo majestic logo threadwatch logo seochat tools logo