Synology mysqli Database benaderen vanuit computer
Ik zou de db van mijn nas willen aanspreken.
via localhost lukt alles. zowel op de nas als op de pc maar vanuit de pc de nas_db lukt me niet.
de code
Code (php)
1
2
2
$con=mysqli_connect('mysql://hass:[email protected]:3306/hass', 'root', 'pw', 'janr_be');
$con=mysqli_connect('192.168.1.14:3306', 'root', 'pw', 'janr_be');
$con=mysqli_connect('192.168.1.14:3306', 'root', 'pw', 'janr_be');
Iemand een idee hoe het wel lukt?
Zowel maria5 als maria10/3306 als 3307 zou moeten lukken dus het gaat uitsluitend om een goed adres :)
Jan
Gewijzigd op 02/01/2020 18:39:39 door Jan R
'192.168.1.14:3306'
naam van de nas eveneens niet (ds2)
toch gedeeltelijk :)
Je moet ook nog het IP toegang geven via de admim console in phpMyAdmin of iets vergelijksbaar
GRANT ALL ON dbnaam.* TO user@'IP_ADRES' IDENTIFIED BY 'hetpaswoord'
De database moet geselecteerd zijn. (use dbnaam)
Bedankt. Het was toch de juiste richting:)
Toevoeging op 02/01/2020 20:43:29:
Ik wil de anderen mijn oplossing niet onthouden.
De bedoeling was dus om de totale database te kopiëren. Ik weet dat een dump de juiste oplossing is maar van 1 pc naar een nas lukt dat volgens mij niet op een automatische manier. :)
Dus hier de totale code welke voor mij werkt. 't is toch maar 78 lijnen.
Jan
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
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
<!DOCTYPE html>
<html lang="en">
<head>
<title>Copy DB</title>
<meta charset="utf-8">
</style>
</head>
<body>
<?php
function h1($msg){
echo '<h1>' . $msg . '</h1>' . PHP_EOL;
}
/*
remember to grant access to all databases
use: GRANT ALL ON *.* TO user@'IP_Adress' IDENTIFIED BY 'your_password' on the database console
http://test.local/remoteDB.php
*/
h1('Remote address: ' . $remoteaddress='192.168.0.1:3307');
h1('Local address: ' . $localaddress='localhost');
$password = 'paswoord';
$db = 'databasenaam';
$user = 'een goede usernaam met de juiste rechten';
$dblink2 = @mysqli_connect($remoteaddress, $user, $password);// connect remote server
if ($dblink2){
h1('Remote database connected.');
$dblink1 = @mysqli_connect($localaddress, $user, $password); // connect server 1
if ($dblink1){
h1('Local database connected.');
mysqli_select_db($dblink1, $db); // select database 1
mysqli_set_charset($dblink1, 'utf8');
$r2 = mysqli_query($dblink2, 'DROP DATABASE IF EXISTS ' . $db . ';');
if(!$r2) {fout(mysqli_error($dblink2));}
$r2 = mysqli_query($dblink2, 'CREATE DATABASE ' . $db . ';');//make database 2
if(!$r2) {fout(mysqli_error($dblink2));}
mysqli_set_charset($dblink2, "utf8");
if(!$r2) {fout(mysqli_error($dblink2));}
mysqli_select_db($dblink2, $db); // select database 2
$tables = mysqli_fetch_all(mysqli_query($dblink1, "SHOW TABLES"), MYSQLI_NUM);
foreach($tables as $table){
h1('Start table: ' . $table[0]);
$tableinfo = mysqli_fetch_array(mysqli_query($dblink1, 'SHOW CREATE TABLE ' . $table[0])); // get structure from table on server 1
mysqli_query($dblink2, $tableinfo[1]); // use found structure to make table on server 2
$result = mysqli_query($dblink1, 'SELECT * FROM ' . $table[0]); // select all content
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
$sql3 = 'INSERT INTO ' . $table[0] . ' VALUES (';
for($j=0; $j<count($row); $j++) {
if(is_null($row[$j])){
$sql3.= ' null';
}else{
$row[$j] = addslashes($row[$j]);
$sql3 .= '"' . $row[$j] . '"' ;
}
if ($j<(count($row)-1)) { $sql3.= ', '; }
}
$sql3 .=');';
$r3 = mysqli_query($dblink2, $sql3); // insert one row into new table
if(!$r3) {fout(mysqli_error($dblink2));fout($sql3);}
}
}
mysqli_close($dblink1);
mysqli_close($dblink2);
}else{
h1('The local database isn't connected!');
}
}else{
h1('The remote database isn't connected!');
}
?>
</body>
</html>
<html lang="en">
<head>
<title>Copy DB</title>
<meta charset="utf-8">
</style>
</head>
<body>
<?php
function h1($msg){
echo '<h1>' . $msg . '</h1>' . PHP_EOL;
}
/*
remember to grant access to all databases
use: GRANT ALL ON *.* TO user@'IP_Adress' IDENTIFIED BY 'your_password' on the database console
http://test.local/remoteDB.php
*/
h1('Remote address: ' . $remoteaddress='192.168.0.1:3307');
h1('Local address: ' . $localaddress='localhost');
$password = 'paswoord';
$db = 'databasenaam';
$user = 'een goede usernaam met de juiste rechten';
$dblink2 = @mysqli_connect($remoteaddress, $user, $password);// connect remote server
if ($dblink2){
h1('Remote database connected.');
$dblink1 = @mysqli_connect($localaddress, $user, $password); // connect server 1
if ($dblink1){
h1('Local database connected.');
mysqli_select_db($dblink1, $db); // select database 1
mysqli_set_charset($dblink1, 'utf8');
$r2 = mysqli_query($dblink2, 'DROP DATABASE IF EXISTS ' . $db . ';');
if(!$r2) {fout(mysqli_error($dblink2));}
$r2 = mysqli_query($dblink2, 'CREATE DATABASE ' . $db . ';');//make database 2
if(!$r2) {fout(mysqli_error($dblink2));}
mysqli_set_charset($dblink2, "utf8");
if(!$r2) {fout(mysqli_error($dblink2));}
mysqli_select_db($dblink2, $db); // select database 2
$tables = mysqli_fetch_all(mysqli_query($dblink1, "SHOW TABLES"), MYSQLI_NUM);
foreach($tables as $table){
h1('Start table: ' . $table[0]);
$tableinfo = mysqli_fetch_array(mysqli_query($dblink1, 'SHOW CREATE TABLE ' . $table[0])); // get structure from table on server 1
mysqli_query($dblink2, $tableinfo[1]); // use found structure to make table on server 2
$result = mysqli_query($dblink1, 'SELECT * FROM ' . $table[0]); // select all content
while ($row = mysqli_fetch_array($result, MYSQLI_NUM)) {
$sql3 = 'INSERT INTO ' . $table[0] . ' VALUES (';
for($j=0; $j<count($row); $j++) {
if(is_null($row[$j])){
$sql3.= ' null';
}else{
$row[$j] = addslashes($row[$j]);
$sql3 .= '"' . $row[$j] . '"' ;
}
if ($j<(count($row)-1)) { $sql3.= ', '; }
}
$sql3 .=');';
$r3 = mysqli_query($dblink2, $sql3); // insert one row into new table
if(!$r3) {fout(mysqli_error($dblink2));fout($sql3);}
}
}
mysqli_close($dblink1);
mysqli_close($dblink2);
}else{
h1('The local database isn't connected!');
}
}else{
h1('The remote database isn't connected!');
}
?>
</body>
</html>
Gewijzigd op 02/01/2020 18:48:06 door Jan R