How to update data in Redis and MySQL at the same

Question

I'm building a background service which boils down to a very complicated queue system. The idea is to use Redis as non-persistent storage, and have a sub/pub scheme which runs on an interval.

All of the subscribers will be behind a load balancer. This removes the complicated problem of maintaining state between all the servers behind the load balancer.

But, this introduces a new problem...how can I ensure that the non-persistent (Redis) and persistent (MySQL) databases are both updated by my application(s)?

It seems like I'm forced to prioritize one, and if I HAVE to prioritize one, I will prioritize persistence. But, in that scenario, what happens if MySQL is updated, Redis is not, and for some reason I have lost the connection to MySQL and cannot undo my last write?

Answer 1

There are two possible solutions to your problem:

1. Following these steps:
   a. Start MySQL transaction with START TRANSACTION
   b. Run your MySQL query INSERT INTO ...
   c. Run your Redis command
   d. Finish your MySQL transaction with COMMIT statement in case if Redis command succeeded or ROLLBACK if command failed
   Using transctions ensures that data is consistent in both storages.
2. Write LUA script for Redis using LuaSQL library (https://realtimelogic.com/ba/doc/en/lua/luasql.html), where you will connect to MySQL, insert your data and then send commands to Redis as well. Then this LUA script can be called from client side with just one command EVAL or EVALSHA

Answer 2

You can try the mysql udf plugin (https://github.com/Ideonella-sakaiensis/lib_mysqludf_redis)

See the post: how to move data from mysql to redis