Brain-dump:

There are some differences between the respective versions, which are all outlined here (though I do not see anything regarding data incompatibility-type issues):

https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/

> I also found another blog from Fred Flintstone saying that migration from MySQL to MariaDB after MySQL 5.5 would be a one-way trip:
> https://mariadb.com/kb/en/mariadb/upgrading-from-mysql-to-mariadb/

Yes, it's a one way trip. This is mainly because the tables in mysql.user are being updated to MariaDB.

In theory, if one copies the tables in mysql.user one can downgrade to MySQL any tables that
have not been altered or created in MariaDB.

> In the future, if there were different implementations of a same feature, would migration from MySQL be fully compatible?

MySQL 5.6 has a different implementation of timestamp with microseconds than MariaDB 5.5
In MariaDB 10.0 we added support also for MySQL version of microseconds.

MySQL 5.7 has different implementation of replication and virtual fields than MariaDB.
MariaDB 10.2 can read MySQL 5.7's replication stream without any problems and we also can read
the .frm files from MySQL 5.7 with virtual fields.

MariaDB 10.2 should have all major features of MySQL 5.7 and be data compatible on disk.

The one feature we haven't yet decided to implement is the x protocol
https://dev.mysql.com/doc/internals/en/x-protocol.html
We want to first see if there is any uptake of this in teh community before spending time to implement this.

> 3. Will migration from MySQL always be as simple as installing MariaDB binary and running mysql_upgrade?

For most cases, yes.
The one case we know about when things will not be smooth is if you are using MySQL encryption.
In this case you have to do a mysqldump and restore, which should always work.

1. I found some MySQL features that are not supported in MariaDB which are not mentioned in this link: https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/
a) MySQL SHA-256 Password Hash
If a customer has a MySQL user which use SHA-256 password hash, he can't use the user in MariaDB after the migration. It's a known issue in MariaDB: https://jira.mariadb.org/browse/MDEV-9804
[23:21:34][root][/rdsdbbin/sql]$ /rdsdbbin/mysql/bin/mysql -u sha256user -p --ssl-cipher=
Enter password: ERROR 1524 (HY000): Plugin 'sha256_password' is not loaded

b) MySQL Memcached Plugin
MariaDB does not support Memcached plugin. But the customers are able to retrieve their data they stored using memcached because the data is stored as InnoDB tables. MariaDB is able to start successfully with an error message of not being able to find libmemcached.so library:
[ERROR] Can't open shared library '/rdsdbbin/mariadb-10.1.14.R1/lib/plugin/libmemcached.so' (errno: 0, cannot open shared object file: No such file or directory)

https://jira.mariadb.org/browse/MDEV-10265
https://jira.mariadb.org/browse/MDEV-10266

Along the way, I ran across the following 3 pages which all contain useful compatibility and new feature details. I think you will find the first two especially helpful:

https://mariadb.com/blog/mysql-56-vs-mariadb-100
https://mariadb.com/blog/mariadb-10-vs-mysql-56-feature-comparison-update
https://mariadb.com/kb/en/mariadb/differences-between-the-mysql-and-mariadb-query-optimizer/

As for some specifics, first of all, I know GTID is briefly mentioned, as there are different implementations. Of course, when migrating, this means you could not mix-and-match MySQL and MariaDB servers in replication using GTID. However, when you do migrate, you would need to change the variables used, so that you could continue to use GTID. Thus I think this is a good topic to highlight for your users. The GTID-related variables in MySQL 5.6 are:

mysql -uroot -p -P3309 -e"select version();show global variables like '%gtid%'"

+------------+

| version() |

+------------+

| 5.6.31-log |

+------------+

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| binlog_gtid_simple_recovery | OFF |

| enforce_gtid_consistency | OFF |

| gtid_executed | |

| gtid_mode | OFF |

| gtid_owned | |

| gtid_purged | |

| simplified_binlog_gtid_recovery | OFF |

+---------------------------------+-------+

Conversely, in MariaDB 10.1, the GTID-related variables are:

mysql -uroot -p -P3316 -e"select version();show global variables like '%gtid%'"

+-----------------+

| version() |

+-----------------+

| 10.1.14-MariaDB |

+-----------------+

+------------------------+-------+

| Variable_name | Value |

+------------------------+-------+

| gtid_binlog_pos | |

| gtid_binlog_state | |

| gtid_current_pos | |

| gtid_domain_id | 0 |

| gtid_ignore_duplicates | OFF |

| gtid_slave_pos | |

| gtid_strict_mode | OFF |

+------------------------+-------+

And the variables (for MariaDB) are described in detail here:

https://mariadb.com/kb/en/mariadb/server-system-variables/

And MariaDB GTID implementation, in general, is described here:

https://mariadb.com/kb/en/mariadb/gtid/

