Dear Kris,

We have a number of old MySQL instances, version 5.5 and 5.6. We want to upgrade them to a current version of MySQL. The databases are between 0.5 TB to 8 TB in size.

Unfortunately, using rsync on a stopped MySQL instance is not an option because the versions are too different, and the new version seems unable to read the binary data. I could use a dump (multi-threaded), but importing a single file with mysql takes forever (it would need to be single-threaded).

Would you simply create a dump per table and then import them in parallel? Or has MySQL improved, allowing for better import methods now?

That is a lot to unpack.

A consistent backup and a binlog position

First off, a backup needs to be consistent. You cannot make a backup of individual tables, one after the …

How do you run non-blocking schema changes in MySQL? This is an eternal question. With a plethora of 3rd party solutions and with recent advancements in MySQL, it's difficult to track which solution is preferable for a given schema migration. In this post, we provide a high level overview of the state of MySQL online schema migrations in 2024. We limit the discussion to ALTER TABLE statements, as other DDL statements are typically fast (DROP TABLE is somewhat of an exception, but out of scope of this post). We'll first examine the native MySQL options: INPLACE and INSTANT. For reference, see Online DDL Operations MySQL 8.0 documentation. INPLACE, aka InnoDB Online DDL This is MySQL's first take on non-blocking schema changes. Some types of ALTER TABLE (see above link for exhaustive list of supported changes) are eligible to run with ALGORITHM=INPLACE. An INPLACE schema change is technically non-blocking, with quite a few caveats: On the server where …

pt-online-schema-change is an amazing tool for assisting in table modifications in cases where ONLINE ALTER is not an option. But if you have foreign keys, this could be an interesting and important read for you. Tables with foreign keys are always complicated, and they have to be handled with care. The use case I am […]

Introduction # I recently encountered an intriguing bug. A user reported that their query was causing vtgate to fetch a large amount of data, sometimes resulting in an Out Of Memory (OOM) error. For a deeper understanding of grouping and aggregations on Vitess, I recommend reading this prior blog post. The Query # The problematic query was: selectsum(user.type)fromuserjoinuser_extraonuser.team_id=user_extra.idgroupbyuser_extra.idorderbyuser_extra.id;The planner was unable to delegate aggregation to MySQL, leading to the fetching of a significant amount of data for aggregation.

Recently MySQL added the ability to write Stored Programs in JavaScript.  This allows developers to leverage JavaScript capabilities for complex data processing and business logic within the database server. We think this is really cool. Here’s why.

MySQL 9.0 is an Innovation Release, but as you may have read in my Quick Peek, I found little innovation. However, one new item caught my eye, and it could be a way to track query performance. MySQL 9.0 EXPLAIN enhancement Here is the syntax that will be referenced in the post. {EXPLAIN | DESCRIBE […]

In this article, we will demonstrate how to have a single MySQL database user account that can connect from specific hosts. We would usually implement it by creating separate user accounts with the same username but different hosts/IPs like <USER>@<HOST1>, <USER>@<HOST2> …. <USER>@<HOSTn>. Then, give those users the same grants(privileges/roles) and settings(password, SSL, etc). Instead […]

Regular expressions are a powerful tool for developers. In this post we will demonstrate how to use regular expressions subexpressions in MySQL

MySQL is certainly a powerful open source database management system, but even the most robust engine struggles when queries take an eternity to execute. For DBAs and developers, improving MySQL query performance is an ongoing goal. Efficient query performance is crucial for ensuring the smooth operation and optimal user experience of applications powered by MySQL […]

Regular expressions are a powerful tool for developers. In this post we re-examine a solution from a previous post to see if we can streamline the query and use a pure regex solution.