Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Migrating from MariaDB to MySQL

lefred
July 04, 2023

Migrating from MariaDB to MySQL

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

lefred

July 04, 2023
Tweet

More Decks by lefred

Other Decks in Technology

Transcript

  1. Frédéric Descamps
    Community Manager
    Oracle MySQL
    Migrating from MariaDB
    to MySQL
    How to migrate to the Original !

    View full-size slide

  2. Frédéric Descamps
    Community Manager
    Oracle MySQL
    Migrating from MariaDB
    to MySQL HeatWave
    How to migrate to the Original... in the Cloud !

    View full-size slide

  3. Who am I ?
    about.me/lefred
    Copyright @ 2023 Oracle and/or its affiliates.
    3

    View full-size slide

  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

    View full-size slide

  5. Agenda
    Copyright @ 2023 Oracle and/or its affiliates.
    5

    View full-size slide

  6. Agenda
    Copyright @ 2023 Oracle and/or its affiliates.
    6

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  10. Why Migrate from MariaDB to MySQL ?
    it's time !
    Copyright @ 2023 Oracle and/or its affiliates.
    7

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  13. Why Migrate from MariaDB to MySQL ?
    Copyright @ 2023 Oracle and/or its affiliates.
    10

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  22. Source: h ps:// nance.yahoo.com/quote/MRDB/
    Copyright @ 2023 Oracle and/or its affiliates.
    18

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  27. Search for Incompatibilities
    not a drop-in replacement anymore
    Copyright @ 2023 Oracle and/or its affiliates.
    23

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  32. Copyright @ 2023 Oracle and/or its affiliates.
    28

    View full-size slide

  33. Copyright @ 2023 Oracle and/or its affiliates.
    29

    View full-size slide

  34. Copyright @ 2023 Oracle and/or its affiliates.
    30

    View full-size slide

  35. Copyright @ 2023 Oracle and/or its affiliates.
    31

    View full-size slide

  36. All components of these High Availability and
    Disaster Recovery Solutions are GPL !
    No propietary BSL
    Copyright @ 2023 Oracle and/or its affiliates.
    32

    View full-size slide

  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

    View full-size slide

  38. Additionally, MySQL HeatWave provides
    point-in-time recovery and Read Replicas
    Copyright @ 2023 Oracle and/or its affiliates.
    34

    View full-size slide

  39. MySQL HeatWave High Availability
    When creating a DB System, you can choose to enable HA:
    Copyright @ 2023 Oracle and/or its affiliates.
    35

    View full-size slide

  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

    View full-size slide

  41. MySQL HeatWave High Availability (3)
    And even a fault domain:
    Copyright @ 2023 Oracle and/or its affiliates.
    37

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  45. MySQL HeatWave Read Replicas (2)
    Architecture Example:
    Copyright @ 2023 Oracle and/or its affiliates.
    41

    View full-size slide

  46. Search for Incompatibilities
    Storage Engines, Functions, Data Types
    Copyright @ 2023 Oracle and/or its affiliates.
    42

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  67. Exporting the Data
    Logical Dump
    Copyright @ 2023 Oracle and/or its affiliates.
    59

    View full-size slide

  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

    View full-size slide

  69. $ mysqlsh [email protected]:10612 -- util dumpInstance
    $ mysqlsh [email protected]: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

    View full-size slide

  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

    View full-size slide

  71. Logical Data Dump (2)
    Copyright @ 2023 Oracle and/or its affiliates.
    63

    View full-size slide

  72. Logical Data Dump to OCI
    If you migrate to MySQL HeatWave on OCI, you can dump the data directly to Object
    Storage:
    $ mysqlsh [email protected]:10612 -- util dumpInstance
    $ mysqlsh [email protected]: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

    View full-size slide

  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

    View full-size slide

  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 [email protected]:10612 -- util dumpInstance
    $ mysqlsh [email protected]: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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  82. Importing the Data
    Fast Load
    Copyright @ 2023 Oracle and/or its affiliates.
    74

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  86. Users and Authentication
    dump & load users and grants
    Copyright @ 2023 Oracle and/or its affiliates.
    78

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  89. Users and Authentication (3)
    Or with the MySQL HeatWave instance:
    Copyright @ 2023 Oracle and/or its affiliates.
    81

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  92. Users and Authentication (5)
    Let's compare both users and grants:
    MariaDB:
    MySQL HeatWave:
    Copyright @ 2023 Oracle and/or its affiliates.
    84

    View full-size slide

  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

    View full-size slide

  94. Migration with Minimal Downtime
    Replication
    Copyright @ 2023 Oracle and/or its affiliates.
    86

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  98. We have now remove the old server and
    enjoy MySQL 8.0
    Live Migration - Replication
    Copyright @ 2023 Oracle and/or its affiliates.
    90

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  101. Live Migration - Replication on-prem
    We use that information to stetup replication and start it:
    Copyright @ 2023 Oracle and/or its affiliates.
    93

    View full-size slide

  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

    View full-size slide

  103. Live Migration - Replication on OCI
    We use that information to create an Inbound Replication Channel:
    Copyright @ 2023 Oracle and/or its affiliates.
    95

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  106. Live Migration - Replication on OCI (4)
    We use the previous saved binary log position information:
    Copyright @ 2023 Oracle and/or its affiliates.
    98

    View full-size slide

  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

    View full-size slide

  108. Extra
    More Incompatibilities
    Copyright @ 2023 Oracle and/or its affiliates.
    100

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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 [email protected], 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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  117. SQL modes
    Copyright @ 2023 Oracle and/or its affiliates.
    109

    View full-size slide

  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

    View full-size slide

  119. Share your
    ❤ to MySQL
    #mysql
    Join our slack channel!
    bit.ly/mysql-slack
    Copyright @ 2023 Oracle and/or its affiliates.
    111

    View full-size slide

  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

    View full-size slide

  121. Questions ?
    Copyright @ 2023 Oracle and/or its affiliates.
    113

    View full-size slide