Context<\/h2>\n\n\n\n- Source database
- MariaDB 10.3<\/li>
- Around 8GB of data<\/li>
- Only InnoDB tables <\/li>
- Many user accounts (important information)<\/li><\/ul><\/li>
- Target databases
- MySQL Database Service 8.0.28<\/li>
- MySQL 8.0.28<\/li><\/ul><\/li><\/ul>\n\n\n\n
In fact, the (real) target was MySQL Database Service<\/strong><\/a> the MySQL PaaS on Oracle Cloud Infrastructure<\/strong><\/a>, but I will also show the workflow for on-premise MySQL. <\/p>\n\n\n\nPlease find below a reenactment of the events…<\/p>\n\n\n\n
<\/div>\n\n\n\nMySQL Shell<\/h2>\n\n\n\n
The first step is to install the MySQL DBA BFF<\/a>: MySQL Shell<\/strong><\/a>.
MySQL Shell is an advanced client and code editor for MySQL. You can manage your data using SQL<\/strong>, JavaScript<\/strong> and Python<\/strong>.<\/p>\n\n\n\nIn addition to data manipulation (btw MySQL 8.0 also provides a NoSQL CRUD API to handle JSON documents), there are also a lot of things you can do with MySQL Shell like create a MySQL cluster (InnoDB Cluster<\/a>, InnoDB ReplicaSet<\/a>, InnoDB ClusterSet<\/a>), verify whether your MySQL server instances is ready for upgrade, dump and restore, etc… :<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/03\/MySQL_Shell_Overview-800x515.png?resize=600%2C386\")
<\/figure>\n\n\n\nBelieve me if you try it, you will love it \ud83d\ude42 <\/p>\n\n\n\n
It is way better than the old mysql text client and some of these features work with MariaDB (at least the ones needed for the migration).<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/03\/mysql_shell_prompt_theme_normal-800x612.png?resize=600%2C459\")
<\/figure>\n\n\n\nEasy to install.
e.g.<\/p>\n\n\n\n
[opc@mariadb10-3 ~]$ \nsudo yum install -y mysql-shell<\/code><\/pre>\n\n\n\n<\/div>\n\n\n\nThe source dataset<\/h2>\n\n\n\n
Let’s have a look on the dataset.<\/p>\n\n\n\n
Unfortunately MariaDB does not have the sys schema<\/a>\u2026
We cannot use this very elegant query:<\/p>\n\n\n\n-- MySQL 5.7 \/ 8.0+\n\nSELECT sys.format_bytes(sum(data_length)) DATA,\n sys.format_bytes(sum(index_length)) INDEXES,\n sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'\nFROM information_schema.TABLES ORDER BY data_length + index_length\n;<\/code><\/pre>\n\n\n\nSo back in the time and let’s use the information_schema instead and the following ugly query:<\/p>\n\n\n\n
-- MySQL (old version) \/ MariaDB --\n\nSQL>\nSELECT \n CONCAT( SUM( ROUND( DATA_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Data,\n CONCAT( SUM( ROUND( INDEX_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Indexes,\n CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) \/ (1024 * 1024) ,2)), 'MB' ) AS 'Total Size' \nFROM information_schema.TABLES ORDER BY DATA_LENGTH + INDEX_LENGTH\n;\n\n+-----------------+-----------------+------------+\n| Data | Indexes | Total Size |\n+-----------------+-----------------+------------+\n| 3044154215.00MB | 5688978432.00MB | 8328.61MB |\n+-----------------+-----------------+------------+<\/code><\/pre>\n\n\n\nApproximately 8GB of data.<\/p>\n\n\n\n
What about the storage engines?<\/p>\n\n\n\n
Again because of lack of sys schema we cannot use this pretty query:<\/p>\n\n\n\n
-- MySQL 5.7 \/ 8.0+\n\nSELECT count(*) as '# TABLES', sys.format_bytes(sum(data_length)) DATA, \n sys.format_bytes(sum(index_length)) INDEXES, \n sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE', \n engine `ENGINE` FROM information_schema.TABLES \nWHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')\nGROUP BY engine\nORDER BY engine;<\/code><\/pre>\n\n\n\nSo let’s back in the time and use instead:<\/p>\n\n\n\n
-- MySQL (old version) \/ MariaDB --\n\nSQL>\nSELECT \n count(*) as '# TABLES',\n CONCAT( SUM( ROUND( DATA_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Data,\n CONCAT( SUM( ROUND( INDEX_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Indexes,\n CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) \/ (1024 * 1024) ,2)), 'MB' ) AS 'Total Size',\n engine `ENGINE` FROM information_schema.TABLES \nWHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')\nGROUP BY engine\nORDER BY engine\n;\n\n+----------+-----------------+-----------------+------------+--------+\n| # TABLES | Data | Indexes | Total Size | ENGINE |\n+----------+-----------------+-----------------+------------+--------+\n| 2 | NULL | NULL | NULL | NULL |\n| 26 | 3043442688.00MB | 5688705024.00MB | 8327.66MB | InnoDB |\n+----------+-----------------+-----------------+------------+--------+<\/code><\/pre>\n\n\n\nAll tables are in InnoDB \\o\/
That’s how DBA’s life should be \ud83d\ude42<\/p>\n\n\n\n
<\/div>\n\n\n\nMigrate the data<\/h2>\n\n\n\n
The source is MariaDB 10.3 and the target is MySQL 8.0, on-premise<\/strong> or using MySQL Database Service<\/strong>.<\/p>\n\n\n\nWith such amount of data ~10GB a dump is not an issue especially if you use MySQL Shell utilities<\/a> (please forget about the aging mysqldump – it deserves a nice retirement).<\/p>\n\n\n\nMigrate to MySQL on-premise<\/h3>\n\n\n\n
Because I need the whole instance, I’m going to use MySQL Shell dumpInstance<\/em><\/a><\/strong> utility.<\/p>\n\n\n\nAn important option of dumpInstance<\/em> is compatibility<\/strong><\/em>. It can <\/p>\n\n\n\n- enforce the InnoDB storage engine (force_innodb<\/em>) ie change
CREATE TABLE<\/code><\/a> statements to use the InnoDB<\/code><\/a> storage engine for any tables that do not already use it <\/li>- ignore any missing primary keys (ignore_missing_pks<\/em>),<\/li>
- automatically add primary keys in invisible columns (create_invisible_pks<\/em>)<\/li>
- etc…<\/li><\/ul>\n\n\n\n
Here, I will only use force_innodb<\/em>. (if your memory is better than mine you remember that all the tables are already in InnoDB, so not really needed in this case, but I must admit that I’m paranoid :-O).
Joke aside, it is in my opinion a good habit to use this option. <\/p>\n\n\n\nIf you are worried about primary keys below a query that would help you to check your schema:<\/p>\n\n\n\n
-- Find tables without PK\n\nSELECT tables.table_schema , tables.table_name , tables.engine \nFROM information_schema.tables LEFT JOIN ( \n SELECT table_schema , table_name\n FROM information_schema.statistics\n GROUP BY table_schema, table_name, index_name \n HAVING SUM( \n case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks \n ON tables.table_schema = puks.table_schema \n AND tables.table_name = puks.table_name \n WHERE puks.table_name IS null \n AND tables.table_type = 'BASE TABLE' \n AND tables.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');<\/code><\/pre>\n\n\n\n<\/div>\n\n\n\nThe MySQL Shell dump instance command for dumping data from MariaDB to MySQL 8.0 on-premise looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\/bkp\/dumpMariadb1\", {threads: 8, compatibility: [\"force_innodb\"]})<\/code><\/pre>\n\n\n\n- The dump will be stored in the \/bkp\/dumpMariadb1<\/strong><\/em> directory<\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to dump chunks of data from the MariaDB instance<\/li>
- compatibility: force_innodb<\/em><\/strong> -> update to InnoDB for any tables that do not already use it<\/li><\/ul>\n\n\n\n
Depending on your context you may have to tune this command.<\/p>\n\n\n\n
<\/div>\n\n\n\nMove the dump on the MySQL 8.0 host and then restore the dump. <\/p>\n\n\n\n
In MySQL 8.0 there is a special ninja technique \ud83d\ude42 to load a dump at the speed of light… All right, I may be exaggerating a bit, but at least at the speed of the sound by disabling crash recovery aka disabling redo logging<\/a>.<\/p>\n\n\n\nDISCLAIMER<\/span>: This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system.<\/em> It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption<\/span>.<\/strong><\/p>\n\n\n\nThe commands to restore the dump on MySQL 8.0 on-premise look like:<\/p>\n\n\n\n
JS>\n\\sql SET GLOBAL local_infile=1;\n\\sql ALTER INSTANCE DISABLE INNODB REDO_LOG;\n\nutil.loadDump(\"\/restore\/dumpMariadb1\", {threads: 8, ignoreVersion: true , skipBinlog: true, loadUsers: false})\n\n\\sql ALTER INSTANCE ENABLE INNODB REDO_LOG;\n\\sql SET GLOBAL local_infile=0;<\/code><\/pre>\n\n\n\n- local_infile=1<\/em><\/strong> -> allows local data<\/a> loading<\/li>
- ALTER INSTANCE DISABLE INNODB REDO_LOG<\/em><\/strong> -> disable redo logging to speed up data loading<\/li>
- The dump has been moved to the MySQL instance at \/restore\/dumpMariadb1<\/em><\/strong> <\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to upload chunks of data to the MySQL instance<\/li>
- ignoreVersion: true<\/em><\/strong> -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version) <\/li>
- skipBinlog: true<\/em><\/strong> -> skips binary logging for the sessions used during the restore <\/li>
- loadUsers: false<\/em><\/strong> -> do not import users and their roles and grants. This one is very important because MariaDB users are not compatibles<\/span><\/strong>. Please note that the default is false (paranoid mode enable) <\/li><\/ul>\n\n\n\n<\/div>\n\n\n\n
Migrate to MySQL Database Service<\/h3>\n\n\n\n
As a prerequisites I’m assuming that you have the basic Oracle Cloud Infrastructure and MySQL Database Service knowledge.
If it is not yet the case, please read my Discovering MySQL Database Service<\/strong><\/a> series.<\/p>\n\n\n\n<\/div>\n\n\n\nTo migrate your data into MySQL Database Service (MDS), MySQL Shell is again your best friend to export the data.
You can either store the data locally or very easily in an OCI object storage bucket<\/a>.<\/p>\n\n\n\nIf the data are located in a bucket you can then use the MDS data Import option to automatically create a new MDS instance populated with your data.
If you choose the \u00ab\u00a0local\u00a0\u00bb storage, the import will be manual only.<\/p>\n\n\n\n
The main thing to keep in mind during the import process is that you will most likely have a user account issue (MariaDB users are not compatibles<\/span><\/strong>), so my recommendation is to take a MySQL Shell dumps with all the business data (i.e. mysql, information_schema, performance_schema, sys are not include) without the user accounts<\/strong><\/span>.<\/p>\n\n\n\nBecause I need the whole instance, in both case I’m using MySQL Shell dumpInstance<\/em><\/a><\/strong> utility.<\/p>\n\n\n\n<\/div>\n\n\n\nBackup the database into a bucket<\/h4>\n\n\n\n
I’m going to store my dump inside an Object Storage bucket so I must create the bucket before the dump. This is very easy by using the OCI interface.<\/p>\n\n\n\n
If you are not familiar with OCI Object Storage you can follow this tutorial: <\/a>Discovering MySQL Database Service \u2013 Episode 4 \u2013 Dump your MySQL data into an Object Storage bucket<\/a>.<\/p>\n\n\n\nIn order to put these data into the OCI bucket, we must make some configuration for allowing MySQL Shell to easily connect to the OCI object storage service.<\/p>\n\n\n\n
To this end, we will create an OCI CLI<\/a><\/strong> configuration file.<\/p>\n\n\n\nThe CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks. The CLI provides the same core functionality as the Console, plus additional commands. Some of these, such as the ability to run scripts, extend Console functionality.<\/p>\n\n\n\n
To be clear, we will not use the CLI but its configuration file. The configuration file name and default location (on Linux) is \/home\/opc\/.oci\/config.<\/p>\n\n\n\n
It should have the following information:<\/p>\n\n\n\n
- user<\/strong>: OCID of the user calling the API.<\/li>
- fingerprint<\/strong>: Fingerprint for the public key that was added to this user.<\/li>
- key_file<\/strong>: Full path and filename of the private key.<\/li>
- tenancy<\/strong>: OCID of your tenancy.<\/li>
- region<\/strong>: An Oracle Cloud Infrastructure region.<\/li><\/ul>\n\n\n\n
For more details please click here<\/a>.<\/p>\n\n\n\nAs an example, mine looks like:<\/p>\n\n\n\n
[DEFAULT]\nuser=ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\nfingerprint=xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx\nkey_file=\/home\/opc\/.oci\/oci_api_key.pem\ntenancy=ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\ncompartment=ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\nregion=eu-frankfurt-1<\/pre>\n\n\n\n<\/div>\n\n\n\nYou may be wondering how to get this information? Fair question!
Don’t panic I already explained everything in this tutorial: Discovering MySQL Database Service \u2013 Episode 4 \u2013 Dump your MySQL data into an Object Storage bucket<\/a><\/p>\n\n\n\n<\/div>\n\n\n\nMy Bucket is named: migrationMariadbNoUser<\/em><\/p>\n\n\n\nThe MySQL Shell dump instance command for migrating data from MariaDB to MDS and stored data into a bucket looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\", {users: false, osBucketName: \"migrationMariadbNoUser\", osNamespace: \"abcdefghijkl\", ocimds: true, threads: 8, compatibility: [\"force_innodb\", \"strip_restricted_grants\", \"strip_definers\", \"strip_tablespaces\"]})<\/code><\/pre>\n\n\n\n- users: false<\/em><\/strong> -> exclude users and their roles and grants in the dump<\/li>
- osBucketName: \u00ab\u00a0migrationMariadbNoUser\u00a0\u00bb<\/em><\/strong> -> name of the Object Storage bucket to which the dump is to be written<\/li>
- osNamespace: \u00ab\u00a0abcdefghijkl\u00a0\u00bb<\/em><\/strong> -> namespace where the Object Storage bucket named by
osBucketName<\/code> is located<\/li>- ocimds: true<\/em><\/strong> -> enables checks and modifications for compatibility with MDS<\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to dump chunks of data from the MariaDB instance<\/li>
- compatibility<\/em><\/strong>: ->
- force_innodb<\/em><\/strong> -> change
CREATE TABLE<\/code><\/a> statements to use the InnoDB<\/code><\/a> <\/li>- strip_restricted_grants<\/em><\/strong> -> remove specific privileges that are restricted by MDS<\/li>
- strip_definers<\/em><\/strong> -> remove the
DEFINER<\/code> clause from views, routines, events, and triggers<\/li>- strip_tablespaces<\/em><\/strong> -> all tables are created in their default tablespace<\/li><\/ul><\/li><\/ul>\n\n\n\n
Complete documentation is available here<\/a>.<\/p>\n\n\n\nThe command above is storing the dump, compatible with MDS, into the bucket migrationMariadbNoUser<\/em>.<\/p>\n\n\n\n<\/div>\n\n\n\nBackup the database locally<\/h3>\n\n\n\n
If your dataset is not big and\/or you are too lazy to spend a little time in configuration, the alternative is to dump your data locally.
The main drawback is that you will have to import the data into MDS manually – but this should not be a big deal if your dataset is not huge.<\/p>\n\n\n\n
If you want to store the dump locally the command looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\/bkp\/dumpMariadbNoUser\", {users: false, threads: 8, compatibility: [\"force_innodb\", \"strip_restricted_grants\", \"strip_definers\", \"strip_tablespaces\", \"create_invisible_pks\"]})<\/code><\/pre>\n\n\n\nThe command is close to the one for the on-prem.
Please note – users: false<\/strong><\/em> – option. You can also dump the user and then run the import without loading the users (default behavior).<\/p>\n\n\n\nIn fact, the most import difference compare to an on-prem data migration are the compatibility options. Because MDS is a PaaS you have less flexibility than the on-prem MySQL so more compatibility needs.<\/p>\n\n\n\n
<\/div>\n\n\n\nRestore into MySQL Database Service from a bucket<\/h3>\n\n\n\n
You have 2 solutions, the automated one, easiest but less flexible or the manual.<\/p>\n\n\n\n
The first one is the MDS Data Import<\/strong> feature using the OCI interface.<\/p>\n\n\n\n![\"MySQL](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2021\/07\/OCI_create_MDS_form7_Data_Import.png?resize=527%2C231\")
MySQL Data Service – Data Import<\/figcaption><\/figure>\n\n\n\nYou’ll find the \u00ab\u00a0How To\u00a0\u00bb in: Discovering MySQL Database Service \u2013 Episode 5 \u2013 Create a MySQL DB system from a MySQL Shell dump<\/a><\/p>\n\n\n\nMore details in the documentation<\/a>.<\/p>\n\n\n\nIf you want to speed up your import you can disable the Crash Recovery<\/strong> (during the import and enable it when it is finished). Some details here<\/a>.<\/p>\n\n\n\n![\"\"](\"https:\/\/i0.wp.com\/dasini.net\/blog\/wp-content\/uploads\/2022\/03\/MDS_Crash_Recovery_Disable-800x102.png?resize=600%2C77\")
<\/figure>\n\n\n\n<\/div>\n\n\n\nYou can also restore the dump manually, in a new fresh MDS instance.<\/p>\n\n\n\n
The command looks like:<\/p>\n\n\n\n
JS>\nutil.loadDump(\"\", {osBucketName: \"migrationMariadbNoUser\", osNamespace: \"abcdefghijkl\", threads: 8, ignoreVersion: true})<\/code><\/pre>\n\n\n\nPlease note that in this case, you won’t be able to speed up the restore by disabling the crash recovery system (Redo Logs).<\/p>\n\n\n\n
<\/div>\n\n\n\nRestore into MySQL Database Service from a local dump<\/h3>\n\n\n\n
In this case we cannot use the crash recovery trick nor the Data Import feature.
Anyway the MySQL Shell dumpInstance<\/em> function will still do the job.<\/p>\n\n\n\nThe command looks like:<\/p>\n\n\n\n
JS>\nutil.loadDump(\"\/restore\/dumpMariadbNoUser\", {threads: 8, ignoreVersion: true})<\/code><\/pre>\n\n\n\n- threads: 8<\/em><\/strong> -> 8 parallel threads to upload chunks of data to the MySQL instance<\/li>
- ignoreVersion: true<\/em><\/strong> -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version) <\/li><\/ul>\n\n\n\n
loadUsers: false<\/em> is not necessary here because the dump was made with users: false<\/em>. <\/p>\n\n\n\n<\/div>\n\n\n\nAnd don’t forget to re-create your users<\/strong><\/a> \ud83d\ude42
You have plenty of authentication plugins<\/strong><\/a> to create secure user accounts.
We also provide Multifactor Authentication<\/a><\/strong>. <\/p>\n\n\n\nBtw if security is one of your concern (and it should be) below some MySQL password management features:<\/p>\n\n\n\n
\nMySQL Security Serie – Password Management<\/span><\/strong><\/p>\n\n\n\n
- MariaDB 10.3<\/li>
- Around 8GB of data<\/li>
- Only InnoDB tables <\/li>
- Many user accounts (important information)<\/li><\/ul><\/li>
- Target databases
- MySQL Database Service 8.0.28<\/li>
- MySQL 8.0.28<\/li><\/ul><\/li><\/ul>\n\n\n\n
In fact, the (real) target was MySQL Database Service<\/strong><\/a> the MySQL PaaS on Oracle Cloud Infrastructure<\/strong><\/a>, but I will also show the workflow for on-premise MySQL. <\/p>\n\n\n\n
Please find below a reenactment of the events…<\/p>\n\n\n\n
<\/div>\n\n\n\nMySQL Shell<\/h2>\n\n\n\n
The first step is to install the MySQL DBA BFF<\/a>: MySQL Shell<\/strong><\/a>.
MySQL Shell is an advanced client and code editor for MySQL. You can manage your data using SQL<\/strong>, JavaScript<\/strong> and Python<\/strong>.<\/p>\n\n\n\nIn addition to data manipulation (btw MySQL 8.0 also provides a NoSQL CRUD API to handle JSON documents), there are also a lot of things you can do with MySQL Shell like create a MySQL cluster (InnoDB Cluster<\/a>, InnoDB ReplicaSet<\/a>, InnoDB ClusterSet<\/a>), verify whether your MySQL server instances is ready for upgrade, dump and restore, etc… :<\/p>\n\n\n\n
<\/figure>\n\n\n\nBelieve me if you try it, you will love it \ud83d\ude42 <\/p>\n\n\n\n
It is way better than the old mysql text client and some of these features work with MariaDB (at least the ones needed for the migration).<\/p>\n\n\n\n<\/figure>\n\n\n\nEasy to install.
e.g.<\/p>\n\n\n\n[opc@mariadb10-3 ~]$ \nsudo yum install -y mysql-shell<\/code><\/pre>\n\n\n\n<\/div>\n\n\n\nThe source dataset<\/h2>\n\n\n\n
Let’s have a look on the dataset.<\/p>\n\n\n\n
Unfortunately MariaDB does not have the sys schema<\/a>\u2026
We cannot use this very elegant query:<\/p>\n\n\n\n-- MySQL 5.7 \/ 8.0+\n\nSELECT sys.format_bytes(sum(data_length)) DATA,\n sys.format_bytes(sum(index_length)) INDEXES,\n sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE'\nFROM information_schema.TABLES ORDER BY data_length + index_length\n;<\/code><\/pre>\n\n\n\nSo back in the time and let’s use the information_schema instead and the following ugly query:<\/p>\n\n\n\n
-- MySQL (old version) \/ MariaDB --\n\nSQL>\nSELECT \n CONCAT( SUM( ROUND( DATA_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Data,\n CONCAT( SUM( ROUND( INDEX_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Indexes,\n CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) \/ (1024 * 1024) ,2)), 'MB' ) AS 'Total Size' \nFROM information_schema.TABLES ORDER BY DATA_LENGTH + INDEX_LENGTH\n;\n\n+-----------------+-----------------+------------+\n| Data | Indexes | Total Size |\n+-----------------+-----------------+------------+\n| 3044154215.00MB | 5688978432.00MB | 8328.61MB |\n+-----------------+-----------------+------------+<\/code><\/pre>\n\n\n\nApproximately 8GB of data.<\/p>\n\n\n\n
What about the storage engines?<\/p>\n\n\n\n
Again because of lack of sys schema we cannot use this pretty query:<\/p>\n\n\n\n
-- MySQL 5.7 \/ 8.0+\n\nSELECT count(*) as '# TABLES', sys.format_bytes(sum(data_length)) DATA, \n sys.format_bytes(sum(index_length)) INDEXES, \n sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE', \n engine `ENGINE` FROM information_schema.TABLES \nWHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')\nGROUP BY engine\nORDER BY engine;<\/code><\/pre>\n\n\n\nSo let’s back in the time and use instead:<\/p>\n\n\n\n
-- MySQL (old version) \/ MariaDB --\n\nSQL>\nSELECT \n count(*) as '# TABLES',\n CONCAT( SUM( ROUND( DATA_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Data,\n CONCAT( SUM( ROUND( INDEX_LENGTH \/ (1024 \/ 1024) ,2)), 'MB' ) AS Indexes,\n CONCAT( SUM( ROUND( (DATA_LENGTH + INDEX_LENGTH) \/ (1024 * 1024) ,2)), 'MB' ) AS 'Total Size',\n engine `ENGINE` FROM information_schema.TABLES \nWHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata')\nGROUP BY engine\nORDER BY engine\n;\n\n+----------+-----------------+-----------------+------------+--------+\n| # TABLES | Data | Indexes | Total Size | ENGINE |\n+----------+-----------------+-----------------+------------+--------+\n| 2 | NULL | NULL | NULL | NULL |\n| 26 | 3043442688.00MB | 5688705024.00MB | 8327.66MB | InnoDB |\n+----------+-----------------+-----------------+------------+--------+<\/code><\/pre>\n\n\n\nAll tables are in InnoDB \\o\/
That’s how DBA’s life should be \ud83d\ude42<\/p>\n\n\n\n<\/div>\n\n\n\nMigrate the data<\/h2>\n\n\n\n
The source is MariaDB 10.3 and the target is MySQL 8.0, on-premise<\/strong> or using MySQL Database Service<\/strong>.<\/p>\n\n\n\n
With such amount of data ~10GB a dump is not an issue especially if you use MySQL Shell utilities<\/a> (please forget about the aging mysqldump – it deserves a nice retirement).<\/p>\n\n\n\n
Migrate to MySQL on-premise<\/h3>\n\n\n\n
Because I need the whole instance, I’m going to use MySQL Shell dumpInstance<\/em><\/a><\/strong> utility.<\/p>\n\n\n\n
An important option of dumpInstance<\/em> is compatibility<\/strong><\/em>. It can <\/p>\n\n\n\n
- enforce the InnoDB storage engine (force_innodb<\/em>) ie change
CREATE TABLE<\/code><\/a> statements to use theInnoDB<\/code><\/a> storage engine for any tables that do not already use it <\/li>- ignore any missing primary keys (ignore_missing_pks<\/em>),<\/li>
- automatically add primary keys in invisible columns (create_invisible_pks<\/em>)<\/li>
- etc…<\/li><\/ul>\n\n\n\n
Here, I will only use force_innodb<\/em>. (if your memory is better than mine you remember that all the tables are already in InnoDB, so not really needed in this case, but I must admit that I’m paranoid :-O).
Joke aside, it is in my opinion a good habit to use this option. <\/p>\n\n\n\nIf you are worried about primary keys below a query that would help you to check your schema:<\/p>\n\n\n\n
-- Find tables without PK\n\nSELECT tables.table_schema , tables.table_name , tables.engine \nFROM information_schema.tables LEFT JOIN ( \n SELECT table_schema , table_name\n FROM information_schema.statistics\n GROUP BY table_schema, table_name, index_name \n HAVING SUM( \n case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks \n ON tables.table_schema = puks.table_schema \n AND tables.table_name = puks.table_name \n WHERE puks.table_name IS null \n AND tables.table_type = 'BASE TABLE' \n AND tables.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys');<\/code><\/pre>\n\n\n\n<\/div>\n\n\n\nThe MySQL Shell dump instance command for dumping data from MariaDB to MySQL 8.0 on-premise looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\/bkp\/dumpMariadb1\", {threads: 8, compatibility: [\"force_innodb\"]})<\/code><\/pre>\n\n\n\n- The dump will be stored in the \/bkp\/dumpMariadb1<\/strong><\/em> directory<\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to dump chunks of data from the MariaDB instance<\/li>
- compatibility: force_innodb<\/em><\/strong> -> update to InnoDB for any tables that do not already use it<\/li><\/ul>\n\n\n\n
Depending on your context you may have to tune this command.<\/p>\n\n\n\n
<\/div>\n\n\n\nMove the dump on the MySQL 8.0 host and then restore the dump. <\/p>\n\n\n\n
In MySQL 8.0 there is a special ninja technique \ud83d\ude42 to load a dump at the speed of light… All right, I may be exaggerating a bit, but at least at the speed of the sound by disabling crash recovery aka disabling redo logging<\/a>.<\/p>\n\n\n\n
DISCLAIMER<\/span>: This feature is intended only for loading data into a new MySQL instance. Do not disable redo logging on a production system.<\/em> It is permitted to shutdown and restart the server while redo logging is disabled, but an unexpected server stoppage while redo logging is disabled can cause data loss and instance corruption<\/span>.<\/strong><\/p>\n\n\n\n
The commands to restore the dump on MySQL 8.0 on-premise look like:<\/p>\n\n\n\n
JS>\n\\sql SET GLOBAL local_infile=1;\n\\sql ALTER INSTANCE DISABLE INNODB REDO_LOG;\n\nutil.loadDump(\"\/restore\/dumpMariadb1\", {threads: 8, ignoreVersion: true , skipBinlog: true, loadUsers: false})\n\n\\sql ALTER INSTANCE ENABLE INNODB REDO_LOG;\n\\sql SET GLOBAL local_infile=0;<\/code><\/pre>\n\n\n\n- local_infile=1<\/em><\/strong> -> allows local data<\/a> loading<\/li>
- ALTER INSTANCE DISABLE INNODB REDO_LOG<\/em><\/strong> -> disable redo logging to speed up data loading<\/li>
- The dump has been moved to the MySQL instance at \/restore\/dumpMariadb1<\/em><\/strong> <\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to upload chunks of data to the MySQL instance<\/li>
- ignoreVersion: true<\/em><\/strong> -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version) <\/li>
- skipBinlog: true<\/em><\/strong> -> skips binary logging for the sessions used during the restore <\/li>
- loadUsers: false<\/em><\/strong> -> do not import users and their roles and grants. This one is very important because MariaDB users are not compatibles<\/span><\/strong>. Please note that the default is false (paranoid mode enable) <\/li><\/ul>\n\n\n\n
<\/div>\n\n\n\nMigrate to MySQL Database Service<\/h3>\n\n\n\n
As a prerequisites I’m assuming that you have the basic Oracle Cloud Infrastructure and MySQL Database Service knowledge.
If it is not yet the case, please read my Discovering MySQL Database Service<\/strong><\/a> series.<\/p>\n\n\n\n<\/div>\n\n\n\nTo migrate your data into MySQL Database Service (MDS), MySQL Shell is again your best friend to export the data.
You can either store the data locally or very easily in an OCI object storage bucket<\/a>.<\/p>\n\n\n\nIf the data are located in a bucket you can then use the MDS data Import option to automatically create a new MDS instance populated with your data.
If you choose the \u00ab\u00a0local\u00a0\u00bb storage, the import will be manual only.<\/p>\n\n\n\nThe main thing to keep in mind during the import process is that you will most likely have a user account issue (MariaDB users are not compatibles<\/span><\/strong>), so my recommendation is to take a MySQL Shell dumps with all the business data (i.e. mysql, information_schema, performance_schema, sys are not include) without the user accounts<\/strong><\/span>.<\/p>\n\n\n\n
Because I need the whole instance, in both case I’m using MySQL Shell dumpInstance<\/em><\/a><\/strong> utility.<\/p>\n\n\n\n
<\/div>\n\n\n\nBackup the database into a bucket<\/h4>\n\n\n\n
I’m going to store my dump inside an Object Storage bucket so I must create the bucket before the dump. This is very easy by using the OCI interface.<\/p>\n\n\n\n
If you are not familiar with OCI Object Storage you can follow this tutorial: <\/a>Discovering MySQL Database Service \u2013 Episode 4 \u2013 Dump your MySQL data into an Object Storage bucket<\/a>.<\/p>\n\n\n\n
In order to put these data into the OCI bucket, we must make some configuration for allowing MySQL Shell to easily connect to the OCI object storage service.<\/p>\n\n\n\n
To this end, we will create an OCI CLI<\/a><\/strong> configuration file.<\/p>\n\n\n\n
The CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks. The CLI provides the same core functionality as the Console, plus additional commands. Some of these, such as the ability to run scripts, extend Console functionality.<\/p>\n\n\n\n
To be clear, we will not use the CLI but its configuration file. The configuration file name and default location (on Linux) is \/home\/opc\/.oci\/config.<\/p>\n\n\n\n
It should have the following information:<\/p>\n\n\n\n
- user<\/strong>: OCID of the user calling the API.<\/li>
- fingerprint<\/strong>: Fingerprint for the public key that was added to this user.<\/li>
- key_file<\/strong>: Full path and filename of the private key.<\/li>
- tenancy<\/strong>: OCID of your tenancy.<\/li>
- region<\/strong>: An Oracle Cloud Infrastructure region.<\/li><\/ul>\n\n\n\n
For more details please click here<\/a>.<\/p>\n\n\n\n
As an example, mine looks like:<\/p>\n\n\n\n
[DEFAULT]\nuser=ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\nfingerprint=xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx\nkey_file=\/home\/opc\/.oci\/oci_api_key.pem\ntenancy=ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\ncompartment=ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\nregion=eu-frankfurt-1<\/pre>\n\n\n\n
<\/div>\n\n\n\nYou may be wondering how to get this information? Fair question!
Don’t panic I already explained everything in this tutorial: Discovering MySQL Database Service \u2013 Episode 4 \u2013 Dump your MySQL data into an Object Storage bucket<\/a><\/p>\n\n\n\n<\/div>\n\n\n\nMy Bucket is named: migrationMariadbNoUser<\/em><\/p>\n\n\n\n
The MySQL Shell dump instance command for migrating data from MariaDB to MDS and stored data into a bucket looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\", {users: false, osBucketName: \"migrationMariadbNoUser\", osNamespace: \"abcdefghijkl\", ocimds: true, threads: 8, compatibility: [\"force_innodb\", \"strip_restricted_grants\", \"strip_definers\", \"strip_tablespaces\"]})<\/code><\/pre>\n\n\n\n- users: false<\/em><\/strong> -> exclude users and their roles and grants in the dump<\/li>
- osBucketName: \u00ab\u00a0migrationMariadbNoUser\u00a0\u00bb<\/em><\/strong> -> name of the Object Storage bucket to which the dump is to be written<\/li>
- osNamespace: \u00ab\u00a0abcdefghijkl\u00a0\u00bb<\/em><\/strong> -> namespace where the Object Storage bucket named by
osBucketName<\/code> is located<\/li>- ocimds: true<\/em><\/strong> -> enables checks and modifications for compatibility with MDS<\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to dump chunks of data from the MariaDB instance<\/li>
- compatibility<\/em><\/strong>: ->
- force_innodb<\/em><\/strong> -> change
CREATE TABLE<\/code><\/a> statements to use theInnoDB<\/code><\/a> <\/li>- strip_restricted_grants<\/em><\/strong> -> remove specific privileges that are restricted by MDS<\/li>
- strip_definers<\/em><\/strong> -> remove the
DEFINER<\/code> clause from views, routines, events, and triggers<\/li>- strip_tablespaces<\/em><\/strong> -> all tables are created in their default tablespace<\/li><\/ul><\/li><\/ul>\n\n\n\n
Complete documentation is available here<\/a>.<\/p>\n\n\n\n
The command above is storing the dump, compatible with MDS, into the bucket migrationMariadbNoUser<\/em>.<\/p>\n\n\n\n
<\/div>\n\n\n\nBackup the database locally<\/h3>\n\n\n\n
If your dataset is not big and\/or you are too lazy to spend a little time in configuration, the alternative is to dump your data locally.
The main drawback is that you will have to import the data into MDS manually – but this should not be a big deal if your dataset is not huge.<\/p>\n\n\n\nIf you want to store the dump locally the command looks like:<\/p>\n\n\n\n
JS>\nutil.dumpInstance(\"\/bkp\/dumpMariadbNoUser\", {users: false, threads: 8, compatibility: [\"force_innodb\", \"strip_restricted_grants\", \"strip_definers\", \"strip_tablespaces\", \"create_invisible_pks\"]})<\/code><\/pre>\n\n\n\nThe command is close to the one for the on-prem.
Please note – users: false<\/strong><\/em> – option. You can also dump the user and then run the import without loading the users (default behavior).<\/p>\n\n\n\nIn fact, the most import difference compare to an on-prem data migration are the compatibility options. Because MDS is a PaaS you have less flexibility than the on-prem MySQL so more compatibility needs.<\/p>\n\n\n\n
<\/div>\n\n\n\nRestore into MySQL Database Service from a bucket<\/h3>\n\n\n\n
You have 2 solutions, the automated one, easiest but less flexible or the manual.<\/p>\n\n\n\n
The first one is the MDS Data Import<\/strong> feature using the OCI interface.<\/p>\n\n\n\n
MySQL Data Service – Data Import<\/figcaption><\/figure>\n\n\n\n You’ll find the \u00ab\u00a0How To\u00a0\u00bb in: Discovering MySQL Database Service \u2013 Episode 5 \u2013 Create a MySQL DB system from a MySQL Shell dump<\/a><\/p>\n\n\n\n
More details in the documentation<\/a>.<\/p>\n\n\n\n
If you want to speed up your import you can disable the Crash Recovery<\/strong> (during the import and enable it when it is finished). Some details here<\/a>.<\/p>\n\n\n\n
<\/figure>\n\n\n\n<\/div>\n\n\n\nYou can also restore the dump manually, in a new fresh MDS instance.<\/p>\n\n\n\n
The command looks like:<\/p>\n\n\n\n
JS>\nutil.loadDump(\"\", {osBucketName: \"migrationMariadbNoUser\", osNamespace: \"abcdefghijkl\", threads: 8, ignoreVersion: true})<\/code><\/pre>\n\n\n\nPlease note that in this case, you won’t be able to speed up the restore by disabling the crash recovery system (Redo Logs).<\/p>\n\n\n\n
<\/div>\n\n\n\nRestore into MySQL Database Service from a local dump<\/h3>\n\n\n\n
In this case we cannot use the crash recovery trick nor the Data Import feature.
Anyway the MySQL Shell dumpInstance<\/em> function will still do the job.<\/p>\n\n\n\nThe command looks like:<\/p>\n\n\n\n
JS>\nutil.loadDump(\"\/restore\/dumpMariadbNoUser\", {threads: 8, ignoreVersion: true})<\/code><\/pre>\n\n\n\n- threads: 8<\/em><\/strong> -> 8 parallel threads to upload chunks of data to the MySQL instance<\/li>
- ignoreVersion: true<\/em><\/strong> -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version) <\/li><\/ul>\n\n\n\n
loadUsers: false<\/em> is not necessary here because the dump was made with users: false<\/em>. <\/p>\n\n\n\n
<\/div>\n\n\n\nAnd don’t forget to re-create your users<\/strong><\/a> \ud83d\ude42
You have plenty of authentication plugins<\/strong><\/a> to create secure user accounts.
We also provide Multifactor Authentication<\/a><\/strong>. <\/p>\n\n\n\nBtw if security is one of your concern (and it should be) below some MySQL password management features:<\/p>\n\n\n\n
\nMySQL Security Serie – Password Management<\/span><\/strong><\/p>\n\n\n\n
- ignoreVersion: true<\/em><\/strong> -> allow the import of the MariaDB dump into MySQL (MariaDB is taken for a MySQL 5.5 version) <\/li><\/ul>\n\n\n\n
- strip_restricted_grants<\/em><\/strong> -> remove specific privileges that are restricted by MDS<\/li>
- osBucketName: \u00ab\u00a0migrationMariadbNoUser\u00a0\u00bb<\/em><\/strong> -> name of the Object Storage bucket to which the dump is to be written<\/li>
- fingerprint<\/strong>: Fingerprint for the public key that was added to this user.<\/li>
- ALTER INSTANCE DISABLE INNODB REDO_LOG<\/em><\/strong> -> disable redo logging to speed up data loading<\/li>
- threads: 8<\/em><\/strong> -> 8 parallel threads to dump chunks of data from the MariaDB instance<\/li>
- ignore any missing primary keys (ignore_missing_pks<\/em>),<\/li>
- enforce the InnoDB storage engine (force_innodb<\/em>) ie change