phpMySQLAutoBackup  1.6.1
Open source - automates the backup of your MySQL database
 All Data Structures Files Functions Variables
schema_for_export.php
Go to the documentation of this file.
1 <?php
2 /*******************************************************************************************
3  phpMySQLAutoBackup - Author: http://www.DWalker.co.uk - released under GPL License
4  For support and help please try the forum at: http://www.dwalker.co.uk/forum/
5 ********************************************************************************************
6 Version Date Comment
7 0.2.0 7th July 2005 GPL release
8 0.3.0 June 2006 Upgrade - added ability to backup separate tables
9 0.4.0 Dec 2006 removed bugs/improved code
10 1.4.0 Dec 2007 improved faster version
11 1.5.0 Dec 2008 improved and added FTP backup to remote site
12 1.5.1 Feb 2009 improved export data - added quotes around field names
13 1.5.2 April 2009 improved "create table" export and added backup time start & end
14 1.5.3 Nov 2009 replaced PHP function "ereg_replace" with "str_replace" - all occurances
15 1.5.4 Nov 2009 replaced PHP function "str_replace" with "substr" line 114
16 1.5.5 Feb 2011 more options for config added - email reports only and/or backup, save backup file to local and/or remote server.
17  Reporter added: email report of last 6 (or more) backup stats (date, total bytes exported, total lines exported) plus any errors
18  MySQL error reporting added and Automated version checker added
19 1.6.0 Dec 2011 PDO version
20 1.6.1 April 2012 - CURLOPT_TRANSFERTEXT turned off (to stop garbaging zip file on transfer) and bug removed from write_back
21 ********************************************************************************************/
23 // ---------------------------------------------------------
25 
26 //add new phpmysqlautobackup table if not there...
27 $result = $dbc->prepare("SHOW TABLES LIKE 'phpmysqlautobackup' ");
28 $rows = $dbc->executeGetRows($result);
29 if(count($rows)<1)
30 {
31  $query = "
32  CREATE TABLE phpmysqlautobackup (
33  id int(11) NOT NULL,
34  version varchar(6) default NULL,
35  time_last_run int(11) NOT NULL,
36  PRIMARY KEY (id)
37  )";
38  $result = $dbc->prepare($query);
39  $result = $dbc->execute($result);
40  $query="INSERT INTO phpmysqlautobackup (id, version, time_last_run)
41  VALUES ('1', '$phpMySQLAutoBackup_version', '0');";
42  $result = $dbc->prepare($query);
43  $result = $dbc->execute($result);
44 }
45 //check time last run - to prevent malicious over-load attempts
46 $query="SELECT * from phpmysqlautobackup WHERE id=1 LIMIT 1 ;";
47 $result = $dbc->prepare($query);
48 $rows = $dbc->executeGetRows($result);
49 if (time() < ($rows[0]['time_last_run']+$time_interval)) exit();// exit if already run within last time_interval
50 //update version number if not already done so
51 if ($rows[0]['version']!=$phpMySQLAutoBackup_version)
52 {
53  $result = $dbc->prepare("update phpmysqlautobackup set version='$phpMySQLAutoBackup_version'");
54  $result = $dbc->execute($result);
55 }
57 
58 $query="UPDATE phpmysqlautobackup SET time_last_run = '".time()."' WHERE id=1 LIMIT 1 ;";
59 $result = $dbc->prepare($query);
60 $result = $dbc->execute($result);
61 
62 if (!isset($table_select))
63 {
64  $result = $dbc->prepare("show tables");
65  $i=0;
66  $table="";
67  $tables = $dbc->executeGetRows($result);
68  foreach ($tables as $table_array)
69  {
70  list(,$table) = each($table_array);
71  $exclude_this_table = isset($table_exclude)? in_array($table, $table_exclude) : false;
72  if(!$exclude_this_table) $table_select[$i]=$table;
73  $i++;
74  //echo "$table<br>";
75  }
76 }
77 
79 
80 $thedomain = $_SERVER['HTTP_HOST'];
81 if (substr($thedomain,0,4)=="www.") $thedomain=substr($thedomain,4,strlen($thedomain));
82 
83 $buffer = '# MySQL backup created by phpMySQLAutoBackup - Version: '.$phpMySQLAutoBackup_version . "\n" .
84  '# ' . "\n" .
85  '# http://www.dwalker.co.uk/phpmysqlautobackup/' . "\n" .
86  '#' . "\n" .
87  '# Database: '. $db . "\n" .
88  '# Domain name: ' . $thedomain . "\n" .
89  '# (c)' . date('Y') . ' ' . $thedomain . "\n" .
90  '#' . "\n" .
91  '# Backup START time: ' . strftime("%H:%M:%S",time()) . "\n".
92  '# Backup END time: #phpmysqlautobackup-endtime#' . "\n".
93  '# Backup Date: ' . strftime("%d %b %Y",time()) . "\n";
94 
95 $i=0;
97 foreach ($table_select as $table)
98  {
99  $i++;
100  $export = " \n" .'drop table if exists `' . $table . '`; ' . "\n";
101 
102  //export the structure
103  $query='SHOW CREATE TABLE `' . $table . '`';
104  $result = $dbc->prepare($query);
105  $tables = $dbc->executeGetRows($result);
106  //$export.= print_r($tables) ."; \n";
107  $export.= $tables[0]['Create Table'] ."; \n";
108 
109  $table_list = array();
110  $result = $dbc->prepare('show fields from `' . $table . '`');
111  $fields = $dbc->executeGetRows($result);
112  foreach ($fields as $field_array) $table_list[] = $field_array['Field'];
113 
114  $buffer.=$export;
115  // dump the data
116  $query='select * from `' . $table . '` LIMIT '. $limit_from .', '. $limit_to.' ';
117  $result = $dbc->prepare($query);
118  $rows = $dbc->executeGetRows($result);
119  foreach ($rows as $row_array)
120  {
121  $export = 'insert into `' . $table . '` (`' . implode('`, `', $table_list) . '`) values (';
122  $lines_exported++;
123  reset($table_list);
124  while (list(,$i) = each($table_list))
125  {
126  if (!isset($row_array[$i])) $export .= 'NULL, ';
127  elseif (has_data($row_array[$i]))
128  {
129  $row = addslashes($row_array[$i]);
130  $row = str_replace("\n#", "\n".'\#', $row);
131  $export .= '\'' . $row . '\', ';
132  }
133  else $export .= '\'\', ';
134  }
135  $export = substr($export,0,-2) . "); \n";
136  $buffer.= $export;
137  }
138  }
139 $recordBackup->save(time(), strlen($buffer), $lines_exported);
140 $buffer = str_replace('#phpmysqlautobackup-endtime#', strftime("%H:%M:%S",time()), $buffer);