File to MySQL

Setup

    1.
    Put the plugin file into you plugin folder and restart the server so that the plugin can generate the new config settings.
    2.
    Set the Type setting under Repository to MySQL
    3.
    If you have an old pets.db file you want to import set ConvertFrom setting under Repository to SQLite(or what Type was before).
    4.
    create a new database (collation: utf8_general_ci) on your MySQL server (default: mypet).
    5.
    replace the credentials under MySQL with your own.
    6.
    restart the server once again. It should create the tables and import the local file to the database.
    7.
    OPTIONAL: If you want to merge multiple pets.db files into one database you can use the script at the bottom of this page.
    8.
    You can run your server like normal but all servers you use can now access the pets from one database!

MySQL Merge Script

If you want to merge two MyPet databases, they need to be on the same server and have different names. Just download the file in the spoiler and fill in the correct credentials and database names and the PHP script will merge them together. Different (MyPet) owner UUIDs are no problem as long as the player name or the Mojang UUID are the same.
Please make backups of your databases before you try to merge them!
1
<?php
2
3
$database_1 = "mypet";
4
$database_2 = "mypet_2";
5
$user = "root";
6
$password = "";
7
$server = "localhost";
8
$port = 3306;
9
10
//----------------------------------------------------------------------------------------------------------------------
11
// Do not change anything after this
12
//----------------------------------------------------------------------------------------------------------------------
13
14
15
$mysqli = new mysqli($server, $user, $password, "", $port);
16
17
/* check connection */
18
if ($mysqli->connect_errno) {
19
printf("Connect failed: %s\n", $mysqli->connect_error);
20
exit();
21
}
22
23
$version_1 = 0;
24
$version_2 = 0;
25
26
if ($result_info = $mysqli->query("SELECT version FROM $database_1.info")) {
27
while ($row = $result_info->fetch_assoc()) {
28
$version_1 = $row["version"];
29
}
30
} else {
31
echo "Version in $database_1.info table not found!";
32
exit();
33
}
34
if ($result_info = $mysqli->query("SELECT version FROM $database_2.info")) {
35
while ($row = $result_info->fetch_assoc()) {
36
$version_2 = $row["version"];
37
}
38
} else {
39
echo "Version in $database_2.info table not found!";
40
exit();
41
}
42
43
if($version_1 != $version_2) {
44
echo "Database versions not equal -> $version_1 != $version_2";
45
exit();
46
}
47
48
49
$uuid_map = [];
50
51
$updated_mypets = 0;
52
53
if ($result_players_1 = $mysqli->query("SELECT * FROM $database_1.players")) {
54
//printf("Select returned %d rows.`<br>`", $result_players_1->num_rows);
55
56
57
while ($row_players_1 = $result_players_1->fetch_assoc()) {
58
59
// find duplicates
60
//printf("find duplicates --------------------------------------------------------------`<br>`");
61
$query = "SELECT * FROM $database_2.players WHERE ";
62
$online_uuid = $row_players_1["mojang_uuid"];
63
if($online_uuid != null) {
64
$query .= "mojang_uuid LIKE \"" . $online_uuid . "\"";
65
}
66
$name = $row_players_1["name"];
67
if($name != null) {
68
if($online_uuid != null) {
69
$query .= " OR ";
70
}
71
$query .= "name LIKE \"" . $name . "\"";
72
}
73
$query .= ";";
74
75
//printf("Query: " . $query . "`<br>`");
76
77
if ($result_players_2 = $mysqli->query($query)) {
78
79
if($result_players_2->num_rows > 0) {
80
//printf("Select returned %d rows.`<br>`", $result_players_2->num_rows);
81
}
82
83
84
while ($row_players_2 = $result_players_2->fetch_assoc()) {
85
$uuid_map[$row_players_1["internal_uuid"]] = $row_players_2["internal_uuid"];
86
87
$qq = "UPDATE $database_2.pets SET owner_uuid=\"" . $row_players_1["internal_uuid"] . "\" WHERE owner_uuid=\"" . $row_players_2["internal_uuid"] . "\"";
88
//printf("Query: %s`<br>`", $qq);
89
$mysqli->query($qq);
90
91
if($mysqli->affected_rows > 0) {
92
//printf("Updated %d pets.`<br>`", $mysqli->affected_rows);
93
$updated_mypets += $mysqli->affected_rows;
94
}
95
}
96
$result_players_2->close();
97
}
98
99
// delete duplicates
100
//printf("delete duplicates --------------------------------------------------------------`<br>`");
101
102
$query = "DELETE FROM $database_2.players WHERE ";
103
$online_uuid = $row_players_1["mojang_uuid"];
104
if($online_uuid != null) {
105
$query .= "mojang_uuid LIKE \"" . $online_uuid . "\"";
106
}
107
$name = $row_players_1["name"];
108
if($name != null) {
109
if($online_uuid != null) {
110
$query .= " OR ";
111
}
112
$query .= "name LIKE \"" . $name . "\"";
113
}
114
$query .= ";";
115
116
//printf("Query: " . $query . "`<br>`");
117
$mysqli->query($query);
118
if($mysqli->affected_rows > 0) {
119
//printf("Deleted %d duplicate players.`<br>`", $mysqli->affected_rows);
120
}
121
122
// copy non duplicates
123
124
}
125
$result_players_1->close();
126
}
127
128
129
foreach ($uuid_map as $key => $value) {
130
echo "$value -> $key`<br>`";
131
}
132
133
printf("Deleted %d duplicate players.`<br>`", count($uuid_map));
134
printf("Updated %d pets.`<br>`", $updated_mypets);
135
136
$query = "INSERT INTO $database_1.players SELECT * from $database_2.players";
137
138
$mysqli->query($query);
139
140
if($mysqli->affected_rows > 0) {
141
printf("Inserted %d players.`<br>`", $mysqli->affected_rows);
142
}
143
144
$query = "INSERT INTO $database_1.pets SELECT * from $database_2.pets";
145
$mysqli->query($query);
146
147
if($mysqli->affected_rows > 0) {
148
printf("Inserted %d pets.`<br>`", $mysqli->affected_rows);
149
}
Copied!
Last modified 2yr ago
Copy link