PDA

View Full Version : can sql upload directly to ftp ?


mampwamp
01-17-2011, 01:38 AM
i want to upload my database, i using phpMyadmin import > file to import to import my sql, but it very slow.

can sql upload directly to ftp ?

domedia
01-17-2011, 06:18 PM
No, it has to be imported by MySql.
Alternatively, you can do it from a console shell.

davidj
01-18-2011, 06:38 PM
does your host allow an SSH connection?

jmichae3
01-19-2011, 08:39 AM
you can write PHP code to do a database dump and write the output to a file and then upload it. I've done dumps before. but there could be a problem with the server giving write access to your file because the server is by default readonly.

function dbdump() {
global $db, $link;
echo "<pre><code>/*Db Dump*/\n";
echo "CREATE DATABASE IF NOT EXISTS `$db`\n";
echo "USE `$db`\n";
$q1=mysql_query($src="SHOW TABLES") or die($src . mysql_error());
while ($row1=mysql_fetch_array($q1,MYSQL_ASSOC)) {
$table=$row1[0];
//process tables
$q2=mysql_query($src="SHOW CREATE TABLE `$table`") or die($src . mysql_error());
while ($row2=mysql_fetch_row($q2)) {
echo $row2[1];
}
//generate table locking statements here
echo "LOCK TABLES `$table` READ;\n";
//generate insert statements
$q2=mysql_query($src="SELECT * FROM `$table`") or die($src . mysql_error());
while ($row2=mysql_fetch_array($q2,MYSQL_ASSOC)) {
echo "INSERT INTO `$table` (";
$n=count($fields);
for ($i=0; $i<$n; $i++) {
echo "'$fields[$i][Field]'";
if ($i!=$n-1) {echo ",";} //not on last item
}
echo ") VALUES\n";
echo "(";
while ($row2=mysql_fetch_array($q2,MYSQL_ASSOC)) {
if ('binary'==$fields[$i]['Collation']) {
//hex dump it
echo mysql_hex($row2[$fields[$i]]);
//echo "\"X";
////...and break up the lines into blocks of 80 chars
////so people can edit the file and it doesn't crash browsers.
//$s=hex($row2[$fields[$i]]);
//for ($i2=0; $i2<strlen($s); $i2+=80) {

// echo substr($s,$i2,80);
//}
//echo "\"";
} else { //not binary
echo "\"" . mysql_escape_string($row2[$fields[$i]]) . "\"";
}
if ($i!=$n-1) {echo ",\n";} //not on last item
}
echo ")\n";
}
//generate table unlocking statements here
echo "UNLOCK TABLES `$table`;\n";
}
echo "</code></pre>\n";
}


this just enumerates across the tables, and for each, generates a MySQL statement to CREATE the table, and generates INSERT statemements for the contents.

jmichae3
01-19-2011, 07:43 PM
tweak to your liking.... I suggest that instead of echo statements, that you use $s .= if you have enough memory (only 128MB for PHP and server space is tight) OR fopen, fclose, and fprintf for writing a file - those 3 functions don't take up hardly any memory. you might want to fopen using mode "w" in order to create always.
here is a sample of how to use the 3 functions:

$handle=fopen("filepath", "w");
$result_tables=mysql_query("SELECT TABLES", $link);
while ($tablerow=mysql_fetch_array($result_tables, MYSQL_ASSOC)) {
fprintf($handle, "INSERT INTO %s(a, b, c) VALUES(", $tablerow[0]); //output to file
//%s is a string you specify with arguments,
//you can fill the fprintf format in with other stuff
mysql_query()
$n=mysql_numrows($result);
$i=0;
while ($row=mysql_fetch_array($result)) {
if ($n-1==$i) {
fprintf($handle, "%s);\n", $row[$i]);
} else {
fprintf($handle, "%s,", $row[$i]);
}
$i++;
}
}
fclose($handle);

I am basically throwing this together, but you get the basic idea of how to use these functions.

here's the repaired version of the functrion. sorely needed updating and fixing. apparently I left it in a non-working state.


