Installing MySQL And phpMyAdmin On FreeNAS

This howto was written when I needed to run some php based web applications and the only system around was FreeNAS. Although many people are using php and MySQL on a FreeNAS box successfully I couldn't find any simple tutorial for the purpose, so I wrote one in case anyone needs it.

For this howto the following software and configurations were used:

  • FreeNAS version 0.7RC1
  • Installed with option "install 'full' OS on HDD + data + swap partition" or "install 'full' OS on HDD + data partition"
  • Installaed with enough space on OS partition to install additional packages
  • Working Internet connection

For the installation of FreeNAS there is a good tutorial available on HowtoForge, click this link.

 

Install MySQL

After a successfull installation and configuration of FreeNAS we will install MySQL on the box using the following steps.

Create the group and user 'mysql' using the FreeNAS WebGUI, it is important because FreeNAS will forget about the users created on command line after reboot.

ssh in to the FreeNAS box:

# ssh 192.168.1.250
root@192.168.1.250's password:
Copyright (c) 1980, 1983, 1986, 1988, 1990, 1991, 1993, 1994
The Regents of the University of California. All rights reserved.

Welcome to FreeNAS!

freenas:~#

Create a directory to store MySQL data files, in my case I create a sub-directory in /mnt/dynamic/db to store database files, the reason to create this folder is to utilize the space on the data partition, as the data files grow it is a good place to store your growing database files.

# mkdir /mnt/dynamic/db/mysql

By default the MySQL server on FreeNAS stores database files in /var/db/mysql which does not exist at this point, so let's make a symbolic link in that directory:

# ln -s /mnt/dynamic/db/mysql /var/db/mysql

Give all permissions to the user and group mysql for /mnt/dynamic/db/mysql:

# chown -R mysql:mysql /mnt/dynamic/db/mysql

Allow anyone to create temporary files in the following direcories, MySQL needs to create temporary files there:

# chmod 777 /tmp
# chmod 777 /var/tmp

Type the following command to install MySQL server:

# pkg_add -r mysql50-server

Execute the following command to create default databases and tables:

# mysql_install_db

Installing MySQL system tables...
090819 11:18:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090819 11:18:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
OK
Filling help tables...
090819 11:18:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
090819 11:18:58 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
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 MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h freenas.local password 'new-password'

Alternatively you can run:
/usr/local/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 manual for more instructions.

You can start the MySQL daemon with:
cd /usr/local ; /usr/local/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com

Now to automatically start mysql after a reboot, add mysql_enable="YES" to rc.conf, GUI is prefereable to make this change, select System -> Advance -> rc.conf:

System->Advance->rc.conf

Click on the red plus sign to create a new entry:

add

After filling the fields as shown in the image click the add button:

Apply changes

Click the button "Apply changes" to apply and save the changes permanently:

Done!

The installation of the MySQL server has successfully been completed. At this point you may reboot FreeNAS and check the proper startup of the MySQL server after reboot.

 

Installation of PHP

Although FreeNAS includes php for its own web interface working upon it, it's good to install extra supporting packages to run php-based applications. Let's start the process with the following commands:

# pkg_add -r php5
# pkg_add -r php5-extensions
# pkg_add -r php5-xmlrpc
# pkg_add -r php5-gettext
# pkg_add -r php5-mcrypt
# pkg_add -r php5-mysql
# pkg_add -r php5-mbstring

These commands install php5 and some basic packages needed to run phpMyAdmin, after the installation you will not be able to access FreeNAS webGUI interface because of a problem, don't panic let's correct it by issuing the following commands:

# mv /usr/local/bin/php /usr/local/bin/php-cli
# cp /usr/local/bin/php-cgi /usr/local/bin/php

Back in business! At this point our installation of the MySQL server and PHP has successfully been completed.

 

Installation of phpMyAdmin

Let's create a proper directory to hold applications, you are welcome to use you own scheme:

# mkdir /mnt/dynamic/apps
# cd /mnt/dynamic/app

Donwload phpMyAdmin from http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/; in my case I've donloaded the phpMyAdmin-3.2.1-english.tar.gz file. You can use the following command on the FreeNAS box or copy via scp.

fetch http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/3.2.1/phpMyAdmin-3.2.1-english.tar.gz

Untar the archeive and create the necessary symbolic links:

