Migration to MySQL - How to swim with the dolphin

MySQL Enterprise Edition Workshop Milano - How to migrate to MySQL

Transcript

1. Frédéric Descamps Community Manager Oracle MySQL MySQL Enterpise Edition Workshop Milano - February 2024 Migration to MySQL How to swim with the dolphin
2. Who am I ? about.me/lefred Copyright @ 2024 Oracle and/or its affiliates. 2
3. @lefred MySQL Evangelist using MySQL since version 3.20 devops believer living in dylandogiano h ps://lefred.be Frédéric Descamps Copyright @ 2024 Oracle and/or its affiliates. 3
4. MySQL Most Popular Open Source Database Copyright @ 2024 Oracle and/or its affiliates. 4
5. Databases = ORACLE If we check which are the most popular databases, we can see that the top 2 are developed by ORACLE: Copyright @ 2024 Oracle and/or its affiliates. 5
6. Databases = ORACLE If we check which are the most popular databases, we can see that the top 2 are developed by ORACLE: MySQL is the most popular Open Source database Copyright @ 2024 Oracle and/or its affiliates. 5
7. Innovative organizations across many industries run MySQL – Do you? Copyright @ 2024 Oracle and/or its affiliates. 6
8. MySQL powers Open Source applications Copyright @ 2024 Oracle and/or its affiliates. 7
9. Not all RDMBS are the same before we start Copyright @ 2024 Oracle and/or its affiliates. 8
10. Before starting a migration to MySQL... ... it's very important to understand the implications of such migration because not all RDBMS are equal and you should not expect the same feaures and capabilities. Copyright @ 2024 Oracle and/or its affiliates. 9
11. Before starting a migration to MySQL... ... it's very important to understand the implications of such migration because not all RDBMS are equal and you should not expect the same feaures and capabilities. If it's generally almost the same when migrating from an Open Source Database, from commercial ones, it might become more complicated. Copyright @ 2024 Oracle and/or its affiliates. 9
12. Before starting a migration to MySQL... ... it's very important to understand the implications of such migration because not all RDBMS are equal and you should not expect the same feaures and capabilities. If it's generally almost the same when migrating from an Open Source Database, from commercial ones, it might become more complicated. But only because of wrong expectations ! Copyright @ 2024 Oracle and/or its affiliates. 9
13. Before starting a migration to MySQL... ... it's very important to understand the implications of such migration because not all RDBMS are equal and you should not expect the same feaures and capabilities. If it's generally almost the same when migrating from an Open Source Database, from commercial ones, it might become more complicated. But only because of wrong expectations ! Set your expectations ahead ! Copyright @ 2024 Oracle and/or its affiliates. 9
14. Commercial RDBMS are not only a Relational Database System For example ORACLE Database is much more than a RDBMS as it includes plenty of other products ! Copyright @ 2024 Oracle and/or its affiliates. 10
15. Our database is used to store data Once you've ensured that the database is only used to store data, you can plan your migration to MySQL. Copyright @ 2024 Oracle and/or its affiliates. 11
16. Our database is used to store data Once you've ensured that the database is only used to store data, you can plan your migration to MySQL. We need to perform several checks: how is the data accessed ? SQL ? extensions ? stored procedures ? data types ? Copyright @ 2024 Oracle and/or its affiliates. 11
17. Connectors Before starting a migration, we need to ensure our application can talk to MySQL. The MySQL Team develop and supports the following connectors: It exists also di erent Community Driven Connectors, like Go and Ruby. Copyright @ 2024 Oracle and/or its affiliates. 12
18. SQL All RDBMSs have di erent levels of compliance with ANSI/ISO SQL standards. Source: h ps://modern-sql.com Copyright @ 2024 Oracle and/or its affiliates. 13
19. SQL Copyright @ 2024 Oracle and/or its affiliates. 14
20. SQL Copyright @ 2024 Oracle and/or its affiliates. 15
21. SQL And sometimes none of them are supported :-D Source: h ps://modern-sql.com Copyright @ 2024 Oracle and/or its affiliates. 16
22. MySQL & ANSI Mode In MySQL the SQL_MODE can be changed to ANSI: MySQL MySQL> > select select @ @@sql_mode @sql_mode\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * @ @@sql_mode @sql_mode: ONLY_FULL_GROUP_BY : ONLY_FULL_GROUP_BY, ,STRICT_TRANS_TABLES STRICT_TRANS_TABLES, ,NO_ZERO_IN_DATE NO_ZERO_IN_DATE, ,NO_ZERO_DATE NO_ZERO_DATE, , ERROR_FOR_DIVISION_BY_ZERO ERROR_FOR_DIVISION_BY_ZERO, ,NO_ENGINE_SUBSTITUTION NO_ENGINE_SUBSTITUTION MySQL MySQL> > set set global global sql_mode sql_mode= ='ANSI' 'ANSI'; ; MySQL MySQL> > select select @ @@sql_mode @sql_mode\G \G * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * 1. 1. row row * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * @ @@sql_mode @sql_mode: REAL_AS_FLOAT : REAL_AS_FLOAT, ,PIPES_AS_CONCAT PIPES_AS_CONCAT, ,ANSI_QUOTES ANSI_QUOTES, ,IGNORE_SPACE IGNORE_SPACE, ,ONLY_FULL_GROUP_BY ONLY_FULL_GROUP_BY, , ANSI ANSI Copyright @ 2024 Oracle and/or its affiliates. 17
23. MySQL & ANSI Mode SQL_MODE='ANSI' use standard (ANSI) SQL syntax instead of MySQL syntax. More information: h ps://dev.mysql.com/doc/refman/8.3/en/compatibility.html Check that your application is not using speci c statements that are not supported by MySQL or not compatible with the standards. Example: select select top top 10 10 . .. .. . vs vs select select . .. .. . limit limit 10 10 Copyright @ 2024 Oracle and/or its affiliates. 18
24. Functions Some RDBMS also use functions that are not existing in MySQL. Those functions, may have an equivalent alternative or not. Missing functions are not a blocking element unless they are used as default column value. If your application is using some of these functions, then it might be necessary to modify it to use the appropriate one in MySQL. Copyright @ 2024 Oracle and/or its affiliates. 19
25. Functions - example In MariaDB and ORACLE, there is a function called ADD_MONTHS(). This function doesn't exist in MySQL and PostgreSQL. This is how in MariaDB, you can check if a table is using 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 @ 2024 Oracle and/or its affiliates. 20
26. Functions - example 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 @ 2024 Oracle and/or its affiliates. 21
27. Functions - example 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 @ 2024 Oracle and/or its affiliates. 22
28. Extensions Some RBMS, like PostgreSQL can be easily extended with third party extensions. This is a very common practice with PostgreSQL, which is less frequent in MySQL production servers. It's important to know which extensions are installed and used by the application as as it could require some changes. postgres postgres= =# SELECT * FROM pg_extension; # SELECT * FROM pg_extension; oid oid | | extname extname | | extowner extowner | | extnamespace extnamespace | | extrelocatable extrelocatable | | extversion extversion | | extconfig extconfig | | extcondition extcondition -------+-----------+----------+--------------+----------------+------------+-----------+------------- -------+-----------+----------+--------------+----------------+------------+-----------+------------- 13532 13532 | | plpgsql plpgsql | | 10 10 | | 11 11 | | f f | | 1.0 1.0 | | | | 16388 16388 | | pg_uuidv7 pg_uuidv7 | | 10 10 | | 2200 2200 | | t t | | 1.4 1.4 | | | | h ps://github.com/fboulnois/pg_uuidv7 Copyright @ 2024 Oracle and/or its affiliates. 23
29. Extensions - you can also extend MySQL On the previous slide you could see the extension pg_uuidv7 to bring uuid v7 for PostgreSQL. It's also possible to extend MySQL with your own extensions (plugins, components): MySQL MySQL > > install component install component "file://component_uuid_v7" "file://component_uuid_v7"; ; Query OK Query OK, , 0 0 rows rows affected affected ( (0.0005 0.0005 sec sec) ) MySQL MySQL > > select select uuid_v7 uuid_v7( () ); ; + +--------------------------------------+ --------------------------------------+ | | uuid_v7 uuid_v7( () ) | | + +--------------------------------------+ --------------------------------------+ | | 0186 0186c285 c285- -2890 2890- -79 79d1 d1- -a8f0 a8f0- -6229 6229ba440ade ba440ade | | + +--------------------------------------+ --------------------------------------+ 1 1 row row in in set set ( (0.0008 0.0008 sec sec) ) Copyright @ 2024 Oracle and/or its affiliates. 24
30. Stored Procedures Since recently, MySQL has never been popular for Stored Procedures. Many other RDBMS support di erent Stored Procedures languages like PL/SQL, Java, Javascript (Oracle) Transact-SQL (Microsoft SQL Server) PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python (PostgreSQL) Copyright @ 2024 Oracle and/or its affiliates. 25
31. Stored Procedures Since recently, MySQL has never been popular for Stored Procedures. Many other RDBMS support di erent Stored Procedures languages like PL/SQL, Java, Javascript (Oracle) Transact-SQL (Microsoft SQL Server) PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python (PostgreSQL) If your are using Store Procedures in your source database, you will have to rewrite them or migrate them to the application. Copyright @ 2024 Oracle and/or its affiliates. 25
32. Stored Procedures in MySQL Enterprise Edition As preview, MySQL Enterprise Edition includes GraalVM allowing to store JavaScript programs in the database. Get the preview h ps://www.oracle.com/mysql/technologies/mysql-enterprise-edition-downloads.html Copyright @ 2024 Oracle and/or its affiliates. 26
33. PostgreSQL: first_name first_name character character varying varying( (45 45) ) NOT NOT NULL NULL, , MySQL: first_name first_name varchar varchar( (45 45) ) NOT NOT NULL NULL, , Datatypes We're not all the same when it comes to data types... and the same goes for our RDBMS ! Example: The list of type mapping can be found in the MySQL Manual. h ps://dev.mysql.com/doc/workbench/en/wb-migration-database-postgresql-typemapping.html h ps://dev.mysql.com/doc/workbench/en/wb-migration-database-mssql-typemapping.html Copyright @ 2024 Oracle and/or its affiliates. 27
34. For example, MariaDB supports INET6 as a data type and in MySQL 8, IPv6 values are stored into VARBINARY(16). 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) ) Datatypes In general, MySQL provides enough data types to store all kind of data. Copyright @ 2024 Oracle and/or its affiliates. 28
35. Datatypes In some RDBMS, like PostgreSQL, there is the ability to create user-de ned data types. These data types must be converted. postgres postgres= =# \dT # \dT List List of of data data types types Schema Schema | | Name Name | | Description Description --------+------------+------------- --------+------------+------------- public public | | bug_status bug_status | | public public | | compfoo compfoo | | ( (2 2 rows rows) ) Copyright @ 2024 Oracle and/or its affiliates. 29
36. Migration moving the data Copyright @ 2024 Oracle and/or its affiliates. 30
37. Data Migration With MariaDB, you can use MySQL Shell Copy or Dump & Load utility to copy your data to MySQL. See: h ps://speakerdeck.com/lefred/migrating-from-mariadb-to-mysql h ps://lefred.be/content/migrate-from-mariadb-to-mysql-heatwave-easier-with-mysql-shell-8-2-1/ With other RDBMS, it's recommended to proceed in three steps: . dump the schemas de nition . x the schemas de nition and load them to MySQL . dump and load the data using csv format Copyright @ 2024 Oracle and/or its affiliates. 31
38. Dumping Schemas De nition The next step is to dump all the schemas de nition into a le to be loaded to MySQL: PostgreSQL: $ pg_dump $ pg_dump -st -st actor dvdrental actor dvdrental SQL Server: mssql-scripter mssql-scripter -S -S localhost localhost -d -d dvdrentals dvdrentals -U -U sa --include-objects dvd.actor sa --include-objects dvd.actor -f -f ./actor.sql ./actor.sql Copyright @ 2024 Oracle and/or its affiliates. 32
39. Fixing Schemas De nition We need to parse the de nition and nd the eventual problems. Most of them will be related to the data types and we should check the data type mapping. We also have another option, we can use a script that check the data dumped to a csv le and let it generate a table de nition that should be compatible with the data. Copyright @ 2024 Oracle and/or its affiliates. 33
40. Data Dump and Load Dumping the data to cvs les is an easy operation. Of course it depends of the RDBMS used. For example in PostgreSQL we use the copy command: dvdrental dvdrental= =# \copy actor to '/output/actor.csv' delimiter ',' CSV HEADER; # \copy actor to '/output/actor.csv' delimiter ',' CSV HEADER; COPY COPY 200 200 Copyright @ 2024 Oracle and/or its affiliates. 34
41. Data Dump and Load This is how we can proceed with Microsoft SQL Server: sqlcmd sqlcmd -S -S localhost localhost -U -U SA SA -P -P 'password' 'password' -d -d dvdrentals dvdrentals \ \ > > -Q -Q "set nocount on; select * from dvd.actor" "set nocount on; select * from dvd.actor" \ \ > > -o -o /mnt/actor.csv -h-1 -s /mnt/actor.csv -h-1 -s"," "," -w -w 700 700 -W -W Copyright @ 2024 Oracle and/or its affiliates. 35
42. Data Dump and Load - automatic table de nition In case you don't really know how to x the datatype or if you want to accelerate the process if you have many tables, you can use a plugin I wrote for MySQL Shell that generates the table de nition from the dumped data into a csv le: Copyright @ 2024 Oracle and/or its affiliates. 36
43. Data Dump and Load - automatic table de nition Copyright @ 2024 Oracle and/or its affiliates. 37
44. Data Load To load the data from the csv les, we use MySQL Shell importTable utility: JS JS> > util util. .importTable importTable( ('actor.csv' 'actor.csv', , { {schema schema: : 'dvdrentals' 'dvdrentals', , table table: : 'actor' 'actor' dialect dialect: : 'csv-unix' 'csv-unix', , skipRows skipRows: : 1 1, , showProgress showProgress: : true true} }) ) Copyright @ 2024 Oracle and/or its affiliates. 38
45. Data Load To load the data from the csv les, we use MySQL Shell importTable utility: JS JS> > util util. .importTable importTable( ('actor.csv' 'actor.csv', , { {schema schema: : 'dvdrentals' 'dvdrentals', , table table: : 'actor' 'actor' dialect dialect: : 'csv-unix' 'csv-unix', , skipRows skipRows: : 1 1, , showProgress showProgress: : true true} }) ) Copyright @ 2024 Oracle and/or its affiliates. 38
46. Live Migration replication Copyright @ 2024 Oracle and/or its affiliates. 39
47. Live Migration using Replication If we plan to migrate from MariaDB to MySQL, it's possible to use Asynchronous Replication to reduce the downtime related to the dump and load of the data. Of course there are some limitations. Copyright @ 2024 Oracle and/or its affiliates. 40
48. Live Migration using Replication If we plan to migrate from MariaDB to MySQL, it's possible to use Asynchronous Replication to reduce the downtime related to the dump and load of the data. Of course there are some limitations. For other products, Oracle Golden Gate can be used to manage replication between heterogenous systems. Copyright @ 2024 Oracle and/or its affiliates. 40
49. Live Migration using Replication There are also some other Open Source Projects like SymmetricDS that can be used for such replication from di erent Data Source to MySQL. Copyright @ 2024 Oracle and/or its affiliates. 41
50. Migration to MySQL summary Copyright @ 2024 Oracle and/or its affiliates. 42
51. . Verify the service (not only the application) requirements . Create a test environment . Migrate the data model . Migrate the data (or a sample) . Fix what is handled di erently . Update the application . Test: functional and performances make the adjustments . Document the full process Migration to MySQL - Summary Copyright @ 2024 Oracle and/or its affiliates. 43
52. . Verify the service (not only the application) requirements . Create a test environment . Migrate the data model . Migrate the data (or a sample) . Fix what is handled di erently . Update the application . Test: functional and performances make the adjustments . Document the full process Migration: . Stop the Sytem (eventually) . Backup . Migrate all the data . Setup replication (eventually) . Final Test . Switch to new environment Migration to MySQL - Summary Copyright @ 2024 Oracle and/or its affiliates. 43
53. Don't forget Every setup is di erent. Copyright @ 2024 Oracle and/or its affiliates. 44
54. Don't forget Every setup is di erent. There are as many ways to migrate to MySQL as there are environments to migrate from. Copyright @ 2024 Oracle and/or its affiliates. 44
55. Don't forget Every setup is di erent. There are as many ways to migrate to MySQL as there are environments to migrate from. ... and it's true that this is not always easy... Copyright @ 2024 Oracle and/or its affiliates. 44
56. But we are here to help you and make this process an easy and successful migration ! Copyright @ 2024 Oracle and/or its affiliates. 45
57. Share your ❤ to MySQL #mysql #MySQLCommunity Join our slack channel! bit.ly/mysql-slack Copyright @ 2024 Oracle and/or its affiliates. 46
58. Questions ? Copyright @ 2024 Oracle and/or its affiliates. 47