MySQL to Redis Migration

Export data from MySQL into Redis

Recently, i was asked to export a few social media tables to Redis that we did not seem fit for use in MySQL. The company has been investing heavily in NoSQL databases such as Cassandra and Redis, of which i am heavily involved. So tasks like this, where we have to migrate heavy writes tables to Cassandra or heavy reads data to Redis will be common. 

Today, i will use an example to demonstrate how i went about achieving the task of migrating tables from MySQl to Redis.

Example table:

So lets assume that the table we want to migrate is employees shown below.

mysql> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

So the first thing we want to do is create a script employee.sql and add the following query:

SELECT CONCAT(
    "*6\r\n",
    '$',LENGTH(redis_cmd),'\r\n',redis_cmd,'\r\n',
    '$',LENGTH(redis_key),'\r\n',redis_key,'\r\n',
    '$',LENGTH(id_var),'\r\n',id_var,'\r\n',
    '$',LENGTH(id_val),'\r\n',id_val,'\r\n',
    '$',LENGTH(name_var),'\r\n',name_var,'\r\n',
    '$',LENGTH(name_val),'\r\n',name_val,'\r'
) FROM (
    SELECT 'hmset' AS redis_cmd,
    CONCAT('employee:id:',id) AS redis_key,
    'id' AS id_var,
    id AS id_val,
    'name' AS name_var,
    name AS name_val
    FROM employees
) AS staff

So using this script, we will export data from MySQL on this table and import it into Redis.

Run command below to pipe the script and import data into redis:

mysql -uroot -psecret dbname --skip-column-names --raw < employees.sql | redis-cli --pipe

That is all that is to it. You can examine the query and appropriately update it to fit any table you want. I would advise you maybe first create the example table above to play with, and maybe try to customize the query on a different table to you feel comfortable with the process.