magento定时备份表数据

 

目的:

当mysql表的数据越来越大时,就会拖慢服务器,应该有必要清理下数据。

主要功能是:

以表autopromo_behavior_record为例.

1,先备份autopromo_behavior_record表到本地
2,再删除autopromo_behavior_record表中6个月前的数据
3,删除本地备份中超过180天的sql文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
class Test_Cron_Model_Cron{
private $_dueDate = null;
private $_table = null;
private $dbhost = ""; // MySQL Host
private $dbuser = ""; // MySQL Username
private $dbpwd = ""; // MySQL password
private $dbname = '';
private $_conn = null;
private $dumpDir = '';
private $backupsToKeep = 180;
private $mysqldump_binary = '/usr/bin/mysqldump';
private $savePermissions = 0664; // Save files with the following permissions
public function run(){
$clearFlag = Mage::getStoreConfig('autopromo/autopromo_basic/clear_database');
if(!$clearFlag){return;}
$timeStarted = time();
$conConfig = simplexml_load_file(Mage::getBaseDir().'/app/etc/local.xml')->global->resources;
$tablePrefix = $conConfig->db->table_prefix;
$connection = $conConfig->default_setup->connection;
// $this->_conn = mysql_connect($connection->host, $connection->username, $connection->password);
$this->dbhost = $connection->host;
$this->dbuser = $connection->username;
$this->dbpwd = $connection->password;
$this->dbname = $connection->dbname;
$this->dumpDir = Mage::getBaseDir().DS.'var/autopromo/';
$this->_dueDate = strtotime('-6 month');
if(!is_dir($this->dumpDir)){
@mkdir($this->dumpDir,0755,true);
}
//1,backup
$this->_table = 'autopromo_behavior_record';
$this->backup();
//2,clear
$this->clear();
echo('Database backup succeeded. Time elapsed: '.(time() - $timeStarted).' sec.');
echo("\n");
}
public function backup(){
$dump_options = array(
'-C', // Compress connection
'-h'.$this->dbhost, // Host
'-u'.$this->dbuser, // User
'-p'.$this->dbpwd, // Password
'--compact' // no need to database info for every table
);
$temp = tempnam(sys_get_temp_dir(), 'sqlbackup-');
$exec = passthru($this->mysqldump_binary.' '.implode($dump_options, ' ').' '.$this->dbname.' '.$this->_table.' | gzip -9 > '.$temp);
if($exec != '') {
@unlink($temp);
$this->errorMessage('Unable to dump file to '.$temp. ' ' .$exec);
}
else {
/* Make sure only complete files get saved */
chmod($temp, $this->savePermissions);
rename($temp, $this->dumpDir.'/'.$this->_table.'-'.date('Ymd').'.sql.gz');
}
}
public function clear(){
$resource = Mage::getSingleton('core/resource');
$writeConnection = $resource->getConnection('core_write');
$query = "DELETE FROM {$this->_table} WHERE unix_timestamp(created_time) <= " . $this->_dueDate;
// echo $query;die;
try {
$writeConnection->query($query);
} catch (Exception $e) {
echo $e->getMessage();
}

//Deleting old backups
exec('find '.$this->dumpDir.' -mtime '.$this->backupsToKeep.' -name "*.sql.gz" -exec rm {} \\;', $output, $result);
if($result) {
echo("Error removing old database backups!");
}
}
}

 

坚持原创技术分享,您的支持将鼓励我继续创作!