php备份整个MySQL数据库，或者指定表

php类实现完整备份数据库，或者备份数据库中指定表的功能：

1. class Backup
2. {
3. /**
4. * @var stores the options
5. */
6. var $config;
8. /**
9. * @var stores the final sql dump
10. */
11. var $dump;
13. /**
14. * @var stores the table structure + inserts for every table
15. */
16. var $struktur = array();
18. /**
19. * @var zip file name
20. */
21. var $datei;
23. /**
24. * this function is the constructor and phrase the options
25. * and connect to the database
26. * @return
27. */
28. public function Backup($options)
29. {
30. // write options
31. foreach($options AS $name => $value)
32. {
33. $this->config[$name] = $value;
34. }
36. // check mysql connection
37. mysql_connect($this->config['mysql'][0], $this->config['mysql'][1], $this->config['mysql'][2]) or die(mysql_error());
38. mysql_select_db($this->config['mysql'][3]) or die(mysql_error());
39. }
41. /**
42. * this function start the backup progress its the core function
43. * @return
44. */
45. public function backupDB()
46. {
47. // start backup
48. if(isset($_POST['backup']))
49. {
50. // check if tables are selected
51. if(empty($_POST['table']))
52. {
53. die("Please select a table.");
54. }
56. /** start backup **/
57. $tables = array();
58. $insert = array();
59. $sql_statement = '';
61. // lock tables
62. foreach($_POST['table'] AS $table)
63. {
64. mysql_query("LOCK TABLE $table WRITE");
66. // Read table structure
67. $res = mysql_query('SHOW CREATE TABLE '.$table.'');
68. $createtable = mysql_result($res, 0, 1);
69. $str = "\n\n".$createtable."\n\n";
71. array_push($tables, $str);
73. // Read table "inserts"
74. $sql = 'SELECT * FROM '.$table;
75. $query = mysql_query($sql) or die(mysql_error());
76. $feld_anzahl = mysql_num_fields($query);
78. $sql_statement = '--
79. -- Data Table `$table`
80. --
82. ';
84. // start reading progress
85. while($ds = mysql_fetch_object($query)){
86. $sql_statement .= 'INSERT INTO `'.$table.'` (';
88. for ($i = 0;$i if ($i ==$feld_anzahl-1){
89. $sql_statement .= mysql_field_name($query,$i);
90. } else {
91. $sql_statement .= mysql_field_name($query,$i).', ';
92. }
93. }
95. $sql_statement .= ') VALUES (';
97. for ($i = 0;$i $name = mysql_field_name($query,$i);
98. if (empty($ds->$name)){
99. $ds->$name = 'NULL';
100. }
101. if ($i ==$feld_anzahl-1){
102. $sql_statement .= '"'.$ds->$name.'"';
103. } else {
104. $sql_statement .= '"'.$ds->$name.'", ';
105. }
106. }
107. $sql_statement .= ");\n";
108. }
110. // insert "Inserts" into an array if not exists
111. if(!in_array($sql_statement, $insert))
112. {
113. array_push($insert, $sql_statement);
114. unset($sql_statement);
115. }
117. unset($sql_statement);
119. }
121. // put table structure and inserts together in one var
122. $this->struktur = array_combine($tables, $insert);
124. // create full dump
125. $this->createDUMP($this->struktur);
127. // create zip file
128. $this->createZIP();
130. /** end backup **/
132. // send an email with the sql dump
133. if(isset($this->config['email']) && !empty($this->config['email']))
134. {
135. $this->sendEmail();
136. }
138. // output
139. echo '

     Backup war erfolgreich

     Download Backup
140. 
     
141. 
     ';
142. }
143. }
145. /**
146. * this function generate an email with attachment
147. * @return
148. */
149. protected function sendEmail()
150. {
151. // start sending emails
152. foreach($this->config['email'] AS $email)
153. {
154. $to = $email;
156. $from = $this->config['email'][0];
158. $message_body = "This email contains the database backup as a zip file.";
160. $msep = strtoupper (md5 (uniqid (time ())));
162. // set email header (only text)
163. $header =
164. "From: $from\r\n" .
165. "MIME-Version: 1.0\r\n" .
166. "Content-Type: multipart/mixed; boundary="$msep"\r\n\r\n" .
167. "--$msep\r\n" .
168. "Content-Type: text/plain\r\n" .
169. "Content-Transfer-Encoding: 8bit\r\n\r\n" .
170. $message_body . "\r\n";
172. // file name
173. $dateiname = $this->datei;
175. // get filesize of zip file
176. $dateigroesse = filesize ($dateiname);
178. // open file to read
179. $f = fopen ($dateiname, "r");
180. // save content
181. $attached_file = fread ($f, $dateigroesse);
182. // close file
183. fclose ($f);
185. // create attachment
186. $attachment = chunk_split (base64_encode ($attached_file));
188. // set attachment header
189. $header .=
190. "--" . $msep . "\r\n" .
191. "Content-Type: application/zip; name='Backup'\r\n" .
192. "Content-Transfer-Encoding: base64\r\n" .
193. "Content-Disposition: attachment; filename='Backup.zip'\r\n" .
194. "Content-Description: Mysql Datenbank Backup im Anhang\r\n\r\n" .
195. $attachment . "\r\n";
197. // mark end of attachment
198. $header .= "--$msep--";
200. // eMail Subject
201. $subject = "Database Backup";
203. // send email to emails^^
204. if(mail($to, $subject, '', $header) == FALSE)
205. {
206. die("The email could not be sent. Please check the email address.");
207. }
209. echo "

     Email was successfully sent.

     ";
210. }
211. }
213. /**
214. * this function create the zip file with the database dump and save it on the ftp server
215. * @return
216. */
217. protected function createZIP()
218. {
220. // Set permissions to 777
221. chmod($this->config['folder'], 0777);
223. // create zip file
224. $zip = new ZipArchive();
225. // Create file name
226. $this->datei = $this->config['folder'].$this->config['mysql'][3]."_".date("j_F_Y_g:i_a").".zip";
228. // Checking if file could be created
229. if ($zip->open($this->datei, ZIPARCHIVE::CREATE)!==TRUE) {
230. exit("cannot open datei.">\n");
231. }
233. // add mysql dump to zip file
234. $zip->addFromString("dump.sql", $this->dump);
235. // close file
236. $zip->close();
238. // Check whether file has been created
239. if(!file_exists($this->datei))
240. {
241. die("The ZIP file could not be created.");
242. }
244. echo "

     The zip was created.

     ";
245. }
247. /**
248. * this function create the full sql dump
249. * @param object $dump
250. * @return
251. */
252. protected function createDUMP($dump)
253. {
254. $date = date("F j, Y, g:i a");
256. $header = -- SQL Dump
257. --
258. -- Host: {$_SERVER['HTTP_HOST']}
259. -- Erstellungszeit: {$date}
261. --
262. -- Datenbank: `{$this->config['mysql'][3]}`
263. --
265. -- --------------------------------------------------------
267. HEADER;
268. foreach($dump AS $name => $value)
269. {
270. $sql .= $name.$value;
271. }
272. $this->dump = $header.$sql;
273. }
275. /**
276. * this function displays the output form to select tables
277. * @return
278. */
279. public function outputForm()
280. {
281. // select all tables from database
282. $result = mysql_list_tables($this->config['mysql'][3]);
284. $buffer = '
285. Select some tables

     ';

     while($row = mysql_fetch_row($result))

     {

     $buffer .= '

     '.$row[0].'';

     }

     $buffer .= '

     
     
     
     ';
287. echo $buffer;
288. }
289. }
290. ?>

复制代码

备份用法:

2. //You can add as many email addresses as you like
3. $options = array('email' => array('email1', 'email2'),
4. 'folder' => './backup/',
5. 'mysql' => array('localhost', 'root', '****', 'database'));
7. $b = new Backup($options);
9. // if submit form start backup
10. if(isset($_POST['backup']))
11. {
12. // start backup
13. $b->backupDB();
14. }
16. // display tables
17. $b->outputForm();
19. ?>

复制代码

php, MySQL