How to migrate from MySQL to MariaDB on FreeBSD

Before migration you should create backup of all databases. You can use mysqlhotcopy, Percona Xtrabackup or other methods.

1) Stop MySQL daemon with command:

$ service mysql-server stop

2) Update ports collection

3) Check which version of MySQL do you have:

$ pkg_version -v | grep mysql
mysql-client-5.5.17                 =   up-to-date with port
mysql-server-5.5.17                 =   up-to-date with port
...

4) We need to uninstall MySQL Server & Client ports.

$ cd /usr/ports/databases/mysql55-server/
$ make deinstall clean
$ cd /usr/ports/databases/mysql55-client/
$ make deinstall clean

5) Installing MariaDB Server, MariaDB Client and MariaDB Scripts:

$ cd /usr/ports/databases/mariadb-server
$ make install clean

You should check following options:

[X] SSL        Activate SSL support (yassl)
[X] ARIADB     Aria storage engine
[X] ARCHIVE    Archive storage plugin
[X] BLACKHOLE  Blackhole storage engine
[X] SPHINX     SE client for Sphinx search daemon
[X] FEDX       FederatedX storage engine (Federated replacement)
[X] XTRADB     XtraDB (InnoDB replacement) engine
[X] PBXT       MVCC-based transactional engine

MariaDB Client will be installed automatically.
You should check following options:

[X] THREADSAFE  Build thread-safe client
[X] SSL         Activate SSL support (yassl)

Installing MariaDB Scripts:

$ cd /usr/ports/databases/mariadb-scripts/
$ make install clean

6) Check if all ports installed ok:

$ pkg_version -v | grep maria
mariadb-client-5.2.9                =   up-to-date with port
mariadb-scripts-5.2.9               =   up-to-date with port
mariadb-server-5.2.9                =   up-to-date with port 

7) Starting MariaDB Server:

$ service mysql-server start

8) If you didn’t have a MySQL server before, you should create password for root user after MariaDB installation:

$ mysqladmin -u root password YOURSECUREPASSWORD

9) Some tips if you get errors like this:

/libexec/ld-elf.so.1: Shared object "libmysqlclient.so.18" not found, required by "postfix"

#1286 - Unknown table engine 'InnoDB'

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1286 Unknown table engine 'InnoDB': SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 167 of /usr/home/example/example.com/htdocs/includes/lock.inc).

apache-2.2.17_1 cannot install: unknown MySQL version: 52.

Tip 1: If your have Postfix or/and Dovecot with MySQL support, you should reinstall them.

Tip 2: If you get message with unknown MySQL version when installing ports, you should edit /etc/make.conf and add line:

MYSQL52_LIBVER=16

Tip 3: If you using Drupal 7, Postfixadmin or other software which requires InnoDB tables or Sphinx Search Engine, you should activate following plugins:

$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4656
Server version: 5.2.9-MariaDB-log Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> INSTALL PLUGIN sphinx SONAME 'ha_sphinx.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> INSTALL PLUGIN innodb SONAME 'ha_xtradb.so';
Query OK, 0 rows affected (0.11 sec)

MariaDB [(none)]> show engines;
+------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                                                          | Transactions | XA   | Savepoints |
+------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance                                           | NO           | NO   | NO         |
| InnoDB     | YES     | XtraDB engine based on InnoDB plugin. Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PBXT       | YES     | High performance, multi-versioning transactional engine                                          | YES          | YES  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables                                        | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                                                               | NO           | NO   | NO         |
| SPHINX     | YES     | Sphinx storage engine 0.9.9                                                                      | NO           | NO   | NO         |
| Aria       | YES     | Crash-safe tables with MyISAM heritage                                                           | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                                                            | NO           | NO   | NO         |
+------------+---------+--------------------------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

MariaDB [(none)]> exit;