function dbdump($db, $link) {
echo "<pre><code>/*Db Dump*/\n";
echo "CREATE DATABASE IF NOT EXISTS `$db`;\n";
echo "USE `$db`;\n";
$results_tables_list=mysql_query($src="SHOW TABLES", $link) or die($src . mysql_error());
while ($row_table=mysql_fetch_array($results_tables_list ,MYSQL_ASSOC)) {
$table=$row_table[0];
//process tables
$results_table_contents=mysql_query($src="SHOW CREATE TABLE `$table`", $link) or die($src . mysql_error());
while ($row_table_contents=mysql_fetch_row($results_tabl e_contents)) {
echo $row_table_contents[1];
}
mysql_free_result($results_table_contents);
//generate table locking statements here
echo "LOCK TABLES `$table` READ;\n";
//generate insert statements


$s="INSERT INTO `$table`(";
$result_column_names = mysql_query($src="SHOW COLUMNS FROM `$table`", $link) or die($src.mysql_error());
while ($row_column_name=mysql_fetch_assoc($result_column _names)) {
$s.=$row_column_names['Field'].",";
}
mysql_free_result($result_column_names);
//chop off last comma to make this a comma separated list
$s = substr($s,0,strlen($s)-strlen(","));
$s.=") VALUES (";
//this doesn't change.

//do rows
$results_rows=mysql_query($src="SELECT * FROM `$table`", $link) or die($src.mysql_error());
while ($row_rows=mysql_fetch_array($results_rows,MYSQL_A SSOC)) {
echo $s;
//do columns. at this point, we are at the data.
$results_columns=mysql_query($src="SELECT FULL COLUMNS FROM `$table` FROM `$db`", $link) or die($src.mysql_error());
$index=0;
$numcolumns=mysql_num_rows($results_columns);
while ($row_columns=mysql_fetch_assoc($results_columns)) {
if ('binary' == $row_columns['Collation']) {
//hex dump it
echo mysql_hex($row_rows[$index]);
//echo "\"X";
////...and break up the lines into blocks of 80 chars
////so people can edit the file and it doesn't crash browsers.
//$s=hex($row_columns[$fields[$i]]);
//for ($i2=0; $i2<strlen($s); $i2+=80) {

// echo substr($s,$i2,80);
//}
//echo "\"";
} else { //not binary
echo "\"" . mysql_real_escape_string($row_rows[$index]) . "\"";
}
if ($index!=$numcolumns-1) { //not on last item
echo ",\n";
}
$index++;
}
mysql_free_result($results_columns);
echo ");\n";
}
mysql_free_result($results_rows);
//generate table unlocking statements here
echo "UNLOCK TABLES `$table`;\n";
}
mysql_free_results($results_tables_list);
echo "</code></pre>\n";
}

jmichae3
01-19-2011, 08:02 PM
if you have XAMPP, run the mysql.exe and experiment with the following commands:
SHOW DATABASES
SHOW FULL COLUMNS FROM `tablename` [FROM `dbname`]
SHOW TABLES
SHOW TABLE `tablename`
SHOW CREATE TABLE `tablename`

or try them out in phpmyadmin.

edbr
01-20-2011, 12:45 AM
thats impressive .
i still would check if the server allows SSH though, you could then use tunnelling withing EMS or sqlyog to synchronise your local and remote databases, i think thars what Davidj was refering to , i can ion my servers but some hosting blocks this

jmichae3
01-20-2011, 08:15 AM
one more time. this time I debugged the code a lot more I don't know about using double quotes in mysql though, I think I remember it works and is preferred for certain cituations.
sorry about doing this again.

