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 :biggrin:

 

[Gauze]

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

Thanks a lot.

 

[garrettroyce]

No problem

If you have anything you'd specifically like to add/change let me know and I (or anyone else here for that matter) can help you out.

 

[Gauze]

Alright, I added some echos to the function to see what is happening.

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
 
 
 
 
 echo $query."<br>"; //I added this
 
 
 
 
 
 $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";
   }
 }
 
 
 
 
 echo $file;// I added this as well.
 
 
 
 
 file_put_contents($fileName, $file); // save file
}

I used the function this way:

include 'accessdb.php';//my php file that I use to log into my database using an account with full acccess
exportMysql(backup_db.sql,robrow_db);

The result was

SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'robrow_db' AND TABLE_TYPE = 'BASE TABLE'
INSERT INTO arms VALUES('10', 'Pinkett', '2600', '13890', '480', '80', '110', '30', '10', 'Standard', '0', '0', '0') INSERT INTO arms VALUES('11', 'Mekhi', '2486', '13298', '450', '77', '102', '39', '14', 'Light', '40000', '0', '0') INSERT INTO arms VALUES('12', 'Frontliner', '3430', '15296', '481', '102', '155', '26', '7', 'Standard', '40000', '0', '0') INSERT INTO arms VALUES('13', 'Rushnik', '2702', '13295', '466', '83', '110', '28', '19', 'Light', '45000', '0', '0')
...etc etc...goes on for awhile...

I guess if I ever lose the database...I'd have to reinitialize the tables?

Additionally, do I need some break between each INSERT INTO line?

I think I'll just copy the output (well, not the first line) into a .sql file or something.

Because it doesn't seem to be saving the file anywhere.

 

[garrettroyce]

You'll need to back up the structure of the tables separately. I'll try to work on this additional functionality in the morning. Also, when you restore the table, it won't check for duplicates, so you should DROP the table first, then create a new one from the definition. The importMysql() function will do the importing for you so you don't need a special break, though traditionally, a .sql file will use semicolons. It should save the file to wherever you specify in relation to where the script is. I verified that it worked on my computer, make sure your directory is writable.

 

[Gauze]

Alright, it works very conveniently.

 

[gomarc]

Gauze,

Hopefully you got your database backup. But just in case you are still facing some problems, I would like to recommend using phpMyBackupPro, a very easy to use, free, web-based MySQL backup application, licensed under the GNU GPL.

I just finish testing it on my free account and it works really great. (Email and scheduled backups not tested)

This application does all the hard work, and I believe it’s exactly what you were looking for.

Link to application: phpMyBackupPro

 

[garrettroyce]

That's pretty neat. I'd use that instead

 

[xav0989]

I'll try to work on this additional functionality in the morning. Also, when you restore the table, it won't check for duplicates, so you should DROP the table first, then create a new one from the definition.

You can use 'IF NOT EXISTS' to check if the table exists and abort the import if it does.