欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  后端开发

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

程序员文章站 2022-05-18 18:00:46
...

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

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

    Backup war erfolgreich

    Download Backup


  114. ';
  115. }
  116. }
  117. /**
  118. * this function generate an email with attachment
  119. * @return
  120. */
  121. protected function sendEmail()
  122. {
  123. // start sending emails
  124. foreach($this->config['email'] AS $email)
  125. {
  126. $to = $email;
  127. $from = $this->config['email'][0];
  128. $message_body = "This email contains the database backup as a zip file.";
  129. $msep = strtoupper (md5 (uniqid (time ())));
  130. // set email header (only text)
  131. $header =
  132. "From: $from\r\n" .
  133. "MIME-Version: 1.0\r\n" .
  134. "Content-Type: multipart/mixed; boundary="$msep"\r\n\r\n" .
  135. "--$msep\r\n" .
  136. "Content-Type: text/plain\r\n" .
  137. "Content-Transfer-Encoding: 8bit\r\n\r\n" .
  138. $message_body . "\r\n";
  139. // file name
  140. $dateiname = $this->datei;
  141. // get filesize of zip file
  142. $dateigroesse = filesize ($dateiname);
  143. // open file to read
  144. $f = fopen ($dateiname, "r");
  145. // save content
  146. $attached_file = fread ($f, $dateigroesse);
  147. // close file
  148. fclose ($f);
  149. // create attachment
  150. $attachment = chunk_split (base64_encode ($attached_file));
  151. // set attachment header
  152. $header .=
  153. "--" . $msep . "\r\n" .
  154. "Content-Type: application/zip; name='Backup'\r\n" .
  155. "Content-Transfer-Encoding: base64\r\n" .
  156. "Content-Disposition: attachment; filename='Backup.zip'\r\n" .
  157. "Content-Description: Mysql Datenbank Backup im Anhang\r\n\r\n" .
  158. $attachment . "\r\n";
  159. // mark end of attachment
  160. $header .= "--$msep--";
  161. // eMail Subject
  162. $subject = "Database Backup";
  163. // send email to emails^^
  164. if(mail($to, $subject, '', $header) == FALSE)
  165. {
  166. die("The email could not be sent. Please check the email address.");
  167. }
  168. echo "

    Email was successfully sent.

    ";
  169. }
  170. }
  171. /**
  172. * this function create the zip file with the database dump and save it on the ftp server
  173. * @return
  174. */
  175. protected function createZIP()
  176. {
  177. // Set permissions to 777
  178. chmod($this->config['folder'], 0777);
  179. // create zip file
  180. $zip = new ZipArchive();
  181. // Create file name
  182. $this->datei = $this->config['folder'].$this->config['mysql'][3]."_".date("j_F_Y_g:i_a").".zip";
  183. // Checking if file could be created
  184. if ($zip->open($this->datei, ZIPARCHIVE::CREATE)!==TRUE) {
  185. exit("cannot open datei.">\n");
  186. }
  187. // add mysql dump to zip file
  188. $zip->addFromString("dump.sql", $this->dump);
  189. // close file
  190. $zip->close();
  191. // Check whether file has been created
  192. if(!file_exists($this->datei))
  193. {
  194. die("The ZIP file could not be created.");
  195. }
  196. echo "

    The zip was created.

    ";
  197. }
  198. /**
  199. * this function create the full sql dump
  200. * @param object $dump
  201. * @return
  202. */
  203. protected function createDUMP($dump)
  204. {
  205. $date = date("F j, Y, g:i a");
  206. $header = -- SQL Dump
  207. --
  208. -- Host: {$_SERVER['HTTP_HOST']}
  209. -- Erstellungszeit: {$date}
  210. --
  211. -- Datenbank: `{$this->config['mysql'][3]}`
  212. --
  213. -- --------------------------------------------------------
  214. HEADER;
  215. foreach($dump AS $name => $value)
  216. {
  217. $sql .= $name.$value;
  218. }
  219. $this->dump = $header.$sql;
  220. }
  221. /**
  222. * this function displays the output form to select tables
  223. * @return
  224. */
  225. public function outputForm()
  226. {
  227. // select all tables from database
  228. $result = mysql_list_tables($this->config['mysql'][3]);
  229. $buffer = '
  230. Select some tables


  231. ';
  232. echo $buffer;
  233. }
  234. }
  235. ?>
复制代码

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

php, MySQL