function dbdump($db, $link) {
$table='';
echo "<pre><code>/*Db Dump*/\n";
echo "CREATE DATABASE IF NOT EXISTS `".$db."`;\n";
echo "USE `$db`;\n";
mysql_select_db($db, $link) or die(mysql_error());
$results_tables_list=mysql_query($src="SHOW TABLES", $link) or die("ERR:".$src . mysql_error());
while ($row_table=mysql_fetch_row($results_tables_list)) {
$table=$row_table[0];
//process tables
$results_table_contents=mysql_query($src="SHOW CREATE TABLE `".$table."`", $link) or die("ERR:".$src . mysql_error());
while ($row_table_contents=mysql_fetch_row($results_tabl e_contents)) {
echo $row_table_contents[1].";\n";
}
mysql_free_result($results_table_contents);
//generate table locking statements here
echo "LOCK TABLES `".$table."` READ;\n";
//generate insert statements


$s="INSERT INTO `".$table."`(";
$result_column_names = mysql_query($src="SHOW COLUMNS FROM `".$table."`", $link) or die("ERR:".$src.mysql_error());
while ($row_column_name=mysql_fetch_row($result_column_n ames)) {
$s.=$row_column_name[0].",";
}
mysql_free_result($result_column_names);
//chop off last comma to make this a comma separated list
$s = substr($s,0,strlen($s)-strlen(","));
$s.=") VALUES (";
//this doesn't change.

//do rows
$results_rows=mysql_query($src="SELECT * FROM `".$table."`", $link) or die("ERR:".$src.mysql_error());
while ($row_rows=mysql_fetch_row($results_rows)) {
echo $s;
//do columns. at this point, we are at the data.
$results_columns=mysql_query($src="SHOW FULL COLUMNS FROM `".$table."` FROM `".$db."`", $link) or die("ERR:".$src.mysql_error());
$index=0;
$numcolumns=mysql_num_rows($results_columns);
while ($row_columns=mysql_fetch_row($results_columns)) {
if ('binary' == $row_columns[2]) {
//hex dump it
echo mysql_hex($row_rows[$index]);
//echo "\"X";
////...and break up the lines into blocks of 80 chars
////so people can edit the file and it doesn't crash browsers.
//$s=hex($row_columns[$fields[$i]]);
//for ($i2=0; $i2<strlen($s); $i2+=80) {

// echo substr($s,$i2,80);
//}
//echo "\"";
} else { //not binary
echo "\"" . mysql_real_escape_string($row_rows[$index]) . "\"";
}
if ($index!=$numcolumns-1) { //not on last item
echo ",";
}
$index++;
}
mysql_free_result($results_columns);
echo ");\n";
}
mysql_free_result($results_rows);
//generate table unlocking statements here
echo "UNLOCK TABLES `$table`;\n";
}
mysql_free_result($results_tables_list);
echo "</code></pre>\n";
}

davidj
02-01-2011, 12:18 PM
its easier from the command line if you have SSH access to your linux box

dump database (Prompts for password)
mysqldump -u username -p databaseName>file.sql import backup (Prompts for password)
mysql -u username -p databaseName<file.sql

