Τροφοδότης νέων

Planet MySQL

Tagged under:

Things you didn’t know about MySQL and Date and Time and DST

(based on a conversation with a colleague, and a bit of Twitter) A Conundrum A developer colleague paged me with this:mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G delta: 420It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-13 year_month) as delta\G delta: 3600It has to be exactly 730 days (2 * 365 days, 2 years):mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 729 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 729 day) as delta\G delta: 3600 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 730 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day) as delta\G delta: 420 mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 731 day) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 731 day) as delta\G delta: 3600 The Reason In our math, we have two expressions mixing MySQL Timestamp data types with UNIX Timestamp Integers. So in the expression UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 year) the part "2021-03-26 03:07:00" is a string, which is converted to a MySQL Timestamp type. This MySQL Timestamp type is then used in an interval arithmethic expression to yield another MySQL Timestamp type. This resulting MySQL Timestamp type is then fed into the UNIX_TIMESTAMP function, and produces an integer. The same happens with UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 2 year), producing another integer. This is not the integer we are looking for:mysql> select from_unixtime(UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day)) as t\G t: 2023-03-26 03:00:00 mysql> show global variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CEST | | time_zone | SYSTEM | +------------------+--------+  The First Level of Wrongness The 2023-03-26 is the day of the proposed time zone switch for 2023. On this date, in the CET/CEST time zone, 02:07:00 is an invalid timestamp. MySQL silently, without error or warning, rounds this up to the next valid timestamp, 03:00:00. This also happened yesterday:$ mysql --show-warnings mysql> select from_unixtime(unix_timestamp("2021-03-28 02:07:00")) as t\G t: 2021-03-28 03:00:00This should error, and must at least warn. It does neither. The Second Level of Wrongness Formysql> select from_unixtime(UNIX_TIMESTAMP("2021-03-26 02:07:00" + interval 730 day)) as t\G t: 2023-03-26 03:00:00there is the choice of producing the correct timestamp or producing an error. Silently fast forwarding to the next valid timestamp is incorrect in all cases. Setting UTC The database is running with the time_zone set to SYSTEM, and the system is running with the system_time_zone (a read-only variable) set to CEST (was: CET), which was picked up after the server start (on my laptop, in this case).mysql> show global variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CEST | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.01 sec) t: 2023-03-26 03:00:00Trying to set the time_zone to UTC fails. This is because the time_zone tables have not been loaded.$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p'' mysql ...With that, I can$ mysql -u root -p mysql> set global time_zone="UTC"; Query OK, 0 rows affected (0.00 sec) mysql> set session time_zone="UTC"; Query OK, 0 rows affected (0.00 sec)And with that, I can avoid the conversion:mysql> select from_unixtime(unix_timestamp("2021-03-28 00:07:00")) as t; +---------------------+ | t | +---------------------+ | 2021-03-28 00:07:00 | +---------------------+ 1 row in set (0.00 sec)mysql> select from_unixtime(unix_timestamp("2021-03-28 01:07:00")) as t; +---------------------+ | t | +---------------------+ | 2021-03-28 01:07:00 | +---------------------+ 1 row in set (0.00 sec)mysql> select from_unixtime(unix_timestamp("2021-03-28 02:07:00")) as t; +---------------------+ | t | +---------------------+ | 2021-03-28 02:07:00 | +---------------------+ 1 row in set (0.00 sec)mysql> select from_unixtime(unix_timestamp("2021-03-28 03:07:00")) as t; +---------------------+ | t | +---------------------+ | 2021-03-28 03:07:00 | +---------------------+ 1 row in set (0.00 sec)This will also yield the correct result for the type-mixed difference I showed above:mysql> select UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) - UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G delta: 3600  Not mixing MySQL Date Types and UNIX Timestamps The original math fails, because it mixes UNIX Timestamps and Date Interval Arithmethics. We can handle this all the way in MySQL, using the extremely weird timestampdiff() function (more on that below):mysql> select timestampdiff( second, date_add("2021-03-26 02:07:00", INTERVAL 2 YEAR), date_add("2021-03-26 03:07:00", INTERVAL 2 YEAR) ) as t\G t: 3600We can handle this all the way in Integers with Unix Timestamps:mysql> select 86400 * 365 as t \G t: 31536000 mysql> select (unix_timestamp("2021-03-26 03:07:00") + 2*31536000) - (unix_timestamp("2021-03-26 02:07:00") + 2* 31536000) as t \G t: 3600Both give us correct results. Date and Time Syntax MySQL provides you INTERVAL syntax with operators:mysql> select "2021-03-29 10:02:03" + interval 1 hour as t\G t: 2021-03-29 11:02:03and with functions:mysql> select date_add("2021-03-29 10:02:03", interval 1 hour) as t\G t: 2021-03-29 11:02:03Interval Syntax is weird. You can’tmysql> select "2021-03-29 10:02:03" + interval 1 month 1 hour as t\G ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 hour as t' at line 1You can onlymysql> select "2021-03-29 10:02:03" + interval 1 month + interval 1 hour as t\G t: 2021-04-29 11:02:03With date_add() it is worse, because you have to nest:mysql> select date_add("2021-03-29 10:02:03", interval 1 month + interval 1 hour) as t\G ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 hour) as t' at line 1 That would be thenmysql> select date_add(date_add("2021-03-29 10:02:03", interval 1 month), interval 1 hour) as t\G t: 2021-04-29 11:02:03So date_add() and date_sub() both take a timestamp and an interval, and can be written as + and -, avoiding the nesting. A Word of Warning on DIFF functions There are two functions with underscores in the name, DATE_ADD() and DATE_SUB(), which take a timestamp and an interval. They produce a new timestamp. There are three functions without underscores in the name DATEDIFF(), TIMEDIFF() and TIMESTAMPDIFF(), which take two timestamps and produce the difference. They are all subtly different, and the parameter order for TIMESTAMPDIFF() is the other way around. Read carefully: datediff(a, b) calculates the DATE difference as a-b. The time part of the timestamps is ignored.mysql> select datediff(now() + interval 1 month, now()) as t\G t: 31 mysql> select datediff(now() + interval 2 month, now() + interval 1 month) as t\G t: 30timediff(a, b) calculates the TIME difference as a-b. The DATE and TIME parts are being used. The range is limited to the range of the TIME type, which is from ‘-838:59:59’ to ‘838:59:59’. That is 5 weeks, less 1 hour and 1 second (5 weeks are 840 hours, 5 * 7 * 24). timestampdiff(unit, a, b) can do “proper” difference between b and a. The result is reported in the unit specified. The order of the parameters is inexplicably reversed: We calculate b-a.mysql> select timestampdiff(hour, "2021-03-29 10:02:03", "2021-03-29 10:02:03" + interval 1 month + interval 1 hour) as t\G t: 745 TL;DR The lack of warning and error is now a MySQL Service Request. The original problem comes up because of the mixing of Unix Timestamp Arithmethic and MySQL Interval Arithmethic. There are ways to do it pure play either way, and they both result in the right result. There is DATEDIFF(), TIMEDIFF(), and TIMESTAMPDIFF(), and they are weird, and inconsistent and you really, really want to read the Date and Time Functions page, very carefully. First published on https://blog.koehntopp.info/ and syndicated here with permission of the author. The post Things you didn’t know about MySQL and Date and Time and DST appeared first on Percona Community Blog.

