Using PHP to backup and load Mysql DB

[Gauze]

Is there a way to create a backup of my two mysql's db using PHP script or even over FTP?

And then import the data into another mysql's?

[gomarc]

If this is sort of a one time thing, the easiest way is to go to your cPanel > Backup wizard.

If you what to schedule this to be done on a regular basis, then you may want to look into cron jobs.

[Gauze]

That's the issue. My CPanel has been down for a long time now and I want to back it up just in case. I do have FTP access still though.

[gomarc]

This tutorial may help you: Using PHP to Backup MySQL Databases

[Gauze]

Thank you very much. It does look very promising.
Edit:
Bah, method one doesn't seem to show any results and method two doesn't work since system is disabled for security reasons.

[misson]

Only admin accounts would have permission allowing INTO OUTFILE, also for security reasons. You can use a normal SELECT * FROM <table> and have your script format and save the results itself. More work for you, but not much more.

If you want a full, completely automated backup, start with a SHOW TABLES to get all the tables, then issue a SHOW CREATE TABLE <table> and SELECT * FROM <table> for each table and save the results.

[garrettroyce]

Maybe this will work:

see newer version below

A couple of things:
This does not create the table structures, only copies data into existing tables.
This does not check if the data is already in the table. If you don't empty the table first, you will have duplicates.

[misson]

SELECT TABLE_NAME FROM TABLES will include tables that you don't want to back up and doesn't tell you which database each table is in. You could use SELECT TABLE_SCHEMA, TABLE_NAME FROM TABLES to get the database and table or select a database and use SHOW TABLES.

A multi-value INSERT is more efficient. Here's a scrap based on garrettroyce's code that generates a single INSERT for each table.

...
$today = strftime('%Y%m%d');
$backupName = "backup-$today.sql";
if (! ($backupFile = fopen($backupName, 'w'))) {
    exit(1);
}
chmod($backupName, 0600);
mysql_select_db('change me!');  // ******Change this to your database name
    ...
    fwrite($backupFile, "INSERT INTO $row[0] VALUES ";
    if ($tableRow = mysql_fetch_row()) {
        // write the first row; no leading comma
        $values = implode("', '", array_map('mysql_escape_string', $tableRow));
        fwrite($backupFile, "('$values')" );
    }
    while($tableRow = mysql_fetch_row()) { // loop through all table rows
        // write subsequent rows, which have leading commas
        $values = implode("', '", array_map('mysql_escape_string', $tableRow));
        fwrite($backupFile, ", ('$values')" );
    }
    fwrite(";\n");
}
fclose($backupFile); 

The above is untested and needs to be cleaned up.

[garrettroyce]

Yeah, I went on to do some testing/refining of the code and realized I forgot to add the WHERE to the first query

<?php
function exportMysql($fileName, $databaseName) {
	mysql_select_db('INFORMATION_SCHEMA');
	$file = ''; // this will hold the information
	$query = "SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = '{$databaseName}' AND TABLE_TYPE = 'BASE TABLE'"; // fetch all table names
	$tableNames = mysql_query($query); // query DB
	if (!$tableNames) { // if there's an error
		 exit(mysql_error());
	}
	mysql_select_db($databaseName); // select database to back up
	while($row = mysql_fetch_row($tableNames)) { // loop through all tables
		 $query = "SELECT * FROM {$row[0]}"; // select everything in the table
		 $tableData = mysql_query($query); //query DB
		 if (!$tableData) { // if there's an error
			  exit(mysql_error());
		 }

		 while($tableRow = mysql_fetch_row($tableData)) { // loop through all table rows
			  $data = implode("', '", $tableRow);
			  $data = "'{$data}'";
			  $file .= "INSERT INTO {$row[0]} VALUES({$data})\n";
		 }
	}
	file_put_contents($fileName, $file); // save file
}
function importMysql($fileName, $databaseName) {
     if (!file_exists($fileName)) {
          exit("File {$fileName} does not exist");
     }
     $file = file_get_contents($fileName);
     $queries = explode("\n", $file);
     mysql_select_db($databaseName);

     foreach($queries as $query) {
          mysql_query($query);
     }
}
?>

That's a good idea:

$values = implode("', '", array_map('mysql_escape_string', $tableRow));

And also, there is no error checking on the import function, so if a query fails, you won't know. Mission and I could probably go on and on all day perfecting this, but you have a pretty good start

[Gauze]

Hey, I'm farther ahead than I would be otherwise.

Thanks a lot.