MySQL and MariaDB are two major open source database management systems that share a common codebase and history. MariaDB started as a MySQL fork in 2009 to provide an alternate database version following Oracle’s acquisition of MySQL.While MariaDB has many features and has grown in popularity among users seeking a more open and community-driven development […]
May
23
2024
23
2024
--
How to Migrate From MariaDB to MySQL
May
16
2024
16
2024
--
Choosing the Right Database: Comparing MariaDB vs. MySQL, PostgreSQL, and MongoDB
Selecting the appropriate database technology for your needs is crucial for ensuring compatibility with existing applications and infrastructure, optimizing application performance, and reducing costs. With so many different choices available, it can be difficult to make such a decision!This blog post provides a head-to-head comparison of MariaDB vs. PostgreSQL, MongoDB, and MySQL, highlighting their similarities […]
Nov
01
2023
01
2023
--
Is ANALYZE TABLE Safe on a Busy MySQL Database Server?
Sometimes, there is a need to update the table and index statistics manually using the ANALYZE TABLE command. Without going further into the reasons for such a need, I wanted to refresh this subject in terms of overhead related to running the command on production systems. However, the overhead discussed here is unrelated to the usual cost of diving into table rows to gather statistics, which we can control by setting the number of sample pages.
Five years ago, my colleague Sveta posted a nice blog post about an improvement introduced in Percona Server for MySQL to address unnecessary stalls related to running the command:
ANALYZE TABLE Is No Longer a Blocking Operation
Historically, the problem with running the ANALYZE TABLE command in MySQL was that the query needed an exclusive lock on the table definition cache entry for the table. This makes the query wait for any long-running queries to finish but also can trigger cascading waiting for other incoming requests. In short, ANALYZE could lead to nasty stalls in busy production environments.
A lot has changed since then, but many production systems alive today still run with affected versions. Let’s recap how the situation has evolved over the years.
MySQL Server – Community Edition
The problem applies to all versions of the upstream MySQL Community up to 8.0.23. There were no improvements in the 5.7 series (btw, EOL will be reached this month!), which means even the latest 5.7.43 is affected. Here is an example scenario you may end up here:
mysql > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 5.7.43 | MySQL Community Server (GPL) | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql > show processlist; +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ | 4 | msandbox | localhost | db1 | Query | 54 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | | 13 | msandbox | localhost | db1 | Query | 29 | Waiting for table flush | analyze table sbtest1 | | 17 | msandbox | localhost | db1 | Query | 0 | starting | show processlist | | 18 | msandbox | localhost | db1 | Query | 15 | Waiting for table flush | select * from sbtest1 where id=100 | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+ 4 rows in set (0.00 sec)
One long query made the ANALYZE wait, but another, normally very fast query, is now waiting, too.
The same situation may happen in MySQL 8.0 series, including 8.0.23. Fortunately, there was a fix in version 8.0.24 addressing this problem. We can only read a bit restrained comment in the release notes about the “wait eliminated”:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html
Indeed, since version 8.0.24, a similar test during a long-running query results in instant query execution:
mysql > select @@version,@@version_comment; +-----------+------------------------------+ | @@version | @@version_comment | +-----------+------------------------------+ | 8.0.24 | MySQL Community Server - GPL | +-----------+------------------------------+ 1 row in set (0.00 sec) mysql > analyze table sbtest1; +-------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------+---------+----------+----------+ | db1.sbtest1 | analyze | status | OK | +-------------+---------+----------+----------+ 1 row in set (0.00 sec)
However, we can still find a warning in the official documentation, even for the 8.1 version, like this:
ANALYZE TABLE removes the table from the table definition cache, which requires a flush lock. If there are long running statements or transactions still using the table, subsequent statements and transactions must wait for those operations to finish before the flush lock is released. Because ANALYZE TABLE itself typically finishes quickly, it may not be apparent that delayed transactions or statements involving the same table are due to the remaining flush lock.
I requested an update of the related bug report as well as the documentation problem accordingly:
https://bugs.mysql.com/bug.php?id=87065
https://bugs.mysql.com/bug.php?id=112670
Percona Server for MySQL
As mentioned above, Percona introduced a fix and removed unnecessary table definition cache lock as a result of solving this bug report:
https://jira.percona.com/browse/PS-2503
When using the Percona variant, running ANALYZE TABLE was safe already since versions 5.6.38 and 5.7.20, as these were the active development series at the time. You may read the announcement in the release notes here:
https://docs.percona.com/percona-server/5.7/release-notes/Percona-Server-5.7.20-18.html#bugs-fixed
Percona Server for MySQL version 8.0 has been free from the issue since the very first release (I tested back, including the first GA release 8.0.13-3), as the improvement was merged from the Percona Server for MySQL 5.7 series.
MariaDB server
The locking ANALYZE TABLE problem applies to all MariaDB versions up to 10.5.3. In version 10.5.4, the solution from Percona was implemented as described in the following report:
https://jira.mariadb.org/browse/MDEV-15101
Therefore, when you run the query in 10.5.3 or lower, and in any previous series, like even the latest 10.4.31, a similar situation may occur:
mysql > select @@version,@@version_comment; +----------------+-------------------+ | @@version | @@version_comment | +----------------+-------------------+ | 10.5.3-MariaDB | MariaDB Server | +----------------+-------------------+ 1 row in set (0.000 sec) mysql > show processlist; +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ | 4 | msandbox | localhost | db1 | Query | 18 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | 0.000 | | 13 | msandbox | localhost | db1 | Query | 16 | Waiting for table flush | analyze table sbtest1 | 0.000 | | 14 | msandbox | localhost | db1 | Query | 14 | Waiting for table flush | select * from sbtest1 where id=100 | 0.000 | | 15 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | 0.000 | +----+----------+-----------+------+---------+------+-------------------------+----------------------------------------------------------------+----------+ 4 rows in set (0.000 sec) mysql > select @@version,@@version_comment; +-----------------+-------------------+ | @@version | @@version_comment | +-----------------+-------------------+ | 10.4.31-MariaDB | MariaDB Server | +-----------------+-------------------+ 1 row in set (0.000 sec) mysql > show processlist; +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | InnoDB purge coordinator | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | InnoDB purge worker | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB shutdown handler | NULL | 0.000 | | 9 | msandbox | localhost | db1 | Query | 18 | Sending data | select avg(k) from sbtest1 where pad not like '%f%' group by c | 0.000 | | 18 | msandbox | localhost | db1 | Query | 16 | Waiting for table flush | analyze table sbtest1 | 0.000 | | 19 | msandbox | localhost | db1 | Query | 12 | Waiting for table flush | select * from sbtest1 where id=100 | 0.000 | | 22 | msandbox | localhost | NULL | Query | 0 | Init | show processlist | 0.000 | +----+-------------+-----------+------+---------+------+--------------------------+----------------------------------------------------------------+----------+ 9 rows in set (0.000 sec)
Summary
As long as your database runs on the most recent version of MySQL or MariaDB variant, running ANALYZE TABLE should be absolutely safe and not cause any unexpected stalls.
Users of all three major Percona Server for MySQL series – 5.6.38+, 5.7.20+, and 8.0.x are all safe.
When, for any reason, you are not able to upgrade the community variant to the latest MySQL 8.0.24+ version yet and have to stick with 5.6 or 5.7 series for now, you may just swap MySQL Community binaries to Percona Server for MySQL ones, which are 100% compatible yet free from the problem. At the same time, you may check our post-EOL support for Percona Server for MySQL 5.7.
MariaDB users must upgrade to 10.5.4 or later to avoid the locking problem.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Oct
31
2023
31
2023
--
Resolving Data Drift in a Dual-Primary Topology With Replica in MySQL/MariaDB
Hello friends,
In Managed Services, we have the opportunity to see different technologies and various topologies, which makes the work fascinating and challenging at the same time.
This time, I’m going to tell you about a particular case: a client with a dual-primary topology plus a replica, as detailed below:
PS-primary-1=192.168.0.14 [RW] |___ PS-primary-2=192.168.0.59 [RW] (Slave_delay: 0) |___ PS-replica-1=192.168.0.99 [R] (Slave_delay: 0)
[RW] means Read/Write access.
[R] means Read Only access.
We received an alert of this kind on PS-primary-2:
Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Update_rows event on table foo.persons; Can't find record in 'persons', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000360, end_log_pos 58177
Seconds later, we noticed that someone (not us) resolved the replication issue, possibly by skipping the error in some way (using pt-slave-restart) or simply executing something like:
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Or by injecting an empty transaction.
(Disclaimer: We won’t deeply analyze the root cause here, but rather the surprise we found during the analysis.)
Having seen this, the most advisable action is to run pt-table-checksum, and if any differences appear, use pt-table-sync to fix the data drift.
Therefore, we executed the pt-table-checksum in the following way:
$ pt-table-checksum h=192.168.0.14 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T12:40:31 0 1 6 1 1 0 0.193 foo.persons
(Second disclaimer: We have populated the table with a small amount of data to simplify and improve visibility in this case. With larger volumes, the same scenario also applies.)
(Credentials, for security reasons, are stored in the file /home/user/.my.cnf, which is why you don’t see them in any executed command. If you want to know how to create it, here’s an example)
At a glance, there seem to be differences. Let’s check the other primary instance and the replica as well.
PS-primary-2> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 08:40:31 | foo | persons | 5 | 1 | +---------------------+-----+---------+------------+--------+ 1 row in set (0.00 sec) PS-replica-1> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 09:53:10 | foo | persons | 4 | 1 | +---------------------+-----+---------+------------+--------+ 1 row in set (0.00 sec)
The next step is to execute pt-table-sync with the –print option to review what the utility will actually do. The following output appears:
$ pt-table-sync --print h=192.168.0.59 --sync-to-master --database=foo --table=persons --replicate percona.checksums
DELETE FROM `foo`.`persons` WHERE `personid`='3' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
A DELETE statement has appeared. How is it possible?
As this is a dual-primary topology, it is advisable to perform a checksum on the other primary server as well, which is PS-primary-2. This will help identify any discrepancies and ensure data consistency between both primary servers.
$ pt-table-checksum h=192.168.0.59 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T13:08:12 0 1 5 1 1 0 0.147 foo.persons
That’s when we notice that one server has six rows, and the other has four. How is it possible? Let’s review:
PS-primary-1 (none)> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 10:03:00 | foo | persons | 6 | 1 | +---------------------+-----+---------+------------+--------+ PS-replica-1 (none)> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 10:03:00 | foo | persons | 6 | 1 | +---------------------+-----+---------+------------+--------+
Let’s see what data the table currently contains:
$ for SERVE in 192.168.0.14 192.168.0.59 192.168.0.99; do echo ; echo $SERVE ; mysql -h $SERVE -e "select * from foo.persons"; done 192.168.0.14 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 | | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | | 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+ 192.168.0.59 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+ 192.168.0.99 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+
As you can see, there is data drift everywhere.
And how can this be possible? I’ll enumerate several possibilities, and it is not an exhaustive list
Data drift and integrity of replica data is complex. Let’s outline some common ways this can happen (generally speaking):
- A statement is executed on a primary with: SET SESSION sql_log_bin = OFF
- A statement was executed directly on the replica
- This can happen if the replica was not in super_read_only and a super user executed
- This can happen if the replica was not in read_only
- A statement was executed on a replica, and the replica was later promoted to a primary without GTID in place
- A primary server is not configured for full ACID compliance, and it crashed
- At some point, the primary was not configured for row-based replication (even briefly)
These are the most common ways that data drift occurs, but certainly not the only ways that it can happen. More exotic cases can involve bugs, engine differences, version differences, etc.
Coming back to the issue, if we run pt-table-sync targeting the second primary instance, we will see the following result:
$ pt-table-sync --print h=192.168.0.14 --sync-to-master --database=foo --table=persons --replicate percona.checksums
DELETE FROM `foo`.`persons` WHERE `personid`='1' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
DELETE FROM `foo`.`persons` WHERE `personid`='7' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
Here, the output is less promising: it suggests deleting the rows with ID 1 and 7, and performing a REPLACE on ID 3.
Therefore, note that it asks us to delete rows, which is not correct. So, what to do? After a discussion with the customer (we’ve shared with them all that we gathered), we agreed on the following:
- We need to add the row with ID=3 to the instances where it’s missing.
- We need to update the row with ID=1 in the instances where it’s missing.
- We need to add the row with ID=7 to the instances where it’s missing.
- We DO NOT need to delete any rows; no DELETES are necessary.
Important note: The client informed us that the data that exists only in one instance and not in the others is necessary. The client also verified that the actions we proposed to them were correct. Remember: we, as DBAs, are the guardians of the data and its integrity, but not necessarily the owners, let alone know the content for ethical reasons.
How do we achieve this, then?
Unfortunately, in this case, using pt-table-sync is not recommended since, as we have seen with the –print option, it would perform DELETES in both cases, regardless of which primary instance we consider as the source of truth.
We also cannot execute the REPLACE on PS-primary-1 because the REPLACE command follows a specific logic explained here
MySQL uses the following algorithm for REPLACE (and LOAD DATA … REPLACE):
- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table
That means that if we execute a REPLACE on PS-primary-1 where the row with ID 7 does not exist but does exist in PS-primary-2, it will result in a duplicate key error, which I’ll show you below (as the row already exists):
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 09:20:13');
Query OK, 1 row affected (0.01 sec)
We connected to PS-primary-2, and indeed, we found the duplicate key error:
PS-primary-2 (none)> PAGER grep "Last_Err"; SHOW SLAVE STATUSG
PAGER set to 'grep "Last_Err"'
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table foo.persons; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000359, end_log_pos 26840
1 row in set (0.01 sec)
The commands we should execute would be the following:
set sql_log_bin=0;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
However, as we observed, the first one will fail due to a duplicate key. So, how do we resolve this? The safest option, in my humble opinion, would be to execute the three REPLACE statements on each of the instances by first invoking this command:
PS-primary-1 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > Bye
PS-primary-2 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.00 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-2 > Bye
PS-replica-1 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > set global super_read_only=1; set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
Finally, we verify that all instances have the same data, either with the SELECT statement or the pt-table-checksum command.
$ for SERVE in 192.168.0.14 192.168.0.59 192.168.0.99; do echo ; echo $SERVE ; mysql -h $SERVE -e "select * from foo.persons"; done
192.168.0.14
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
192.168.0.59
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
192.168.0.99
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
$ pt-table-checksum h=192.168.0.14 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T14:12:32 0 0 7 0 1 0 0.185 foo.persons
$ pt-table-checksum h=192.168.0.59 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T14:12:50 0 0 7 0 1 0 0.148 foo.persons
Conclusion
Maintaining this type of topology (dual primaries) can be a headache, and they are prone to such problems. Therefore, resolving them is not always as simple as it may seem. The tools pt-table-checksum and pt-table-sync are essential for resolving these issues in most cases and for efficient verification, as in this case.
Remember that if you have any questions or concerns, you can always contact us.
Lastly, don’t forget to run the pt-table-sync with the –print option to verify and share with the client or data owner whether the data to be corrected and the proposed corrections seem appropriate. Always.
Let’s stay synchronized, and I’ll see you in the next blog!
Oct
31
2023
31
2023
--
Resolving Data Drift in a Dual-Primary Topology With Replica in MySQL/MariaDB
Hello friends,
In Managed Services, we have the opportunity to see different technologies and various topologies, which makes the work fascinating and challenging at the same time.
This time, I’m going to tell you about a particular case: a client with a dual-primary topology plus a replica, as detailed below:
PS-primary-1=192.168.0.14 [RW] |___ PS-primary-2=192.168.0.59 [RW] (Slave_delay: 0) |___ PS-replica-1=192.168.0.99 [R] (Slave_delay: 0)
[RW] means Read/Write access.
[R] means Read Only access.
We received an alert of this kind on PS-primary-2:
Last_SQL_Errno: 1032 Last_SQL_Error: Could not execute Update_rows event on table foo.persons; Can't find record in 'persons', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000360, end_log_pos 58177
Seconds later, we noticed that someone (not us) resolved the replication issue, possibly by skipping the error in some way (using pt-slave-restart) or simply executing something like:
mysql> STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
Or by injecting an empty transaction.
(Disclaimer: We won’t deeply analyze the root cause here, but rather the surprise we found during the analysis.)
Having seen this, the most advisable action is to run pt-table-checksum, and if any differences appear, use pt-table-sync to fix the data drift.
Therefore, we executed the pt-table-checksum in the following way:
$ pt-table-checksum h=192.168.0.14 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T12:40:31 0 1 6 1 1 0 0.193 foo.persons
(Second disclaimer: We have populated the table with a small amount of data to simplify and improve visibility in this case. With larger volumes, the same scenario also applies.)
(Credentials, for security reasons, are stored in the file /home/user/.my.cnf, which is why you don’t see them in any executed command. If you want to know how to create it, here’s an example)
At a glance, there seem to be differences. Let’s check the other primary instance and the replica as well.
PS-primary-2> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 08:40:31 | foo | persons | 5 | 1 | +---------------------+-----+---------+------------+--------+ 1 row in set (0.00 sec) PS-replica-1> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 09:53:10 | foo | persons | 4 | 1 | +---------------------+-----+---------+------------+--------+ 1 row in set (0.00 sec)
The next step is to execute pt-table-sync with the –print option to review what the utility will actually do. The following output appears:
$ pt-table-sync --print h=192.168.0.59 --sync-to-master --database=foo --table=persons --replicate percona.checksums
DELETE FROM `foo`.`persons` WHERE `personid`='3' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.14 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.59 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:1519 user:percona host:localhost.localdomain*/;
A DELETE statement has appeared. How is it possible?
As this is a dual-primary topology, it is advisable to perform a checksum on the other primary server as well, which is PS-primary-2. This will help identify any discrepancies and ensure data consistency between both primary servers.
$ pt-table-checksum h=192.168.0.59 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T13:08:12 0 1 5 1 1 0 0.147 foo.persons
That’s when we notice that one server has six rows, and the other has four. How is it possible? Let’s review:
PS-primary-1 (none)> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 10:03:00 | foo | persons | 6 | 1 | +---------------------+-----+---------+------------+--------+ PS-replica-1 (none)> SELECT max(ts), db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM percona.checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl; +---------------------+-----+---------+------------+--------+ | max(ts) | db | tbl | total_rows | chunks | +---------------------+-----+---------+------------+--------+ | 2023-07-28 10:03:00 | foo | persons | 6 | 1 | +---------------------+-----+---------+------------+--------+
Let’s see what data the table currently contains:
$ for SERVE in 192.168.0.14 192.168.0.59 192.168.0.99; do echo ; echo $SERVE ; mysql -h $SERVE -e "select * from foo.persons"; done 192.168.0.14 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 | | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | | 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+ 192.168.0.59 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+ 192.168.0.99 +----------+-----------+-----------+---------+------------+---------------------+ | PersonID | LastName | FirstName | Address | City | Insert_timestamp | +----------+-----------+-----------+---------+------------+---------------------+ | 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 | | 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 | | 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 | | 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 | +----------+-----------+-----------+---------+------------+---------------------+
As you can see, there is data drift everywhere.
And how can this be possible? I’ll enumerate several possibilities, and it is not an exhaustive list
Data drift and integrity of replica data is complex. Let’s outline some common ways this can happen (generally speaking):
- A statement is executed on a primary with: SET SESSION sql_log_bin = OFF
- A statement was executed directly on the replica
- This can happen if the replica was not in super_read_only and a super user executed
- This can happen if the replica was not in read_only
- A statement was executed on a replica, and the replica was later promoted to a primary without GTID in place
- A primary server is not configured for full ACID compliance, and it crashed
- At some point, the primary was not configured for row-based replication (even briefly)
These are the most common ways that data drift occurs, but certainly not the only ways that it can happen. More exotic cases can involve bugs, engine differences, version differences, etc.
Coming back to the issue, if we run pt-table-sync targeting the second primary instance, we will see the following result:
$ pt-table-sync --print h=192.168.0.14 --sync-to-master --database=foo --table=persons --replicate percona.checksums
DELETE FROM `foo`.`persons` WHERE `personid`='1' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
DELETE FROM `foo`.`persons` WHERE `personid`='7' LIMIT 1 /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10') /*percona-toolkit src_db:foo src_tbl:persons src_dsn:P=3306,h=192.168.0.59 dst_db:foo dst_tbl:persons dst_dsn:h=192.168.0.14 lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:5732 user:percona host:localhost.localdomain*/;
Here, the output is less promising: it suggests deleting the rows with ID 1 and 7, and performing a REPLACE on ID 3.
Therefore, note that it asks us to delete rows, which is not correct. So, what to do? After a discussion with the customer (we’ve shared with them all that we gathered), we agreed on the following:
- We need to add the row with ID=3 to the instances where it’s missing.
- We need to update the row with ID=1 in the instances where it’s missing.
- We need to add the row with ID=7 to the instances where it’s missing.
- We DO NOT need to delete any rows; no DELETES are necessary.
Important note: The client informed us that the data that exists only in one instance and not in the others is necessary. The client also verified that the actions we proposed to them were correct. Remember: we, as DBAs, are the guardians of the data and its integrity, but not necessarily the owners, let alone know the content for ethical reasons.
How do we achieve this, then?
Unfortunately, in this case, using pt-table-sync is not recommended since, as we have seen with the –print option, it would perform DELETES in both cases, regardless of which primary instance we consider as the source of truth.
We also cannot execute the REPLACE on PS-primary-1 because the REPLACE command follows a specific logic explained here
MySQL uses the following algorithm for REPLACE (and LOAD DATA … REPLACE):
- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table
That means that if we execute a REPLACE on PS-primary-1 where the row with ID 7 does not exist but does exist in PS-primary-2, it will result in a duplicate key error, which I’ll show you below (as the row already exists):
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 09:20:13');
Query OK, 1 row affected (0.01 sec)
We connected to PS-primary-2, and indeed, we found the duplicate key error:
PS-primary-2 (none)> PAGER grep "Last_Err"; SHOW SLAVE STATUSG
PAGER set to 'grep "Last_Err"'
Last_Errno: 1062
Last_Error: Could not execute Write_rows event on table foo.persons; Duplicate entry '3' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000359, end_log_pos 26840
1 row in set (0.01 sec)
The commands we should execute would be the following:
set sql_log_bin=0;
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
However, as we observed, the first one will fail due to a duplicate key. So, how do we resolve this? The safest option, in my humble opinion, would be to execute the three REPLACE statements on each of the instances by first invoking this command:
PS-primary-1 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-1 > Bye
PS-primary-2 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.00 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-2 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-primary-2 > Bye
PS-replica-1 > set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('3', 'Colonel', 'Vil', 'PER', 'Lima', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('1', 'Iglesias', 'Par', 'IND', 'Jaipur', '2023-07-27 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > REPLACE INTO `foo`.`persons`(`personid`, `lastname`, `firstname`, `address`, `city`, `insert_timestamp`) VALUES ('7', 'Ewd', 'Woo', 'CAN', 'Vancouver', '2023-07-28 07:10:10');
Query OK, 1 row affected (0.01 sec)
PS-replica-1 > set global super_read_only=1; set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
Finally, we verify that all instances have the same data, either with the SELECT statement or the pt-table-checksum command.
$ for SERVE in 192.168.0.14 192.168.0.59 192.168.0.99; do echo ; echo $SERVE ; mysql -h $SERVE -e "select * from foo.persons"; done
192.168.0.14
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
192.168.0.59
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
192.168.0.99
+----------+-----------+-----------+---------+------------+---------------------+
| PersonID | LastName | FirstName | Address | City | Insert_timestamp |
+----------+-----------+-----------+---------+------------+---------------------+
| 1 | Iglesias | Par | IND | Jaipur | 2023-07-27 07:10:10 |
| 2 | Rooster | War | IND | Tamil Nadu | 2023-07-27 07:10:10 |
| 3 | Colonel | Vil | PER | Lima | 2023-07-27 07:10:10 |
| 4 | SoS | Syl | CAN | Ontario | 2023-07-27 07:10:10 |
| 5 | Al-lot | Pat | IND | Delhi | 2023-07-27 07:10:10 |
| 6 | The Crazy | Vai | IND | Vadodara | 2023-07-27 07:10:10 |
| 7 | Ewd | Woo | CAN | Vancouver | 2023-07-28 07:10:10 |
+----------+-----------+-----------+---------+------------+---------------------+
$ pt-table-checksum h=192.168.0.14 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T14:12:32 0 0 7 0 1 0 0.185 foo.persons
$ pt-table-checksum h=192.168.0.59 --port=3306 --no-check-binlog-format --no-check-replication-filters --replicate percona.checksums --recursion-method=hosts --max-load Threads_running=50 --max-lag=100 --databases=foo --tables=persons
Checking if all tables can be checksummed ...
Starting checksum ...
TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE
07-28T14:12:50 0 0 7 0 1 0 0.148 foo.persons
Conclusion
Maintaining this type of topology (dual primaries) can be a headache, and they are prone to such problems. Therefore, resolving them is not always as simple as it may seem. The tools pt-table-checksum and pt-table-sync are essential for resolving these issues in most cases and for efficient verification, as in this case.
Remember that if you have any questions or concerns, you can always contact us.
Lastly, don’t forget to run the pt-table-sync with the –print option to verify and share with the client or data owner whether the data to be corrected and the proposed corrections seem appropriate. Always.
Let’s stay synchronized, and I’ll see you in the next blog!
Sep
15
2023
15
2023
--
Convert MariaDB Binary Log File and Position-Based Replication to GTID Replication
At Percona Managed Services, we manage Percona Server for MySQL, Community MySQL, and MariaDB. There are slight differences when configuring and managing MariaDB GTID replication. In this blog, we’ll show you how to convert MariaDB binary log file and position-based replication to GTID replication.
In my lab, we have two test nodes; both servers will have Debian 11 and MariaDB10.5.19 with mariabackup installed.
The PRIMARY server is deb11m8m (IP: 192.168.56.190), and the REPLICA server name is deb11m8s (IP: 192.168.56.191).
1. Install MariaDB on Debian 11 on PRIMARY and REPLICA
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup chmod +x ./mariadb_repo_setup ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19" apt-get install mariadb-server mariadb-backup systemctl daemon-reload root@deb11m8:~#wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup https://downloads.mariadb.com/MariaDB/mariadb_repo_setup downloads.mariadb.com (downloads.mariadb.com)|104.17.191.14|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 36188 (35K) [application/octet-stream] Saving to: ‘mariadb_repo_setup’ 2023-08-30 14:42:51 (26.0 MB/s) - ‘mariadb_repo_setup’ saved [36188/36188] root@deb11m8m:~# chmod +x ./mariadb_repo_setup root@deb11m8m:~# ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5.19" # [info] Checking for script prerequisites. # [warning] Found existing file at /etc/apt/sources.list.d/mariadb.list. Moving to /etc/apt/sources.list.d/mariadb.list.old_1 # [info] MariaDB Server version 10.5.19 is valid # [info] Repository file successfully written to /etc/apt/sources.list.d/mariadb.list # [info] Adding trusted package signing keys... # [info] Running apt-get update… # [info] Done adding trusted package signing keys root@deb11m8m:~# root@deb11m8m:~# apt-get install mariadb-server mariadb-backup Reading package lists... Done Building dependency tree... Done Reading state information... Done The following packages were automatically installed and are no longer required: libconfig-inifiles-perl libmecab2 libopengl0 Use 'apt autoremove' to remove them. The following additional packages will be installed: libdbd-mariadb-perl libmariadb3 mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server-10.5 mariadb-server-core-10.5 Suggested packages: mailx mariadb-test netcat-openbsd TSelecting previously unselected package mariadb-client-core-10.5. Preparing to unpack .../mariadb-client-core-10.5_1%3a10.5.19+maria~deb11_amd64.deb ... Unpacking mariadb-client-core-10.5 (1:10.5.19+maria~deb11) ... …… Setting up mariadb-server (1:10.5.19+maria~deb11) ... Processing triggers for man-db (2.9.4-2) ... Processing triggers for libc-bin (2.31-13+deb11u6) ... root@deb11m8m:~#
1.1 Reset root user password and create a replication user
MariaDB [(none)]>Alter user 'root'@'localhost' identified by ‘#######’; MariaDB [(none)]>GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION ; MariaDB [(none)]flush privileges; MariaDB [(none)]CREATE USER 'repl'@'%' IDENTIFIED BY ‘#######’; MariaDB [(none)]GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%'; MariaDB [(none)]flush privileges;
1.2 Configure my.cnf on PRIMARY and REPLICA
PRIMARY
[mysqld] pid-file = /run/mysqld/mysqld.pid basedir = /usr server_id=1 bind-address=192.168.56.190 log-bin = /var/lib/mysql/bin_log expire_logs_days = 10 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci
REPLICA [mysqld] pid-file = /run/mysqld/mysqld.pid basedir = /usr <b>server_id=2</b> <b>bind-address=192.168.56.191</b> log-bin = /var/lib/mysql/bin_log expire_logs_days = 10 character-set-server = utf8mb4 collation-server = utf8mb4_general_ci
2. Let’s set up binary log and position-based replication between PRIMARY and REPLICA with the stream copy method.
2.1 on REPLICA
root@deb11m8s: nc -l -p 2222 | mbstream -x -C /var/lib/mysql
2.2 on PRIMARY
root@deb11m8m:/var/lib/mysql# mariabackup --stream=mbstream --parallel=4 --backup | nc -w 2 192.168.56.191 2222 [00] 2023-08-30 18:02:08 Connecting to server host: localhost, user: root, password: set, port: not set, socket: /run/mysqld/mysqld.sock [00] 2023-08-30 18:02:08 Using server version 10.5.19-MariaDB-1:10.5.19+maria~deb11-log mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64) [00] 2023-08-30 18:02:08 uses posix_fadvise(). [00] 2023-08-30 18:02:08 cd to /var/lib/mysql/ [00] 2023-08-30 18:02:08 open files limit requested 0, set to 1024 [00] 2023-08-30 18:02:08 mariabackup: using the following InnoDB configuration: [00] 2023-08-30 18:02:08 innodb_data_home_dir = [00] 2023-08-30 18:02:08 innodb_data_file_path = ibdata1:12M:autoextend [00] 2023-08-30 18:02:08 innodb_log_group_home_dir = ./ [00] 2023-08-30 18:02:08 InnoDB: Using Linux native AIO 2023-08-30 18:02:08 0 [Note] InnoDB: Number of pools: 1 [00] 2023-08-30 18:02:08 mariabackup: Generating a list of tablespaces [00] 2023-08-30 18:02:08 >> log scanned up to (10912597) [00] 2023-08-30 18:02:08 mariabackup: Starting 4 threads for parallel data files transfer [03] 2023-08-30 18:02:08 Streaming ./mysql/innodb_index_stats.ibd [04] 2023-08-30 18:02:08 Streaming ibdata1 …….. '/var/lib/mysql/xtrabackup_backupfiles/' [00] 2023-08-30 18:02:10 MySQL binlog position: filename 'bin_log.000002', position '326', GTID of the last change '' [00] 2023-08-30 18:02:10 Streaming backup-my.cnf [00] 2023-08-30 18:02:10 Streaming xtrabackup_info [00] 2023-08-30 18:02:10 Redo log (from LSN 10912585 to 10912597) was copied. [00] 2023-08-30 18:02:10 completed OK!
2.3 On REPLICA, get binlog information
<root@deb11m8s:/var/lib/mysql# cat xtrabackup_binlog_info bin_log.000002 326
2.4 On REPLICA, prepare the backup
root@deb11m8s:/var/lib/mysql# mariabackup --prepare --use-memory=1G --target-dir=/var/lib/mysql mariabackup based on MariaDB server 10.5.19-MariaDB debian-linux-gnu (x86_64) [00] 2023-08-30 18:08:32 cd to /var/lib/mysql/ [00] 2023-08-30 18:08:32 open files limit requested 0, set to 1024 . 2023-08-30 18:08:32 0 [Note] InnoDB: Initializing buffer pool, total size = 1073741824, chunk size = 1073741824 2023-08-30 18:08:32 0 [Note] InnoDB: Completed initialization of buffer pool 2023-08-30 18:08:32 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=10912585,10912585 [00] 2023-08-30 18:08:32 Last binlog file , position 0 [00] 2023-08-30 18:08:32 completed OK! root@deb11m8s:/var/lib/mysql#
2.5 Set up the replication
root@deb11m8s:/var/lib/mysql# chown -R mysql:mysql /var/lib/mysql root@deb11m8s:/var/lib/mysql# systemctl start mysql root@deb11m8s:/var/lib/mysql# mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 6 Server version: 10.5.19-MariaDB-0+deb11u2-log Debian 11 MariaDB [(none)]> stop slave; reset slave; reset slave all; reset master; Query OK, 0 rows affected, 1 warning (0.000 sec) Query OK, 0 rows affected (0.001 sec) Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.009 sec) ariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.56.190', MASTER_USER='repl', MASTER_PASSWORD='#######', MASTER_PORT=3306, MASTER_LOG_FILE='bin_log.000002', MASTER_LOG_POS=326, MASTER_CONNECT_RETRY=10; Query OK, 0 rows affected (0.022 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.190 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin_log.000002 Read_Master_Log_Pos: 326 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 553 Relay_Master_Log_File: bin_log.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes … Exec_Master_Log_Pos: 326 Relay_Log_Space: 863 …. Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec)
We have now set up binary log and position-based replication between the PRIMARY and REPLICA servers. Our next step is to convert this replication to GTID-based replication.
MariaDB and MySQL have different GTID implementations.
MariaDB GTID consists of three numbers separated with dashes ‘-‘. For example:
0-1-10: The first number, 0, is the domain ID, the second number is the server ID, and the third number is the sequence number.
MySQL GTID has two parts, source_id and transaction_id, separated by a colon character (:) .
e.g., 3E11FA47-71CA-11E1-9E33-C80AA9429562:23
The source_id is the source server’s server_uuid, and the transaction_id is the transaction sequence number committed on the source.
Let’s continue.
On PRIMARY, we enable GTID by running the below command.
MariaDB [(none)] SET GLOBAL gtid_domain_id = 1; MariaDB [(none)] set global gtid_strict_mode=1 ; And persist in my.cnf Verify GTID is enabled with below query MariaDB [(none)]> select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id; +-------------+----------------+-----------+--------------------+------------------+ | @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id | +-------------+----------------+-----------+--------------------+------------------+ | 1 | 192.168.56.190 | 1 | <b>1 | 1 | +-------------+----------------+-----------+--------------------+------------------+ 1 row in set (0.000 sec)</b>
ON REPLICA
MariaDB [(none)]>SET GLOBAL gtid_domain_id = 2; MariaDB [(none)]set global gtid_strict_mode=1 ; And persist in my.cnf Verify GTID is enabled with below query MariaDB [(none)]select @@server_id,@@bind_address,@@log_bin,@@gtid_strict_mode,@@gtid_domain_id; +-------------+----------------+-----------+--------------------+------------------+ | @@server_id | @@bind_address | @@log_bin | @@gtid_strict_mode | @@gtid_domain_id | +-------------+----------------+-----------+--------------------+------------------+ | 2 | 192.168.56.191 | 1 | 1 |2 |; +-------------+----------------+-----------+--------------------+------------------+ 1 row in set (0.000 sec)
On REPLICA, we need to stop replication and extract the values of Relay_Master_Log_File and Exec_Master_Log_Pos from the output.
MariaDB [test_db]STOP SLAVE; Query OK, 0 rows affected (0.007 sec) MariaDB [test_db] show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.56.190 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin_log.000002 Read_Master_Log_Pos: 1206 Relay_Log_File: mysqld-relay-bin.000007 Relay_Log_Pos: 553 Relay_Master_Log_File: bin_log.000002 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1206 Relay_Log_Space: 863 …….. Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec)
In our case, the Relay_Master_Log_File value is bin_log.000002, and the Exec_Master_Log_Pos is 1206.
On PRIMARY, we could use the below query to get the GTID position that corresponds to these binary log coordinates on PRIMARY.
MariaDB[test_db] SELECT BINLOG_GTID_POS('bin_log.000002', 1206),@@hostname;
+-----------------------------------------+------------+
| BINLOG_GTID_POS('bin_log.000002', 1206) | @@hostname |
+-----------------------------------------+------------+
| 1-1-1,0-1-4 |
+-----------------------------------------+------------+
1 row in set (0.000 sec)
We can now modify the replica configuration on the REPLICA server deb11m8s by executing the following statements:
MariaDB [test_db]SET GLOBAL gtid_slave_pos = '1-1-1,0-1-4'; MariaDB [test_db] CHANGE MASTER TO master_use_gtid=slave_pos; MariaDB [test_db]START SLAVE; MariaDB [test_db] show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.190 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: bin_log.000002 Read_Master_Log_Pos: 1391 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 910 Relay_Master_Log_File: bin_log.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1391 Relay_Log_Space: 1220 ……. Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 1-1-2,0-1-4 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 1 1 row in set (0.000 sec)
The SHOW SLAVE STATUS output Using_Gtid: Slave_Pos and Gtid_IO_Pos: 1-1-2,0-1-4 indicates that the slave uses GTIDs to track replication. We could use the above command to set up GTID replication on other replicas as well.
When running CHANGE MASTER TO master_use_gtid=slave_pos; and setting the MASTER_USE_GTID replication parameter, you have the option of enabling Global Transaction IDs to use either the current_pos or slave_pos values.
Using the value current_pos causes the replica to set its position based on the gtid_current_pos system variable, which is a union of gtid_binlog_pos and gtid_slave_pos. Using the value slave_pos causes the replica to instead set its position based on the gtid_slave_pos system variable. You may run into issues when you use the value current_pos if you write any local transactions on the replica. For details, please refer to https://mariadb.com/kb/en/gtid/.
On Community MySQL or Percona Server for MySQL, the parameter to enable GTID replication is
gtid-mode=ON enforce-gtid-consistency.
The command is:
change master to master_host = '192.168.1.120', master_port=3306, master_user = 'repl', master_password = 'password', master_auto_position=1;
Conclusion
We can see that the procedure to enable GTIDs on MariaDB replication is simple, but the commands and parameters are a bit different.
As mentioned, Percona offers Support and Managed Services for Community versions of MariaDB. At the same time, we encourage MariaDB users to explore Percona Server for MySQL as an alternative. Should you make the decision to migrate to Percona Software for MySQL, our experts will assist throughout the migration process and support you after the migration is complete.
Contact us to discuss migration options
Hope this is helpful for your daily MySQL and MariaDB management.
Aug
18
2023
18
2023
--
Want to Migrate From MariaDB 10.4 to MySQL 8.0 but Facing Hurdles? MySQL 5.7 to the Rescue!
Note that MariaDB 10.4 is not the latest version and has had new releases since 10.4. The client requirement was to move to MySQL 8.0 on the cloud for specific RDS features.
Caution: It is important to verify that you are not using any specific MariaDB features before migrating to MySQL 8.0.x.
Recently, we had a client wanting to move from MariaDB 10.4 to MySQL 8.0, and this transition presented several challenges. Incompatibilities between the versions and unsupported replication made a direct upgrade impossible. This blog will explore the hurdles encountered during the upgrade process and the innovative solution that saved the day.
The incompatibility roadblock
MariaDB 10.4 uses the Aria engine for system tables. Since Aria is a MariaDB-only engine, a direct MariaDB 10.4 > MySQL 8 ‘in-place’ upgrade is impossible for and after 10.4. I’m not listing all incompatibilities/differences, but you can find them here. I tried the in-place upgrade on my test VM for my own curiosity and got some redo log format errors/Data Dictionary errors.
2023-05-12T08:10:38.946611Z 1 [ERROR] [MY-013894] [InnoDB] Found redo log file ./ib_logfile0 which has format (v104) and is stored outside #innodb_redo. 2023-05-12T08:10:38.946683Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error. 2023-05-12T08:10:39.374389Z 1 [ERROR] [MY-011013] [Server] Failed to initialize DD Storage Engine.
Importing tablespace — risky along with downtime
As the client dataset was 1TB+, we tried other quicker options before going for the logical backup/restore method (the safe/preferred option). We tried schema dump and importing tablespaces (refer to Importing InnoDB tables for more details), which worked fine on the test VM, but this option involved read lock/downtime, which wasn’t preferred for a production setting and was also considered a risky option.
The logical backup/restore approach — but how to keep data in sync?
At last, after consensus, the logical backup/restore option was selected as the safest option. But after that, we also had to test the ‘unsupported’ replication setup to ensure data can be synced from MariaDB 10.4 to MySQL 8.x until we are ready for migration to the MySQL 8.x server. This replication test worked OK on my test VMs having minimal data apart from some expected user creation statements failure which had an easy workaround.
However, while testing with client data, we were not so lucky; error logs revealed unexpected event sequences during the replication setup, leading to a crash on the MySQL 8 node.
[Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master 'source-bin.000046' binary log file, at position 368. [Warning] [MY-010444] [Repl] QUERY(BEGIN) is not expected in an event stream in the middle of a DDL. [Warning] [MY-010590] [Repl] An unexpected event sequence was detected by the IO thread while queuing the event received from master 'source-bin.000046' binary log file, at position 394363. - mysqld got signal 11 ;
We did our research for the error, but it was clear the binlogs were incompatible, and the replication setup was unreliable.
Exploring the next option — MySQL 5.7 intermediate replica
We needed a replication link to establish minimal interruption, giving us time to do the cutover with minimal downtime. As the replication to MySQL 8 didn’t work, we tried replication from MariaDB 10.4 to MySQL 5.7 to test the binlog compatibility. So we configured a MySQL 5.7 replica after logical backup restore.
And it worked! The replication setup from MariaDB 10.4 > MySQL 5.7 worked, and the replica got in sync without any issues. We monitored the replica for several days to ensure no errors were encountered. Later we configured another node with MySQL 8.0 replicating from the MySQL 5.7 node making a chain replication topology, and the migration to MySQL 8.x was done as usual. In short, the topology was configured as below:
Conclusion
Introducing a MySQL 5.7 intermediate replica proved to be a helpful solution in this case, overcoming the replication challenges between MariaDB 10.4 and MySQL 8.0. It also shows the importance of thorough testing in complex database upgrades. I hope it encourages others facing similar obstacles during database migrations to consider alternative strategies and embrace the power of testing for successful outcomes. Or you have Percona to help with that!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Aug
04
2023
04
2023
--
Database Server Lifecycle for MySQL and MariaDB
In this blog post, we will look at the lifecycle and release management for MySQL and MariaDB servers — where we are now and relevant historical background.
It is worth noting both MySQL and MariaDB have Community and Enterprise versions. For MySQL, both releases are made by the same company (Oracle), follow the same version numbering, and the Enterprise version is a superset of what is available in Community. For MariaDB, the Community version is provided by MariaDB Foundation, while Enterprise is provided by MariaDB PLC, following its own lifecycle, and has a different feature set. To keep things simple, we will focus our attention on the Community versions.
MariaDB
As you probably are well aware, MariaDB started as a MySQL fork, and in the early days, things were rather similar. Things were starting to significantly diverge back in 2014 when MariaDB 10 was released. This was a departure from matching MySQL versions, as was happening with MySQL 5.1 and MySQL 5.5.
Getting its own versions tracked allowed MariaDB to innovate at its own (faster) pace without confusing users who, because of shared roots, expected some kind of compatibility for MySQL and MariaDB of the same version. (It is worth noting that MariaDB 5.2 and MariaDB 5.3 existed, too, while there were no matching MySQL versions.)
MariaDB started to move fast. MariaDB 10.1 was released the next year in 2015, and MariaDB 10.2 in 2017; after that, major releases came every one to two years, with MariaDB 10.6 released in 2021. This fast pace of development, however, was combined with long-term support of five years for all releases, which meant many releases to maintain created an undue burden for the engineering team.
To address this burden, the new Innovation Release Model launched at the end of 2021, similar to how Ubuntu Linux is developed — quarterly releases are maintained for one year only while there are select long-term support releases, which are to be supported for at least five years as before. Short-term and long-term support releases follow the same version pattern, and you really need to know which is which.
Additionally, MariaDB recently changed the leading version from 10 to 11. As Kaj Arno explains, expensive changes to Optimizer and its cost model are the main reason for this change.
The MariaDB 11 series did not yet have any long-term supported (LTS) releases, which are the releases most would consider for running mission-critical databases in production. The latest long-term support (LTS) release is MariaDB 10.11, to be supported until February 2028.
MySQL
Under Oracle’s leadership, at first, MySQL continued to be following the lifecycle it had followed for a while. Every couple of years, there would be feature releases, and then there would be binary-compatible “bugfix only” minor releases. This was the case for MySQL 5.5, MySQL 5.6, and MySQL 5.7.
This release cycle had the benefit of stability; minor release upgrades were rather low risk, and if you needed to roll back, you could do it by quickly swapping out the binary without needing to do anything to your data. As with everything, though, there are tradeoffs — and the downside of this approach was the slow rollout of new features and big changes between major releases, making upgrades potentially messy and time-consuming.
With MySQL 8, this approach has drastically changed. MySQL 8 became what seemed like a “forever release.” While the initial GA release came out in April 2018, we have not seen a new major release for five years! This does not mean there’s no innovation in MySQL 8; on the contrary, MySQL 8 now is very different from the one released back in 2018 because, in every minor release, new features were introduced together with bug fixes.
If you are someone who loves getting new features faster, you would love this new approach. In theory, this also means less risk with those “feature releases” upgrades, which contained a few months of development work as compared to years of work for major releases in the past. This is not how things work out in practice, though, as some of the releases contained new features with bugs critical enough to warrant release recalls. What is worse, MySQL 8 also maintained only roll-forward binary compatibility, so once you upgrade to the new MySQL version, there is no guarantee the previous version will be able to operate on the same data.
It took a while, but the MySQL team recognized the MySQL 8 approach is not something you just need to get used to but rather something which does not work for some database environments; so moving forward, the New Release Model is introduced. This model introduces “Innovation Releases,” which are released approximately quarterly and where only the latest Innovation Release is supported (i.e., any bug fixes will be rolled with new features and rolled out as the next innovation release, similarly to how MySQL 8.0 operates now). Another kind of release will be Long Term Supported (LTS) Releases, which will come out every couple of years and will be supported by Oracle for eight years (five Standard + three Extended).
MySQL LTS Releases will operate similarly to how MySQL operated before MySQL 8. The Innovation Releases are somewhat similar to the “milestone releases” the MySQL team used at some point, but where Milestone Releases were not considered “Production Ready” and were intended for Development and Preview, Innovation Releases are considered “production-grade quality.”
MySQL 8.0 has a special place in this release model. Currently, it is basically an Innovation Style release, but with MySQL 8.0.34, it will become an LTS Release getting bug fixes only.
Differences between MySQL and MariaDB approaches
It is interesting to see both communities seem to have come to the understanding we need both a high pace of innovation AND stability. You also can’t really have it both ways in the same release series. You also need to keep support and maintenance costs under control; hence, you can’t have too many actively-supported releases.
Both MariaDB and MySQL came to the conclusion they need LTS versions and releases which focus on the speed of Innovation at the same time.
The cadence of LTS Releases is likely to be similar between MySQL and MariaDB, too. MySQL expects LTS Releases to come out approximately every two years, which is similar to MariaDB “at least every other year.” The difference is that MariaDB also collaborates with major Linux distributions to align MariaDB LTS releases to Linux Distribution LTS release plans, while MySQL did not state any such goals.
Where a difference exists is how Non-LTS Releases are approached. Where MariaDB goes the “Short Term Support” route when there are “bugfix only” releases for a limited time, MySQL chooses a path of supported rolling Innovation releases where bug fixes are included only with the latest Innovation release. It will be interesting to see how those choices work out — the MariaDB approach is more “user friendly” as it gives users more control over when to upgrade to the next feature release, whereas the MySQL approach reduces the effort needed to support releases.
Another important difference is what kind of upgrades are supported. Where MySQL supports upgrades to the next major version only (i.e., you can’t upgrade from MySQL 5.6 to MySQL 8 directly), MariaDB supports skipping major versions in an upgrade.
Percona plans
As you read this on the Percona Blog, you may be interested in Percona’s plan for Percona Server for MySQL regarding announced changes. The short answer is that we will follow the newly announced Innovation Release model and will produce LTS and Innovation releases, too. For more details, check out the blog post, LTS and Innovation Releases for Percona Server for MySQL.
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Jun
01
2023
01
2023
--
Migrating From MariaDB to Percona Server for MySQL 8: The Security Witchcraft
Hello friends, on certain occasions, some clients, for whatever reason, ask us to migrate a supposedly “equivalent” brand of an engine in terms of belonging to the MySQL family or ecosystem.
In this case, we will be analyzing the migration of MariaDB to Percona Server for MySQL 8 in particular. A comment worth clarifying is that, although Percona Server for MySQL is a drop-in replacement for MySQL since it offers compatibility continuity (Percona Server for MySQL even offers certain features that the community version does not, for free!), the same does not happen with MariaDB, which from a specific version, we could say in some way, distanced itself from MySQL and Percona Server for MySQL. Much has been said about the subject, and several interesting links on the ‘net talk about it.
In this opportunity, we will focus strictly on how the migration should be done, and in particular, I will “zoom” (if you allow me the analogy) in on everything related to security.
Based on our experience, the safest way to migrate MariaDB to Percona Server for MySQL is logical. For this, we recommend using mydumper since it offers several advantages over mysqldump, such as parallelism, compression, and other features that make it really interesting.
With all this said, what would be the steps to follow at a general plan level? Basically, it would be something like:
- Make a logical backup from MariaDB using mydymper.
- Upload that dump to the Percona Server for MySQL using myloader.
Simple, right? No, my friend, it’s not that simple. Leaving aside certain complexities regarding the compatible data types, and the code created in the instance (Store Procedures, Functions, etc.), which we will not talk about in this blog, there is another fence that many underestimate and is essential: Security.
Why do I bother talking about this? Because in MariaDB (also in Percona Server for MySQL 8), there are ROLES and such, they may have been used. Roles are very convenient for grouping permissions and assigning them to users. That has already been discussed, and here we can see one example.
So what would the steps be like now? We would have to:
- Make a security backup of the “source” instance (MariaDB) for this. The most practical/usual is to use pt-show-grants.
- Once the users, roles, etc., have been created, execute the two steps mentioned above but with the caveat that ONLY the application schemas will be exported/imported AND NOT those of the internal data dictionary (such as sys, mysql, information_schema, and so on).
Alright, let’s go to the example to see if it’s as easy as it seems.
CentOS7_2 192.168.0.71 (CentOS 7.9 + Percona Server 8.0.32-24) CentOS7_3 192.168.0.72 (CentOS 7.9 + MariaDB 10.11.3)
Let’s create the table and insert data into it.
MariaDB [test]> CREATE TABLE Persons (PersonID int primary key, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)); Query OK, 0 rows affected (0.116 sec) mysql> insert into Persons values (1,'Joey','Koz','USA','N. Carolina'); Query OK, 1 row affected (0.00 sec) mysql> insert into Persons values (2,'Wally','G','ARG','MZA'); Query OK, 1 row affected (0.02 sec) mysql> insert into Persons values (3,'Fer','Matt','ARG','QUI'); Query OK, 1 row affected (0.00 sec)
Let’s start creating roles, and users, and assigning them:
MariaDB [(none)]> create role role_can_read; Query OK, 0 rows affected (0.017 sec) MariaDB [(none)]> create role role_can_write; Query OK, 0 rows affected (0.012 sec) MariaDB [(none)]> create role role_can_all; Query OK, 0 rows affected (0.024 sec) MariaDB [(none)]> grant select on test.* to role_can_read; Query OK, 0 rows affected (0.015 sec) MariaDB [(none)]> grant insert,delete,update on test.* to role_can_write; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> create user user_want_read identified by 'wantread'; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> create user user_want_write identified by 'wantwrite'; Query OK, 0 rows affected (0.019 sec) MariaDB [(none)]> create user user_want_all identified by 'wantnall'; Query OK, 0 rows affected (0.013 sec) MariaDB [(none)]> grant role_can_read to user_want_read; Query OK, 0 rows affected (0.013 sec) MariaDB [(none)]> grant role_can_write to user_want_write; Query OK, 0 rows affected (0.033 sec) MariaDB [(none)]> grant role_can_all to user_want_all; Query OK, 0 rows affected (0.013 sec)
We check everything:
MariaDB [(none)]> show grants for user_want_read; +---------------------------------------------------------------------------------------------------------------+ | Grants for user_want_read@% | +---------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_read` TO `user_want_read`@`%` | | GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D' | +---------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> show grants for user_want_write; +----------------------------------------------------------------------------------------------------------------+ | Grants for user_want_write@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_write` TO `user_want_write`@`%` | | GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> show grants for user_want_all; +--------------------------------------------------------------------------------------------------------------+ | Grants for user_want_all@% | +--------------------------------------------------------------------------------------------------------------+ | GRANT `role_can_all` TO `user_want_all`@`%` | | GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4' | +--------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec)
And this is where all the magic begins.
On a third server, to avoid file transfers, etc., we execute the following commands:
a) The security stuff:
somewhere $ mkdir -p /home/percona/MIGRATION_SECURITY
somewhere $ cd /home/percona/MIGRATION_SECURITY
somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('CREATE USER IF NOT EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys' union all select concat('CREATE ROLE IF NOT EXISTS ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null > step1.sql
somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('ALTER USER IF EXISTS ',user,'@''',host,'''',' IDENTIFIED WITH mysql_native_password AS ''',authentication_string,''';') from mysql.user where is_role='N' and user <> 'mariadb.sys'" 2>/dev/null > step2.sql
somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY" > step3.sql
somewhere $ mysql -h 192.168.0.72 -u root -proot -Bsne "select concat('SHOW GRANTS FOR ',user,';') from mysql.user where is_role='Y' order by 1 ;" 2>/dev/null| grep -vi PUBLIC | mysql -Bsn -h 192.168.0.72 -u root -proot 2>/dev/null | sed 's/$/;/g' > step4.sql
somewhere $ for NUM in {1..4}; do mysql -h 192.168.0.71 -u root -proot -f -vve "source step$NUM.sql"; done
b) The data migration stuff (clarification: in this “somewhere” there should be enough space to be able to hold the data that we want to migrate).
somewhere $ mkdir -p /home/percona/MIGRATION_DATA somewhere $ cd /home/percona/MIGRATION_DATA somewhere $ mydumper -h 192.168.0.72 -u root -p root -v 3 --database=test --outputdir /home/percona/MIGRATION_DATA/ somewhere $ myloader -h 192.168.0.71 -u root -p root -v 3 --database=test --overwrite-tables -d /home/percona/MIGRATION_DATA/
And that’s it.
Now you will say, why didn’t you directly use the output of MariaDB’s pt-show-grants and perform all these series of witchcraft or cheap tricks? I’ll tell you, friend: unfortunately, the tool is not very friendly to MariaDB. Although it decently generates “something” as output, it is insufficient, and the project will undoubtedly fail.
See the output generated by pt-show-grants (I’ve filtered headers and comments, not much) for the MariaDB instance:
somewhere $ pt-show-grants -u root -p root -h 192.168.0.72 | egrep -v "Grants|Dumped" GRANT DELETE, SELECT ON `mysql`.`global_priv` TO `mariadb.sys`@`localhost`; GRANT USAGE ON *.* TO `mariadb.sys`@`localhost`; GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'; GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' WITH GRANT OPTION; GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION; GRANT `role_can_all` TO `root`@`localhost` WITH ADMIN OPTION; GRANT `role_can_read` TO `root`@`localhost` WITH ADMIN OPTION; GRANT `role_can_write` TO `root`@`localhost` WITH ADMIN OPTION; GRANT USAGE ON *.* TO `user_want_all`@`%` IDENTIFIED BY PASSWORD '*210F0DA943585CF2372ADF07342C92824DAA9EE4'; GRANT `role_can_all` TO `user_want_all`@`%`; GRANT USAGE ON *.* TO `user_want_read`@`%` IDENTIFIED BY PASSWORD '*9084B8A46A36BE084E6FB06E1E750D596B15CC5D'; GRANT `role_can_read` TO `user_want_read`@`%`; GRANT USAGE ON *.* TO `user_want_write`@`%` IDENTIFIED BY PASSWORD '*5C7B1B946CC5F748678A0BA197B9122AD7EA5634'; GRANT `role_can_write` TO `user_want_write`@`%`;
- Do you see the CREATE USER command and password authentication method needed in Percona Server for MySQL 8? I don’t (hence the step1.sql).
- Do you see any password manipulation that might work using MariaDB commands but on Percona Server for MySQL 8? I don’t think so (hence the step2.sql).
- The only step to get the most out of the utility is in the role assignment (step3.sql).
- Do you see the GRANTS assigned to the schemas? I don’t (that’s why the step4.sql).
This is the output of pt-show-grants for the migrated instance (Percona Server for MySQL 8):
somewhere $ pt-show-grants -u root -p root -h 192.168.0.71 | egrep -v "Grants|IDENTIFIED|mariadb.sys|PROXY|Dumped|oles" CREATE ROLE IF NOT EXISTS `role_can_all`; CREATE ROLE IF NOT EXISTS `role_can_read`; CREATE ROLE IF NOT EXISTS `role_can_write`; CREATE USER IF NOT EXISTS `role_can_write`@`%`; GRANT DELETE, INSERT, UPDATE ON `test`.* TO `role_can_write`@`%`; GRANT USAGE ON *.* TO `role_can_write`@`%`; CREATE USER IF NOT EXISTS `role_can_read`@`%`; GRANT SELECT ON `test`.* TO `role_can_read`@`%`; GRANT USAGE ON *.* TO `role_can_read`@`%`; CREATE USER IF NOT EXISTS `role_can_all`@`%`; GRANT USAGE ON *.* TO `role_can_all`@`%`; GRANT `role_can_read`@`%`,`role_can_write`@`%` TO `role_can_all`@`%`; CREATE USER IF NOT EXISTS `mysql.infoschema`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.infoschema`@`localhost`; GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`; CREATE USER IF NOT EXISTS `mysql.session`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,FIREWALL_EXEMPT,PERSIST_RO_VARIABLES_ADMIN,SESSION_VARIABLES_ADMIN,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN ON *.* TO `mysql.session`@`localhost`; GRANT SELECT ON `mysql`.`user` TO `mysql.session`@`localhost`; GRANT SELECT ON `performance_schema`.* TO `mysql.session`@`localhost`; GRANT SHUTDOWN, SUPER ON *.* TO `mysql.session`@`localhost`; CREATE USER IF NOT EXISTS `mysql.sys`@`localhost`; GRANT AUDIT_ABORT_EXEMPT,FIREWALL_EXEMPT,SYSTEM_USER ON *.* TO `mysql.sys`@`localhost`; GRANT SELECT ON `sys`.`sys_config` TO `mysql.sys`@`localhost`; GRANT TRIGGER ON `sys`.* TO `mysql.sys`@`localhost`; GRANT USAGE ON *.* TO `mysql.sys`@`localhost`; CREATE USER IF NOT EXISTS `root`@`%`; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `root`@`%` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`%` WITH GRANT OPTION; CREATE USER IF NOT EXISTS `root`@`localhost`; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `root`@`localhost` WITH GRANT OPTION; GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION; GRANT `role_can_all`@`%`,`role_can_read`@`%`,`role_can_write`@`%` TO `root`@`localhost` WITH ADMIN OPTION; CREATE USER IF NOT EXISTS `user_want_all`@`%`; GRANT USAGE ON *.* TO `user_want_all`@`%`; GRANT `role_can_all`@`%` TO `user_want_all`@`%`; CREATE USER IF NOT EXISTS `user_want_read`@`%`; GRANT USAGE ON *.* TO `user_want_read`@`%`; GRANT `role_can_read`@`%` TO `user_want_read`@`%`; CREATE USER IF NOT EXISTS `user_want_write`@`%`; GRANT USAGE ON *.* TO `user_want_write`@`%`; GRANT `role_can_write`@`%` TO `user_want_write`@`%`;
It is what it should be.
Conclusion
Since MariaDB has gone farther away from MySQL, migrating back to the MySQL ecosystem and hence to Percona Server for MySQL is not as straightforward as it could be. Due to the same reasons, Percona Toolkit will not be able to assist in the migration task. This blog post will give you the tricks needed for a successful migration.
Of course, you always have the chance to contact us and ask for assistance with any migration. You can also learn how Percona experts can help you migrate to Percona Server for MySQL seamlessly here.
I hope you enjoyed the blog, and see you in the next one!
Percona Distribution for MySQL is the most complete, stable, scalable, and secure open source MySQL solution available, delivering enterprise-grade database environments for your most critical business applications… and it’s free to use!
Jun
02
2022
02
2022
--
How 3 Companies Used Percona to Boost Performance, Availability, and Market Value
At Percona, we love collaborating with companies to achieve their goals – no matter how ambitious. With each new customer, we get the chance to apply our open source expertise and technology to solve complex and fascinating database challenges. We’re grateful to work with so many amazing companies and want to share some recent success stories.
Appsuite
Appsuite is a full-scale SaaS provider for hospitality businesses. The company serves as a customer relationship management solution, offering point-of-sale integrated CRM; loyalty, reward, and gift card programs; and online ordering applications. At the onset of COVID-19, Appsuite recognized that, in addition to helping their existing customers adjust to rapid changes resulting from the pandemic, there were new opportunities to create value as a SaaS provider. To expand their business and serve new segments, they needed to scale their operations to support customers with a maximum of 30 locations to those with 800 locations or more.
The Percona Consulting team guided Appsuite through the design and implementation of a scalable, highly reliable open source database architecture. This included a move to Percona’s open source database software, Percona Server for MySQL. Appsuite continues to collaborate with Percona on critical support projects for their production database systems.
“Percona has the best engineers in the world. Percona provides us with immediate 24×7 access to world-class MySQL engineering support. Avoiding the need to directly hire a skilled DBA has saved us a considerable amount of money.” – Ross Hunton, Appsuite CTO
Otto Office
Otto Office is one of the largest business-to-business distance retailers for office supplies in Germany, specializing in mail-order sales, communications equipment, and office furniture to businesses and consumers. Their MySQL database cluster is the central point of their online sales system and requires a fast and reliable system architecture.
Otto Office was already using cluster functionality via Percona XtraDB Cluster to provide quick data synchronization over their different data centers, but they also wanted to improve database latency and architecture. Otto Office again partnered with Percona, this time to review their database environment and recommend improvements for making their cluster more stable, performant, and highly available, including tolerance of data center failure. Following this consulting engagement, Otto Office chose Percona to provide database support in case of an emergency.
“Percona consultants are database experts that focus on stable solutions. The expertise of the Percona consultants fitted perfectly with our team and led to good exchanges that found fast solutions.” – Sven Jacobsen, Director IT & eCommerce, Otto Office
BlaBlaCar
BlaBlaCar is the world’s leading community-based travel network. Headquartered in Paris, France, The company enables more than 100 million members to share rides across 22 countries via its website and mobile apps. To support customer demand for flexible commute scheduling, BlaBlaCar must maintain a fast and reliable system architecture.
Working closely with the Percona Support team, BlaBlaCar used MariaDB to implement performance improvements to increase the uptime of their mission-critical applications. BlaBlaCar chose Percona because of its transparency, unbiased nature, and dedication to the open source database world, believing that the DNA of Percona matches their philosophy of always “choosing the right tool for the job.”
“BlaBlaCar’s Database Reliability Engineering team and Percona share the same belief that unbiased open source database expertise provides great products and attractive communication. Percona Support offers us a guarantee to run our business safely and allows us to be focused on high-value areas.” – Maxime Fouilleul Engineering Manager for BlaBlaCar’s DBRE Team
Get unbiased open source database expertise
In summary, Percona’s open source expertise enabled:
- Appsuite to use MySQL to scale their business and expand their value in the market.
- Otto Office to make their MySQL-based central sales system more stable, performant, and highly available.
- BlaBlaCar to use MariaDB to improve their mission-critical application performance and uptime.
So what do you want to do? Our unbiased open source database experts are ready to roll up their sleeves and help you meet your goals too. Learn more about Percona support and services here.