Consul, ProxySQL And MySQL HA

When we load MYSQL SERVERS, our writer host also gets configured in the reader hostgroup automatically by ProxySQL to handle all those queries which are redirected to reader hostgroup in case no slaves are online.

This feature is dependent on reader/writer hostgroup which we configured in table mysql_replication_hostgroups.

When the USE db_name command is issued from the MySQL CLI, it sends a COM_INIT_DB command to change the database, and also sends a SHOW TABLES query for the database specified.

(In order to prevent the SHOW TABLES query from being sent, it is necessary to execute the mysql command with the -A option)

Download File 🔥 https://urluso.com/2xYdGL 🔥

When you add a user in mysql_users with both backend=1 and frontend=1, you are actually creating 2 users: one for frontend and one for backend. Although in mysql_users they can be represented (only represented) as one row, they are actually 2 users. In fact, runtime_mysql_users shows 2 users and 2 rows.

ProxySQL uses runtime_mysql_users for syncing users, thus 2 users are synchronized, and in the receiving nodes they are recorded as 2 rows in mysql_users too.

Once again: even if you see only 1 row, they are actually 2 users.

As you can see, the consul watcher service of tenants watches for changes in its respective key paths in consul. Here, the consul watcher running on all tenant-A proxySQL nodes watches for changes under the mysql_cluster/tenant-A key path. Similarly the tenant-B watcher services watch for changes under the mysql_cluster/tenant-B key path.

Not mentioned here so I am including another cause of this issue. In my case, while using the mysql command line client, the error was caused by a low value of 30 seconds of interactive_timeout:

-system-variables.html#sysvar_interactive_timeout

This will persist across sessions but not a server restart.

Hi There, I'm wanting to run a blue / green deployment using 2 docker swarms and glusterfs as the shared file system between them both. Unfortunately, we can't run both environments simultaneously as that'll mean 2 instances will be accessing the Mysql data directory at the same time, hence the mysql container will crash on the other environment. How do you get around this? Are you even running mysql in containers? Or as a VM.

(Note that I can log into my local mysql install just fine by running mysql (and it will log me in as root) and also note that I can get into mysql in the remote server by logging into via ssh and then invoking mysql), but I am unable to connect to the remote server via my terminal using the host, and I need to do it that way so that I can then use mysql workbench.

It appears you correctly commented out the bind-address directive in my.cnf. But this change needs to be made on the remote server in order to have any effect there; while you seem to have made the change on your local machine. Therefore the change only has effect on your local mysqld, and not the remote mysqld you're trying to access. So you need to ssh into the remote machine and make the change on the remote machine (and then restart mysqld there). You'll also have to check the remote machine's firewall to ensure that it allows you access.

The mysql.cfg file, however, contains listen blocks that route to the currently active source hosts. This file is autogenerated and kept up-to-date by Consul Template using the template file haproxy_mysql.cfg.tpl. The template will generate a listen block like this for every MySQL source host:

It also implements one part of our STONITH approach: If the Consul key mysql/master/$cluster/failed exists, it will black-hole all traffic to this cluster by pointing it to 127.0.0.1:1337, a non-existing host. be457b7860