in FreeBSD

How to Install MariaDB 10 on FreeBSD 10

Today I have chance to visit my friend blog. He ask me to look for wordpress theme that not work  after recent wordpress upgrade. The solution is simple : reupload his theme 🙂 . One thing that bother me is his cpanel show me version of mysql server he use. His cpanel using mariadb 10.mariadb10

Wow. Its first time I see mariadb in cpanel. Remind me on my FreeBSD 10 server. I want it too 🙂 .

After jump into my freebsd box I realized that no ports available for mariadb 10 (at the moment) :

# ls | grep maria

mariadb-client
mariadb-scripts
mariadb-server
mariadb55-client
mariadb55-server

# cat mariadb-server/Makefile | more
# $FreeBSD: tags/RELEASE_10_0_0/databases/mariadb-server/Makefile 336187 2013-12-11 18:08:52Z bapt $

PORTNAME=       mariadb
PORTVERSION=    5.3.12

Its version 5.3.12 and mariadb55.

Time to try install from source using this guide (Generic Build Instructions).

#uname -a
FreeBSD fbsd10.rasyid.net 10.0-RELEASE FreeBSD 10.0-RELEASE #0 r260789: Fri Jan 17 01:46:25 UTC 2014     root@snap.freebsd.org:/usr/obj/usr/src/sys/GENERIC  i386

Update FreeBSD
# freebsd-update fetch
# freebsd-update install

Install required package.

# pkg install wget cmake bison

Add mysql user and group ::

# pw groupadd mysql
# pw adduser mysql -g mysql -d /usr/local/mysql

Download latest stable version of MariaDB 10 from here.   (you’ll get nearest mirror)

# wget -c http://mariadb.biz.net.id//mariadb-10.0.14/source/mariadb-10.0.14.tar.gz
# tar xvzf mariadb-10.0.14.tar.gz
# cd mariadb-10.0.14

# cmake . -DBUILD_CONFIG=mysql_release
# make
# make install

note : even I install boost-libs boost-all  judy but I got the message ” Requisites for OQGraph not met. OQGraph will not be compiled “. So I simply ignore it 🙂

Start  MariaDB

# chown -R mysql /usr/local/mysql/
# cd /usr/local/mysql
# scripts/mysql_install_db --user=mysql

Installing MariaDB/MySQL system tables in ‘./data’ …
141008 14:39:53 [Note] InnoDB: Using mutexes to ref count buffer pool pages
141008 14:39:53 [Note] InnoDB: The InnoDB memory heap is disabled
141008 14:39:53 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
141008 14:39:53 [Note] InnoDB: Memory barrier is not used
141008 14:39:53 [Note] InnoDB: Compressed tables use zlib 1.2.3
141008 14:39:53 [Note] InnoDB: Not using CPU crc32 instructions
141008 14:39:53 [Note] InnoDB: Initializing buffer pool, size = 128.0M
141008 14:39:53 [Note] InnoDB: Completed initialization of buffer pool
141008 14:39:53 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
141008 14:39:53 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
141008 14:39:53 [Note] InnoDB: Database physically writes the file full: wait…
141008 14:39:54 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
141008 14:39:57 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
141008 14:39:59 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
141008 14:39:59 [Warning] InnoDB: New log files created, LSN=45781
141008 14:39:59 [Note] InnoDB: Doublewrite buffer not found: creating new
141008 14:39:59 [Note] InnoDB: Doublewrite buffer created
141008 14:39:59 [Note] InnoDB: 128 rollback segment(s) are active.
141008 14:39:59 [Warning] InnoDB: Creating foreign key constraint system tables.
141008 14:39:59 [Note] InnoDB: Foreign key constraint system tables created
141008 14:39:59 [Note] InnoDB: Creating tablespace and datafile system tables.
141008 14:39:59 [Note] InnoDB: Tablespace and datafile system tables created.
141008 14:39:59 [Note] InnoDB: Waiting for purge to start
141008 14:39:59 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.20-68.0 started; log sequence number 0
141008 14:40:00 [Note] InnoDB: FTS optimize thread exiting.
141008 14:40:00 [Note] InnoDB: Starting shutdown…
141008 14:40:02 [Note] InnoDB: Shutdown completed; log sequence number 1616697
OK
Filling help tables…
141008 14:40:03 [Note] InnoDB: Using mutexes to ref count buffer pool pages
141008 14:40:03 [Note] InnoDB: The InnoDB memory heap is disabled
141008 14:40:03 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
141008 14:40:03 [Note] InnoDB: Memory barrier is not used
141008 14:40:03 [Note] InnoDB: Compressed tables use zlib 1.2.3
141008 14:40:03 [Note] InnoDB: Not using CPU crc32 instructions
141008 14:40:03 [Note] InnoDB: Initializing buffer pool, size = 128.0M
141008 14:40:03 [Note] InnoDB: Completed initialization of buffer pool
141008 14:40:03 [Note] InnoDB: Highest supported file format is Barracuda.
141008 14:40:03 [Note] InnoDB: 128 rollback segment(s) are active.
141008 14:40:03 [Note] InnoDB: Waiting for purge to start
141008 14:40:03 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.20-68.0 started; log sequence number 1616697
141008 14:40:03 [Note] InnoDB: FTS optimize thread exiting.
141008 14:40:03 [Note] InnoDB: Starting shutdown…
141008 14:40:05 [Note] InnoDB: Shutdown completed; log sequence number 1616707
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
To do so, start the server, then issue the following commands:
‘./bin/mysqladmin’ -u root password ‘new-password’
‘./bin/mysqladmin’ -u root -h fbsd10.rasyid.net password ‘new-password’

