Friday, October 26, 2012

Backup MySQL : Execute database query using PHP file.


Below is an example of using SELECT INTO OUTFILE query for creating table backup 

<?php
$dbhost = '';
$dbuser = ' ';
$dbpass = ' ';
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'db_name';
mysql_select_db($dbname);
$tableName  = 'table_name';
$backupFile = '/backup/file_name.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query) or die();
mysql_close($conn);
}
?>

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

<?
 $tableName  = 'table_name';
$backupFile = 'file.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);
?>

Facing Problem :

when you try to create output file on server may be faced 
Error : Can't create/write to file '/var/www/test/backup/mypets.sql' (Errcode: 13)

Solution :

Recent Ubuntu Server Editions (such as 10.04) ship with AppArmor and MySQL's profile might be in enforcing mode by default. You can check this by executing sudo aa-status like so:

# sudo aa-status
5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.
If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messages when AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqld and add /backup/ or /data/* near the bottom like so:

...
/usr/sbin/mysqld {
    ...
    /var/log/mysql/ r,
    /var/log/mysql/* rw,
    /var/run/mysqld/mysqld.pid w,
    /var/run/mysqld/mysqld.sock w,
    /data/ r,
    /data/* rw,
   /backup/* rw,
}

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload
WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.



No comments:

Post a Comment