Using PHP to backup and load Mysql DB

[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 :P

[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.