This text exists mainly so that I paste the URL into the #mysql channel in Libera IRC.

The mysqldump tools allows you to convert a MySQL database server or individual schemas back to SQL. You are left with a script that is supposed to be loadable into a target server and gives you back the full database, including all objects in it.

Peewee is a simple and small ORM. It has few (but expressive) concepts, making it easy to learn and intuitive to use.

a small, expressive ORM

• python 2.7+ and 3.4+ (developed with 3.6)
• supports sqlite, mysql, postgresql and cockroachdb
• tons of extensions
• Peewee’s source code hosted on GitHub.

UTF8 in MySQL encodes the Unicode BMP . The Unicode Basic Multilingual Plane, or BMP, is the original 65536 character plane of Unicode 1.0. It was thought to be enough for all scripts in the world. It wasn’t. Unicode was extended, as early as with the 2.0 release, to have more characters than that, in more code planes. There can now be up to 17 Unicode planes.

There are various ways to encode codepoints of Unicode.

Some of them are fixed with. For example, Windows uses internally a 16 bit character encoding, also from the time when people thought that Unicode 1.0 would solve the worlds writing problems. This is not only limited, but also wasteful: When writing western text, every other byte in Windows Unicode text is a null byte.

TLDR

We learned:

• Your programming languages utf8 is called utf8mb4 in MySQL.
• utf8 and utf8mb4 sort differently due to changes in the Unicode Collation Algorithm (UCA).
• Indexes are physically materialized sort orders of column sets. They can become pretty large.
• MySQL never changes sort orders of character sets (collations), even if they are buggy. Instead a new collation with a new name is created.
• That is, because changing a sort order may require dropping and recreating an index, which is expensive if the index is large.
• Sorting things can be done in many different ways, depending on data set size, column size and other considerations.
• Implementation details can make sorting even more complicated.
• MySQL 8 is a worthwhile upgrade.
• Databases manage state so you don’t have to. If you think databases are complicated, consider what you would have to do if the database and your DBA would not be there for you.

orchestrator is a MySQL high availability and replication management tool, runs as a service and provides command line access, HTTP API and Web interface. orchestrator supports:

When you insert data into a database and run COMMIT you expect things to be there: Atomically, Consistent, Isolated and Durable, like Codd commanded us 40 years ago, but also quickly. There is a surprising amount of sophistication being poured into this, but since I do not want to shame MongoDB and Redis developers in this post, I am not going to talk about that much in this place.

We are instead trying to understand what our databases are doing all day, from the point of view of the storage stack.

Piwigo ist eine Open-Source Fotogallerie für das Web. Entwickelt für Organisationen, Teams und Privatpersonen.

Some months ago, Shlomi Noah published a series about Service Discovery. In his posts, Shlomi describes many ways for an application to find the master. He also gives detail on how these solutions cope with failover to a slave, including their integration with Orchestrator.

This is a great series, and I recommend its reading for everybody implementing master failover, with or without Orchestrator, and even if you are not fully automating the process yet. Taking a step back, I realized that service discovery is only one of the five parts of a full MySQL Master Failover Strategy, and this post is about these five parts. In some follow-up posts, I might analyze some deployments using the framework presented in this post.

This is the first in a series of posts reviewing methods for MySQL master discovery: the means by which an application connects to the master of a replication tree. Moreover, the means by which, upon master failover, it identifies and connects to the newly promoted master.

These posts are not concerned with the manner by which the replication failure detection and recovery take place. I will share orchestrator specific configuration/advice, and point out where cross DC orchestrator/raft setup plays part in discovery itself, but for the most part any recovery tool such as MHA, replication-manager, severalnines or other, is applicable.

We discuss asynchronous (or semi-synchronous) replication, a classic single-master-multiple-replicas setup. A later post will briefly discuss synchronous replication (Galera/XtraDB Cluster/InnoDB Cluster).

We usually take care of things we value, whether it is an expensive smartphone or the company’s servers. Data is one of the most important assets of the organisation, and although we do not see it, it has to be carefully protected. We implement data at rest encryption to encrypt database files or whole volumes which contain our data. We implement data in transit encryption using SSL to make sure no one can sniff and collect data sent across networks. Backups are no different. No matter if this is a full backup or incremental, it will store at least a part of your data. As such, backups have to be encrypted too. In this blog post, we will look into some options you may have when it comes to encrypting backups. First though, let’s look at how you can encrypt your backups and what could be use cases for those methods.

DBdeployer is a tool that deploys MySQL database servers easily. This is a port of MySQL-Sandbox, originally written in Perl, and re-designed from the ground up in Go. See the features comparison for more detail.

Hybrid replication, i.e. combining Galera and asynchronous MySQL replication in the same setup, became much easier since GTID got introduced in MySQL 5.6. Although it was fairly straightforward to replicate from a standalone MySQL server to a Galera Cluster, doing it the other way round (Galera → standalone MySQL) was a bit more challenging. At least until the arrival of GTID.

Yes, it does! In this blog post, I am going to share my recent experiences with ProxySQL and how important the database software version number can be.

How do MySQL tables get corrupted? There are many ways to spoil data files. Often, corruption is due to defects in the underlying platform, which MySQL relies on to store and retrieve data - disk subsystem, controllers, communication channels, drivers, firmware or other hardware faults. Data corruption can also occur if the MySQL server daemon restarts suddenly, or your server reboots due to a crash of other OS components. If the database instance was in the middle of writing data to disk, it could write the data partially which may end up with a page checksum that is different than expected. There have also been bugs in MySQL so even if the server hardware is ok, MySQL itself can cause corruption.

Usually when MySQL data gets corrupted the recommendation is to restore it from the last backup, switch to DR server or take down the affected node if you have Galera cluster to serve data immediately from other nodes. In some cases you can't - if the backup is not there, the cluster was never set up, your replication is down for a very long time, or the DR procedure was never tested. Even if you have a backup, you may still want to take some actions to attempt recovery as it may take less time get back online.

utf8mb4In this blog post, we’ll look at options for migrating database charsets to utf8mb4.

Migrating charsets, in my opinion, is one of the most tedious tasks in a DBA’s life. There are so many things involved that can screw up our data, making it work is always hard. Sometimes what seems like a trivial task can become a nightmare very easily, and keeps us working for longer than expected.

Decode mysql replication issues with mysqlbinlog