Proxy TCP stream (MySQL and Redis) with Nginx

I do not think Nginx can be used for what you're trying to use it for. While it can be used to proxy tcp streams or load balance them, it's not necessarily knowledgable of the protocol and request structure within them. So to answer your question:

from database sharding perspective - is it possible to dispatch request to sharded database based on some logic apart from $remote_addr?

Not really. It can't route different Redis requests or MySQL queries from a single connection to different servers based on their content, as those things, to nginx, are just a stream which it cannot read. It assigns it a destination during the stream initiation and makes sure that all packets back and forth go to the same destinations, and that's about it.

As an example, in the MySQL sharding article you linked, it executing the mysql command prompt to run a single query and check the node name. Each time it's run, it makes a connection, runs the query, and disconnects. So each time it does this, the new connection gets routed to another server by nginx. However, if you were to run the same query twice over the same mysql command prompt instance, it would give you the same node name. It's also worth noting that this is using MySQL Galera, which is a multi-master configuration of MySQL which handles read and write routing within itself, which allows for arbitrary client-side query routing like this. This would not be true of redis.

Also, this does not inherently get rid of all connection handling for your application either. Errors, such as the downstream being unavailable, may still propagate up to the client in many circumstances.

You may be able to use this to simplify configuration for your application, however, it may also just be pushing the complexity into a local nginx config. So that may or may not be an actual benefit.

can we intercept cookie from http section and reuse in stream section where we don't have any headers?

Probably not. HTTP headers are not passed through to database connections as they are fundamentally different protocols, so this would not be available to nginx even if it could read the streams.

Can we inject Lua code in the stream section?

This is potentially possible, but would require a Lua module that's able to parse the application protocols, such as Redis' and MySQLs wire protocol. But as I mentioned previously, routing of requests within a connection from (even if you could detect and route them in nginx) these is not necessarily simple from a database / consistency standpoint and I wouldn't really recommend doing that.