Mysql – User, host, and privileges

This is detailed in When Privilege Changes Take Effect in the MySQL documentation.

A grant table reload affects privileges for each existing client connection as follows:

Table and column privilege changes take effect with the client's next request.

Database privilege changes take effect the next time the client executes a USE db_name statement.

Note Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database or flushing the privileges.

Global privileges and passwords are unaffected for a connected client. These changes take effect only for subsequent connections.

The excellent support techs at AWS clarified the issue.

As far as I understood, the tcp6 entries were a red herring and caused due to netstat using AF_INET6 sockets which would have been using IPv6 had the address been of the type ::ffff:1.2.3.4, 1.2.3.4 being the corresponding IPv4 address. However that is not the case here.

Here, replication continued even though I blocked the slave using master's ufw firewall because the ufw rule I added only blocks new connections and does nothing to disrupt ESTABLISHED ones.

So to simulate network connectivity issues, you have to somehow kill the established connections - which is not as easy as say, kill for processes, because you need to use tcpkill or other scripts knowing that tcpkill comes with the dsniff package (which has other network scanning / testing and MITM tools and should be used very carefully), or, use iptables rules to drop packets on the master like so:

# iptables -I INPUT 1 -s SLAVE_IP -j DROP

and then later when done with testing, delete the rule by:

# iptables -D INPUT 1

Alternatively, you could stop and start both master and slave after deleting the ufw rule that allowed replication - given you can stop master (obviously do not test in production).

So this has nothing to do with IPv6 - except that MySQL listens to IPv6 too as the :::3306 lines above show. But since I haven't configured IPv6 on my servers or network, it is not being used.