# tar -zxvf phpMyAdmin-3.2.1-english.tar.gz
# ln -s /mnt/dynamic/apps/phpMyAdmin-3.2.1-english/ /mnt/dynamic/apps/phpMyAdmin

Now create a link to run phpMyAdmin from the default location:

# ln -s /mnt/dynamic/apps/phpMyAdmin/ /usr/local/www/phpMyAdmin

You can access phpMyAdmin from http://< FreeNAS IP >/phpMyAdmin/.

Enjoy!

Share this page:

42 Comment(s)

Add comment

Comments

From: llambion at: 2009-10-15 09:04:26

I try to install phpmyadmin and this error shows,

trasgu:/# pkg_add -r php5-mysql
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.2-release/Latest/php5-mysql.tbz... Done.
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.2-release/All/mysql-client-5.0.77_1.tbz... Done.
pkg_add: package 'mysql-client-5.0.77_1' conflicts with mysql-client-5.1.33
pkg_add: please use pkg_delete first to remove conflicting package(s) or -f to force installation
pkg_add: pkg_add of dependency 'mysql-client-5.0.77_1' failed!

 

From: Anonymous at: 2010-03-07 09:32:54

Hi. I cannot change mysql password because give me an error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Please help me, thanks

From: Anonymous at: 2010-03-17 17:38:54

i have the same problem...

 Has anyone solution?

From: Anonymous at: 2010-04-14 05:08:52

Seems this tutorial is kinda " dodgy " at best. I would advise people to NOT use this tutorial unless you are well versed in linux / unix / *nix systems and the way they operate (or complain when you break something :p )

 There's quite a bit of information in this tut, but alas, some of it is quite useless, or not fully detailed enough to be rightfully called a tutorial.

again, unless you know how to recover your system from a bad install / broken package links etc.. don't attempt this. 

 The fact i'm not exactly a newbie when it comes to *nix systems, yet i'm still having issues tells me there's a missing step / steps that are crucial.

 for example, if you do this, you'll also need to install the following packages before rebooting / rehashing:

[code]

# pkg_add -r -v xmlstarlet.tbz

# pkg_add -r -v libxslt.tbz

[/code]

 

Mind you, i'm still not able to run MySQL commands due to the fact i recieve a socket error when attempting to. My guess is MySQLD isn't actually running.

From: Eric at: 2009-11-29 18:15:16

Once I installed all the php5 packages I reboot and then it's impossible to connect to my freenas by ssh or by Web Gui, I tried also to ping it... and impossible to reach the resquested host, then I try to loggin directly to the console, I see this error message:

 /libexec/ld-elf.so.1: /usr/local/lib/libxml2.so.5: version LIBXML2_2.6.0 required by xml not defined

In order to get access to the command prompt, I do CTRL+Z and I try to execute directly:

 :~#xml

Then again the same error message appear, so now I understand that my config.xml cannot be read at boot. I'll really appreciate, if somebody have a solution.

From: Anonymous at: 2009-12-11 16:25:50

I have such problem, but I find out may be package "libxml2.7.3.tbz" installed, missing some settings.

Install package "xmlstarlet" new edition to fix problem

pkg_add -r xmlstarlet

The problem will fixed.

Full pack for phpalbum on Freenas

pkg_add -r gd
pkg_add -r php5
pkg_add -r php5-extensions
pkg_add -r php5-xmlrpc
pkg_add -r php5-gettext
pkg_add -r php5-mcrypt
pkg_add -r php5-mbstring
pkg_add -r php5-gd

pkg_add -r xmlstarlet

After the installation you will not be able to access FreeNAS webGUI interface because of a problem, to correct problem the following commands

mv /usr/local/bin/php /usr/local/bin/php-cli
cp /usr/local/bin/php-cgi /usr/local/bin/php

After restart will be work fine.

 

From: assad at: 2010-01-12 19:29:55

Thank you Anonymous. after that nas started and many services works fine.

but upnp doesnot work.

on start fuppesd writes

/libexec/ld-elf.so.1: /usr/local/lib/libxml2.so.5: version LIBXML2_2.6.5 required by /usr/local/lib/libfuppes.so.0 not defined

From: assad at: 2010-01-13 20:36:18

I have fixed this issue having built fuppes in the freebsd 7.2 and move binaries into the freenas.

 

From: Ric Hochet at: 2010-02-14 14:05:26

How did you built fuppes ?

