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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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