MySQL and UUIDs

In ALTER TABLE for UUID we discuss currently proper way to store and handle UUID in MySQL. Currently it works, even in a performant way, but it still hurts. It should not. Definition of UUID The RFC 4122 defines various types of UUID, and how they are being formatted for presentation and as a bit field on the wire. As this document was written bei Leach and Salz, among others, RFC 4122 UUIDs are also called “Leach-Salz UUIDs” (for example in the Java Documentation). There are other UUID variants, used in other systems (NCS, and Microsoft “backwards compatibility”). A RFC 4122 UUID is a special 128 bit long value (“16 octets”). It is supposed to be laid out like this: 0 1 2 3 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_low | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | time_mid | time_hi_and_version | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ |clk_seq_hi_res | clk_seq_low | node (0-1) | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ | node (2-5) | +-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+The high bits in clk_seq_hi_res define the variant, and anything starting with the bit sequence 10 is a RFC 4122 compliant UUID. The high nibble in time_hi_and_version defines the RFC 4122 Type (or version), the algorithm used. RFC 4122 itself defines versions 1 to 5 (version 0 is unused). There is an expired draft that defines a version 6, which is specifically designed to use a primary key for databases. It uses the same timestamp value as Type 1, but stores the bytes so that ordering by creation time is preserved. It also relaxes the requirements on the node field, officially allowing different sources for bits than the MAC address. Sample implementations are available in various languages. UUIDs are all over the place in Windows and also in Java. Databases have to handle them as “application generated primary keys”. That’s why they are important and should be treated with some attention. Handling UUID in MySQL 8 MySQL provides functions to generate UUIDs, to check if a thing is a validly formatted string that looks like a UUID, and to convert UUIDs to 128 bit bitstrings and back. The latter two functions have a special flag to improve performance with InnoDB. This flag is by default off. No attempt is made to automatically do the right thing dependent on the UUID type. UUID function The MySQL function UUID() returns a RFC 4122 Type 1 UUID. mysql> select uuid_to_bin(uuid()) as uuid; +------------------------------------+ | uuid | +------------------------------------+ | 0x80462D3C96AB11EB94BBBA2278F258E1 | +------------------------------------+ 1 row in set (0.00 sec)Formatted like in the RFC, we get the following values: 80462D3C 96AB 1 1EB 9 4 BB B A22 78F258E1Specifically, the clk_seq_hi_res contains the variant value, 9, and the time_hi_and_version contains the version, 1. According to the RFC, the variant 9 = 1001 = 10x (a RFC 4122 compliant UUID). Of the Versions defined in RFC 4122, it is Version 1 or Type 1, “The time-based version in this document”. The generation algorithm is defined in section 4.2 of that RFC. So a MySQL generated UUID is always a RFC 4122 Type 1 (time based) UUID. Java generated UUID values Compare with the Java UUID Class: We have randomUUID(), returning a Type 4 UUID and nameUUIDFromBytes() for Type 3 and a generic Constructor that takes any 128 bit value. So what we get from Java is likely name based or random UUIDs generated by the application. Name based UUIDs are fixed for a fixed name, so they pose no problem when used with MySQL as a primary key. Random UUIDs are completely random. There is nothing to optimize for MySQL here. Neither is a good choice to use as a kind of application controlled distribiuted primary key. Based on the reasoning in ALTER TABLE for UUID, Java developers that are using UUID for this purpose would be well advised to implement and use a Type 1 UUID. It seems that such an implementation is not available by default as part of java.lang.util, but other implementations exist. IS_UUID() function The IS_UUID() predicate returns 1 for all strings that can be parsed as a UUID. That is, it checks for one of three ways to write the UUID, and that it contains only valid hex digits in the places that actually contain data. No attempt is made to validate any of the bits. Code is here, and the actual parsing happens here. UUID_TO_BIN() function MySQL allows three ways to write UUIDs, as a 128 bit hex string (6ccd780cbaba102695645b8c656024db), as a 128 bit hex string with dashes in the right places (6CCD780C-BABA-1026-9564-5B8C656024DB) and as a 128 bit hex string with dashes in the right places and enclosed in curly braces ({6CCD780C-BABA-1026-9564-5B8C656024DB}). This is not a particular dense packing of data for storage. The UUID_TO_BIN() function takes any of these strings and returns a VARBINARY(16) for storage. The function has an optional second parameter, the swap_flag. When applied to a Type 1 UUID, the time bytes are being swapped around so that chronological ascending UUIDs from the same node are also having numerically ascending values. This optimizes storage with InnoDB. Type 1 UUID: It is recommended you define UUID columns as VARBINARY(16) and use theUUID_TO_BIN(uuid_string, 1) function to store data. Type 6 UUID: You should use UUID_TO_BIN(uuid_string, 0) to store Type 6 UUIDs, because Type 6 has been specifically created to avoid the swapping of time bytes around. Other types: These do not profit from swapping, so also use UUID_TO_BIN(uuid_string, 0). BIN_TO_UUID function The inverse function to UUID_TO_BIN is BIN_TO_UUID(). It needs the same swap_flag value as has been used at write time in order to unpack the data correctly. It should hurt less This all should hurt less. MySQL should have a native UUID column type, which stores data internally in a VARBINARY(16) (or BINARY(16)). It should accept all three notations as input (hex string, with dashes, with curly braces and dashes). It should return a hex string without dashes or curlies. It should validate variant and type (version), allowing Types 1 to 6. It should auto-convert (swap) data for Type 1, but not for any other type. There should be a formatting function that turns a hex string without dashes into a UUID string with dashes, or a UUID string with dashes and curlies. That is, I want to be able to mysql> create table u ( u uuid not null primary key, dummy integer ); mysql> insert into u values (uuid(), 1); mysql> select u from u\G u: 6ccd780cbaba102695645b8c656024db mysql> select format_uuid(u, 0) as u from u\G u: 6CCD780C-BABA-1026-9564-5B8C656024DB mysql> select format_uuid(u) as u from u\G u: 6CCD780C-BABA-1026-9564-5B8C656024DB mysql> select format_uuid(u, 1) as u from u\G u: {6CCD780C-BABA-1026-9564-5B8C656024DB}and internally, this is stored as 0x1026BABA6CCD780C95645B8C656024DB, because variant is RFC 4122 and Type is 1. For any other Type of the RFC 4122 the internal swapping does not happen. Trying to store anything that is not a RFC 4122 variant is an error. Trying to store anything that is a RFC 4122 variant but not a Type 1 to 6 is an error.

