Category: PHP

Creating MySQL DB Backups in PHP

There are at least three ways to backup your MySQL Database :


1.) Execute a database backup query from PHP file.
2.) Run mysqldump using system() function.
3.) Use phpMyAdmin to do the backup.
 
 
Execute a database backup query from PHP file
Below is an example of using SELECT INTO OUTFILE query for creating table backup :



<?php
include ‘config.php’;
include ‘opendb.php’;


$tableName  = ‘mypet’;
$backupFile = ‘backup/mypet.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  = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query      = “LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName”;
$result = mysql_query($query);



include ‘closedb.php’;
?>



It’s a good idea to name the backup file as tablename.sql so you’ll know from which table the backup file is



Run mysqldump using system() function
The system() function is used to execute an external program. Because MySQL already have built in tool for creating MySQL database backup (mysqldump) let’s use it from our PHP script



<?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’;
?>



 
Use phpMyAdmin to do the backup
This option as you may guessed doesn’t involve any programming on your part. However I think i mention it anyway so you know more options to backup your database.


To backup your MySQL database using phpMyAdmin click on the “export” link on phpMyAdmin main page. Choose the database you wish to backup, check the appropriate SQL options and enter the name for the backup file.

mySQL error : Client does not support authentication protocol

You might encounter this error if you are using MySQL 4.1 or greater. The cause of this problem is that the newest version of MySQL has a longer password than the older implementations. One way to fix this problem is reseting the password to the old format inorder so that your client to still use the server.


the first thing that you need to do is run mysql and login as root:
mysql -u root -p


then run the following script to reset the password of a specific user to the old format:
UPDATE mysql.user
SET password=OLD_PASSWORD(‘yourpassword’)
WHERE user=’usertobeupdated’
AND host=’yourhost’;

you need to flush the tables after running this script:
flush privileges;

now, issue a ‘quit’ command to exit the mysqlclient