jelastic-jps / mysql-cluster Goto Github PK

Hi,

We have been using a mysql cluster (Master-master) for testing purposes for more than a month now and have been testing these past days horizontal scaling of the mysql nodes. What we found is that when we scale through the UI, a slave node is created (which is fine), but it immediately logs a replication error and is added to the ProxySQL balancing... which causes query errors on the application.

What we expected is that horizontal scaling with 0 downtime would be possible by just adding a node through the topology UI.

We think the error happens when the newly created slave starts catching up by reading the binlogs, but as the oldest binlogs have been deleted it misses any operations that are older than 30 days (the default value for automatic log file removal).

Here is the slave log:

2020-09-28T16:32:50.385084Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL. 
2020-09-28T16:33:01.263173Z 13 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=node43424-oa-mysql-dev-relay-bin' to avoid this problem. 
2020-09-28T16:33:01.267862Z 13 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL '' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='node41185', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. 
2020-09-28T16:33:01.274050Z 14 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. 
2020-09-28T16:33:01.275945Z 14 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'repl-7086965@node41185:3306',replication started in log 'FIRST' at position 4 
2020-09-28T16:33:01.285289Z 15 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Unknown database 'oadev'' on query. Default database: 'oadev'. Query: 'update `knex_migrations_lock` set `is_locked` = 1 where `is_locked` = 0', Error_code: MY-001049 
2020-09-28T16:33:01.285329Z 15 [Warning] [MY-010584] [Repl] Slave: Unknown database 'oadev' Error_code: MY-001049 
2020-09-28T16:33:01.285340Z 15 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000016' position 156

We are looking for the right approach here to be able to scale horizontally at any time. Maybe by making a dump available somewhere where the slave would automatically load it up at first boot for example.

It would be great if we could choose Percona as the server type in addition to MySQL or MariaDB.

And to have a "Monitoring" checkbox which sets up or not a Percona PMM server.

What do you think ?

Keeping in line with current trends, rename the master / slave to primary / replica
This will require a fork of teh source manifest: https://github.com/jelastic-jps/mysql-cluster/

We've been working with a cluster in our development environment for a few months now and we have been using a scheduler to avoid having it stay running idle outside of work hours.

It had been shutting down and booting up without any issue for a while, until yesterday when we discovered replication error messages on an alter operation on a table. We think that what most likely happened is that the cluster shutdown in the middle of a replication operation that was taking some time (an alter on a big table) and that is when things went awry.

We were not able to resolve the replication issue the next day. There are ways to safely shut down a cluster, they probably could be integrated into a script to avoid the kind of issues we experienced: https://www.percona.com/blog/2020/05/07/prepare-mysql-for-a-safe-shutdown/

I'm trying to get my hands on a multi-region solution for this. Everything I've seen so far only applies to one region. With the exception of the extensive Wordpress Multi-region set up.

Hi,

We are testing a mysql cluster with the objective to deploy one for our platform. We noticed that by default, firewall rules of the environment leave many ports open to "all". We disabled most of them, leaving only the ones we believe to be necessary.

In the SQL nodes section of the rules, we leave the 3306 port to the ProxySQL subgroup to let the LB nodes forward requests to the SQL nodes. Other rules are disabled. We saw that the consequence of this is that SQL nodes can no longer access each other, causing replication to break down.

Ideally, we would want to add a rule for SQL nodes to allow them to access other nodes of their own subgroup ( SQL node 1 should be able to access SQL node 2)

The UI does not allow the submission of such a rule. Is there any other way to implement this? I find this restriction surprising as it is possible to target any other node group of the environment.

It's not always happening but I have an error while installing the auto-scale Mysql cluster into an environnement (using ON_ENV_INSTALL variable in a manifest).

# Checking for binary logging on master...
ERROR: Master must have binary logging turned on.

Sometimes, the check passes and the installation works perfectly.

I check /etc/mysql/conf.d/master.conf on the master node and log-bin = mysql-bin is here.
So I supposed it's a problem of ordering during installation.

Hi,

I did an installation with auto-clustering MySQL and ProxySQL with default settings. the problem is that the ram is never released even after a long time without activity, it just keeps increasing and i am forced to put low and fixed cloudlets count.

It gets expensive for not much, I have to restart MySQL servers manually from time to time.

It's the InnoDB buffer that stays full no matter what, do you have any idea how to fix this?

When I setup a mariaDB cluster master+master with ProxySQL and then clone the environment, the cluster is not functioning properly.

The application is unable to connect to ProxySQL, and referenced cluster nodes are the ones of the initial environment that was cloned.

Is there a way to handle this? Otherwise, can you adapt the scripts to allow cloning without additional configuration?

Thanks!

Hi, It could be awesome to implement proxySQL Cluster in the proxysql.cnf:

proxysql_servers =
(
  {
      hostname="172.16.0.101"
      port=6032
      weight=0
      comment="proxysql1"
  },
  {
      hostname="172.16.0.102"
      port=6032
      weight=0
      comment="proxysql2"
  }
)

In order to sync configuration, users, servers... between nodes.

https://proxysql.com/documentation/proxysql-cluster/

This is a test issue, please ignore. We need to check if this email will be delivered to a dev team.