-
Notifications
You must be signed in to change notification settings - Fork 34
Expand file tree
/
Copy pathdbbackup.php
More file actions
152 lines (126 loc) · 3.73 KB
/
Copy pathdbbackup.php
File metadata and controls
152 lines (126 loc) · 3.73 KB
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
<?php
$database = "wftutorials";
$db_user = "root";
$db_password = "";
$backupLocation = "C:\Users\wfranklin\Documents\GitHub\\test";
$alltables = null;
$backLimit =0;
function get_connection(){
global $database;
global $db_user;
global $db_password;
$dsn = "mysql:host=localhost;dbname=$database";
$user = $db_user;
$passwd = $db_password;
$conn = new PDO($dsn, $user, $passwd);
return $conn;
}
function cleanString($string) {
$string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.
return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
}
function back_by_tables(){
global $database;
global $backupLocation;
global $alltables;
global $backLimit;
$tables = [];
// get all table names
// remove old files
if($backLimit > 0){
echo "Old Files check \r\n";
removeOldFiles();
}
$conn = get_connection();
if(empty($alltables)){
$sql = "SELECT TABLE_NAME AS tablename
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = '$database'";
$query = $conn->prepare($sql);
$query->execute([]);
$results = $query->fetchAll();
foreach ($results as $result){
$tables[] = $result['tablename'];
}
}else{
$tables = explode(',', $alltables);
}
$output = "";
foreach ($tables as $table) {
echo "Saving the $table \r\n";
$query = $conn->prepare('SELECT * FROM ' . $table);
$query->execute([]);
$result = $query->fetchAll(PDO::FETCH_ASSOC); // important
$output .= 'DROP TABLE IF EXISTS ' . $table . ';';
$query2 = $conn->prepare('SHOW CREATE TABLE ' . $table);
$query2->execute([]);
$q2result = $query2->fetchAll();
if(isset($q2result[0]['Create Table'])){
$output .= "\n\n" . $q2result[0]['Create Table'] . ";\n\n";
}
foreach ($result as $row) {
$output .= 'INSERT INTO ' . $table . ' VALUES(';
foreach ($row as $data) {
$data = addslashes($data);
// Updated to preg_replace to suit PHP5.3 +
$data = preg_replace("/\n/", "\n", $data);
if (isset($data)) {
$output.= '"' . $data . '"';
} else {
$output.= '""';
}
$output.= ',';
}
$output = substr($output, 0, strlen($output) - 1);
$output.= ");\n";
}
$output.="\n\n\n";
}
$filepath = $backupLocation . '\db_'.$database. '_' . strtotime(date("D M d, Y G:i:s")).'.sql';
echo "Writing to file: $filepath \r\n";
$handle = fopen($filepath, 'w+');
fwrite($handle, $output);
fclose($handle);
echo "Backup complete \r\n";
}
function get_arguments(){
$arguments = getopt("d:t:l:s:");
return $arguments;
}
function removeOldFiles(){
$fCount = 0;
$firstFile = null;
global $backLimit;
global $backupLocation;
global $database;
foreach (new DirectoryIterator($backupLocation) as $file) {
if($file->isDot()) continue;
if (strpos($file->getFilename(), 'db_'.$database) !== false) {
if(empty($firstFile)){
$firstFile = $file->getFilename();
}
$fCount++;
}
}
if($fCount >= $backLimit && !empty($firstFile)){
echo "Removing file $firstFile \r\n";
unlink($backupLocation . DIRECTORY_SEPARATOR . $firstFile);
}
}
// not running from cli
if(!defined('STDIN') ){
echo("Not Running from CLI"); exit;
}
$flags = get_arguments();
//var_dump($flags); exit;
$database = !empty($flags['d']) ? $flags['d'] : $database; // set the database
$alltables = !empty($flags['t']) ? $flags['t'] : null; // set the tables to backup
$backupLocation = !empty($flags['l']) ? $flags['l'] : $backupLocation; // set backup location
$backLimit = !empty($flags['s']) ? $flags['s'] : $backLimit;
$filepath = "";
try{
echo "Starting backup...\r\n";
back_by_tables();
}catch(Exception $e){
echo $e->getMessage() . "\r\n";
}