Restitutor is the pride of <SUBJECT HOMETOWN HERE>

CoreProtect MySQL Restoration Process

No replies
RestitutorOrbis's picture
Name: RestitutorOrbis (Restitutor)
Offline
Princeps
Joined: Aug 24 2014
Posts:

This post probably won't be useful to anyone, however for the curious it will explain the process through which the CoreProtect data from pre-June 10 2016 was merged with the current database.

Initial Summary
A co purge was run on July 9th 2016 resulting in all CO data except that 30 days prior to be lost. A backup was given by fela a few days later. However, the logs from the backup needed to be merged with what was left (and the new CO data) without creating any duplicates. Here is the process used.

Importing and Pruning
The backup was imported on to a local machine. Then, all non-coreprotect tables were deleted. Afterwards, each table was looked through to see which needed to be merged.

For example,
co_art_map, co_entity_map, co_material_map could be erased as they only map ids to strings rather than store specific data about logged events (therefore a newer copy exists on the main machine).
co_chat, co_command are not logged and could be removed.

Only co_block, co_container, co_sign, and co_skull were kept.

Note Making
SELECT * FROM mcserver.co_container ORDER BY rowid;
Shows the earliest recorded piece of data.

SELECT * FROM mcserver.co_container ORDER BY rowid DESC;
Shows the latest recorded piece of data.

The two commands above were run on both the local machine (with the backup) and the main machine (with MC1's current database). The unix time on the rows were noted and converted with WolframAlpha into actual dates.

The following was noted.

Local
Earliest time - 1453152844 9:34:04 pm UTC | Monday, January 18, 2016
Latest time - 1467857873 2:17:53 am UTC | Thursday, July 7, 2016

Main
Earliest time - 1465601296 11:28:16 pm UTC | Friday, June 10, 2016
Latest time - 1483067104 3:05:04 am UTC | Friday, December 30, 2016

Note the overlap between the earliest time on the main machine (June) and the latest time on the local machine (July).

Removing the Overlap
DELETE FROM mcserver.co_container WHERE time >= 1465601296;

The following command removed all co_container data from the local copy which overlapped with the data still present on the main machine. The same command was run 3 more times for the remaining tables.

Exporting the Local to Remote
Table Data Export tool was used in MySQL Workbench where the field separator was defined as , rather than the default ;. This yielded the 4 necessary csv files.

All 3 but co_block took approximately a night to export.
co_block's export ran from Dec 30 2016 5:27 AM to Jan 10 2017 5:23 PM. (it ran for a year)

Importing The CSVs to the Main Machine Part 1
The first line of the file was purged due to issues with importing.

The Table Data Import tool was used and ran for a few hours on co_sign and co_skull.

Importing The CSVs to the Main Machine Part 2
Given how the co_block table contains millions rather than only ~3000 records, it would take literally weeks to import using the Table Data Import tool. Instead, a different process was used.

MC1 was shutdown and CoreProtect disabled so that the needed tables would not be used.

FileZilla was used to upload the two remaining CSV files to fela's machine.
The CSV files were renamed to TXT files and the first lines removed.

mv co_block.csv co_block.txt
nano co_block.txt
The command above was used to rename and edit the file.

mysql -u mcsa -p --local-infile mcserver
The command above was used to open the MySQL console.

load data local infile '/home/mc/co_block.txt' INTO TABLE co_block FIELDS TERMINATED BY ',';
The command above was used to import the CO data.

SELECT * FROM co_block ORDER BY rowid LIMIT 10;
Used to determine if the backup was now in the main machine successfully by comparing the rows on the exported CSV/TXT file with the output.

MC1 was turned on and CoreProtect re-enabled.

n/a