davidj
02-01-2011, 12:30 PM
@ jmichae3 (http://www.dreamweaverclub.com/forum/member.php?u=30537)

Echoing from within a function is bad practice.

If your going to use a script to handle this then I would use an object methodology. It needs to more abstract I think but with flexibility to extend it

jmichae3
02-02-2011, 05:00 AM
I wasn't advocating echoing the dump. I specifically said, "rewrite and use fopen(), fprintf(), and fclose(). I did not rewrite the function for him. I had hoped he was able to do rewrite the output for himself (I could rewrite it for him if he needs me to, though).

the mysql executable command to do the dump is also a good idea, but you should probably specify the database, otherwise one of 2 things will happen:
- you are probably going to get all databases (is that going to be with icky errors in the database log?)
- you will get no databases whatsoever

you must specify the entire absolute path to the mysql binary if you want to execute it (your tech support at the hosting would know what the address is if you are making this a cron job). you can make it a cron job in the control panel IF your hosting supports cron jobs (many hosting companies plans don't).

if you are good with BASH scripting and combining it with PHP, you can make something that automatically ftp's the file to a remote place - but be sure that (do testing with filezilla) that your upload, even with timeouts, does not take longer than your time difference between cron job periods.

also, with cron jobs, you need to be aware that there might be hosting rules which state that you cannot have cron jobs overcrossing one another, or closer than a certain frequency - or your account can be in trouble.

jmichae3
02-02-2011, 05:26 AM
function dbdumptofile($db, $link, $filepath) {
//returns FALSE on error, TRUE on success.
//chown()/chgrp(), whatever you have to do to a server to make it allow you to write a file
$fhandle=fopen($filepath, 'w');
if (FALSE===$fhandle) {
return FALSE;
}
$table='';
fprintf($fhandle, "/*Db Dump*/\n");
fprintf($fhandle, "CREATE DATABASE IF NOT EXISTS `%s`;\n", $db);
fprintf($fhandle, "USE `%s`;\n", $db);
mysql_select_db($db, $link) or return FALSE; //die(mysql_error());
$results_tables_list=mysql_query($src="SHOW TABLES", $link) or return FALSE; //die("ERR:".$src . mysql_error());
while ($row_table=mysql_fetch_row($results_tables_list)) {
$table=$row_table[0];
//process tables
$results_table_contents=mysql_query($src="SHOW CREATE TABLE `".$table."`", $link) or return FALSE; //die("ERR:".$src . mysql_error());
while ($row_table_contents=mysql_fetch_row($results_tabl e_contents)) {
echo $row_table_contents[1].";\n";
}
mysql_free_result($results_table_contents);
//generate table locking statements here
fprintf($fhandle, "LOCK TABLES `%s` READ;\n", $table);
//generate insert statements


$s="INSERT INTO `".$table."`(";
$result_column_names = mysql_query($src="SHOW COLUMNS FROM `".$table."`", $link) or return FALSE; //die("ERR:".$src.mysql_error());
while ($row_column_name=mysql_fetch_row($result_column_n ames)) {
$s.=$row_column_name[0].",";
}
mysql_free_result($result_column_names);
//chop off last comma to make this a comma separated list
$s = substr($s,0,strlen($s)-strlen(","));
$s.=") VALUES (";
//this doesn't change.

//do rows
$results_rows=mysql_query($src="SELECT * FROM `".$table."`", $link) or return FALSE; //die("ERR:".$src.mysql_error());
while ($row_rows=mysql_fetch_row($results_rows)) {
fprintf($fhandle, "%s", $s);
//do columns. at this point, we are at the data.
$results_columns=mysql_query($src="SHOW FULL COLUMNS FROM `".$table."` FROM `".$db."`", $link) or return FALSE; //die("ERR:".$src.mysql_error());
$index=0;
$numcolumns=mysql_num_rows($results_columns);
while ($row_columns=mysql_fetch_row($results_columns)) {
if ('binary' == $row_columns[2]) {
//hex dump it
echo mysql_hex($row_rows[$index]);
//echo "\"X";
////...and break up the lines into blocks of 80 chars
////so people can edit the file and it doesn't crash browsers.
//$s=hex($row_columns[$fields[$i]]);
//for ($i2=0; $i2<strlen($s); $i2+=80) {

// echo substr($s,$i2,80);
//}
//echo "\"";
} else { //not binary
fprintf($fhandle, "\"%s\"", mysql_real_escape_string($row_rows[$index]));
}
if ($index!=$numcolumns-1) { //not on last item
fprintf($fhandle, ",");
}
$index++;
}
mysql_free_result($results_columns);
fprintf($fhandle, ");\n");
}
mysql_free_result($results_rows);
//generate table unlocking statements here
fprintf($fhandle, "UNLOCK TABLES `%s`;\n", $table);
}
mysql_free_result($results_tables_list);
fprintf($fhandle, "\n");
fclose($fhandle);
//right here, UNchmod()/chgrp() the server or whatever you have to do to set things normal again.
//it's 644 octal for files I think and 755 octal for directories.
//if you need that in decimal it's 420 and 493, respectively.
return TRUE;
}


I like the one-liner better, but if you are not allowed to do cron jobs, then this is the other alternative. I have FTP code as well. do you need that?

jmichae3
02-02-2011, 06:09 AM
I have never seen what it takes to do the proper PHP coding to take a regular hosting and make it allow a file to be written to in the httpdocs area, like for images, etc.

ahhh. but you can write to the /tmp/ directory I think. invent a particluarly unique filename, and maybe use it over and over (don't make lots of them).
you can also use the logs directory in your ./home/ directory structure, or the place where backups are stored, but the root directory of home is probably safer....

jmichae3
02-03-2011, 09:26 AM
can you tell me why it's a bad practice to echo within a function? is this purely some sort of style thing, or is there some security reason? don't just say "that's bad". say why so someone can (re-)learn. this was essentially something from software engineering courses in college which stated wasn't it (it's starting to come back now - 20+ years - or was it my memory's fuzzy)?

I don't remember it being a problem in engineering classes to do File I/O in a function or method. that sort of thing was done a lot.

enjoy the code. I just hope somebody besides me uses it for something. it worketh.

davidj
02-04-2011, 12:26 PM
when dealing in scripting languages its always best practice to keep your logic separate from your output. The structure of a function which returns a result should have a 'return' included in order to pass back the result.

[example]


/// function
function myFunc(){

$result = 100;

return $result;
}

/// call and assign
$output = myFunc();

/// echo var inside HTML
echo $output;



By adopting Object Orientated methodologies then this wouldn't be an issue as your logic would be held in a Class and you would not use a Class to output in this way