Modern applications need to be highly available and easy to scale. A three-node MySQL InnoDB Cluster—built on MySQL Group Replication and connected through MySQL Router—provides a reliable way to support critical workloads. To set up this architecture, you start by deploying three MySQL server instances. In this example, the nodes are assigned the following hostname-to-IP […]
Innodb Cluster or ClusterSet topologies already have secondary instances that can act as a failover for primary or also offload read requests. However, with MySQL 8.4, we now have the feasibility of adding a separate async replica to the cluster for serving various special/ad-hoc queries or some reporting purposes. This will also help offload read traffic away […]
MySQL 8.4 has two good options for High Availability clusters: Galera Cluster (and Percona XtraDB Cluster (PXC)) plus MySQL InnoDB Cluster. Both solutions are now mature enough, so what is the deciding factor? Workload characteristics, application compatibility (Galera is virtually synchronous; InnoDB Cluster offers 2 modes – single primary mode, which is one instance being read-write, with the rest being read only, and multi-primary mode, which is closer to what Galera Cluster is) and operational preferences, most likely will decide which one comes out top in 2025, is what we aim to answer at next week’s webinar.
Due to the lack of real-world usage statistics (we can tell you how many Galera Cluster downloads we have, for example, or even customer numbers, but we won’t have anything to compare against), we look at proxy statistics, like …
[Read more]I thought I’d share a quick addition to the MySQL Router bootstrap command and hope it helps others, as always.
First of all, you all know the MySQL Router documentation, and have probably looked into a specific option when executing commands, but I’ve found one quite handy that allows me to add in certain parametrization for my routers: –conf-set-option
It’s simple to use, so I’ll just share what I used as a reference and let you play away.
mysqlrouter --bootstrap icadmin:'MyP4ssword!'@node01:3306 \
--name="router_node1" --account=routerAdmin \
--conf-base-port=3306 --report-host=rtnode01 --conf-use-gr-notifications \ …
[Read more]
So, as you’ve noticed, I’ve been playing around with MySQL Router v8.4.3 and read-write splitting, and now I’ve come across some issues that my environment has generated and I’ve opened some bugs against the fiend:
Bug 116950 – Router Read-Write splitting config causes PHP RSET_HEADER error
Bug 116951 – mysqlrouter Error parsing stats_updates_frequency errors
Bug 116952 – Router w/ RW split causes ERROR: 4501 if sql script contains comments lines (“– “)
(Yup, I prepared each bug description, repeatable tasks and suggested fix …
[Read more]I’m just trying out the v8.4 Read-Write configuration for MySQL Router, that comes default in the community edition and, as they say, TL;DR “it’s default upon bootstrap”.
What does this mean and how can I go about it?
The overview is simple:
- Either install or upgrade your existing MySQL Router to v8.4 at least.
- Bootstrap the Router.
- Use the port 3310 or change it to fit your needs.
Let’s get to it:
Stop your running router:
systemctl stop mysqlrouter
Copy the old config, just in case:
cp /etc/mysqlrouter/mysqlrouter.conf /etc/mysqlrouter/mysqlrouter_v80.conf
I normally keep my mysql linux repository disabled so no “yum update” takes over version control and gives me a nasty surprise afterwards. As so, I uncomment the “mysql” entry:
vi /etc/yum.conf …
[Read more]
After installing & configuring Dolphie, let’s take a look into how we can “re-record not fadeaway” and avoid using a VHS tape.
One of the coolest features is being able to go back in time with Dolphie and analyze what was happening at a specific moment.
This feature requires recording so we can replay.
Setting Dolphie up for recording mode.
I’m really just going to share the links to the the github site and organize my steps so someone else might want to rinse’n’repeat or “replay”. I’m just a mere messenger.
It works via the Daemon mode. …
[Read more]I have been looking into “dolphie” lately, and have to say, “thanks Charles!”. I actually first saw dolphie via Lefred’s MySQL Belgian Days and installed it just after looking into the slidedeck. But never got around to looking further… until now.
We can use so many different observability (o11y?) tools to get notifications, alerts, react, generate reports, etc. from so many different companies, using agents, proxies, repositories, and so on and so forth. And after exchanging experiences with dolphie’s author himself, Charles, the idea here is to go that little bit further in …
[Read more]Different ways to get the status:
mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status
mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster status
mysqlsh icadmin:'P4ssw0rD'@db01:3306 -- cluster status --extended=0
mysqlsh icadmin@$MYROUTER1:3306 -- cluster status --extended=1
watch -n 5 “mysqlsh --login-path=icadmin -h$MYROUTER1 -- cluster status”
Set the Primary Instance (switching):
mysqlsh --login-path=icadmin -h${HOSTNAME} --redirect-primary -- cluster set_primary_instance "db01"
Obtaining MySQL InnoDB Cluster basics:
select cluster_id, cluster_name, description, cluster_type, primary_mode, clusterset_id from mysql_innodb_cluster_metadata.clusters;
Members of our cluster:
select * from performance_schema.replication_group_members order by MEMBER_ROLE;
Local & Remote Trans Q’s:
…[Read more]
So, we’ve heard that jemalloc is better than malloc for MySQL usage, and in fact, certain versions / forks of mysql already include this, eg. Percona Server (https://github.com/percona/jemalloc).
But, how can I install and configure my system to use it?
Here’s a quick push in the, hopefully, right direction.
First things first, what is “jemalloc” and how does it affect my system? I think it’s quite well explained here: https://www.percona.com/blog/impact-of-memory-allocators-on-mysql-performance/ albeit an old article.
The right place to get the latest …
[Read more]