In my talk at Percona Live 2021, “Creating Chaos in Databases”, I discussed how creating a controlled interruption in available resources (I used primary pod and network interruptions) allows us to test the stability of a database, and in our case, Percona XtraDB Cluster.
I also mentioned in the talk that my testing led to diagnosing a few unpleasant bugs, namely:
We talk a lot about Galera Cluster for MySQL being great for High Availability, but what about Disaster Recovery (DR)? Database outages may occur when you lose a data centre due to power outages or natural disaster, so why not plan appropriately in advance?
In this webinar, we will discuss the business considerations including achieving the highest possible uptime, focus on business impact, risk mitigation, and disaster recovery itself. What scenarios are right for you: fully synchronous replication across various data centres? A mix of synchronous and asynchronous to an offsite location?
We will discuss Galera Cluster, mixed setups, the use of proxies, and also evaluate some third party solutions. We will debunk claims about how Galera Cluster might not work across data centers, tell you how to successfully do this across data centres, with performance tuning settings for all the segments. Galera 4 has also been released, making …
[Read more]I recently learned of some odd behavior using MySQL BETWEEN operator queries, filtering by a DATETIME column. I wrote about this over on Medium so I am sharing the post for any readers here who are interested…
Image by _Alicja_ from Pixabay
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit …
[Read more]
The students needed yet another example of LEFT
JOIN, RIGHT JOIN, and FULL JOIN
syntax (by combining a left and right join with the UNION set
operator). To that end, I put this set of examples together.
The example also shows how to order the result set from a derived
table with the UNION operator. It uses the
WITH clause to build a Common Table Expression
(CTE), which allows the query to order the UNION set
operator’s product based on the left and right join queries. It
uses a CASE statement to order the result sets. The
left_table is the parent table and the
right_table is the child table in the relationship,
which means the right_table holds a
left_id foreign key column that lets you connect
matching rows in the left_table.
You build the little model with the following …
[Read more]When organizing things helps to simplify life.
In the previous article, we start to explore dynamic privileges and the interaction with static ones. We also saw how to remove SUPER privilege from a DBA account.
What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly.
Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes.
Instead, we can use ROLES to group, assign, and revoke the correct privileges in a much easier way.
This is becoming even more important in MySQL with the advent of dynamic privileges.
What should we do to correctly use ROLES? Well first of all design. …
[Read more]When trying to make things better, make our life very complicated.
I was working on a Security Threat Tool script, when I had to learn more about the interaction between static and dynamic privileges in MySQL 8.
Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance the FLUSH operation now has dedicated Privileges and by scope.
Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change in respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic)
Static privileges are the classical privileges available in MySQL …
[Read more]When trying to make things better, make our life very complicated.
I was working on a Security Threat Tool script, when I had to learn more about the interaction between static and dynamic privileges in MySQL 8.
Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance the FLUSH operation now has dedicated Privileges and by scope.
Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change in respect to the components or plugin when activated. (https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/privileges-provided.html#privileges-provided-dynamic)
Static privileges are the classical privileges available in MySQL …
[Read more]When trying to make things better, make it very complicated.
I was working on a Security Threat Tool script when I had to learn more about the interaction between static and dynamic privileges in MySQL 8.
Dynamic privileges is a “new” thing added in MySQL 8 to easily extend the privileges definition, and at the same time to provide more granularity. For instance, the FLUSH operation now has dedicated Privileges and by scope.
Dynamic privileges are assigned at runtime. Most of them are active when the server starts. But they can also change with respect to the components or plugin when activated. ( …
[Read more]When organizing things helps to simplify life.
In the previous article we start to explore dynamic privileges and the interaction with the static ones. We also saw how to remove SUPER privilege from a DBA account.
What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly.
Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes.
Instead we can use ROLES to group, assign and revoke the correct privileges in a much easier way.
This is becoming even more important in MySQL with the advent of dynamic privileges.
What should we do to correctly use ROLEs? Well first of all …
[Read more]This post explains the inherent problem of running online schema changes in MySQL, on tables participating in a foreign key relationship. We'll lay some ground rules and facts, sketch a simplified schema, and dive into an online schema change operation. Our discussion applies to gh-ost, pt-online-schema-change, and VReplication based migrations, or any other online schema change tool that works with a shadow/ghost table like the Facebook tools. Why Online DDL? # Online schema change tools come as workarounds to an old problem: schema migrations in MySQL were blocking, uninterruptible, aggressive in resources, replication unfriendly.
