TBD
Tracking data are saved on a MySQL database inside the ethoscope and in principle, one could access the MySQL db over the network and perform a dump and then a conversion to sqlite3 format. The script is something I’ve used in the past to recover some data in particular situations
#!/bin/sh
# Converts a mysqldump file into a Sqlite 3 compatible file. It also extracts the MySQL `KEY xxxxx` from the
# CREATE block and create them in separate commands _after_ all the INSERTs.
# Awk is choosen because it's fast and portable. You can use gawk, original awk or even the lightning fast mawk.
# The mysqldump file is traversed only once.
# Usage: $ ./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite
# Example: $ ./mysql2sqlite --no-data -u root -pMySecretPassWord myDbase | sqlite3 database.sqlite
# Thanks to and @artemyk and @gkuenning for their nice tweaks.
mysqldump --compatible=ansi --skip-extended-insert --compact "$@" | \\
awk '
BEGIN {
FS=",$"
print "PRAGMA synchronous = OFF;"
print "PRAGMA journal_mode = MEMORY;"
print "BEGIN TRANSACTION;"
}
# CREATE TRIGGER statements have funny commenting. Remember we are in trigger.
/^\\/\\*.*CREATE.*TRIGGER/ {
gsub( /^.*TRIGGER/, "CREATE TRIGGER" )
print
inTrigger = 1
next
}
# The end of CREATE TRIGGER has a stray comment terminator
/END \\*\\/;;/ { gsub( /\\*\\//, "" ); print; inTrigger = 0; next }
# The rest of triggers just get passed through
inTrigger != 0 { print; next }
# Skip other comments
/^\\/\\*/ { next }
# Print all `INSERT` lines. The single quotes are protected by another single quote.
/INSERT/ {
gsub( /\\\\\\047/, "\\047\\047" )
gsub(/\\\\n/, "\\n")
gsub(/\\\\r/, "\\r")
gsub(/\\\\"/, "\\"")
gsub(/\\\\\\\\/, "\\\\")
gsub(/\\\\\\032/, "\\032")
print
next
}
# Print the `CREATE` line as is and capture the table name.
/^CREATE/ {
print
if ( match( $0, /\\"[^\\"]+/ ) ) tableName = substr( $0, RSTART+1, RLENGTH-1 )
}
# Replace `FULLTEXT KEY` or any other `XXXXX KEY` except PRIMARY by `KEY`
/^ [^"]+KEY/ && !/^ PRIMARY KEY/ { gsub( /.+KEY/, " KEY" ) }
# Get rid of field lengths in KEY lines
/ KEY/ { gsub(/\\([0-9]+\\)/, "") }
# Print all fields definition lines except the `KEY` lines.
/^ / && !/^( KEY|\\);)/ {
gsub( /AUTO_INCREMENT|auto_increment/, "" )
gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )
gsub( /DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP|default current_timestamp on update current_timestamp/, "" )
gsub( /(COLLATE|collate) [^ ]+ /, "" )
gsub(/(ENUM|enum)[^)]+\\)/, "text ")
gsub(/(SET|set)\\([^)]+\\)/, "text ")
gsub(/UNSIGNED|unsigned/, "")
if (prev) print prev ","
prev = $1
}
# `KEY` lines are extracted from the `CREATE` block and stored in array for later print
# in a separate `CREATE KEY` command. The index name is prefixed by the table name to
# avoid a sqlite error for duplicate index name.
/^( KEY|\\);)/ {
if (prev) print prev
prev=""
if ($0 == ");"){
print
} else {
if ( match( $0, /\\"[^"]+/ ) ) indexName = substr( $0, RSTART+1, RLENGTH-1 )
if ( match( $0, /\\([^()]+/ ) ) indexKey = substr( $0, RSTART+1, RLENGTH-1 )
key[tableName]=key[tableName] "CREATE INDEX \\"" tableName "_" indexName "\\" ON \\"" tableName "\\" (" indexKey ");\\n"
}
}
# Print all `KEY` creation lines.
END {
for (table in key) printf key[table]
print "END TRANSACTION;"
}
'
exit 0
One can also force backup from the command line on the node using the following syntax:
cd /opt/ethoscope-node/node_src/scripts
[gg@node scripts]$ python backup_tool.py --help
Usage: backup_tool.py [options]
Options:
-h, --help show this help message and exit
-D, --debug Set DEBUG mode ON
-r RESULTS_DIR, --results-dir=RESULTS_DIR
Where result files are stored
-i SERVER, --server=SERVER
The server on which the node is running will be
interrogated first for the device list
-s, --safe Set Safe mode ON
-e ETHOSCOPE, --ethoscope=ETHOSCOPE
Force backup of given ethoscope number (eg: 007)
This would run as in the example below
[gg@node scripts]$ sudo python backup_tool.py -e 120
Forcing backup for ethoscope 120
INFO:root:Initiating backup for device 12046165bcf144baafb356ae82c40f8f
INFO:root:Running backup for device 12046165bcf144baafb356ae82c40f8f
INFO:root:Initializing local database static tables at /ethoscope_data/results/12046165bcf144baafb356ae82c40f8f/ETHOSCOPE_120/2020-07-20_14-37-01/2020-07-20_14-37-01_12046165bcf144baafb356ae82c40f8f.db
INFO:root:Making parent directories
WARNING:root:[Errno 17] File exists: '/ethoscope_data/results/12046165bcf144baafb356ae82c40f8f/ETHOSCOPE_120/2020-07-20_14-37-01'
INFO:root:Ensuring DAM file exists at /ethoscope_data/results/12046165bcf144baafb356ae82c40f8f/ETHOSCOPE_120/2020-07-20_14-37-01/2020-07-20_14-37-01_12046165bcf144baafb356ae82c40f8f.txt
INFO:root:Database mirroring initialised
TBD