mysql-backup
Code (php)
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
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
<?
#create a sql backup and send it to your e-mail address
//define settings
//database host
DEFINE('dbhost','localhost');
//database name
DEFINE('dbnaam','databasenaam');
//database user
DEFINE('dbuser','databaseuer');
//database passwordt
DEFINE('dbpass','wachtwoord');
//domeinnaam
DEFINE('domein','phphulp.nl');
//doel
DEFINE('email','[email protected]');
//connect with database
$link = mysql_connect(dbhost,dbuser,dbpass) or die('No datbase conection could be establised');
mysql_select_db(dbnaam,$link) or die('No datbase selected');
//query to recive table names
$query = mysql_query('SHOW TABLE STATUS') or die(mysql_error());
$sql_backup = '';
//whileloop to loop trough every table
while($row = mysql_fetch_assoc($query))
{
//show sql query to rebuild the query
$sql = 'SHOW CREATE TABLE '.$row['Name'].'';
//exucte error or give a error
$query2 = mysql_query($sql) or die(mysql_error());
//create sql
$sql_backup.="\r\n#Create table ".$row['Name']."\r\n\r\n";
$out = mysql_fetch_assoc($query2);
$sql_backup.=$out['Create Table'].";\r\n\r\n";
$sql_backup.="#Dump data\r\n\r\n";
//SQL code to select everything for table
$sql = 'SELECT * FROM '.$row['Name'];
$out = mysql_query($sql);
$sql_code = '';
//loop trough the colloms
while($code = mysql_fetch_array($out,MYSQL_ASSOC))
{
$sql_code .= "INSERT INTO ".$row['Name']." SET ";
foreach($code as $insert => $value)
{
$sql_code.=$insert ."='".addslashes($value)."',";
}
$sql_code = substr($sql_code, 0, -1);
$sql_code.= ";\r\n";
}
$sql_backup.= $sql_code;
}
//generade a unique id
$unique = md5(uniqid(time()));
//message
$message = "Backup procedure van database: ".dbnaam." is met succes verlopen. U vind in de bijlage een backup van de inhoud gemaakt op. ".date('d-m-Y H:i:s')."\r\n Autobot";
//maak headers aan
//form header
$headers = "From: Autobot <noreply@".domein.">\r\n";
//terug sturen naar een niet bestaand mail adress ([email protected])
$headers .= "Reply-To: Autobot <noreply@".domein.">\r\n";
//vertel dat het een mine versie is
$headers .= "MIME-Version: 1.0\r\n";
//email bestaat uit meerdere bestanden dus vertel wat de scheidings teken is en dat het een multipart is
$headers .= "Content-Type: multipart/mixed; boundary=\"".$unique."\"\r\n";
//we zenden een attachment mee
$headers .= "Content-Disposition: attachment\r\n";
//generenen een body. Dit is een multi part gezeik
$body.= "This is a multi-part message in MIME format.\r\n";
$body.= "\r\n";
//boundary
$body.= "--".$unique."\r\n";
//content type + charater set (iso in dit geval)
$body.= "Content-Type: text/plain; charset=iso-8859-1\r\n";
//codering (7 bit)
$body.= "Content-Transfer-Encoding: 7bit\r\n";
$body.= "\r\n";
//het bericht
$body.= $message ."\r\n";
//boundaty
$body.= "--".$unique."\r\n";
//content type + naam bestand (database.sql)
$body .= "Content-Type: application/octet-stream; name=database.sql\r\n";
//codering
$body .= "Content-Transfer-Encoding: base64\r\n";
//als bijlage toegevoegd
$body.= "Content-disposition: attachment\r\n";
$body .= "\n";
//de inhoud van het bestand
$body .= chunk_split(base64_encode($sql_backup)) . "\r\n";
//zet alles op email!
//hier kan wat tijdsverschil in zitten!
mail(email,'Mysql backup '.date('d-m-Y H:i:s'),$body,$headers);
?>
#create a sql backup and send it to your e-mail address
//define settings
//database host
DEFINE('dbhost','localhost');
//database name
DEFINE('dbnaam','databasenaam');
//database user
DEFINE('dbuser','databaseuer');
//database passwordt
DEFINE('dbpass','wachtwoord');
//domeinnaam
DEFINE('domein','phphulp.nl');
//doel
DEFINE('email','[email protected]');
//connect with database
$link = mysql_connect(dbhost,dbuser,dbpass) or die('No datbase conection could be establised');
mysql_select_db(dbnaam,$link) or die('No datbase selected');
//query to recive table names
$query = mysql_query('SHOW TABLE STATUS') or die(mysql_error());
$sql_backup = '';
//whileloop to loop trough every table
while($row = mysql_fetch_assoc($query))
{
//show sql query to rebuild the query
$sql = 'SHOW CREATE TABLE '.$row['Name'].'';
//exucte error or give a error
$query2 = mysql_query($sql) or die(mysql_error());
//create sql
$sql_backup.="\r\n#Create table ".$row['Name']."\r\n\r\n";
$out = mysql_fetch_assoc($query2);
$sql_backup.=$out['Create Table'].";\r\n\r\n";
$sql_backup.="#Dump data\r\n\r\n";
//SQL code to select everything for table
$sql = 'SELECT * FROM '.$row['Name'];
$out = mysql_query($sql);
$sql_code = '';
//loop trough the colloms
while($code = mysql_fetch_array($out,MYSQL_ASSOC))
{
$sql_code .= "INSERT INTO ".$row['Name']." SET ";
foreach($code as $insert => $value)
{
$sql_code.=$insert ."='".addslashes($value)."',";
}
$sql_code = substr($sql_code, 0, -1);
$sql_code.= ";\r\n";
}
$sql_backup.= $sql_code;
}
//generade a unique id
$unique = md5(uniqid(time()));
//message
$message = "Backup procedure van database: ".dbnaam." is met succes verlopen. U vind in de bijlage een backup van de inhoud gemaakt op. ".date('d-m-Y H:i:s')."\r\n Autobot";
//maak headers aan
//form header
$headers = "From: Autobot <noreply@".domein.">\r\n";
//terug sturen naar een niet bestaand mail adress ([email protected])
$headers .= "Reply-To: Autobot <noreply@".domein.">\r\n";
//vertel dat het een mine versie is
$headers .= "MIME-Version: 1.0\r\n";
//email bestaat uit meerdere bestanden dus vertel wat de scheidings teken is en dat het een multipart is
$headers .= "Content-Type: multipart/mixed; boundary=\"".$unique."\"\r\n";
//we zenden een attachment mee
$headers .= "Content-Disposition: attachment\r\n";
//generenen een body. Dit is een multi part gezeik
$body.= "This is a multi-part message in MIME format.\r\n";
$body.= "\r\n";
//boundary
$body.= "--".$unique."\r\n";
//content type + charater set (iso in dit geval)
$body.= "Content-Type: text/plain; charset=iso-8859-1\r\n";
//codering (7 bit)
$body.= "Content-Transfer-Encoding: 7bit\r\n";
$body.= "\r\n";
//het bericht
$body.= $message ."\r\n";
//boundaty
$body.= "--".$unique."\r\n";
//content type + naam bestand (database.sql)
$body .= "Content-Type: application/octet-stream; name=database.sql\r\n";
//codering
$body .= "Content-Transfer-Encoding: base64\r\n";
//als bijlage toegevoegd
$body.= "Content-disposition: attachment\r\n";
$body .= "\n";
//de inhoud van het bestand
$body .= chunk_split(base64_encode($sql_backup)) . "\r\n";
//zet alles op email!
//hier kan wat tijdsverschil in zitten!
mail(email,'Mysql backup '.date('d-m-Y H:i:s'),$body,$headers);
?>