From: Russel at: 2010-03-05 20:49:19

assad, 

could you be so kind to make a small HowTo with created binaries?

I have the same problem.

From: assad at: 2010-04-23 20:50:58

You can download freebsd 7.2 and isntall it. you can download fuppes sources and untar it. 

wget http://sunet.dl.sourceforge.net/project/fuppes/fuppes/SVN-660/fuppes-0.660.tar.gz

cd fuppes-0.660

./configure

make

I found some compile error. but I fix it. I think that in new version if fuppes it should be fixed.

after fix error anyway

make

than tar files and copy on the place old fuppes.

It is about fuppes.

But now I see that it is not good idea to install php and my sql as described here. distributive is broken after that.

proftpd - broken and I rebuild it.

Unicorn rsync - does not work. 

using pkg_add is dangerous. Now I try it 

pkg_add -r -n .... and if system packet doesn't changed I install something.

Now I find way to install mysql and some php libraries w/o change core system files.

After sixty days system fails and doesn't start. I reinstall it.

From: Anonymous at: 2010-07-28 23:36:14

how you rebuild the proftpd after following this i stop having remote ftp access i only can access ftp on my network.

From: Piotrk at: 2011-07-27 17:12:51

do:

pkg_add -r php5-mysqli

Small letter i makes the difference. The mysqli connector is just in an another package and the php5 uses MySQLi by default to connect mysql databases.



From: Anonymous at: 2010-04-15 13:42:16

Thank's for that one... hadn't noticed all those #!/usr/local/bin/php all over the place that seem to override the lighttpd cgi setting. Saved me an age debugging a white screen.

From: Louisd at: 2010-04-15 18:14:04

After following the tutorial, your WebGUI might not work

I have the same problem

My solution:

1. Put the FreeNAS live CD in your computer

2. In the installation menu, choose Upgrade Full OS from CD-ROM

3. Upgrade the OS

4. Restart the FreeNAS server

Everything works fine again!

 

Louisdji

From: Marco at: 2009-12-13 23:07:09

Hi Mohammad,

great Tutorial!
Just a general question, I downloaded

mysql51-server.tbz and
mysql51-client.tbz from:

ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-6.4-release/Latest/

These files are ~5.4MB and ~1MB.
On the mysql webside I saw the freebsd version with more than 100MB
http://dev.mysql.com/downloads/mysql/5.1.html#freebsd

How can this be?

One more question, how can I check if the server is up?

Greetings
Marco

From: Rautamiekka at: 2009-12-20 22:07:06

Glad someone had finally made a tut but this ain't compatible with FN v0.7 (amd64)+MySQL v5.1.33+phpMyAdmin v3.2.4 cuz after reboot trying to access phpMyAdmin brings "404 not found" & SSH access is blocked & console menu disabled & hostname doesn't show on SYSTEM STATUS page regardless of settings. Also, there's a ton of complaint by whole system about undefined stuff, on bootup. I only have the following complaint written which I receive after FN have booted to the console menu:

 


/libexec/lb-elf.so.1: /usr/local/lib/libxml2.so.5: version LIBXML2_2.6.0 required by /usr/local/bin/xml not defined

