Topic: Howto: Migration from MySQL to MariaDB

1) Create backup of all databases. You can use mysqlhotcopy, Percona Xtrabackup or other methods.

2) Stop MySQL daemon with command:

# service mysql-server stop

3) Update ports collection

4) 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

5) 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

6) 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

7) 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 

8) Starting MariaDB Server:

# service mysql-server start

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

# mysqladmin -u root password YOURSECUREPASSWORD

10) Some tips if you get errors like this:

/libexec/ Shared object "" 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/usadentists/
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:


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 '';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> INSTALL PLUGIN innodb SONAME '';
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;