Migrating from MariaDB to MySQL

How to migrate to the Original MySQL on premise or on OCI MySQL HeatWave

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL Migrating from MariaDB to MySQL How to migrate to the Original !
2. Frédéric Descamps Community Manager Oracle MySQL Migrating from MariaDB to MySQL HeatWave How to migrate to the Original... in the Cloud !
3. Who am I ? about.me/lefred Copyright @ 2023 Oracle and/or its affiliates. 3
4. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in h ps://lefred.be Frédéric Descamps Copyright @ 2023 Oracle and/or its affiliates. 4
5. Agenda Copyright @ 2023 Oracle and/or its affiliates. 5
6. Agenda Copyright @ 2023 Oracle and/or its affiliates. 6
7. Why Migrate from MariaDB to MySQL ? Search for Incompatibilities High Availability Storage Engines Functions Data Types Agenda Copyright @ 2023 Oracle and/or its affiliates. 6
8. Why Migrate from MariaDB to MySQL ? Search for Incompatibilities High Availability Storage Engines Functions Data Types Exporting the Data Importing the Data Users and Authentication Agenda Copyright @ 2023 Oracle and/or its affiliates. 6
9. Why Migrate from MariaDB to MySQL ? Search for Incompatibilities High Availability Storage Engines Functions Data Types Exporting the Data Importing the Data Users and Authentication Migration with Minimal Downtime Extra Sequences System-Versioned Tables SQL Mode Agenda Copyright @ 2023 Oracle and/or its affiliates. 6
10. Why Migrate from MariaDB to MySQL ? it's time ! Copyright @ 2023 Oracle and/or its affiliates. 7
11. Why Migrate from MariaDB to MySQL ? . Oracle's support . Improved performance . New data dictionary . Improved Security, especially with MySQL Enterprise Edition . Native JSON Datatype . MySQL Document Store . They are not drop-in replacements for each others anymore . Vendor Locking: MySQL and various distros are real drop-in replacements . Stability . You get the same as Community Edition with additions when using the Enterprise Edition, not less. Copyright @ 2023 Oracle and/or its affiliates. 8
12. Why Migrate to MySQL HeatWave ? . Oracle's support . Fully managed cloud database Service . Operated by the MySQL Team . Security & Compliance: network isolation, encryption at rest and in transit, secure user authentication (GDPR & HIPAA) . Scalability (shapes and read replicas) . Best High Availability based on Group Replication . Peformance with HeatWave engine for OLTP and/or OLAP . Machine Learning capabilities . LakeHouse (Data Lakes and Data Warehouses) . Cost Saving (most competitive pay-per-use service) Copyright @ 2023 Oracle and/or its affiliates. 9
13. Why Migrate from MariaDB to MySQL ? Copyright @ 2023 Oracle and/or its affiliates. 10
14. Why Migrate from MariaDB to MySQL ? "A Grande Catastrophe" MariaDB headlines in the main Finishi daily HS. Copyright @ 2023 Oracle and/or its affiliates. 10
15. h ps://www.continuent.com/resources/blog/grande-catastrophe-mariadb-headlines-main- nnish-daily-hs "I am aware that MariaDB is losing $50M a year." "...investors seemed to have pulled out $266M, out $269M of the proceeds..." "...leaving just $2.6M additional funding." Copyright @ 2023 Oracle and/or its affiliates. 11
16. h ps://twi er.com/ElinaLappalaine/status/1615655206460481537 Business journalist in @Hs_visio @hs . Covering startups, games, technology. Awarded author of 5 non ction books. elina.lappalainen@hs. "MariaDB's SPAC was a disaster, 99% of the investors redeemed their money." Copyright @ 2023 Oracle and/or its affiliates. 12
17. h ps://twi er.com/TechJournalist/status/1605049374702276608 "I don't recall ever seeing as catastrophic a disaster in the database industry as the @mariadb IPO/SPAC thing ..." "...down 40% on its rst day..." Copyright @ 2023 Oracle and/or its affiliates. 13
18. h ps://www.hbl. /artikel/ab21e0b5-b226-46e6-82b3-8a253e28c11a "MariaDB opped on the stock market - needs more money immediately."" Finland's leading Newspaper Copyright @ 2023 Oracle and/or its affiliates. 14
19. h ps://twi er.com/GlogauGordon/status/1605194604751384578 Strategy & Corporate Development @Paylocity|Ex. @GoldmanSachs Investment Banking Division (TMT) "Really was doomed from the start..."" Copyright @ 2023 Oracle and/or its affiliates. 15
20. MariaDB SEC Filings: h ps://www.nasdaq.com/market-activity/stocks/mrdb/sec- lings "There is substantial doubt about our ability to continue as a going concern…" "We anticipate needing to raise additional capital to meet our projected working capital, operating needs, and debt repayment for periods after June 30, 2023" Copyright @ 2023 Oracle and/or its affiliates. 16
21. h ps://medium.com/@imashadowphantom/mariadb-com-is-dead-long-live-mariadb-org-b8a0ca50a637 "The lawsuits are piling up and the employees are going to take the hit. Payroll is going to be missed." Copyright @ 2023 Oracle and/or its affiliates. 17
22. Source: h ps:// nance.yahoo.com/quote/MRDB/ Copyright @ 2023 Oracle and/or its affiliates. 18
23. h ps://www.google.com/ nance/quote/MRDB:NYSE Stock's value is now very low Copyright @ 2023 Oracle and/or its affiliates. 19
24. h ps://www.infoworld.com/article/3693711/after-job-cuts-mariadb-faces-uncertain- nancial-future.html "...in addition to laying o 26 sta ers..." "...revenue won't be enough to support operations for the next 12 months..." Copyright @ 2023 Oracle and/or its affiliates. 20
25. Why Migrate from MariaDB to MySQL ? If you need a Cloud Provider, Commerical Support and a strong company behind your Open Source database MySQL is the answer ! Copyright @ 2023 Oracle and/or its affiliates. 21
26. Oracle Word's Most Popular Commercial Database MySQL Word's Most Popular Open Source Database Developed at Oracle World's #1 and #2 Most Popular Databases Copyright @ 2023 Oracle and/or its affiliates. 22
27. Search for Incompatibilities not a drop-in replacement anymore Copyright @ 2023 Oracle and/or its affiliates. 23
28. Not a drop-in replacement anymore MariaDB Version Drop-in Replacement Transportable ibd Logical 5.5 * (using 5.7) * * 10.3 * * * 10.4   * 10.5   * 10.6   * 10.8   * 10.9   * 10.10   * 10.11   * Copyright @ 2023 Oracle and/or its affiliates. 24
29. 2023-04-22T08:59:02Z UTC - mysqld got signal 8 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware. BuildID[sha1]=e0d4895db012310b67f6c40df56188f44a982ce2 Thread pointer: 0x7fe780012c40 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7fe7e41f5c00 thread_stack 0x100000 /home/fred/opt/mysql/8.0.33/bin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x2e) [0x206ba4e] /home/fred/opt/mysql/8.0.33/bin/mysqld(print_fatal_signal(int)+0x35f) [0xfb8adf] /home/fred/opt/mysql/8.0.33/bin/mysqld(handle_fatal_signal+0xa5) [0xfb8b95] /lib64/libc.so.6(+0x3cb20) [0x7fe7f4c5fb20] /home/fred/opt/mysql/8.0.33/bin/mysqld(AbstractCallback::init(unsigned long, buf_block_t const*)+0x123) [0x21bcd23] /home/fred/opt/mysql/8.0.33/bin/mysqld(fil_tablespace_iterate(Encryption_metadata const&, dict_table_t*, unsigned long, Compression::Type, /home/fred/opt/mysql/8.0.33/bin/mysqld(row_import_for_mysql(dict_table_t*, dd::Table*, row_prebuilt_t*)+0xa8b) [0x21c3c8b] /home/fred/opt/mysql/8.0.33/bin/mysqld(ha_innobase::discard_or_import_tablespace(bool, dd::Table*)+0x34b) [0x20c27cb] /home/fred/opt/mysql/8.0.33/bin/mysqld(Sql_cmd_discard_import_tablespace::mysql_discard_or_import_tablespace(THD*, Table_ref*)+0x183) [0xee /home/fred/opt/mysql/8.0.33/bin/mysqld(mysql_execute_command(THD*, bool)+0xb01) [0xe563c1] /home/fred/opt/mysql/8.0.33/bin/mysqld(dispatch_sql_command(THD*, Parser_state*)+0x4f4) [0xe5a024] /home/fred/opt/mysql/8.0.33/bin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0xd14) [0xe5b2d4] /home/fred/opt/mysql/8.0.33/bin/mysqld(do_command(THD*)+0x1df) [0xe5d65f] /home/fred/opt/mysql/8.0.33/bin/mysqld() [0xfa95a0] /home/fred/opt/mysql/8.0.33/bin/mysqld() [0x26d2805] /lib64/libc.so.6(+0x8b12d) [0x7fe7f4cae12d] /lib64/libc.so.6(+0x10cbc0) [0x7fe7f4d2fbc0] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7fe780161630): alter table employees import tablespace Copyright @ 2023 Oracle and/or its affiliates. 25
30. Migrate to DBaaS However, to migrate to a MySQL Service in the cloud like MySQL HeatWave in OCI, you must use a logical dump because, you do not have access to the le system as a user. Copyright @ 2023 Oracle and/or its affiliates. 26
31. High Availability In MariaDB, HA is provided by Galera, a plugin developed by another company, Codership. MySQL includes native, built-in, HA and DR using Group Replication, InnoDB Cluster, ClusterSet and ReplicaSet. Data provisioning is also natively inegrated with InnoDB CLONE. All tables must have a primary key and use InnoDB. GIPK mode: SET SET PERSIST sql_generate_invisible_primary_key PERSIST sql_generate_invisible_primary_key= =1 1; ; Copyright @ 2023 Oracle and/or its affiliates. 27
32. Copyright @ 2023 Oracle and/or its affiliates. 28
33. Copyright @ 2023 Oracle and/or its affiliates. 29
34. Copyright @ 2023 Oracle and/or its affiliates. 30
35. Copyright @ 2023 Oracle and/or its affiliates. 31
36. All components of these High Availability and Disaster Recovery Solutions are GPL ! No propietary BSL Copyright @ 2023 Oracle and/or its affiliates. 32
37. In MySQL HeatWave, we use the same backend technologies to provide High Availability, Disaster Recovery: MySQL Group Replication, Asynchronous Replication and Asynchronous Connection Failover Copyright @ 2023 Oracle and/or its affiliates. 33
38. Additionally, MySQL HeatWave provides point-in-time recovery and Read Replicas Copyright @ 2023 Oracle and/or its affiliates. 34
39. MySQL HeatWave High Availability When creating a DB System, you can choose to enable HA: Copyright @ 2023 Oracle and/or its affiliates. 35
40. MySQL HeatWave High Availability (2) You have the possibility to choose the preferred Availability Domain for the Primary Primary node: Copyright @ 2023 Oracle and/or its affiliates. 36
41. MySQL HeatWave High Availability (3) And even a fault domain: Copyright @ 2023 Oracle and/or its affiliates. 37
42. MySQL HeatWave High Availability (4) And as recommended, you can also enable the GIPK Mode for your MySQL HeatWave instance: Copyright @ 2023 Oracle and/or its affiliates. 38
43. MySQL HeatWave Point-in-Time Recovery Point-in-Time can be enabled in the backup section: Copyright @ 2023 Oracle and/or its affiliates. 39
44. MySQL HeatWave Read Replicas It's very easy to create Read Replicas for a DB System: Copyright @ 2023 Oracle and/or its affiliates. 40
45. MySQL HeatWave Read Replicas (2) Architecture Example: Copyright @ 2023 Oracle and/or its affiliates. 41
46. Search for Incompatibilities Storage Engines, Functions, Data Types Copyright @ 2023 Oracle and/or its affiliates. 42
47. Storage Engines MariaDB Community Edition, contains various storage engines, in alpha or beta stages. These engines are not included in MariaDB Enterprise Edition. InnoDB is the main engine used for transaction processing. Before migration, you will need to convert data in other storage engines to InnoDB. MySQL primary storage engine is InnoDB. Copyright @ 2023 Oracle and/or its affiliates. 43
48. Storage Engines (2) Verify the Storage Engines actually used on your database: SELECT SELECT COUNT COUNT( (* *) ) as as '# TABLES' '# TABLES', , CONCAT CONCAT( (ROUND ROUND( (sum sum( (data_length data_length) ) / / ( ( 1024 1024 * * 1024 1024 * * 1024 1024 ) ), , 2 2) ), , 'G' 'G') ) DATA DATA, , CONCAT CONCAT( (ROUND ROUND( (sum sum( (index_length index_length) ) / / ( ( 1024 1024 * * 1024 1024 * * 1024 1024 ) ), , 2 2) ), , 'G' 'G') ) INDEXES INDEXES, , CONCAT CONCAT( (sum sum( (ROUND ROUND( (( ( data_length data_length + + index_length index_length ) ) / / ( ( 1024 1024 * * 1024 1024 * * 1024 1024 ) ), , 2 2) )) ), , 'G' 'G') ) 'TOTAL SIZE' 'TOTAL SIZE', , ENGINE ENGINE FROM FROM information_schema information_schema. .TABLES TABLES WHERE WHERE TABLE_SCHEMA TABLE_SCHEMA NOT NOT IN IN ( ('mysql' 'mysql', , 'information_schema' 'information_schema', , 'performance_schema' 'performance_schema', , 'sys' 'sys') ) GROUP GROUP BY BY engine engine; ; + +----------+-------+---------+------------+--------+ ----------+-------+---------+------------+--------+ | | # TABLES | DATA | INDEXES | TOTAL SIZE | ENGINE | # TABLES | DATA | INDEXES | TOTAL SIZE | ENGINE | + +----------+-------+---------+------------+--------+ ----------+-------+---------+------------+--------+ | | 1 1 | | 0.00 0.00G G | | 0.00 0.00G G | | 0.00 0.00G G | | Aria Aria | | | | 5 5 | | 0.00 0.00G G | | 0.00 0.00G G | | 0.00 0.00G G | | InnoDB InnoDB | | | | 1 1 | | 0.00 0.00G G | | 0.00 0.00G G | | 0.00 0.00G G | | MyISAM MyISAM | | + +----------+-------+---------+------------+--------+ ----------+-------+---------+------------+--------+ Copyright @ 2023 Oracle and/or its affiliates. 44
49. Storage Engines (3) From the previous slide's output we can see that there is one table using a Storage Engine not supported in MySQL 8.0: SELECT SELECT TABLE_SCHEMA TABLE_SCHEMA, , TABLE_NAME TABLE_NAME, , ENGINE ENGINE FROM FROM information_schema information_schema. .TABLES TABLES WHERE WHERE TABLE_SCHEMA TABLE_SCHEMA NOT NOT IN IN ( ('mysql' 'mysql', , 'information_schema' 'information_schema', , 'performance_schema' 'performance_schema', , 'sys' 'sys') ) AND AND engine engine NOT NOT IN IN ( ('MyISAM' 'MyISAM', ,'InnoDB' 'InnoDB') ); ; + +--------------+------------+--------+ --------------+------------+--------+ | | TABLE_SCHEMA TABLE_SCHEMA | | TABLE_NAME TABLE_NAME | | ENGINE ENGINE | | + +--------------+------------+--------+ --------------+------------+--------+ | | mydatabase mydatabase | | t4 t4 | | Aria Aria | | + +--------------+------------+--------+ --------------+------------+--------+ 1 1 row row in in set set ( (0.001 0.001 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 45
50. Storage Engines (4) To x it, you need to convert it to InnoDB: ALTER ALTER TABLE TABLE mydatabase mydatabase. .t4 t4 ENGINE ENGINE= =InnoDB InnoDB; ; (*) There is a limitation on row size when using InnoDB, changing charset can help Copyright @ 2023 Oracle and/or its affiliates. 46
51. Functions MariaDB has some functions that are not present or having another name in MySQL, like JSON_DETAILED, which is called JSON_PRETTY in MySQL 8.0. MariaDB maintains a list of these functions but the information is sometimes outdated. Check h ps://mariadb.com/kb/en/incompatibilities-and-feature-di erences-between- mariadb-10-6-and-mysql-8-/, but pay a ention that invisible columns, virtual columns, wait, intersect, except and more are also available in MySQL 8.0. Copyright @ 2023 Oracle and/or its affiliates. 47
52. Functions (2) This is not a blocking factor unless those functions are present in the default value of a column. If your application uses some of these functions, it may be necessary to modify it to use the appropriate one in MySQL 8.0. Copyright @ 2023 Oracle and/or its affiliates. 48
53. Functions (2) This is not a blocking factor unless those functions are present in the default value of a column. If your application uses some of these functions, it may be necessary to modify it to use the appropriate one in MySQL 8.0. To illustrate this, let’s use the ADD_MONTHS function. Copyright @ 2023 Oracle and/or its affiliates. 48
54. Functions - ADD_MONTHS example First let’s see if we have this function as default for some columns: SELECT SELECT TABLE_NAME TABLE_NAME, , COLUMN_NAME COLUMN_NAME FROM FROM information_schema information_schema. .COLUMNS COLUMNS WHERE WHERE COLUMN_DEFAULT COLUMN_DEFAULT LIKE LIKE '%add_months%' '%add_months%'; ; Empty Empty set set ( (0.055 0.055 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 49
55. Functions - ADD_MONTHS example First let’s see if we have this function as default for some columns: SELECT SELECT TABLE_NAME TABLE_NAME, , COLUMN_NAME COLUMN_NAME FROM FROM information_schema information_schema. .COLUMNS COLUMNS WHERE WHERE COLUMN_DEFAULT COLUMN_DEFAULT LIKE LIKE '%add_months%' '%add_months%'; ; Empty Empty set set ( (0.055 0.055 sec sec) ) Great !…. mmm but I’m sure I’ve created a table with that speci c function as default. This is what I did: ALTER ALTER TABLE TABLE t6 t6 ADD ADD COLUMN COLUMN future future DATETIME DATETIME DEFAULT DEFAULT ( (ADD_MONTHS ADD_MONTHS( (NOW NOW( () ), , 2 2) )) ); ; Copyright @ 2023 Oracle and/or its affiliates. 49
56. Functions - ADD_MONTHS example (2) In fact, several functions are acting like aliases. If we check the output of SHOW CREATE TABLE statement, we can see that the function is translated: SHOW SHOW CREATE CREATE TABLE TABLE t6\G t6\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Table Table: t6 : t6 Create Create Table Table: : CREATE CREATE TABLE TABLE ` `t6 t6` ` ( ( ` `id id` ` int int( (11 11) ) NOT NOT NULL NULL DEFAULT DEFAULT nextval nextval( (` `mydatabase mydatabase` `. .` `s3 s3` `) ), , ` `b b` ` int int( (11 11) ) DEFAULT DEFAULT NULL NULL, , ` `future future` ` datetime datetime DEFAULT DEFAULT ( (current_timestamp current_timestamp( () ) + + interval interval 2 2 month month) ), , PRIMARY PRIMARY KEY KEY ( (` `id id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =latin1 latin1 COLLATE COLLATE= =latin1_swedish_ci latin1_swedish_ci 1 1 row row in in set set ( (0.000 0.000 sec sec) ) We can see that ADD_MONTHS() was translated by + interval 2 month. Copyright @ 2023 Oracle and/or its affiliates. 50
57. Functions - ADD_MONTHS example (3) This means that this function is not a problem when it has been used as default value when creating a table. Now let's check if an application is using that function in the queries sent to the database. We have 3 options: enabling and parsing general log enabling and parsing slow log (slow_query_log=1 & long_query_time=0) using Performance_Schema Copyright @ 2023 Oracle and/or its affiliates. 51
58. Functions - ADD_MONTHS example (4) Let's use Performance_Schema: SELECT SELECT DIGEST_TEXT DIGEST_TEXT FROM FROM performance_schema performance_schema. .events_statements_summary_by_digest events_statements_summary_by_digest WHERE WHERE DIGEST_TEXT DIGEST_TEXT LIKE LIKE '%add_months%' '%add_months%'; ; + +------------------------------------------------------------------------------------------+ ------------------------------------------------------------------------------------------+ | | DIGEST_TEXT DIGEST_TEXT | | + +------------------------------------------------------------------------------------------+ ------------------------------------------------------------------------------------------+ | | ALTER ALTER TABLE TABLE ` `t6 t6` ` ADD ADD COLUMN COLUMN ` `future future` ` DATETIME DATETIME DEFAULT DEFAULT ( ( ADD_MONTHS ADD_MONTHS ( ( NOW NOW ( ( ) ) , , ? ? ) ) ) ) | | | | SELECT SELECT ID ID , , ` `b b` ` , , ADD_MONTHS ADD_MONTHS ( ( ` `future future` ` , , ? ? ) ) ` `present present` ` FROM FROM ` `t6 t6` ` | | | | SELECT SELECT ID ID , , ` `b b` ` , , ADD_MONTHS ADD_MONTHS ( ( ` `future future` ` , , ? ? ) ) ` `present present` ` FROM FROM ` `t6 t6` ` ORDER ORDER BY BY ` `b b` ` | | | | SELECT SELECT ADD_MONTHS ADD_MONTHS ( ( ` `future future` ` , , ? ? ) ) ` `present present` ` , , COUNT COUNT ( ( * * ) ) FROM FROM ` `t6 t6` ` GROUP GROUP BY BY ` `present present` ` | | + +------------------------------------------------------------------------------------------+ ------------------------------------------------------------------------------------------+ 4 4 rows rows in in set set ( (0.000 0.000 sec sec) ) Those last 3 queries should be rewri en ! Copyright @ 2023 Oracle and/or its affiliates. 52
59. Functions - ADD_MONTHS example (5) If the application cannot be easily modi ed, the DBA can use the MySQL Query Rewrite Plugin. SELECT SELECT * * FROM FROM query_rewrite query_rewrite. .rewrite_rules\G rewrite_rules\G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * id: id: 13 13 pattern: pattern: SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , ? ?) ) present present FROM FROM t6 t6 pattern_database: mydatabase pattern_database: mydatabase replacement: replacement: SELECT SELECT ID ID, , b b, , future future + + interval interval ? ? month month present present FROM FROM t6 t6 enabled: YES enabled: YES message: message: NULL NULL pattern_digest: pattern_digest: 528521 528521c1593c34c03cc7a5d00181b06e4df6a5700b4a8e8f871391974b506457 c1593c34c03cc7a5d00181b06e4df6a5700b4a8e8f871391974b506457 normalized_pattern: normalized_pattern: select select ` `ID ID` `, ,` `b b` `, ,` `ADD_MONTHS ADD_MONTHS` `( (` `future future` `, ,? ?) ) from from ` `mydatabase mydatabase` `. .` `t6 t6` ` 1 1 row row in in set set ( (0.01 0.01 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 53
60. Functions - ADD_MONTHS example (6) SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , 2 2) ) present present FROM FROM t6 t6; ; ERROR ERROR 1305 1305 ( (42000 42000) ): : FUNCTION FUNCTION mydatabase mydatabase. .ADD_MONTHS does ADD_MONTHS does not not exist exist SET SET GLOBAL GLOBAL rewriter_enabled rewriter_enabled= =1 1; ; SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , 2 2) ) present present FROM FROM t6 t6; ; + +------+------+---------------------+ ------+------+---------------------+ | | ID ID | | b b | | present present | | + +------+------+---------------------+ ------+------+---------------------+ | | - -100 100 | | 1 1 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -90 90 | | 10 10 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -80 80 | | 99 99 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -70 70 | | 1000 1000 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :42 42 | | + +------+------+---------------------+ ------+------+---------------------+ 4 4 rows rows in in set set, , 1 1 warning warning ( (0.00 0.00 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 54
61. Functions - ADD_MONTHS example (6) SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , 2 2) ) present present FROM FROM t6 t6; ; ERROR ERROR 1305 1305 ( (42000 42000) ): : FUNCTION FUNCTION mydatabase mydatabase. .ADD_MONTHS does ADD_MONTHS does not not exist exist SET SET GLOBAL GLOBAL rewriter_enabled rewriter_enabled= =1 1; ; SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , 2 2) ) present present FROM FROM t6 t6; ; + +------+------+---------------------+ ------+------+---------------------+ | | ID ID | | b b | | present present | | + +------+------+---------------------+ ------+------+---------------------+ | | - -100 100 | | 1 1 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -90 90 | | 10 10 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -80 80 | | 99 99 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :18 18 | | | | - -70 70 | | 1000 1000 | | 2023 2023- -06 06- -15 15 11 11: :20 20: :42 42 | | + +------+------+---------------------+ ------+------+---------------------+ 4 4 rows rows in in set set, , 1 1 warning warning ( (0.00 0.00 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. show show warnings warnings\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * Level Level: Note : Note Code: Code: 1105 1105 Message: Query Message: Query 'SELECT ID, b, ADD_MONTHS(future , 2) present FROM t6' 'SELECT ID, b, ADD_MONTHS(future , 2) present FROM t6' rewritten rewritten to to 'SELECT ID, b, future + interval 2 month present FROM t6' 'SELECT ID, b, future + interval 2 month present FROM t6' by by a query rewrite plugin a query rewrite plugin 1 1 row row in in set set ( (0.00 0.00 sec sec) ) 54
62. Functions - ADD_MONTHS example (7) MySQL Query Rewrite Plugin is not enabled in MySQL HeatWave. Those queries will have to be rewri en in the application. SELECT SELECT ID ID, , b b, , ADD_MONTHS ADD_MONTHS( (future future , , ? ?) ) present present FROM FROM t6 t6 Must become: SELECT SELECT ID ID, , b b, , future future + + interval interval ? ? month month present present FROM FROM t6 t6 Copyright @ 2023 Oracle and/or its affiliates. 55
63. Data types MySQL and MariaDB have some di erent data types. For example, MariaDB supports INET6 as a data type and in MySQL 8.0 IPv6 values are stored into VARBINARY(16). But on the other hand, MySQL 8.0 supports JSON data type that in MariaDB are stored as LONGTEXT like this: ` `doc doc` ` longtext longtext CHARACTER CHARACTER SET SET utf8mb4 utf8mb4 COLLATE COLLATE utf8mb4_bin utf8mb4_bin DEFAULT DEFAULT NULL NULL CHECK CHECK ( (json_valid json_valid( (` `doc doc` `) )) ) Don't forget that with MySQL 8.0, JSON is a native datatype allowing multiple functions and enhancements related to performance and replication. Copyright @ 2023 Oracle and/or its affiliates. 56
64. Data types (2) To list all data types used in your database, you can execute the following query: SELECT SELECT DATA_TYPE DATA_TYPE , , count count( (* *) ) TOT TOT FROM FROM information_schema information_schema. .COLUMNS COLUMNS WHERE WHERE TABLE_SCHEMA TABLE_SCHEMA NOT NOT IN IN ( ('mysql' 'mysql', , 'sys' 'sys', , 'information_schema' 'information_schema', , 'performance_schema' 'performance_schema') ) GROUP GROUP BY BY 1 1; ; + +-----------+-----+ -----------+-----+ | | DATA_TYPE DATA_TYPE | | TOT TOT | | + +-----------+-----+ -----------+-----+ | | bigint bigint | | 14 14 | | | | datetime datetime | | 1 1 | | | | inet6 inet6 | | 1 1 | | | | int int | | 10 10 | | | | longtext longtext | | 3 3 | | | | tinyint tinyint | | 2 2 | | + +-----------+-----+ -----------+-----+ 6 6 rows rows in in set set ( (0.001 0.001 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 57
65. Data types (3) If in MariaDB you use an unknown data type by MySQL 8.0, the logical dump will fail with an error message like this: Util Util. .dumpInstance dumpInstance: : Unknown data_type Unknown data_type: : inet6 and column_type inet6 and column_type: : inet6 inet6 ( (LogicError LogicError) ) Copyright @ 2023 Oracle and/or its affiliates. 58
66. Data types (3) If in MariaDB you use an unknown data type by MySQL 8.0, the logical dump will fail with an error message like this: Util Util. .dumpInstance dumpInstance: : Unknown data_type Unknown data_type: : inet6 and column_type inet6 and column_type: : inet6 inet6 ( (LogicError LogicError) ) If we encounter similar issue, we need to modify the table before launching the dump process: ALTER ALTER TABLE TABLE t5 t5 MODIFY MODIFY address address VARBINARY VARBINARY( (16 16) ); ; Copyright @ 2023 Oracle and/or its affiliates. 58
67. Exporting the Data Logical Dump Copyright @ 2023 Oracle and/or its affiliates. 59
68. parallel dump instance, schema or table compression rate (limiting throughput) integrated with Cloud (Oracle Object Storage, AWS S3, Microsoft Azure Blog Storage) ... Logical Data Dump We use MySQL Shell dump & load utility to perform logical dump. MySQL Shell Dump & Load Utility is the recommended tool to perform MySQL dumps: Please forget mysqldump ;-) Copyright @ 2023 Oracle and/or its affiliates. 60
69. $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance "/tmp/dump_mariadb_10_6" "/tmp/dump_mariadb_10_6" \ \ --users --users= =false false NOTE: Backup lock is not supported NOTE: Backup lock is not supported in in MySQL MySQL 5.6 5.6 and DDL changes will not be blocked. and DDL changes will not be blocked. The dump may fail with an error The dump may fail with an error if if schema changes are made schema changes are made while while dumping. dumping. Acquiring global Acquiring global read read lock lock Global Global read read lock acquired lock acquired Initializing - Initializing - done done WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. 2 2 out of out of 6 6 schemas will be dumped and within them schemas will be dumped and within them 5 5 tables, tables, 0 0 views. views. Gathering information - Gathering information - done done All transactions have been started All transactions have been started Global Global read read lock has been released lock has been released Writing global DDL files Writing global DDL files Running data dump using Running data dump using 4 4 threads. threads. NOTE: Progress information uses estimated values and may not be accurate. NOTE: Progress information uses estimated values and may not be accurate. NOTE: Table statistics not available NOTE: Table statistics not available for for ` `mydatabase mydatabase` `. .` `t2 t2` `, chunking operation may , chunking operation may be not optimal. be not optimal. Please consider running Please consider running 'ANALYZE TABLE `mydatabase`.`t2`;' 'ANALYZE TABLE `mydatabase`.`t2`;' first. first. Copyright @ 2023 Oracle and/or its affiliates. 61
70. Writing schema metadata - Writing schema metadata - done done Writing DDL - Writing DDL - done done Writing table metadata - Writing table metadata - done done Starting data dump Starting data dump 122 122% % ( (11 11 rows / ~9 rows rows / ~9 rows) ), , 0.00 0.00 rows/s, rows/s, 0.00 0.00 B/s uncompressed, B/s uncompressed, 0.00 0.00 B/s compressed B/s compressed Dump duration: 00:00:00s Dump duration: 00:00:00s Total duration: 00:00:00s Total duration: 00:00:00s Schemas dumped: Schemas dumped: 2 2 Tables dumped: Tables dumped: 5 5 Uncompressed data size: Uncompressed data size: 287 287 bytes bytes Compressed data size: Compressed data size: 224 224 bytes bytes Compression ratio: Compression ratio: 1.3 1.3 Rows written: Rows written: 11 11 Bytes written: Bytes written: 224 224 bytes bytes Average uncompressed throughput: Average uncompressed throughput: 287.00 287.00 B/s B/s Average compressed throughput: Average compressed throughput: 224.00 224.00 B/s B/s Copyright @ 2023 Oracle and/or its affiliates. 62
71. Logical Data Dump (2) Copyright @ 2023 Oracle and/or its affiliates. 63
72. Logical Data Dump to OCI If you migrate to MySQL HeatWave on OCI, you can dump the data directly to Object Storage: $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance "fromMariaDB" "fromMariaDB" \ \ --osBucketName --osBucketName= ="migration" "migration" --users --users= =false false --osNamespace --osNamespace= =xxxxxxx xxxxxxx \ \ --threads --threads= =8 8 --ocimds --ocimds= =true true --compatibility --compatibility= ="strip_definers,force_innodb" "strip_definers,force_innodb" NOTE: Backup lock is not supported NOTE: Backup lock is not supported in in MySQL MySQL 5.6 5.6 and DDL changes will not be blocked. and DDL changes will not be blocked. The dump may fail with an error The dump may fail with an error if if schema changes are made schema changes are made while while dumping. dumping. Acquiring global Acquiring global read read lock lock Global Global read read lock acquired lock acquired Initializing - Initializing - done done WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. 3 3 out of out of 7 7 schemas will be dumped and within them schemas will be dumped and within them 7 7 tables, tables, 2 2 out of out of 0 0 views. views. Gathering information - Gathering information - done done All transactions have been started All transactions have been started Global Global read read lock has been released lock has been released Copyright @ 2023 Oracle and/or its affiliates. 64
73. Logical Data Dump to OCI (2) But as you can see, some checks are performed to see how the data is compatible with MySQL HeatWave: Checking Checking for for compatibility with MySQL Database Service compatibility with MySQL Database Service 8.0 8.0.33 .33 NOTE: MySQL Server NOTE: MySQL Server 5.6 5.6 detected, please consider upgrading to detected, please consider upgrading to 8.0 8.0 first. first. ERROR: Table ERROR: Table ` `mydatabase mydatabase` `. .` `t t` ` does not have a Primary Key, does not have a Primary Key, which which is required is required for for High Availability High Availability in in MDS MDS NOTE: Table NOTE: Table ` `mydatabase mydatabase` `. .` `t3 t3` ` had unsupported engine MyISAM changed to InnoDB had unsupported engine MyISAM changed to InnoDB Validating MDS compatibility - Validating MDS compatibility - done done ERROR: While ERROR: While 'Validating MDS compatibility' 'Validating MDS compatibility': : Error Error while while dumping temporary DDL dumping temporary DDL for for view view 'mydatabase' 'mydatabase'. .'s3' 's3': : unordered_map::at unordered_map::at Copyright @ 2023 Oracle and/or its affiliates. 65
74. Logical Data Dump to OCI (3) For the missing primary key, we have an option to force the creation of invisible ones: create_invisible_pks. And for the sequences (or any incompatible tables), we can also skip them using excludeTables. $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance $ mysqlsh root@127.0.0.1:10612 -- util dumpInstance "fromMariaDB" "fromMariaDB" \ \ --osBucketName --osBucketName= ="migration" "migration" --users --users= =false false --osNamespace --osNamespace= =ixxxxxxxxj ixxxxxxxxj \ \ --threads --threads= =8 8 --ocimds --ocimds= =true true --compatibility --compatibility= ="strip_definers,force_innodb,create_invisible_pks" "strip_definers,force_innodb,create_invisible_pks" \ \ --excludeTables --excludeTables= ="mydatabase.s1,mydatabase.s3" "mydatabase.s1,mydatabase.s3" NOTE: Backup lock is not supported NOTE: Backup lock is not supported in in MySQL MySQL 5.6 5.6 and DDL changes will not be blocked. and DDL changes will not be blocked. The dump may fail with an error The dump may fail with an error if if schema changes are made schema changes are made while while dumping. dumping. Acquiring global Acquiring global read read lock lock Global Global read read lock acquired lock acquired Initializing - Initializing - done done WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. WARNING: Failed to fetch value of @@GLOBAL.GTID_EXECUTED. Copyright @ 2023 Oracle and/or its affiliates. 66
75. 3 3 out of out of 7 7 schemas will be dumped and within them schemas will be dumped and within them 7 7 tables, tables, 0 0 views. views. Gathering information - Gathering information - done done All transactions have been started All transactions have been started Global Global read read lock has been released lock has been released Checking Checking for for compatibility with MySQL Database Service compatibility with MySQL Database Service 8.0 8.0.33 .33 NOTE: MySQL Server NOTE: MySQL Server 5.6 5.6 detected, please consider upgrading to detected, please consider upgrading to 8.0 8.0 first. first. NOTE: Table NOTE: Table ` `mydatabase mydatabase` `. .` `t t` ` does not have a Primary Key, this will be fixed does not have a Primary Key, this will be fixed when the dump is loaded when the dump is loaded NOTE: Table NOTE: Table ` `mydatabase mydatabase` `. .` `t3 t3` ` had unsupported engine MyISAM changed to InnoDB had unsupported engine MyISAM changed to InnoDB NOTE: One or NOTE: One or more more tables without Primary Keys were found. tables without Primary Keys were found. Missing Primary Keys will be created automatically when this dump is loaded. Missing Primary Keys will be created automatically when this dump is loaded. This will This will make make it possible to it possible to enable enable High Availability High Availability in in MySQL Database Service MySQL Database Service instance without application impact. instance without application impact. However, Inbound Replication into an MDS HA instance However, Inbound Replication into an MDS HA instance ( (at the at the time time of the release of MySQL of the release of MySQL Shell Shell 8.0 8.0.24 .24) ) will still not be possible. will still not be possible. Compatibility issues with MySQL Database Service Compatibility issues with MySQL Database Service 8.0 8.0.33 were found and repaired. .33 were found and repaired. Please review the changes made before loading them. Please review the changes made before loading them. Validating MDS compatibility - Validating MDS compatibility - done done Writing global DDL files Writing global DDL files Copyright @ 2023 Oracle and/or its affiliates. 67
76. Running data dump using Running data dump using 8 8 threads. threads. NOTE: Progress information uses estimated values and may not be accurate. NOTE: Progress information uses estimated values and may not be accurate. Writing schema metadata - Writing schema metadata - done done NOTE: Table statistics not available NOTE: Table statistics not available for for ` `mydatabase mydatabase` `. .` `t2 t2` `, chunking operation , chunking operation may be not optimal. Please consider running may be not optimal. Please consider running 'ANALYZE TABLE `mydatabase`.`t2`;' 'ANALYZE TABLE `mydatabase`.`t2`;' first. first. Writing DDL - Writing DDL - done done Writing table metadata - Writing table metadata - done done Starting data dump Starting data dump 115 115% % ( (22 22 rows / ~19 rows rows / ~19 rows) ), , 28.00 28.00 rows/s, rows/s, 0.00 0.00 B/s uncompressed, B/s uncompressed, 0.00 0.00 B/s compressed B/s compressed Dump duration: 00:00:01s Dump duration: 00:00:01s Total duration: 00:00:02s Total duration: 00:00:02s Schemas dumped: Schemas dumped: 3 3 Tables dumped: Tables dumped: 7 7 Uncompressed data size: Uncompressed data size: 405 405 bytes bytes Compressed data size: Compressed data size: 349 349 bytes bytes Compression ratio: Compression ratio: 1.2 1.2 Rows written: Rows written: 22 22 Bytes written: Bytes written: 349 349 bytes bytes Average uncompressed throughput: Average uncompressed throughput: 253.80 253.80 B/s B/s Average compressed throughput: Average compressed throughput: 218.71 218.71 B/s B/s Copyright @ 2023 Oracle and/or its affiliates. 68
77. Logical Data Dump to OCI (4) This can also be done interactively: JS JS > > util util. .dumpInstance dumpInstance( ('fromMariaDB_interactive' 'fromMariaDB_interactive', , { {osBucketName osBucketName: : "migration" "migration", , users users: : false false, , osNamespace osNamespace: :'xxxxxxxx' 'xxxxxxxx', , threads threads: :8 8, ,ocimds ocimds: : true true, ,excludeTables excludeTables: : [ ["mydatabase.s1" "mydatabase.s1", ,"mydatabase.s3" "mydatabase.s3"] ], , compatibility compatibility: : [ ["force_innodb" "force_innodb", ,"strip_definers" "strip_definers", ,"create_invisible_pks" "create_invisible_pks"] ]} }) ) Copyright @ 2023 Oracle and/or its affiliates. 69
78. Logical Data Dump to OCI (5) As you can see we can use MySQL Shell Dump & Load in the command line or interactively. But we have to remember to use the {users: false} option as the MariaDB accounts are not compatible with MySQL 8.0. And if you have MyISAM tables and you must convert them to InnoDB, this can be done using force_innodb in the compatibility options. You can also create a PAR (Pre-Authenticated Request) manifest, using {ociParManifest: true} Copyright @ 2023 Oracle and/or its affiliates. 70
79. Logical Data Dump to OCI (6) If you created a PAR manifest, you can create the PAR URL from the @.manifest.json le: Copyright @ 2023 Oracle and/or its affiliates. 71
80. Logical Data Dump to OCI (6) If you created a PAR manifest, you can create the PAR URL from the @.manifest.json le: Copyright @ 2023 Oracle and/or its affiliates. 72
81. Logical Data Dump to OCI (6) If you created a PAR manifest, you can create the PAR URL from the @.manifest.json le: Copyright @ 2023 Oracle and/or its affiliates. 73
82. Importing the Data Fast Load Copyright @ 2023 Oracle and/or its affiliates. 74
83. Logical Data Load On a freshly installed MySQL 8.0 instance, we use again MySQL Shell to load the dump: Copyright @ 2023 Oracle and/or its affiliates. 75
84. The dump in Object Storage can be loaded at the creation of the MySQL HeatWave instance. Logical Data Load to MySQL HeatWave in OCI Copyright @ 2023 Oracle and/or its affiliates. 76
85. Logical Data Load to MySQL HeatWave in OCI (2) Or manually, using MySQL Shell on a compute instance and the PAR URL: JS JS> > util util. .loadDump loadDump( ("https://o..com/p/F..W/n/i..j/b/migration/o/fromMariaDB_par/@.manifest.json" "https://o..com/p/F..W/n/i..j/b/migration/o/fromMariaDB_par/@.manifest.json", , { {progressFile progressFile: : "progress.json" "progress.json", , ignoreVersion ignoreVersion: : true true} }) ) Copyright @ 2023 Oracle and/or its affiliates. 77
86. Users and Authentication dump & load users and grants Copyright @ 2023 Oracle and/or its affiliates. 78
87. Users and Authentication To be able to create the logical dump we had to skip the users ({users: false}). I have create a MySQL Shell Plugin to manage users and grants. h ps://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants JS JS > > user user. .getUsersGrants getUsersGrants( ("fred" "fred") ) -- -- User User ` `fred fred` `@ @` `% %` ` CREATE CREATE USER USER IF IF NOT NOT EXISTS EXISTS ` `fred fred` `@ @` `% %` ` IDENTIFIED IDENTIFIED WITH WITH 'mysql_native_password' 'mysql_native_password' AS AS '*6C69D17939B2C1D04E17A96F9B29B284832979B7' '*6C69D17939B2C1D04E17A96F9B29B284832979B7'; ; GRANT GRANT ALL ALL PRIVILEGES PRIVILEGES ON ON * *. .* * TO TO ` `fred fred` `@ @` `% %` `; ; GRANT GRANT SELECT SELECT, , UPDATE UPDATE, , DELETE DELETE ON ON ` `mydatabase mydatabase` `. .* * TO TO ` `fred fred` `@ @` `% %` `; ; And then replay the statements on the new MySQL 8.0 or MySQL HeatWave instance. Copyright @ 2023 Oracle and/or its affiliates. 79
88. Users and Authentication (2) Or we can use the user.copy() method for the on-premise instance: Copyright @ 2023 Oracle and/or its affiliates. 80
89. Users and Authentication (3) Or with the MySQL HeatWave instance: Copyright @ 2023 Oracle and/or its affiliates. 81
90. Users and Authentication (3) Be aware that the authentication plugin used is the old one (mysql_native_password) which is not default in MySQL 8.0. In MySQL 8.0 a more secure authentication method is used: caching_sha2_password. Be aware that in MySQL HeatWave Database Service, some grants are not allowed for the user accounts. Copyright @ 2023 Oracle and/or its affiliates. 82
91. Users and Authentication (4) This is why we used ocimds to true: JS JS > > \h user \h user. .copy copy NAME NAME copy copy - - Copy a user to another server Copy a user to another server SYNTAX SYNTAX user user. .copy copy( ([ [dryrun dryrun] ][ [, , ocimds ocimds] ][ [, , force force] ][ [, , session session] ]) ) WHERE WHERE dryrun dryrun: : Bool Bool - - Don't run the statements but only shows them Don't run the statements but only shows them. . ocimds ocimds: : Bool Bool - - Use Use OCI OCI MDS MDS compatibility mode compatibility mode. . Default is False Default is False. . force force: : Bool Bool - - Reply Reply "yes" "yes" to all questions when the plan is to copy to all questions when the plan is to copy multiple users multiple users. . Default is False Default is False. . session session: : Object Object - - The optional session object used to query the database The optional session object used to query the database. . If omitted the MySQL Shell's current session will be used If omitted the MySQL Shell's current session will be used. . Copyright @ 2023 Oracle and/or its affiliates. 83
92. Users and Authentication (5) Let's compare both users and grants: MariaDB: MySQL HeatWave: Copyright @ 2023 Oracle and/or its affiliates. 84
93. Users and Authentication - MySQL HeatWave Admin You can use the administrator role to have an account with the same privileges of the admin user created during the deployment of the MySQL HeatWave DB Instance: SQL SQL> > GRANT GRANT 'administrator' 'administrator' TO TO 'dev1' 'dev1'; ; Copyright @ 2023 Oracle and/or its affiliates. 85
94. Migration with Minimal Downtime Replication Copyright @ 2023 Oracle and/or its affiliates. 86
95. If you don't use any speci c features related to MariaDB, it's also possible to use standard MySQL Asynchronous Replication between both systems: we use binlog position based replication GTIDs are not compatible Live Migration - Replication Copyright @ 2023 Oracle and/or its affiliates. 87
96. When test are concluded and you are satis ed with the results, that replication is not breaking and that the MySQL Replica is in sync we can start the process: Live Migration - Replication Copyright @ 2023 Oracle and/or its affiliates. 88
97. We point the application to the new MySQL instance. We stop the old MariaDB server. Live Migration - Replication Copyright @ 2023 Oracle and/or its affiliates. 89
98. We have now remove the old server and enjoy MySQL 8.0 Live Migration - Replication Copyright @ 2023 Oracle and/or its affiliates. 90
99. Live Migration - Replication (2) The position to use to setup replication is located in the dump directory, in the @.json le: Copyright @ 2023 Oracle and/or its affiliates. 91
100. Live Migration - Replication (2) The position to use to setup replication is located in the dump directory, in the @.json le: Copyright @ 2023 Oracle and/or its affiliates. 92
101. Live Migration - Replication on-prem We use that information to stetup replication and start it: Copyright @ 2023 Oracle and/or its affiliates. 93
102. Live Migration - Replication on-prem (2) And we can verify that replication is working and progressing: Copyright @ 2023 Oracle and/or its affiliates. 94
103. Live Migration - Replication on OCI We use that information to create an Inbound Replication Channel: Copyright @ 2023 Oracle and/or its affiliates. 95
104. Live Migration - Replication on OCI (2) We need to use the public IP address of the MariaDB or use a VPN: Copyright @ 2023 Oracle and/or its affiliates. 96
105. Live Migration - Replication on OCI (3) The easiest is to use unencrypted connection, otherwise you will need to provide the certi cates: Copyright @ 2023 Oracle and/or its affiliates. 97
106. Live Migration - Replication on OCI (4) We use the previous saved binary log position information: Copyright @ 2023 Oracle and/or its affiliates. 98
107. Live Migration - Replication on OCI (5) The replication events can be ltered out, like ignoring the sequence tables or other system tables: Copyright @ 2023 Oracle and/or its affiliates. 99
108. Extra More Incompatibilities Copyright @ 2023 Oracle and/or its affiliates. 100
109. Sequences System-versioned tables SQL_MODE=ORACLE Be careful ! There are other incompatibilities with MariaDB features that are rarely used: Copyright @ 2023 Oracle and/or its affiliates. 101
110. How to Migrate MariaDB sequences Check if sequences are used: SELECT SELECT COUNT COUNT( (* *) ), , TABLE_TYPE TABLE_TYPE FROM FROM information_schema information_schema. .TABLES TABLES GROUP GROUP BY BY table_type table_type; ; + +----------+------------------+ ----------+------------------+ | | COUNT COUNT( (* *) ) | | TABLE_TYPE TABLE_TYPE | | + +----------+------------------+ ----------+------------------+ | | 117 117 | | BASE BASE TABLE TABLE | | | | 2 2 | | SEQUENCE SEQUENCE | | | | 1 1 | | SYSTEM VERSIONED SYSTEM VERSIONED | | | | 79 79 | | SYSTEM SYSTEM VIEW VIEW | | | | 101 101 | | VIEW VIEW | | + +----------+------------------+ ----------+------------------+ 5 5 rows rows in in set set ( (0.0250 0.0250 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 102
111. How to Migrate MariaDB sequences (2) We can see that we have 2 sequences. Usually sequences are used as default values in columns, if this is the case we can try to nd out which table they are related to: WITH WITH seqlist seqlist ( (a a) ) AS AS ( ( SELECT SELECT CONCAT CONCAT( ('%`' '%`', ,TABLE_SCHEMA TABLE_SCHEMA, ,'`.`' '`.`', , TABLE_NAME TABLE_NAME, ,'`%' '`%') ) a a FROM FROM information_schema information_schema. .TABLES TABLES WHERE WHERE table_type table_type= ="SEQUENCE" "SEQUENCE") ) SELECT SELECT TABLE_NAME TABLE_NAME, , COLUMN_NAME COLUMN_NAME FROM FROM information_schema information_schema. .COLUMNS COLUMNS JOIN JOIN seqlist seqlist WHERE WHERE COLUMN_DEFAULT COLUMN_DEFAULT LIKE LIKE seqlist seqlist. .a a; ; + +------------+-------------+ ------------+-------------+ | | TABLE_NAME TABLE_NAME | | COLUMN_NAME COLUMN_NAME | | + +------------+-------------+ ------------+-------------+ | | t5 t5 | | a a | | | | t6 t6 | | id id | | + +------------+-------------+ ------------+-------------+ 2 2 rows rows in in set set ( (0.023 0.023 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 103
112. How to Migrate MariaDB sequences (3) If we don't x those tables manually, the dump will work but the load will fail with the following messages: ERROR: ERROR: [ [Worker003 Worker003] ] Error processing Error processing table table ` `mydatabase mydatabase` `. .` `t6 t6` `: MySQL Error : MySQL Error 1064 1064 ( (42000 42000) ): : You have an error You have an error in in your your SQL SQL syntax syntax; ; check check the manual that corresponds the manual that corresponds to to your your MySQL server version MySQL server version for for the the right right syntax syntax to to use use near near 'nextval(`mydatabase`.`s3`), 'nextval(`mydatabase`.`s3`), `b` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `future` datetime DEFA' `future` datetime DEFA' at line at line 2 2: : CREATE CREATE TABLE TABLE IF IF NOT NOT EXISTS EXISTS ` `t6 t6` ` ( ( ` `id id` ` int int( (11 11) ) NOT NOT NULL NULL DEFAULT DEFAULT nextval nextval( (` `mydatabase mydatabase` `. .` `s3 s3` `) ), , ` `b b` ` int int( (11 11) ) DEFAULT DEFAULT NULL NULL, , ` `future future` ` datetime datetime DEFAULT DEFAULT ( (current_timestamp current_timestamp( () ) + + interval interval 2 2 month month) ), , PRIMARY PRIMARY KEY KEY ( (` `id id` `) ) ) ) ENGINE ENGINE= =InnoDB InnoDB DEFAULT DEFAULT CHARSET CHARSET= =latin1 latin1 COLLATE COLLATE= =latin1_swedish_ci latin1_swedish_ci ERROR: Aborting ERROR: Aborting load load. .. .. . Copyright @ 2023 Oracle and/or its affiliates. 104
113. How to Migrate MariaDB sequences (4) To x the le, we need to replace in the sql le directly from the dump and replace the unknown syntax. For example in the le mydatabase@t5.sql, this: CREATE CREATE TABLE TABLE IF IF NOT NOT EXISTS EXISTS ` `t5 t5` ` ( ( ` `a a` ` int int( (11 11) ) NOT NOT NULL NULL DEFAULT DEFAULT nextval nextval( (` `mydatabase mydatabase` `. .` `s1 s1` `) ), , becomes: CREATE CREATE TABLE TABLE IF IF NOT NOT EXISTS EXISTS ` `t5 t5` ` ( ( ` `a a` ` int int( (11 11) ) NOT NOT NULL NULL auto_increment auto_increment, , Copyright @ 2023 Oracle and/or its affiliates. 105
114. How to Migrate MariaDB system-versioned tables Using again the same query we used for sequence we can check if the MariaDB system is using system-versioned tables: SELECT SELECT COUNT COUNT( (* *) ), , TABLE_TYPE TABLE_TYPE FROM FROM information_schema information_schema. .TABLES TABLES GROUP GROUP BY BY table_type table_type; ; + +----------+------------------+ ----------+------------------+ | | COUNT COUNT( (* *) ) | | TABLE_TYPE TABLE_TYPE | | + +----------+------------------+ ----------+------------------+ | | 117 117 | | BASE BASE TABLE TABLE | | | | 2 2 | | SEQUENCE SEQUENCE | | | | 1 1 | | SYSTEM VERSIONED SYSTEM VERSIONED | | | | 79 79 | | SYSTEM SYSTEM VIEW VIEW | | | | 101 101 | | VIEW VIEW | | + +----------+------------------+ ----------+------------------+ 5 5 rows rows in in set set ( (0.0250 0.0250 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 106
115. To get a list of the eventual System Versioned tables we run this query. SELECT SELECT TABLE_SCHEMA TABLE_SCHEMA, , TABLE_NAME TABLE_NAME FROM FROM information_schema information_schema. .TABLES TABLES WHERE WHERE TABLE_TYPE TABLE_TYPE= ='system versioned' 'system versioned'; ; + +--------------+------------+ --------------+------------+ | | TABLE_SCHEMA TABLE_SCHEMA | | TABLE_NAME TABLE_NAME | | + +--------------+------------+ --------------+------------+ | | mydatabase mydatabase | | t t | | + +--------------+------------+ --------------+------------+ 1 1 row row in in set set ( (0.0090 0.0090 sec sec) ) How to Migrate MariaDB system-versioned tables (2) We can see there is one table using this feature. If we don't change anything, such table will just be ignored during the dump process. Copyright @ 2023 Oracle and/or its affiliates. 107
116. How to Migrate MariaDB system-versioned tables (3) If we want to migrate the most recent data of the table without the versioning information, we need to drop the versioning. ALTER ALTER TABLE TABLE mydatabase mydatabase. .t t DROP DROP SYSTEM VERSIONING SYSTEM VERSIONING; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0269 0.0269 sec sec) ) Copyright @ 2023 Oracle and/or its affiliates. 108
117. SQL modes Copyright @ 2023 Oracle and/or its affiliates. 109
118. SQL mode = ORACLE SET SET SQL_MODE SQL_MODE= ='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS, 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS, NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT' NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT'; ; This mode is not supported in MySQL 8. Copyright @ 2023 Oracle and/or its affiliates. 110
119. Share your ❤ to MySQL #mysql Join our slack channel! bit.ly/mysql-slack Copyright @ 2023 Oracle and/or its affiliates. 111
120. Resources h ps://lefred.be/content/how-to-migrate-from-mariadb-to-mysql-8-0/ h ps://lefred.be/content/migrating-from-mariadb-to-mysql-using-mysql-shell/ h ps://lefred.be/content/replace-mariadb-10-3-by-mysql-8-0/ h ps://lefred.be/content/migrate-from-mariadb-to-the-mysql-on-centos/ h ps://blogs.oracle.com/mysql/post/how-to-migrate-from-mariadb-to-mysql-80 h ps://blogs.oracle.com/mysql/post/webinar-from-mariadb-to-mysql-80 h ps://blogs.oracle.com/mysql/post/how-to-migrate-from-mariadb-to-mysql- heatwave Copyright @ 2023 Oracle and/or its affiliates. 112
121. Questions ? Copyright @ 2023 Oracle and/or its affiliates. 113