Second, optimizer changes are not mentioned on the "compatibility" page, as they are really behind-the-scenes anyway, however, it is something to be aware of (especially for those who might notice some differing EXPLAIN plans on queries). I did add this link above, but I wanted to highlight it here, to be clear:

https://mariadb.com/kb/en/mariadb/differences-between-the-mysql-and-mariadb-query-optimizer/

Third, I do not see any mention of the "federated" storage engine. MariaDB dropped Federated and instead uses FederatedX, since active development had stopped on Federated. They should behave the same, and the CREATE TABLE syntax appears to be the same. The only difference I see is that MySQL allows Federated tables to be created by both CREATE TABLE and CREATE SERVER commands. I do not see a CREATE SERVER command for MariaDB. Thus any Federated tables created in this manner in MySQL would need to be re-created in MariaDB using the CREATE TABLE command, as far as I can tell.

https://mariadb.com/kb/en/mariadb/about-federatedx/
http://dev.mysql.com/doc/refman/5.6/en/federated-storage-engine.html
http://dev.mysql.com/doc/refman/5.6/en/create-server.html

Fourth, I see "delayed replication" (MASTER_DELAY) as an option in MySQL 5.6, but do not see it available in MariaDB:

http://dev.mysql.com/tech-resources/articles/whats-new-in-mysql-5.6.html
https://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html

That is about it, at least as far as I can find, for MySQL 5.6.

Getting into MySQL 5.7, I see it offers both ngram and MeCab full-text parser plugins (for Chinese/Japanese/Korean languages):

http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

I do not see these in MariaDB, so that is a difference. I did file feature requests to add them, however:

https://jira.mariadb.org/browse/MDEV-10267
https://jira.mariadb.org/browse/MDEV-10268

I also see 5.7 supports JSON with a native type. However, MariaDB currently has support for JSON using the CONNECT JSON table type:

http://dev.mysql.com/doc/refman/5.7/en/json.html
https://mariadb.com/kb/en/mariadb/connect-json-table-type/

Lastly, I see that MySQL 5.7 supports multiple triggers (of the same type) for a table. This does not currently exist in MariaDB, though there is an existing feature request for that here:

https://jira.mariadb.org/browse/MDEV-6112

Downgrading, if necessary: However, this approach gets complicated though, and is prone to change as well, so I would opt to use the mysqldump/import method for the 'mysql' schema tables (i.e., mysql.user) that need migrating, which, again, is the same/only method MySQL recommends for a 5.7 -> 5.6 downgrade.

Feel free to ask me any questions about any of the above if anything is not clear, but I needed to brain-dump all of these findings somewhere before I forget.

I added lines to the Compatibility page for the five points that I couldn't find mention of otherwise:

• FederatedX, optimizer changes in re EXPLAIN statements, and delayed replication for 10.0/5.6 differences.
• CONNECT JSON and multiple triggers for 10.1/5.7 differences.

Given that this ticket is a couple years old, is there anything else that we need to add on these sections?

https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/#incompatibilities-between-mariadb-101-and-mysql-57

The documentation is probably still missing some details, but I don't know how much research you want to do to ensure that its complete. For example, a quick Google search shows that MySQL 5.7 has the sha256_password plugin, but MariaDB does not.

https://dev.mysql.com/doc/refman/5.7/en/sha256-pluggable-authentication.html

https://jira.mariadb.org/browse/MDEV-9804

MySQL 5.7 also has transportable tablespaces for partitioned InnoDB tables, but MariaDB does not.

https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html

https://jira.mariadb.org/browse/MDEV-10568

http://www.geoffmontee.com/importing-innodb-partitions-in-mysql-5-6-and-mariadb-10-010-1/

MySQL 5.7 also has online undo tablespace truncation, but that was only added in MariaDB 10.2.

https://dev.mysql.com/doc/refman/5.7/en/truncate-undo-tablespace.html

https://jira.mariadb.org/browse/MDEV-8994

MySQL 5.7 also has a new version of performance_schema and a sys schema wrapper, but MariaDB does not yet support that.

https://dev.mysql.com/doc/refman/5.7/en/performance-schema.html

https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html

https://jira.mariadb.org/browse/MDEV-6114

https://jira.mariadb.org/browse/MDEV-9077

MySQL 5.7 added multi-source replication and replication channels, but MariaDB's multi-source replication (added in 10.0) uses different syntax.

https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html

https://dev.mysql.com/doc/refman/5.7/en/replication-channels.html

https://mariadb.com/kb/en/library/multi-source-replication/

MySQL 5.7 added group replication, but this is incompatible with MariaDB's Galera Cluster replication.

https://dev.mysql.com/doc/refman/5.7/en/group-replication.html

https://mariadb.com/kb/en/library/galera-cluster/

There may be others too. It might help to go through the "What's new in MySQL 5.7" page.

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Thanks Geoff. Added content for the points you made as well as a few others found on MySQL's What's New page.