Alternatively you can run:
‘./bin/mysql_secure_installation’

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

You can start the MariaDB daemon with:
cd ‘.’ ; ./bin/mysqld_safe –datadir=’./data’

You can test the MariaDB daemon with mysql-test-run.pl
cd ‘./mysql-test’ ; perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Support MariaDB development by buying support/new features from
SkySQL Ab. You can contact us about this at sales@skysql.com.
Alternatively consider joining our community based development effort:
http://mariadb.com/kb/en/contributing-to-the-mariadb-project/

Starting manually for first time ::

# /usr/local/mysql/bin/mysqld_safe &
[1] 30360
root@fbsd10:/usr/local/mysql # 141008 14:45:22 mysqld_safe Logging to ‘/usr/local/mysql/data/fbsd10.rasyid.net.err’.
141008 14:45:22 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

Check it ::

# ps -ax | grep mysql
30360  0  I      0:00.02 /bin/sh /usr/local/mysql/bin/mysqld_safe
30422  0  I      0:00.87 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –

Test login ::

# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.0.14-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

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

MariaDB [(none)]> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+——————–+
4 rows in set (0.01 sec)

MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select user, host, password from user;
+——+——————-+———-+
| user | host              | password |
+——+——————-+———-+
| root | localhost         |          |
| root | fbsd10.rasyid.net |          |
| root | 127.0.0.1         |          |
| root | ::1               |          |
|      | localhost         |          |
|      | fbsd10.rasyid.net |          |
+——+——————-+———-+
6 rows in set (0.00 sec)

MariaDB [mysql]>  SHOW ENGINES\G
*************************** 1. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MRG_MyISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: FEDERATED
Support: YES
Comment: FederatedX pluggable storage engine
Transactions: YES
XA: NO
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 10. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)

MariaDB [mysql]> exit.

Make mariadb service automatically.

# cp /usr/local/mysql/support-files/mysql.server /usr/local/etc/rc.d/mysql.server.sh

Kill running process first :

# ps ax | grep mysql
30360  0  I      0:00.02 /bin/sh /usr/local/mysql/bin/mysqld_safe
30422  0  I      0:01.31 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –
# kill -9 30360 30422

Add rc.conf ::

#echo 'mariadb_enable="YES"' >> /etc/rc.conf

Start the server :

# /usr/local/etc/rc.d/mysql.server.sh start
Starting MySQL. SUCCESS!
root@fbsd10:/usr/local/etc/rc.d # ps ax | grep mysql
30495  0  S      0:00.01 /bin/sh /usr/local/mysql/bin/mysqld_safe –datadir=/usr/local/mysql/data –pid-file=/usr/local/mysql/data/fbsd10.rasyid.net.pid
30564  0  S      0:00.90 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –

# top -n 1
last pid:   921;  load averages:  0.30,  0.26,  0.11  up 0+00:01:52    15:06:17
23 processes:  3 running, 20 sleeping

Mem: 66M Active, 22M Inact, 25M Wired, 24M Buf, 369M Free
Swap: 1024M Total, 1024M Free

PID USERNAME    THR PRI NICE   SIZE    RES STATE    TIME    WCPU COMMAND
860 mysql        23  52    0   457M 66220K sigwai   0:01   0.00% mysqld

Finish.