Can anyone recommend their procedure for archiving historical tables such as “sqlt_data_1_2011_01” for example? I can do mysqldump to file without issue, its removing the tables that I am concerned about. I am unable to open the current db in MySQL workbench to do a reverse engineer due to number of objects. On test databases I can see how current month is connected, and all other tables seem to be just archived.
So I would like to know how others are handling the archiving of their database. Ideally I want to just backup the individual table and remove it entirely. Should the entire table be removed or just the data within and keep the table and structure?
Great question. I hope you get a response.
You can indeed just backup the individual table and remove it. You will also however have to remove the reference to the table in the sqlth_partitions table - you should just remove the entire record pointing to the table name.
Obviously by removing the table Ignition will no longer be able to access its data. The data itself is in a format used by Ignition. To use it elsewhere you will have to join the table with the sqlth_te table to get the names of the tags. The time stamp field is the number of milliseconds since 00:00:00 on 01/01/1970, so you’ll have to convert it to make it human readable. See this manual page for help with this.