Backup and restore MySQL database using command line or PHP

Log in MySQL server

Backup MySQL database: databasename:

mysqldump -usqlusername -p --opt databasename >/home/username/databasename.backup.sql

 (replace sqlusername with real sql user name, keep -u,

  replace databasename with real database name )

restore MySQL database  databasename:

mysql -usqlusername -p databasename </home/username/databasename.backup.sql

 (replace sqlusername with real sql user name, keep -u, 

 replace databasename with real database name )

Furthur reading:

1. http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html 

2. MySQL Replication 

3. MySQL cluster 

PHP:
method 1: using system command:
<?php
include 'config.php';
include 'opendb.php';
$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";
system($command);
include 'closedb.php';
?>
method 2: using  table backup
Below is an example of using SELECT INTO OUTFILE query for creating table backup :

<?php
include 'config.php';
include 'opendb.php';
$tableName  = 'mytest';
$backupFile = 'backup/mytest.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>

To restore the backup you just need to run LOAD DATA INFILE query like this :

<?php
include 'config.php';
include 'opendb.php';
$tableName  = 'mytest';
$backupFile = 'mytest.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);

include 'closedb.php';
?>

 reference:
http://www.php-mysql-tutorial.com/wikis/mysql-tutorials/using-php-to-backup-mysql-databases.aspx