Migrate from MySQL to MariaDB in FreeBSD

The usage of MySQL for development is free. As you are not giving away that product (MySQL), no GPL restrictions apply. If you want to distribute MySQL in some form, the licenses apply. See: MySQL commercial license

MariaDB is a community-developed fork of the MySQL relational database management system, the impetus being the community maintenance of its free status under the GNU GPL. As a fork of a leading open source software system, it is notable for being led by its original developers and triggered by concerns over direction by an acquiring commercial company Oracle. Contributors are required to share their copyright with Monty Program AB.

The intent is also to maintain high compatibility with MySQL, ensuring a “drop-in” replacement capability with library binary equivalency and exacting matching with MySQL APIs and commands. It includes the XtraDB storage engine as a replacement for InnoDB, as well as a new storage engine, Aria, that intends to be both a transactional and non-transactional engine perhaps even included in future versions of MySQL.

Notes:

1) Please Backup your databases before you start this migration

2) Stop MySQL daemon with command:

# service mysql-server stop

3) Update ports collection

4) Check which version of MySQL 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 now 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/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/usadentists/usadentists.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 re-install 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;