· 10 years ago · Sep 21, 2016, 05:08 PM
1<?php
2
3// Report all errors
4error_reporting(E_ALL);
5
6/**
7 * Define database parameters here
8 */
9define("DB_USER", 'root');
10define("DB_PASSWORD", 'root');
11define("DB_NAME", 'perfect');
12define("DB_HOST", 'localhost');
13//define("OUTPUT_DIR", '/path/to/folder');
14define("TABLES", '*');
15
16/**
17 * Instantiate Backup_Database and perform backup
18 */
19$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
20$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
21echo "<br /><br /><br />Backup result: ".$status;
22
23/**
24 * The Backup_Database class
25 */
26class Backup_Database {
27 /**
28 * Host where database is located
29 */
30 var $host = '';
31
32 /**
33 * Username used to connect to database
34 */
35 var $username = '';
36
37 /**
38 * Password used to connect to database
39 */
40 var $passwd = '';
41
42 /**
43 * Database to backup
44 */
45 var $dbName = '';
46
47 /**
48 * Database charset
49 */
50 var $charset = '';
51
52 /**
53 * Connection variable
54 */
55
56 var $db = '';
57
58 /**
59 * Constructor initializes database
60 */
61 function Backup_Database($host, $username, $passwd, $dbName, $charset = 'utf8')
62 {
63 $this->host = $host;
64 $this->username = $username;
65 $this->passwd = $passwd;
66 $this->dbName = $dbName;
67 $this->charset = $charset;
68
69 $this->initializeDatabase();
70 }
71
72 //protected function initializeDatabase()
73 function initializeDatabase()
74 {
75 $this->db = new PDO("mysql:host=$this->host;dbname=$this->dbName;charset=$this->charset", $this->username, $this->passwd );
76 $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
77 }
78
79 /**
80 * Backup the whole database or just some tables
81 * Use '*' for whole database or 'table1 table2 table3...'
82 * @param string $tables
83 */
84 public function backupTables($tables = '*', $outputDir = '.')
85 {
86 try
87 {
88 /**
89 * Tables to export
90 */
91 if($tables == '*')
92 {
93 $tables = array();
94 foreach( $this->db->query('SHOW TABLES') as $row )
95 {
96 $tables[] = $row[0];
97 }
98 }
99 else
100 {
101 $tables = is_array($tables) ? $tables : explode(',',$tables);
102 }
103
104 $sql = 'CREATE DATABASE IF NOT EXISTS '.$this->dbName.";\n\n";
105 $sql .= 'USE '.$this->dbName.";\n\n";
106
107 /**
108 * Iterate tables
109 */
110 foreach($tables as $table)
111 {
112 // echo "Backing up ".$table." table...";
113
114 $result = $this->db->query( 'SELECT * FROM ' . $table );
115 $numFields = $result->columnCount();
116
117 $sql .= 'DROP TABLE IF EXISTS '.$table.';';
118 $result2 = $this->db->query( 'SHOW CREATE TABLE ' . $table );
119 $row2 = $result2->fetch();
120
121 $sql.= "\n\n".$row2[1].";\n\n";
122
123 for ($i = 0; $i < $numFields; $i++)
124 {
125 // while($row = mysql_fetch_row($result))
126 while( $row = $result->fetch() )
127 {
128 $sql .= 'INSERT INTO '.$table.' VALUES(';
129 for($j=0; $j<$numFields; $j++)
130 {
131 $row[$j] = addslashes($row[$j]);
132 $row[$j] = preg_replace( '/\n/' , "\\n" , $row[$j] );
133 if (isset($row[$j]))
134 {
135 $sql .= '"'.$row[$j].'"' ;
136 }
137 else
138 {
139 $sql.= '""';
140 }
141
142 if ($j < ($numFields-1))
143 {
144 $sql .= ',';
145 }
146 }
147
148 $sql.= ");\n";
149 }
150 }
151
152 $sql.="\n\n\n";
153
154 //echo " OK" . "<br />"
155 ;
156 }
157 }
158 catch (Exception $e)
159 {
160 var_dump($e->getMessage());
161 return false;
162 }
163
164 return $this->saveFile($sql, $outputDir);
165 }
166
167 /**
168 * Save SQL to file
169 * @param string $sql
170 */
171 protected function saveFile(&$sql, $outputDir = '.')
172 {
173 if (!$sql) return false;
174
175 try
176 {
177 $handle = fopen('/backup_'.$this->dbName.'_'.date("d").'_'.date("m").'_'.date("Y").'.sql','w+');
178 fwrite($handle, $sql);
179 fclose($handle);
180 }
181 catch (Exception $e)
182 {
183 var_dump($e->getMessage());
184 return false;
185 }
186
187 return true;
188 }
189}