how to backup mysql database

[mhupadhyay]

I have a database created in mysql iwant to create a backup utility for backing up the data on a routine base

[smantscheff]

Use the command line tool mysqldump to dump the whole database to a file which you can then store at a safe place.

[simplypixie]

This is the code I use and run a cron job:

backup_tables('user','password','database');


/* backup the db OR just a table */
function backup_tables($user,$pass,$db)
{

    $suffix = date("mdY-Hi");
  #Execute the command to create backup sql file
  exec("mysqldump --user={$user} --password={$pass} --quick --add-drop-table --add-locks --extended-insert --lock-tables --all {$db} > ../db_backups/backup.sql");

  #Now zip that file
  $zip = new ZipArchive();
  $filename = "../db_backups/backup-$suffix.zip";
  if ($zip->open($filename, ZIPARCHIVE::CREATE) !== TRUE) {
   exit("cannot open <$filename>\n");
  }
  $zip->addFile("../db_backups/backup.sql" , "backup.sql");
  $zip->close();
  #Now delete the .sql file without any warning
  @unlink("../db_backups/backup.sql");
  #Return the path to the zip backup file
  return "../db_backups/backup-$suffix.zip";
}