SQL export query
Het liefste wil ik een .SQl bestand krijgen, net zoals PHPmyAdmin die genereert..
Weet iemand hoe ik dit voor elkaar kan krijgen?
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
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
<?php
// @TODO: queries via de database class
// @TODO: mail via mail class
class Backup
{
private $db, $sql_backup;
function __construct()
{
$this->db = new Database();
}
public function getData()
{
$query = mysql_query('SHOW TABLE STATUS');
$this->sql_backup = '';
while ($row = mysql_fetch_assoc($query))
{
$query2 = mysql_query('SHOW CREATE TABLE '.$row['Name']);
$this->sql_backup.= "\r\n#Create table ".$row['Name']."\r\n\r\n";
$out = mysql_fetch_assoc($query2);
$this->sql_backup.= $out['Create Table'].";\r\n\r\n";
$this->sql_backup.= "#Dump data\r\n\r\n";
$out = mysql_query('SELECT * FROM '.$row['Name']);
$sql_code = '';
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";
}
$this->sql_backup .= $sql_code;
}
}
public function mailData()
{
$unique = md5(uniqid(time()));
$message = "Backup procedure van database is met succes verlopen. U vindt in de bijlage een backup van de inhoud gemaakt op ".date('d-m-Y H:i:s')."\r\n Autobot";
$headers = "From: Autobot <noreply@".DOMAIN.">\r\n";
$headers .= "Reply-To: Autobot <noreply@".DOMAIN.">\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: multipart/mixed; boundary=\"".$unique."\"\r\n";
$headers .= "Content-Disposition: attachment\r\n";
$body.= "This is a multi-part message in MIME format.\r\n";
$body.= "\r\n";
$body.= "--".$unique."\r\n";
$body.= "Content-Type: text/plain; charset=iso-8859-1\r\n";
$body.= "Content-Transfer-Encoding: 7bit\r\n";
$body.= "\r\n";
$body.= $message."\r\n";
$body.= "--".$unique."\r\n";
$body.= "Content-Type: application/octet-stream; name=database.sql\r\n";
$body.= "Content-Transfer-Encoding: base64\r\n";
$body.= "Content-disposition: attachment\r\n";
$body.= "\n";
$body.= chunk_split(base64_encode($this->sql_backup))."\r\n";
if (@mail(EMAIL, 'Mysql backup '.date('d-m-Y H:i:s'), $body, $headers))
{
return true;
}
return false;
}
}
?>
// @TODO: queries via de database class
// @TODO: mail via mail class
class Backup
{
private $db, $sql_backup;
function __construct()
{
$this->db = new Database();
}
public function getData()
{
$query = mysql_query('SHOW TABLE STATUS');
$this->sql_backup = '';
while ($row = mysql_fetch_assoc($query))
{
$query2 = mysql_query('SHOW CREATE TABLE '.$row['Name']);
$this->sql_backup.= "\r\n#Create table ".$row['Name']."\r\n\r\n";
$out = mysql_fetch_assoc($query2);
$this->sql_backup.= $out['Create Table'].";\r\n\r\n";
$this->sql_backup.= "#Dump data\r\n\r\n";
$out = mysql_query('SELECT * FROM '.$row['Name']);
$sql_code = '';
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";
}
$this->sql_backup .= $sql_code;
}
}
public function mailData()
{
$unique = md5(uniqid(time()));
$message = "Backup procedure van database is met succes verlopen. U vindt in de bijlage een backup van de inhoud gemaakt op ".date('d-m-Y H:i:s')."\r\n Autobot";
$headers = "From: Autobot <noreply@".DOMAIN.">\r\n";
$headers .= "Reply-To: Autobot <noreply@".DOMAIN.">\r\n";
$headers .= "MIME-Version: 1.0\r\n";
$headers .= "Content-Type: multipart/mixed; boundary=\"".$unique."\"\r\n";
$headers .= "Content-Disposition: attachment\r\n";
$body.= "This is a multi-part message in MIME format.\r\n";
$body.= "\r\n";
$body.= "--".$unique."\r\n";
$body.= "Content-Type: text/plain; charset=iso-8859-1\r\n";
$body.= "Content-Transfer-Encoding: 7bit\r\n";
$body.= "\r\n";
$body.= $message."\r\n";
$body.= "--".$unique."\r\n";
$body.= "Content-Type: application/octet-stream; name=database.sql\r\n";
$body.= "Content-Transfer-Encoding: base64\r\n";
$body.= "Content-disposition: attachment\r\n";
$body.= "\n";
$body.= chunk_split(base64_encode($this->sql_backup))."\r\n";
if (@mail(EMAIL, 'Mysql backup '.date('d-m-Y H:i:s'), $body, $headers))
{
return true;
}
return false;
}
}
?>
Gewijzigd op 11/01/2011 14:28:55 door Jaron T
Hiermee maak je eenvoudig de backup die je beschrijft.
Voorbeeld:
/usr/bin/mysqldump web_users >/disk/mnt/web_users_backup.sql