MYSQL backup terugzetten via PHP
Ik maak een backup van mijn MYSQL database en dit werkt perfect alleen ik zou niet weten hoe ik deze .sql file kan restoren met PHP, ik heb al gegoogled maar de meeste scripts exploden op dingen die als het goed is helemaal niet voorkomen in mijn backup.
Zo ziet mijn backup.sql eruit:
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
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
DROP TABLE extension_slideshow_items;
CREATE TABLE `extension_slideshow_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slider_id` int(11) NOT NULL,
`slide_image_url` varchar(600) NOT NULL,
`slide_image_title` varchar(255) NOT NULL,
`slide_image_sort` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=488 DEFAULT CHARSET=latin1;
INSERT INTO extension_slideshow_items VALUES("484","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/29.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","title 1","1");
INSERT INTO extension_slideshow_items VALUES("485","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/7.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","title 2","2");
INSERT INTO extension_slideshow_items VALUES("486","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/29.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","no strip","4");
INSERT INTO extension_slideshow_items VALUES("487","11","<p>http://www.mrconservative.com/files/2013/05/hot-girl-inside.jpg</p>
\n","no strip title 2","5");
DROP TABLE extension_slideshow_sliders;
CREATE TABLE `extension_slideshow_sliders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slider_id` int(11) NOT NULL,
`anim_speed` int(11) NOT NULL,
`pause_time` int(11) NOT NULL,
`direction_nav` int(11) NOT NULL,
`control_nav` int(11) NOT NULL,
`controlnav_thumbs` int(11) NOT NULL,
`pause_on_hover` int(11) NOT NULL,
`prev_text` varchar(255) NOT NULL,
`next_text` varchar(255) NOT NULL,
`random_start` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`width` varchar(255) NOT NULL,
`height` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;
INSERT INTO extension_slideshow_sliders VALUES("64","11","1500","5000","0","1","0","1","Vorige","Volgende","0","frontpage-header","750","250");
DROP TABLE extensions;
CREATE TABLE `extensions` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`uploadpath` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO extensions VALUES("1","Slideshow","Some slideshow stuff","/cms/admin/modules/slideshow/");
INSERT INTO extensions VALUES("3","SocialMedia","Social Media bar","/cms/admin/modules/social-bar/");
CREATE TABLE `extension_slideshow_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slider_id` int(11) NOT NULL,
`slide_image_url` varchar(600) NOT NULL,
`slide_image_title` varchar(255) NOT NULL,
`slide_image_sort` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=488 DEFAULT CHARSET=latin1;
INSERT INTO extension_slideshow_items VALUES("484","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/29.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","title 1","1");
INSERT INTO extension_slideshow_items VALUES("485","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/7.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","title 2","2");
INSERT INTO extension_slideshow_items VALUES("486","11","<p><img alt=\"\" src=\"/cms/admin/modules/ckfinder/userfiles/images/29.jpg\" style=\"height:2000px; width:1500px\" /></p>
\n","no strip","4");
INSERT INTO extension_slideshow_items VALUES("487","11","<p>http://www.mrconservative.com/files/2013/05/hot-girl-inside.jpg</p>
\n","no strip title 2","5");
DROP TABLE extension_slideshow_sliders;
CREATE TABLE `extension_slideshow_sliders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`slider_id` int(11) NOT NULL,
`anim_speed` int(11) NOT NULL,
`pause_time` int(11) NOT NULL,
`direction_nav` int(11) NOT NULL,
`control_nav` int(11) NOT NULL,
`controlnav_thumbs` int(11) NOT NULL,
`pause_on_hover` int(11) NOT NULL,
`prev_text` varchar(255) NOT NULL,
`next_text` varchar(255) NOT NULL,
`random_start` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`width` varchar(255) NOT NULL,
`height` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=66 DEFAULT CHARSET=latin1;
INSERT INTO extension_slideshow_sliders VALUES("64","11","1500","5000","0","1","0","1","Vorige","Volgende","0","frontpage-header","750","250");
DROP TABLE extensions;
CREATE TABLE `extensions` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`uploadpath` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO extensions VALUES("1","Slideshow","Some slideshow stuff","/cms/admin/modules/slideshow/");
INSERT INTO extensions VALUES("3","SocialMedia","Social Media bar","/cms/admin/modules/social-bar/");
Gewijzigd op 25/09/2013 09:33:53 door Furio Scripting
Maar met mysqli_multi_query kan je dat gehele bestand in één keer uitvoeren
Ger van Steenderen op 25/09/2013 09:46:48:
Omdat de meeste van die scripts gebaseerd zijn op de oude mysql extensie.
Maar met mysqli_multi_query kan je dat gehele bestand in één keer uitvoeren
Maar met mysqli_multi_query kan je dat gehele bestand in één keer uitvoeren
Bedankt!
Toevoeging op 25/09/2013 11:29:55:
Helaas werkt het nog steeds niet, als ik mijn $q echo dan krijg ik een heleboel content te zien die nooit door mysql heen kan zonder errors.
Is er een methode om de sql file rechtstreeks als query te gebruiken?
Toevoeging op 25/09/2013 11:42:12:
Opgelost middels volgende code:
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
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
<?php // Name of the file
$filename = 'C:\wamp\www\cms\cms-db-backup.sql';
// MySQL host
$mysql_host = '';
// MySQL username
$mysql_username = '';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = '';
//////////////////////////////////////////////////////////////////////////////////////////////
// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line_num => $line) {
// Only continue if it's not a comment
if (substr($line, 0, 2) != '--' && $line != '') {
// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';') {
// Perform the query
mysql_query($templine) or print('Error performing query \'<b>' . $templine . '</b>\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
} ?>
$filename = 'C:\wamp\www\cms\cms-db-backup.sql';
// MySQL host
$mysql_host = '';
// MySQL username
$mysql_username = '';
// MySQL password
$mysql_password = '';
// Database name
$mysql_database = '';
//////////////////////////////////////////////////////////////////////////////////////////////
// Connect to MySQL server
mysql_connect($mysql_host, $mysql_username, $mysql_password) or die('Error connecting to MySQL server: ' . mysql_error());
// Select database
mysql_select_db($mysql_database) or die('Error selecting MySQL database: ' . mysql_error());
// Temporary variable, used to store current query
$templine = '';
// Read in entire file
$lines = file($filename);
// Loop through each line
foreach ($lines as $line_num => $line) {
// Only continue if it's not a comment
if (substr($line, 0, 2) != '--' && $line != '') {
// Add this line to the current segment
$templine .= $line;
// If it has a semicolon at the end, it's the end of the query
if (substr(trim($line), -1, 1) == ';') {
// Perform the query
mysql_query($templine) or print('Error performing query \'<b>' . $templine . '</b>\': ' . mysql_error() . '<br /><br />');
// Reset temp variable to empty
$templine = '';
}
}
} ?>