How to Install MySQL Server with phpMyAdmin on iocage in FreeNAS

MySQL is a free and open source relational management system. It stores data in tabular format. It is the most popular way of storing the data into the database. phpMyAdmin is also a free and open source application used to administrate a MySQL server instance through a rich graphical user interface. phpMyAdmin is written in PHP. To install phpMyAdmin, we will also need to install a web server with PHP on FreeBSD.

In this tutorial, we will install MySQL with phpphp.ini.MyAdmin along with Apache web server with PHP 5.6.

Prerequisites

  • Minimal FreeBSD 11 server.
  • Root privileges. This guide is written as the root user, if you are logged in as sudo user, run sudo -i.

Before installing any package it is recommended that you update the packages and repository using the following commands.

pkg update
pkg upgrade

Once the system is updated proceed to install MySQL server.

Install MySQL Server

To install MySQL run the following command.

pkg install mysql57-server mysql57-client

This command will install version 5.7 on to the the server. Enable the server to run at startup with.

sysrc mysql_enable="yes"

And finally, start the server.

service mysql-server start

To obtain the initial root password which was set during installation, run the following command.

cat $HOME/.mysql_secret

You should see following output, which includes the current MySQL root password that should look similar in format to this.

# cat $HOME/.mysql_secret
# Password set for user 'root@localhost' at 2017-07-23 17:42:39 y4j?<!sH3fh9r

You should secure the MySQL instance before using it for production use. Run the following command to secure the MySQL instance.

mysql_secure_installation

The above command will automatically read the password in $HOME/.mysql_secret and will continue to set validate password plugin.

# mysql_secure_installation 

Securing the MySQL server deployment.

Connecting to MySQL server using password in '/root/.mysql_secret'

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of the password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 1

It is recommended to use validate password plugin as it bounds the user to set a very strong password for MySQL users. Choose y to set the validate password plug in and enter the level of validation policy according to your choice.

Also, change the root password. Also, choose y for all other options. The questions are self-explanatory.

Change the password for root ? : y
Do you wish to continue with the password provided? : y
Remove anonymous users? : y
Disallow root login remotely? : y
Remove test database and access to it? : y
Reload privilege tables now? : y
All done!

The MySQL server is now installed on your server. You can login to MySQL shell by running the following command.

mysql -u root -p

You should be successfully logged in after providing the password. Your shell will change, and you will get following output.

# mysql -u root -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8

Server version: 5.7.18-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. root@localhost [(none)]>

You can exit from MySQL prompt by running EXIT; command.

Install Apache web server

You can easily install Apache web server through the default package manager pkg. Run the following command to install Apache web server on your system.

pkg install apache24

Enable Apache in rc.conf by running the following command.

sysrc apache24_enable="YES"

Now you can start the Apache web server by running the following command.

service apache24 start

Test that your newly installed web server working by browsing the following URL through your favourite web browser.

http://Your_Server_IP

You should see the following words in your browser.

It Works!

Install and configure PHP

phpMyAdmin is written in PHP, so you will need to install PHP on your web server. In this section you will install PHP 5.6 along with the required modules by running the following command.

pkg install mod_php56 php56-session php56-xml php56-ctype php56-openssl php56-mcrypt php56-filter php56-gd php56-json php56-mysqli php56-mbstring php56-zlib php56-zip php56-bz2 php56-hash

Once the above packages are installed, copy the production PHP configuration to the default location of php.ini. Run the following command for same.

cp /usr/local/etc/php.ini-production /usr/local/etc/php.ini

You can check the version of PHP by running:

php -v

You should get following output:

# php -v PHP
5.6.31 (cli) (built: Jul 13 2017 01:25:09)
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies

Enable PHP to work with Apache by editing the Apache configuration file httpd.conf. Run the following command to edit Apache configuration.

nano /usr/local/etc/apache24/httpd.conf

Find the following lines:

<IfModule dir_module>
     DirectoryIndex index.html
</IfModule>

Add index.php to the directory index. Once the configuration is updated, it should look like:

<IfModule dir_module>
     DirectoryIndex index.php index.html
</IfModule>

Also, add the following lines at the end of the file:

<FilesMatch "\.php$">
    SetHandler application/x-httpd-php
</FilesMatch>
<FilesMatch "\.phps$"> 
    SetHandler application/x-httpd-php-source
</FilesMatch>

Restart the Apache web server by running:

service apache24 restart

Install phpMyAdmin

At the time of writing this guide, phpMyAdmin was not available to install directly so we will download the software from the phpMyAdmin website. Before that we will need to install some additional software.

pkg install wget zip

Once installed you will need to change the directory to /usr/local/www

cd /usr/local/www

Next use the following command to download the phpMyAdmin software.

wget https://files.phpmyadmin.net/phpMyAdmin/4.8.1/phpMyAdmin-4.8.1-all-languages.zip

Please be aware that the version number will have changed since writing this guide so you will have to go to the phpmyAdmin website to get the most recent version. For the purposes of this tutorial we will refer to phpMyAdmin-4.8.1-all-language.

Now we have to unzip the folder.

unzip phpMyAdmin-4.8.1-all-languages.zip

This will now unzip the folder into the www directory in a folder aptly named phpMyAdmin-4.8.1-all-languages. Now we need to change the folders name with.

mv -r phpMyAdmin-4.8.1-all-languages.zip phpMyAdmin

The reason for doing this is because the folder will be part of the URL. Renaming the folder allows for an easier URL the remember. Now change directory into the phpMyAdmin folder with.

cd phpMyAdmin

and copy the sample phpMyAdmin configuration to its default location by running:

cp /usr/local/www/phpMyAdmin/config.sample.inc.php /usr/local/www/phpMyAdmin/config.inc.php

Edit the Apache configuration file using the following command.

nano /usr/local/etc/apache24/httpd.conf

Add the following lines at the end of the file.

Alias /phpmyadmin/ "/usr/local/www/phpMyAdmin/" 

<Directory "/usr/local/www/phpMyAdmin/">
    Options None
    AllowOverride Limit
    Require all granted
</Directory>

Restart the Apache web server by running:

service apache24 restart

Accessing phpMyAdmin

You can now access the phpMyAdmin interface at the following web address.

http://Your_Server_IP/phpmyadmin/

And you should see the login screen

Once logged in you will then see the dashboard.

You have now successfully set up MySQL, Apache Server configured with PHP and you have installed and configured phpMyAdmin as a web based database administration GUI.