MariaDB 10.1 to MySQL 5.6 migration

Uninstall MariaDB:

sudo apt-get remove mysql*

Install MySQL 5.6, as described in the MySQL docs:

sudo dpkg -i mysql-apt-config_0.6.0-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

MySQL may fail to start with the following errors:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[Note] mysqld (mysqld 5.6.27-0ubuntu0.14.04.1-log) starting as process 12796 ...
[Warning] Can't create test file /home/mysql_db/hostname.lower-test
[Warning] Can't create test file /home/mysql_db/hostname.lower-test
[Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
[Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13 - Permission denied)
[ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
[Note] InnoDB: Using atomics to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Not using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 10.0G
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: ./ibdata1 can't be opened in read-write mode
[ERROR] InnoDB: The system tablespace must be writable!
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting
[...]
[Note] mysqld: Shutdown complete

The mysqld: Can't find file: './mysql/plugin.frm' error is caused by AppArmor preventing access to our custom database folder: /home/mysql_db. Add the following lines to /etc/apparmor.d/usr.sbin.mysqld to fix this:

# Data files
/home/mysql_db/ r,  # change `/home/mysql_db/` to your datadir in the my.cnf file.
/home/mysql_db/** rwk,

We should also delete ib_logfile0 and ib_logfile1 files, because MySQL crashes when trying to read those files. Unlike ibdata1, those files are not essential.

rm /home/mysql_db/ib_logfile0 /homne/mysql_db/ib_logfile1

Now MySQL starts, but there are still a few errors:

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[Note] mysqld (mysqld 5.6.27-0ubuntu0.14.04.1-log) starting as process 12903 ...
[Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
[Note] Plugin 'FEDERATED' is disabled.
[ERROR] Function 'innodb' already exists
[Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
[ERROR] Function 'federated' already exists
[Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
[ERROR] Function 'blackhole' already exists
[Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
[ERROR] Function 'archive' already exists
[Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
[Note] InnoDB: Using atomics to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Not using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 10.0G
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB: 100 200
[Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB: 100 200
[Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
[Warning] InnoDB: New log files created, LSN=2805375586693
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: 5.6.27 started; log sequence number 2805375586828
[ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 39, event_type: -93
[Warning] Error reading GTIDs from binary log: -1
[above two lines repleated many times...]
[Note] Server hostname (bind-address): '*'; port: 3306
[Note] IPv6 is available.
[Note]   - '::' resolves to '::';
[Note] Server socket created on IP: '::'.
[ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(16), found type char(80).
[ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141).
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
[Note] mysqld: ready for connections.
Version: '5.6.27-0ubuntu0.14.04.1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

The Function 'innodb' already exists error is due to missing plugins in the mysql.plugin table. To get rid of those errors, remove the corresponding rows from the table (for more detail, see this post):

DELETE FROM mysql.plugin
WHERE name in ('innodb', 'federated', 'blackhole', 'archive');

The Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have type char(77), found type char(141) error is due to a change in the schema of the mysql.event table. Change it back to MySQL format:

SET SQL_MODE = '';

ALTER TABLE mysql.event
MODIFY definer CHAR(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

MySQL should now start without any errors.

sudo service mysql restart