[: -eq: argument expected


From: Anonymous at: 2009-12-25 19:04:38

This doesn't even come close to working.

From: Anonymous at: 2012-04-18 15:53:08

Mysql is not available for FreeNAS. Disappointed. going back to Opensuse Server

 

From: Anonymous at: 2009-11-05 14:36:32

Do you think it is possible to do this with a LiveCD running version of FreeNAS?

You can't write to the / directory, so getting the packages to install as expected might be tricky....

From: Ben. at: 2009-10-06 17:50:17

Just execute "rehash" to make the shell-command available without reboot.

From: daniel at: 2009-10-10 08:06:15

after i did everthing i get the error Cannot start session without errors, please check errors given in your PHP and/or webserver log file and configure your PHP installation properly. by phpmyadmin how do i fix that i cant get acces to mysql ether

From: Anonymous at: 2009-09-20 15:19:01

Works great for me thank you very much. Just had to reboot nas before running this command mysql_install_db

 

And then all was fine.

 

Thanx again

From: lorenzo at: 2009-09-04 01:18:09

Great tutorial: the start is good, but skipping steps for a total newby makes it difficult to figure out when things go wrong.

i had some problems and wonder if i'm the only one?

the command mysql_install_db

returns the following:

mysql_install_db: Command not found.

also i was quite puzzled by your comment here: "To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system"

what does this mean? i have no clue what this entails: where would the default be?

up to here i think everything worked fine, but i'm stuck after:

daNas:/mnt/dynamic/db# chown -R mysql:mysql /mnt/dynamic/db/mysql
daNas:/mnt/dynamic/db# chmod 777 /tmp
daNas:/mnt/dynamic/db# chmod 777 /var/tmp
daNas:/mnt/dynamic/db# pkg_add mysql51-server
pkg_add: can't stat package file 'mysql51-server'
daNas:/mnt/dynamic/db# pkg_add -r mysql51-server
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.2-release/Latest/mysql51-server.tbz... Done.
Fetching ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.2-release/All/mysql-client-5.1.33.tbz... Done.
mysql:*:1001:
You already have a group "mysql", so I will use it.
mysql:*:1002:1001::0:0:db access:/mnt:/bin/tcsh
You already have a user "mysql", so I will use it.

************************************************************************

Remember to run mysql_upgrade (with the optional --datadir=<dbdir> flag)
the first time you start the MySQL server after an upgrade from an
earlier version.

************************************************************************

 any further info will be very much appreciated and i'm sure others will welcome more details!

 thanks

From: Anonymous at: 2010-02-08 18:41:44

I followed all your steps apart form few moments where I had to "use" my linux experience or better said - unix-like thinking everything works fine for me. sound as a pound mate. thanks. I even didn't have to reboot anything. rehash after intalling all the packages worked just fine for me. ps. there are few moments in you tutorial where newbie might have  a problem.

1.  chown -R mysql:mysql /mnt/dynamic/db/mysql -didn't do much unless I did pkg_add -r mysql50-server

So first pkg_add -rv mysql50-server

then

mkdir -p /mnt/dynamic/db/mysql

chown -R mysql:mysql /mnt/dynamic/db/mysq

chmod 777 /tmp

chmod 777 /var/tmp

cd /usr/local/bin/

./mysql_install_db


if not then try 

./usr/local/bin/mysql_secure_installation

./usr/local/bin/mysqladmin -u root password put_some_password_in_here
 /usr/local/bin/mysqladmin -u root -h freenas.local password some_password

and so on...

2.

# mkdir /mnt/dynamic/apps
# cd /mnt/dynamic/app - should be cd /mnt/dynamic/apps ... :) 

and so on :)

 

But all in all great tut.

From: Anonymous at: 2010-03-21 06:10:19

INstall and setup works OK.

 But when rebooted, the user mysql, gets deleted, so allways have to start over from start ????

From: vkyk at: 2010-04-17 07:17:54

llambion, I solve this crash by downgrading mysql-client & mysql-server to 5.0.77_1. 

I only know little bit about Freenas (and Freebsd) but install Freenas 0.7.1 Shere (revision 5127) and follow the procedures mentioned above to install mysql, php & phpAdmin.  And face the same problem.

I use pkg_delete to remove mysql-client-5.1.33 and mysql-server-5.1.33 being installed. Then download mysql-client-5.0.77_1.tbz and mysql-server-5.0.77_1.tbz directly from ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.2-release/All/

In freenas gui, I use Package to add back mysql-client-5.0.77_1 and mysql-server-5.0.77_1.  Then reinstall 'php5-mysql' and follow the remaining procedures to complete the installation of mysql, php & phpAdmin.

My Freenas now also run webmin and opendocman. 

From: UCColor Regards at: 2010-05-18 11:10:04

This is a very good link on howto install mysql on freenas:

 http://www.novell.com/communities/node/11406/low-cost-netware-backup-disk-using-freenas-part-ii

 Regards.

From: Anonymous at: 2010-06-25 12:32:14

Thanks for the link, but it was clearly mentioned and even the text copied in the web page referenced  in above post was taken from this article.

 Regards,

 

From: UCColor at: 2011-01-22 23:56:51

Yeah, but this one actually works.

From: Felix at: 2011-01-25 08:24:25

Yeah!, UCColor is right.  That links to a more comprehensive tutorial!  Very handy ;-)

From: Adolfo Rodrigues at: 2011-03-04 14:30:12

Hi, I have followed the guide from here: http://www.novell.com/communities/node/11406/low-cost-netware-backup-disk-using-freenas-part-ii

 

