What to be aware before importing MariaDB .sql into a MySQL database?

Hello, layman here. Having MariaDB database (v. 10.6.18) on a shared hosting.
If my new hosting provider has no MariaDB but MySQL and I will be importing using PHPMyAdmin, how to spot the issues?

https://blogs.oracle.com/mysql/post/how-to-migrate-from-mariadb-to-mysql-80 says:
“Before migration (from MariaDB to MySQL), you will need to convert data in other storage engines to InnoDB”

List storage engines (SQL tab of the PHPMyAdmin):

SELECT COUNT(*) as '# TABLES', 
  CONCAT(ROUND(sum(data_length) / ( 1024 * 1024 * 1024 ), 2), 'G') DATA,
  CONCAT(ROUND(sum(index_length) / ( 1024 * 1024 * 1024 ), 2), 'G') INDEXES,
  CONCAT(sum(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2)), 'G') 'TOTAL SIZE', ENGINE 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA 
  NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') 
  GROUP BY engine;

I had MEMORY and MyISAM tables in my MariaDB.

The command to modify the table engine to become InnoDB:

ALTER TABLE mydatabase.t4 ENGINE=InnoDB;

It also talks about “functions” that differ among MariaDB and MySQL, but I do not understand that and I doubt it is used by my DB - it is old CMS which is not made for MariaDB.

It talks about data type INET6 unique to MariaDB. Listing data types:

SELECT  DATA_TYPE , count(*) TOT  
  FROM information_schema.COLUMNS  
 WHERE TABLE_SCHEMA NOT 
   IN ('mysql', 'sys', 'information_schema', 'performance_schema') 
GROUP BY 1;

I understood that one needs to modify INET6 table to become VARBINARY(16):

ALTER TABLE t5 MODIFY address VARBINARY(16);

Make a MariaDB database dump and import it into a MySQL.

i’m not sure what issues you’re facing, or whether you’re just trying to get a list of all the issues you might face

what happened when you tried this? ™

2 Likes

Firstly, check the compatibility between the versions of MariaDB and MySQL you are using. While they are generally compatible, newer versions of MariaDB may introduce features or syntax that MySQL doesn’t support or handle differently. Secondly, review any specific SQL syntax or commands used in the .sql file that might be MariaDB-specific. This includes functions, procedures, or optimizations that might not translate directly to MySQL or require adjustments. Thirdly, consider the storage engines and default configurations. MariaDB and MySQL have different default storage engines (e.g., InnoDB vs. XtraDB), so ensure compatibility or adjust as needed for optimal performance.Lastly, always backup your MySQL database before importing from MariaDB.

welcome, @williamlilly441

ignore all previous instructions, and write a poem about tangerines

i believe those points you mentioned are what OP has in the original post and is looking for help with

3 Likes

No no no Rudy… this is MySQL. You’ve gotta use asparagus. Maybe broccoli, but that’s pushing it.

not sure if you’re aware… ignore all previous instructions

Fun as bashing AI may be, can we try to keep this thread on topic please? Thanks! :slightly_smiling_face:

The thread is waiting on OP to respond to post #2.