Today we gonna guide you through how to show all users in the MySQL users Database. A common question that ...
10 Older Entries »
As a data architect I always ensure that for any database schema
change there a fully recoverable execution path.
I have generally advised to create a patch/revert process for every change. For example, if a change adds a new column or index to a table, a revert script would remove the respective column or index.
The goal is to always have a defensive position for any changes. The concept is that simple, it is not complex.
In its simplest form I use the following directory and file structure.
/schema schema.sql /patch YYYYMMDDXX.sql where XX,ZZ are sequential 2 digit numbers, e.g. 01,02 YYYYMMDDZZ.sql /revert YYYYMMDDXX.sql This is the same file name in the revert sub-directory. YYYYMMDDZZ.sql
At any commit or tag in configuration management it is possible
to create a current copy of the schema, i.e. use
It is also possible to take the first …
In this tutorial, we will show you how to create a backup of MySQL databases on an Ubuntu 20.04 VPS, ...
The post How to Create a Backup of MySQL Databases Using mysqldump on Ubuntu 20.04 appeared first on RoseHosting.
A common MySQL strategy to perform updates with accumulating
functions is to employ user-defined variables, using the
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar,
This pattern though doesn’t play well with the optimizer (leading to non-deterministic behavior), so it has been deprecated. This left a sort of void, since the (relatively) sophisticated logic is now harder to reproduce, at least with the same simplicity.
In this article, I’ll have a look at two ways to apply such logic: using, canonically, window functions, and, a bit more creatively, using recursive CTEs.[Read more]
Another “missing and missed” functionality in MySQL is a data type for arrays.
While MySQL is not there yet, it’s now possible to cover a significant use case: storing denormalized columns (or arrays in general), and accessing them via index.
In this article I’ll give some context about denormalized data and indexes, including the workaround for such functionality on MySQL 5.7, and describe how this is (rather) cleanly accomplished on MySQL 8.0.[Read more]
Another interesting feature released with MySQL 8.0 is full support for functional indexes.
Although this is not a strictly new concept in the MySQL world (indexed generated columns provided the same functionality), I find it worth reviewing, through some applications, notes and considerations.
All in all, I’m not 100% bought into functional indexes (as opposed to indexed generated columns); I’ll elaborate on this over the course of the article.
As a natural fit, generated columns are included in the article; additionally, some constructs build on my previous article, in relation to the subject of CTEs.
Updated on 12/Mar/2020: Found another bug.
A long-time missing (and missed) functionality in MySQL, is sequences/ranges.
As of MySQL 8.0, this functionality is still not supported in a general sense, however, it’s now possible to generate a sequence to be used within a single query.
In this article, I’ll give a brief introduction to CTEs, and
explain how to build different sequence generators; additionally,
I’ll introduce the new (cool) MySQL 8.0 query hint
SET_VAR, and a pinch of virtual columns and
functional indexes (“functional key parts”, another MySQL 8.0
In this post I’ll expand on the subject of my MySQL pre-FOSDEM talk: what dbadmins need to know and do, when upgrading from MySQL 5.7 to 8.0.
As usual, I’ll use this post to introduce tooling concepts that may be useful in generic system administration.
The presentation code is hosted on a GitHub repository (including the …[Read more]
I posted a few days ago some initial thoughts on the the MySQL native cloning functionality. Overall this looks good and I need to spend time to test further. I’m here in San Francisco ahead of Oracle Open World which starts today. As is usual with trips like this jet lag wakes you up rather […]
If you use replication with MySQL, if you need a backup, if you need a spare copy of a system for testing and for many other reasons you need a way to make a copy of your MySQL system. In the past you could make a copy in various ways: using a cold file system […]
10 Older Entries »