But I keep getting an error, every time I try to install using pkg_add. Here it is:

Error: Unable to get ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.3-release/Latest/php5.tbz: No address record
pkg_add: unable to fetch 'ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7.3-release/Latest/php5.tbz' by URL

Does anyone know how to fix this??

Thanks in advance.

Regards

From: Anonymous at: 2011-03-20 19:55:16

The problem is the version in the error it says /i386/packages-7.3-release/  but the version is not 7.3 check your admin part of the freenas, change /packages-7.3-release/ in /packages-7-stable/

and the most will be found also look at ftp.freebsd.org/pub/i386/ for the right package name

From: jim at: 2011-10-24 03:48:35

This one took me a minute to figure out. You need to add the following line to ~/.cshrc:

setenv PACKAGESITE ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7
-stable/Latest/

 After that it should work.

From: Alexandre Jacquin at: 2010-06-17 09:04:16

Hello, when I write that : mkdir /mnt/dynamic/db/mysql, server responding that : "mkdir: /mnt/dynamic/db: No such file or directory".
What is the matter ?
 
Thanks, 
Alexandre
 
PS : my HDD is called "dynamic".

From: Anonymous at: 2010-09-14 09:39:19

Good tut but after install i've lost all my groups.

From: DYoda at: 2011-03-23 15:17:50

Im a beginner in freenas/linux so i ask for help.

 Everthing until step "# pkg_add -r mysql50-server" after that command i have info "No space left on device"

I have Freenas installed on a 1GB Pendrive , then i mounted 1TB harddisk only for files.

When i go in QuiXplorer to folder "mnt" there is a 270GB freespace , but everelse like "home" directory only 522KB freespace

 I would be very grateful for the help.

Sorry for english.

From: freerider at: 2011-05-05 02:28:38

Hi,

I have a problem when i want to start mysql manually. I run /usr/local/bin/mysqld_safe but mysql service don´t start well.

the response of the console is the following:

freenas:/usr/local/bin# mysqld_safe

Starting mysqld daemon with databases from /var/db/mysql

STOPPING server from pid file /var/db/mysql/freenas.local.pid

110505 02:22:10  mysqld ended

 
Moreover, the user "mysql" disappears after restarting and i have to create the user when i want to start the service.

From: Alexander at: 2011-05-25 08:07:46

1) Create user and group 'mysql' for web interface;
2) mkdir -p /mnt/dynamic/db/mysql;
3) ln -s /mnt/dynamic/db/mysql /var/db/mysql;
4) chown -R mysql:mysql /mnt/dynamic/db/mysql;
5) chmod 777 /tmp;
6) chmod 777 /var/tmp;
7) pkg_add -r mysql55-server;
8) /usr/local/bin/mysql_install_db;
9) chown -R mysql:mysql /mnt/dynamic/db/*;
10) Now to automatically start mysql after a reboot, add mysql_enable="YES" to rc.conf, GUI is prefereable to make this change, select System -> Advance -> rc.conf;
11) reboot;
12) mkdir /mnt/dynamic/apps;
13) cd /mnt/dynamic/apps;
14) fetch http://sourceforge.net/projects/phpmyadmin/files/phpMyAdmin/3.4.2/phpMyAdmin-3.4.2-english.tar.gz;
15) tar -zxvf phpMyAdmin-3.4.2-english.tar.gz;
16) ln -s /mnt/dynamic/apps/phpMyAdmin-3.4.2-english/ /mnt/dynamic/apps/phpMyAdmin;
17) ln -s /mnt/dynamic/apps/phpMyAdmin/ /usr/local/www/phpMyAdmin;
And Enjoy now!
Sorry for english.

From: Anonymous at: 2011-06-20 01:36:52

I did thhis method and got the error when connectng to phpMyAdmin:

 "phpMyAdmin - Error

The mysqli extension is missing. Please check your PHP configuration."

 Any suggestions?

Regards

From: Marema at: 2011-07-08 22:08:09

Hi, I have same error.

Any solutions?

thx

Marena

From: Anonymous at: 2012-01-14 03:25:43

I ran into a problem where mysql was not starting after reboot. Turns out that /usr/sbin/daemon was missing on my new install of 0.7.2.8191. I replace the file and MySQL is starting up after reboot now. Not sure why FreeNAS left that out of rev 8191.