How to Encrypt MySQL Data Using AES Techniques

Sometimes clients want that the information they collected from the user should be encrypted and stored in a database. Data encryption and…Continue reading on Thinkdiff »

Percona XtraBackup Point-In-Time Recovery for the Single Database

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command. PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s). However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, mysql command-line client, and mysqlbinlog programs only. There is an alternative approach that involves creating a fake source server, that is described in MySQL Point in Time Recovery the Right Way. You may consider it, especially if you need to apply changes to a single table. Percona XtraBackup Point-In-Time Recovery For our example we will create data first, then run DROP and DELETE commands on two different tables. Then we will rollback these commands. First, let’s assume we have a server with two databases: test and sbtest. We are using GTIDs and row-based binary log format. We also run the server with the option innodb_file_per_table=1 and all our InnoDB tables use individual tablespaces. Otherwise, the individual restore method would not work.mysql> show tables from sbtest; +------------------+ | Tables_in_sbtest | +------------------+ | sbtest1 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | +------------------+ 8 rows in set (0.00 sec) mysql> show tables from test; +----------------+ | Tables_in_test | +----------------+ | bar | | baz | | foo | +----------------+ 3 rows in set (0.00 sec)We will experiment with tables foo and bar. We assume that at the time of our first backup, each of the tables contained five rows. Tables in the database sbtest also contain data, but it does not really matter for our experiment.mysql> select count(*) from foo; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from bar; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from baz; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)Since we want to restore individual tables, we need to make a preparation before taking a backup: store database structure. We will do it with help of the mysqldump command. In this example, I store structure per database to make partial PITR easier, but you are free to use the option --all-databases.mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events test > test_structure.sql mysqldump --no-data --set-gtid-purged=OFF --triggers --routines --events sbtest > sbtest_structure.sqlThen we are ready to take the backup.xtrabackup --parallel=8 --target-dir=./full_backup --backupI am using the option --parallel to speed up the backup process. Now let’s do some testing. First, let’s update rows in the table foo.mysql> update foo set f1=f1*2; Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0 mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | +----+------+ 5 rows in set (0.00 sec)And then drop it and delete all rows from the table bar.mysql> drop table foo; Query OK, 0 rows affected (0.02 sec) mysql> delete from bar; Query OK, 5 rows affected (0.01 sec)Finally, let’s insert a few rows into the tables bar and baz.mysql> insert into bar(f1) values(6),(7),(8),(9),(10); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> insert into baz(f1) values(1),(2),(3),(4),(5); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0Assume that the DROP TABLE and DELETE command was an accident and we want to restore the state of the tables foo and bar as they were before these unlucky statements. First, we need to prepare the backup. Since we are interested in restoring only tables in the database test we need to prepare the backup with a special option --export that exports tablespaces in a way that they could be later imported:xtrabackup --prepare --export --target-dir=./full_backupNow the directory for the database test contains not only table definition files (.frm, only before 8.0) and tablespace files (.ibd) but also configuration files (.cfg). Since we want all changes that happened after backup and before the problematic DROP TABLE and DELETE statements were applied, we need to identify which binary log and position were actual at the backup time. We can find it in the xtrabackup_binlog_info file:$ cat full_backup/xtrabackup_binlog_info master-bin.000004 1601 0ec00eed-87f3-11eb-acd9-98af65266957:1-56Now we are ready to perform restore. First, let’s restore the table foo from the backup. Restoring individual tablespaces requires the ALTER TABLE... IMPORT TABLESPACE command. This command assumes that the table exists in the server. However, in our case, it was dropped and therefore we need to re-create it. We will recreate the full database test from the file test_structure.sql Since we do not want these administrative tasks to be re-applied, I suggest disabling binary logging for the session which will recreate the database structure.mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> source test_structure.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ....Once tables are recreated discard their tablespaces. I will show an example for the table foo. Adjust the code for the rest of the tables.mysql> alter table foo discard tablespace; Query OK, 0 rows affected (0.01 sec)Then, in another terminal, copy the tablespace and configuration files from the backup to the database directory:cp full_backup/test/foo.{ibd,cfg} var/mysqld.1/data/test/And, finally, import the tablespace:mysql> alter table foo import tablespace; Query OK, 0 rows affected (0.05 sec)Repeat for the other tables in the database test. Now you can enable binary logging back. You can do the same task in a script. For example:for table in `mysql test --skip-column-names --silent -e "show tables"` > do > mysql test -e "set sql_log_bin=0; alter table $table discard tablespace" > cp full_backup/test/$table.{ibd,cfg} var/mysqld.1/data/test/ > mysql test -e "set sql_log_bin=0; alter table $table import tablespace" > doneOur tables are recovered but do not have the updates made after the backup.mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) mysql> select * from bar; +----+------+ | id | f1 | +----+------+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | | 5 | 5 | +----+------+ 5 rows in set (0.00 sec) mysql> select * from baz; Empty set (0.00 sec)Therefore, we need to restore data from the binary logs. To do it we first need to identify the GTID of the disaster event. It can be done if we dump all binary logs updated and created after backup into a dump file and then search for the DROP TABLE and DELETE statements and skipping them. First, let’s check which binary logs do we have.mysql> show binary logs; +-------------------+-----------+ | Log_name | File_size | +-------------------+-----------+ | master-bin.000001 | 1527476 | | master-bin.000002 | 3035 | | master-bin.000003 | 1987 | | master-bin.000004 | 2466 | | master-bin.000005 | 784 | +-------------------+-----------+ 5 rows in set (0.00 sec)So we need to parse them, starting from the log master-bin.000004 and position 1601:mysqlbinlog --start-position=1601 -vvv --base64-output=decode-rows --database=test master-bin.000004 master-bin.000005 > binlog_test.sqlI used options -vvv that prints SQL representation of row events, so we can find the one which we want to skip and --base64-output=decode-rows to not print row events at all. We will not use this file for the restore, only for searching the DROP TABLE and DELETE events. Here they are, at the positions 2007 and 2123, with GTID 0ec00eed-87f3-11eb-acd9-98af65266957:58 and 0ec00eed-87f3-11eb-acd9-98af65266957:59# at 2007 #210321 13:29:58 server id 1 end_log_pos 2123 CRC32 0xd1eb9854 Query thread_id=138 exec_time=0 error_code=0 use `test`/*!*/; SET TIMESTAMP=1616322598/*!*/; DROP TABLE `foo` /* generated by server */ /*!*/; # at 2123 #210321 13:30:08 server id 1 end_log_pos 2188 CRC32 0xfc9b2088 GTID last_committed=7 sequence_number=8 rbr_only=yes original_committed_timestamp=0 immediate_commit_timestamp=0 transaction_length=0 /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; # original_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET) # immediate_commit_timestamp=0 (1970-01-01 02:00:00.000000 EET) /*!80001 SET @@session.original_commit_timestamp=0*//*!*/; /*!80014 SET @@session.original_server_version=0*//*!*/; /*!80014 SET @@session.immediate_server_version=0*//*!*/; SET @@SESSION.GTID_NEXT= '0ec00eed-87f3-11eb-acd9-98af65266957:59'/*!*/; # at 2188 #210321 13:30:08 server id 1 end_log_pos 2260 CRC32 0x1d525b11 Query thread_id=138 exec_time=0 error_code=0 SET TIMESTAMP=1616322608/*!*/; BEGIN /*!*/; # at 2260 #210321 13:30:08 server id 1 end_log_pos 2307 CRC32 0xb57ecb73 Table_map: `test`.`bar` mapped to number 226 # at 2307 #210321 13:30:08 server id 1 end_log_pos 2387 CRC32 0x6770a7e2 Delete_rows: table id 226 flags: STMT_END_F ### DELETE FROM `test`.`bar` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=1 /* INT meta=0 nullable=1 is_null=0 */ ### DELETE FROM `test`.`bar` ### WHERE ...Note that decoded row event contains a DELETE command for each affected row. We may also find to which binary log this event belongs if search for the "Rotate to" event. In our case “Rotate to master-bin.000005” happened after the found positions, so we only need file master-bin.000004 In your case, you may need to skip events from the previous log files too. So to restore the data we need to run mysqlbinlog one more time, this time with parameters:mysqlbinlog  --start-position=1601 --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 --database=test --skip-gtids=true master-bin.000004 master-bin.000005 > binlog_restore.sqlI removed options -vvvbecause we are not going to examine this restore file and option --base64-output=decode-rows because we need row events to present in the resulting file. I also used option --exclude-gtids=0ec00eed-87f3-11eb-acd9-98af65266957:58-59 to exclude GTIDs that we do not want to re-apply. We also need to use --skip-gtids=true because otherwise updates will be skipped since such GTIDs already exist on the server. Now binlog_restore.sql contains all updates to the database test made after the backup and before the DROP statement. Let’s restore it.mysql test < binlog_restore.sqlRestore went successfully. Our tables have all past updates.mysql> select * from foo; +----+------+ | id | f1 | +----+------+ | 1 | 2 | | 2 | 4 | | 3 | 6 | | 4 | 8 | | 5 | 10 | +----+------+ 5 rows in set (0.01 sec) mysql> select count(*) from bar; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from baz; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) Conclusion You may save the time required for PITR if use the per-database restore method. However, you need to take into account the following considerations: mysqlbinlog does not support filtering per table, therefore you either need to restore the full database or use a fake server method, described in MySQL Point in Time Recovery the Right Way. Per-database filters depend on the USE statement in the statement-based binary log format. Therefore option --database can only be considered safe with a row-based format. If you do not use GTID you still can use this method. You will need to combine options --start-position and --stop-position to skip the event. Percona XtraBackup is a free, open source database backup solution for Percona Server for MySQL and MySQL.

