My backup file size is too big
so when I using MyphpAdmin to backup my file, it will appear
>>

exceding 300 seconds limit

>>

does any one can tell me how to solve this problem
or
any other way to backup sql file without using MyphpAdmin
and how?

Use this function

<?
function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)
{

//this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database

//Parameters:
// $host: usually "localhost" but depends on where the MySQL database engine is mounted
// $dbname : The MySQL database name
// $uid : the database's username (not your account's), leave blank if none is required
// $pwd : the database's password
// $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank.
// $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output.

// **************
// IMPORTANT: If you use this function, for personal or commercial use, AND you feel an overwhelming sense of gratitude that someone actually took the time and wrote it,
// immediately go to your paypal account and send me $10 with a small comment of how and how much it helped! Set the payment recipient to woodystanford@yahoo.com .
// **************

if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;

//connect to MySQL database
$con=mysql_connect("localhost",$uid, $pwd);
$db=mysql_select_db($dbname,$con);

//open back-up file ( or no file for browser output)

//set up database
out($fptr, "create database $dbname;\n\n");

//enumerate tables
$res=mysql_list_tables($dbname);
$nt=mysql_num_rows($res);

for ($a=0;$a<$nt;$a++)
{
$row=mysql_fetch_row($res);
$tablename=$row[0];

//start building the table creation query
$sql="create table $tablename\n(\n";

$res2=mysql_query("select * from $tablename",$con);
$nf=mysql_num_fields($res2);
$nr=mysql_num_rows($res2);

$fl="";

//parse the field info first
for ($b=0;$b<$nf;$b++)
{
$fn=mysql_field_name($res2,$b);
$ft=mysql_fieldtype($res2,$b);
$fs=mysql_field_len($res2,$b);
$ff=mysql_field_flags($res2,$b);

$sql.=" $fn ";

$is_numeric=false;
switch(strtolower($ft))
{
case "int":
$sql.="int";
$is_numeric=true;
break;

case "blob":
$sql.="text";
$is_numeric=false;
break;

case "real":
$sql.="real";
$is_numeric=true;
break;

case "string":
$sql.="char($fs)";
$is_numeric=false;
break;

case "unknown":
switch(intval($fs))
{
case 4: //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type
$sql.="tinyint";
$is_numeric=true;
break;

default: //we could get a little more optimzation here! (i.e. check for medium ints, etc.)
$sql.="int";
$is_numeric=true;
break;
}
break;

case "timestamp":
$sql.="timestamp";
$is_numeric=true;
break;

case "date":
$sql.="date";
$is_numeric=false;
break;

case "datetime":
$sql.="datetime";
$is_numeric=false;
break;

case "time":
$sql.="time";
$is_numeric=false;
break;

default: //future support for field types that are not recognized (hopefully this will work without need for future modification)
$sql.=$ft;
$is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown
break;
}

//VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator

if (strpos($ff,"unsigned")!=false)
{
//timestamps are a little screwy so we test for them
if ($ft!="timestamp") $sql.=" unsigned";
}

if (strpos($ff,"zerofill")!=false)
{
//timestamps are a little screwy so we test for them
if ($ft!="timestamp") $sql.=" zerofill";
}

if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";
if (strpos($ff,"not_null")!=false) $sql.=" not null";
if (strpos($ff,"primary_key")!=false) $sql.=" primary key";

//End of field flags

if ($b<$nf-1)
{
$sql.=",\n";
$fl.=$fn.", ";
}
else
{
$sql.="\n);\n\n";
$fl.=$fn;
}

//we need some of the info generated in this loop later in the algorythm...save what we need to arrays
$fna[$b]=$fn;
$ina[$b]=$is_numeric;

}

out($fptr,$sql);

if ($structure_only!=true)
{
//parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...
for ($c=0;$c<$nr;$c++)
{
$sql="insert into $tablename ($fl) values (";

$row=mysql_fetch_row($res2);

for ($d=0;$d<$nf;$d++)
{
$data=strval($row[$d]);

if ($ina[$d]==true)
$sql.= intval($data);
else
$sql.="\"".mysql_escape_string($data)."\"";

if ($d<($nf-1)) $sql.=", ";

}

$sql.=");\n";

out($fptr,$sql);

}

out($fptr,"\n\n");

}

mysql_free_result($res2);

}

if ($fptr!=false) fclose($fptr);
return 0;

}

function out($fptr,$s)
{
if ($fptr==false) echo("$s"); else fputs($fptr,$s);
}
?>

Copy the above function into a file called "mysqlbackup.h" and invoke it with the following script:

<html>
<?php
include("mysqlbackup.h");
mysqlbackup("localhost","yerdatabase","yerusername","yerpassword","/home/sites/site90/web/backup/sqldata.txt", true);
?>

The database's structure has been saved to "/home/sites/site90/web/backup/sqldata.txt" FTP download it at your convenience.

not very understand ?

do you want me to download this file(sqldata.txt)
and make change or ......................................

where should I put this function?????

mysqldump --opt databasenamehere > filenamehere.sql

Then to read it into another database on another server, or
same server, mysql databasenamehere < filenamehere.sql

also read "man mysqldump" and "man mysql"

mysqldump --opt databasenamehere > filenamehere.sql

Then to read it into another database on another server, or
same server, mysql databasenamehere < filenamehere.sql

also read "man mysqldump" and "man mysql"




totally not nuderstand what are you talking about???????

Originally posted by randytsa
totally not nuderstand what are you talking about???????

Logon to your server via SSH if you can.. Then type:

man mysqldump

This will give you instructions on how to backup your database to a file.
Then you can do whatever you want with the file.
It will be on your server though, so make sure you have enough space for it.
You can then use your favorite FTP program and download the file to another location.

Example, your database name is accounting

i would logon to my server via SSH and type:


mysqldump --opt accounting > todaysbackup.sql

In the same directory that I'm in when I typed that command, I will have a file called todaysbackup.sql .
I can then do whatever I want with that file.

I understand now

thank you very much!!!!!

But when I execute the command
it appear this message
>>
mysqldump: Got error: 1045: Access denied for user: 'yhtsai@localhost' (Using password: NO) when trying to connect
>>

I typed the password already, but it said the password : NO

did I do anything wrong?

mysqldump -aceq --opt --result-file=filename.sql --user=root --password=password databasename

Maybe you don't have full access to execute that command.

Do you have cPanel? if so use the backup in there, it shouldn't time out.