Migrate from MySQL to MariaDB in FreeBSD

by
Share this Article: Facebook29Google+17Twitter20LinkedIn3Reddit0Pinterest0StumbleUpon0Digg
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;

For questions please refer to our Q/A forum at : http://ask.unixmen.com/

Share this Article: Facebook29Google+17Twitter20LinkedIn3Reddit0Pinterest0StumbleUpon0Digg