Upgrading to MySQL 8: Embrace the Challenge

Nobody likes change, especially when that change may be challenging.  When faced with a technical challenge, I try to remember this comment from Theodore Roosevelt: “Nothing in the world is worth having or worth doing unless it means effort, pain, difficulty.”  While this is a bit of an exaggeration, in this case, the main concept is still valid.  We shouldn’t shy away from an upgrade path because it may be difficult. MySQL 8.0 is maturing and stabilizing.  There are new features (too many to list here) and performance improvements.  More and more organizations are upgrading to MySQL 8 and running it in production, which expedites the stabilization.  While there is still some significant runway on 5.7 and it is definitely stable (EOL slated for October 2023), organizations need to be preparing to make the jump if they haven’t already.  What Changed? So how is a major upgrade to 8.0 different than in years past?  It honestly really isn’t that different.  The same general process applies: Upgrade in a lower environment Test, test, and then test some more Upgrade a replica and start sending read traffic Promote a replica to primary Be prepared for a rollback as needed The final bullet point is the biggest change, especially once you have finished upgrading to MySQL 8.  Historically, minor version upgrades were fairly trivial.  A simple instance stop, binary swap, and instance start were enough to revert to a previous version.   In 8.0, this process is no longer supported (as noted in the official documentation):  “Downgrade from MySQL 8.0 to MySQL 5.7, or from a MySQL 8.0 release to a previous MySQL 8.0 release, is not supported.” That is a definite change in the release paradigm, and it has shown real issues across minor releases.  One good example of how this can impact a live system was captured well in the blog post MySQL 8 Minor Version Upgrades Are ONE-WAY Only from early 2020.  How to Approach Upgrading to MySQL 8 With this new paradigm, it may seem scary to push through with the upgrade.  I think in some ways, it can be a positive change.  As mentioned above, proper preparation and testing should be the majority of the process.  The actual upgrade/cutover should essentially be a non-event.  There is nothing a DBA loves more than finalizing an upgrade with nobody noticing (aside from any potential improvements). Unfortunately, in practice, proper testing and preparation is generally an afterthought.  With how easy upgrades (and particularly rollbacks) have been, it was generally easier to just “give it a shot and roll back if needed”.  As downgrades are no longer trivial, this should be viewed as a golden opportunity to enhance the preparation phase of an upgrade.   Some extra focus should be given to: Reviewing the release notes in detail for any potential changes (new features are also sometimes enabled by default in 8.0) Testing the system with REAL application traffic (benchmarks are nice, but mean nothing if they are generic) Solidifying the backup and restore process (this is already perfect, right?) Reviewing automation (this means no more automated “silent” upgrades) The actual upgrade process (starting at the bottom of the replication chain and maintaining replicas for rollbacks if needed) With so much emphasis on the preparation, we should hopefully start to see the actual upgrade become less impactful.  It should also instill more confidence across the organization. What About “At Scale”? Having worked with a wide range of clients as a TAM, I’ve seen environments that range from a single primary/replica pair to 1000s of servers.  I will freely admit that completing a major version upgrade across 10,000 servers is non-trivial.  Nothing is more painful than needing to rollback 5000 servers when something crops up halfway through the upgrade process.  While no amount of testing can completely eliminate this possibility, we can strive to minimize the risk. At this scale, testing actual traffic patterns is so much more critical.  When you are looking at complex environments and workloads, the likelihood of hitting an edge case definitely increases.  Identifying those edge cases in lower environments is critical for a smooth process in production.  Similarly, ensuring processes and playbooks exist for rolling back (in the event an issue does appear) is critical.   Finally, deploying upgrades in phases is also critical.  Assuming you have monitoring such as Percona Monitoring and Management in place, A/B testing and comparison can be invaluable.  Seeing version X and Y on the same dashboard while serving the same traffic allows a proper comparison.  Comparing X in staging to Y in production is important, but can sometimes be misleading.   Conclusion Overall, upgrading to MySQL 8 isn’t that different from previous versions.  Extra care needs to be taken during the preparation phase, but that should be viewed as a positive overall.  We definitely shouldn’t shy away from the change, but rather embrace it as it needs to happen eventually.  The worst thing that can happen is to continue to kick the can down the road and then be pressed for time as 5.7 EOL approaches. To solidify the preparation phase and testing, what tools do you think are missing?  What would make it easier to accurately replay traffic against test instances?  While there are tools available, is there anything that would help ensure these best practices are followed? If your organization needs help in preparing for or completing an upgrade, our Professional Services team can be a great asset.  Likewise, our Support engineers can help your team as you hit edge cases in testing.   And, finally, the most important strategy when it comes to upgrades: read-only Friday should be at the top of your playbook!