I have a stats section which show a counter which values randomly increases every second by 1000-10000 and the random number will increase as the number gets bigger, so in the future it might be 10000-1 million.
How do I store this in the event that something goes wrong and the server shuts down that I can retrieve this value back where it last stopped?
The easiest way is to just store it in MySQL and update it every second but that would take lots of resources and I don't think it is meant for that.
I know Redis can do it but I am in a scenario where I cannot afford another database. It is just 1 number which will keep increasing, what is the most efficient way to do this?
You can use this scenario:
Create one InnoDB table and one Memory table. You will hold one entry in the Memory table with the following columns: id, counter.
Everytime you have to increase the counter you do the following:
In case of fail down you will initialize the Memory table counter by counting the InnoDB table (because Memory tables lost their data in case of server fail/restart).
Why using InnoDB for log? Because it has row lock and inserting new data won't lock the whole table (like MyIsam does).
Why using Memory table for counter, and not InnoDB? In case of too many queries concurency for updating one row you may experience problems and performance issues because of the row lock.
You can also use Memcache instead of Memory table. This approach will avoid using mysql for the counter, but also shares the same problems with data loss (so the data has to be initialized in case of failure).