Page 1 of 1

QNAP – Installing MySQL and phpMyAdmin

QNAP – Installing MySQL and phpMyAdmin
   16

As you might have noticed; I really like my QNAP NAS – it has proven to be flexible and reliable.

One of the nice things is that quite a bit of functionality comes pre-installed.
For example MySQL is default installed on your QNAP (not enabled).

I’ll show you how to enable MySQL and how to install phpMyAdmin.




Enabling MySQL

Enabling MySQL is straight forward; your QNAP NAS already comes with MySQL pre-installed.
NoteMySQL is available for other platforms as well: Windows, Linux, MacOS X.

Let’s get started … Open the Administration page of your QNAP and go to “Application Servers“. In that section select “MySQL Server“.

QNAP - Enable MySQL

QNAP – Enable MySQL

Now check “Enable MySQL Server” and check “Enable TCP/IP Networking” – make sure the “Port Number” is set to “3306” (you can change this later if you’d like, but for now we will use the default MySQL port).

Click the “Apply” button and wait for the MySQL server to activate. In essence: we’re done.

 QNAP Default Username/Password for MySQL 

The default username for MySQL on your QNAP is “root” and the default password is “admin“.In case you changed the password of root and forgot what it was;
In the “Application Servers” → “MySQL Server” page a “Reset root password” will allow you to reset it to it’s defaults.

– SSH / Command line access

You can access MySQL from the command line (using SSH) as well. It can be found at:


/mnt/ext/opt/mysql/bin/

Web Server (Apache)

To make access to your MySQL database easier we’d also like to install phpMyAdmin so we can actually play with MySQL in a more convenient way, however phpMyAdmin runs on a web-server and needs PHP … so we need to do something about that first.

Both Apache and PHP are already installed on your QNAP under the package “Web Server” – again, we just need to enable it.

Go to “Network Services” and select “Web Server“.

Check “Enable Web Server“, make sure “Port Number” is set to “80“, remember or bookmark the link for access to the web-server (optional – during the phpMyAdmin installation, a link well be shown again) and click “Apply“.

QNAP - Enable the webserver for PHPMyAdmin

QNAP – Enable the webserver for PHPMyAdmin

So, now the web server on your QNAP is running as well – which automatically includes PHP.
You can test this by going to the link indicated when you enabled the web server (see image above).
Typically this link looks like this: http://<ip of your NAS>

You should see something like this:

QNAP - Your webserver is running and welcomes you ...

QNAP – Your webserver is running and welcomes you …

 QNAP Administration Page Access 

If you always used http://<ip of your NAS> to get to the admin pages of your QNAP, then you will notice that this no longer works.
Instead use: https://<ip of your NAS> or http://<ip of your NAS>:8080

phpMyAdmin QPKG

Finally we can now install the phpMyAdmin QPKG (download through the QPKG page of your QNAP or directly here)- you can do this “Application Server” → “QPKG Plugins” section. See How to Install a QNAP QPKG for more details.

When the installation is completed, take note of (or bookmark) the link to access phpMyAdmin:
http://<ip of your NAS>/phpMyAdmin

QNAP - PHPMyAdmin Installed! Take note of that link!

QNAP – PHPMyAdmin Installed! Take note of that link!

OK, we’re almost there … let’s try that link and see if we can access phpMyAdmin.
First you should see a login page – remember the default username (root) and password (admin) and click “Go” to login:

QNAP - PHPMyAdmin login

QNAP – PHPMyAdmin login

After login:

QNAP -PHPMyAdmin after login

QNAP -PHPMyAdmin after login

If this all worked out just fine; then we’re good to go … 

Donation options


Donations are very much appreciated, but not required. Donations will be used for web-hosting expenses, project hardware or a motivational boost (a drink or snack). Thank you very much for those have donated already! It's truly AwEsOmE to see that folks like our articles and small applications.

Comments


There are 16 comments. You can read them below.
You can post your own comments by using the form below, or reply to existing comments by using the "Reply" button.

  • Sep 16, 2013 - 8:41 AM - Raj Comment Link

    I’ve had a QNAP NAS for a few years now and just bought a second one. I really like them over their competitors however I’ve hit my first real snag. The version of MySQL installed is 5.1 and the current being 5.6. I’m trying to develop an application but I require the latest version of MySQL but I can’t see anyway to update it. Any ideas?

    Reply

    Raj

    • Sep 16, 2013 - 9:46 AM - hans Comment Link

      Hi Raj,

      First off: I would avoid do a manual update if MySQL, it could come with a boat load of issues.

      If your QNAP is Intel based, you might be able to use a linux version, but if your QNAP is ARM based, you will probably have to compile MySQL.

      Option 1: Try if apt-get update works … 
      Option 2: Recompile like this guy did …
      Option 3: Place a support ticket at QNAP requesting assistance …

      Personally, I would not do a manual upgrade (option 1 & 2). It seems to be an ongoing request though (QNAP forum).
      I’ve done quite a bit of development of apps that use MySQL and haven’t run into limitations yet … so I’m not sure what functionality you’re missing.

      Reply

      hans

      • Sep 16, 2013 - 9:55 AM - Raj Comment Link

        Hi,

        Thanks for the reply. My problem comes when trying to the MySQL database with the latest version of Umbraco CMS. The setup db scripts it runs on creating a new a site fails at 90% due to some historical limitation on the number of “TIMESTAMP” columns you can have in a table. I’ve read that the latest version of MySQL lifts this limitation.

        Mine is an Intel based one but I’m not familiar enough with the internal working of the NAS (or Linux in general) so I wouldn’t even attempt to meddle with it.

        I too have just read the requests in the QNAP forums. It’s just a shame I can’t utilise the built in DB in the meantime.

        Reply

        Raj

      • Sep 16, 2013 - 10:07 AM - hans Comment Link

        Hi Raj,

        You’re welcome, and I wish I had a more helpful answer for you.

        What you could try is making a separate directory on your QNAP and dumping the files of a Linux Intel distribution in it.
        You’d have to modify the default port (3306), to not conflict with the original QNAP MySQL, in the config file.
        Then start mysql from there and see if it works ….
        This is a very poor way of doing it and the success rate is probably not very high.

        Since the rather high version requirement, I’d assume that more people run into this problem … or is MySQL not the “default” database they use?

        Reply

        hans

        • Sep 16, 2013 - 10:13 AM - Raj Comment Link

          I might just give that a try!

          MySQL isn’t their (Umbraco) primary data source but it is the best free option. A few other developers (and I think many more to come) have run into this issue but they mostly likely aren’t using the MySQL installation in a QNAP product as I am trying to so updating isn’t an issue as it is for me.

          Thanks for your help though.

          Reply

          Raj

          • Sep 16, 2013 - 10:38 AM - hans Comment Link

            You;re welcome Raj … wish I could be more helpful 

            hans

  • Dec 6, 2014 - 11:07 AM - yodann Comment Link

    Hi Hans,

    unlike you, i have a problem with mysql on qnap. it worked few days until suddenly the mysql stopped working, and when i checked the directory, there’s no more mysql/bin. And i got the message of upgrading the firmware to the latest version. I checked the latest version of firmware, and my firmware is actually up to date.

    Do you think you know the problem?

    Reply

    yodann

    • Dec 7, 2014 - 3:18 AM - hans - Author: Comment Link

      Hi Yodann,

      I have not heard of this problem before, but missing a mysql/bin then I’d say that is critical.
      I’m not near my QNAP (traveling) but you could try uninstall and reinstall of MySQL through the QNAP qpkg’s – I’m not sure if that’s an option since MySQL seems a standard part of the QNAP software (see: Enabling MySQL).

      Not sure if the upgrade message is related.
      I’m always very nervous when upgrading my QNAP, I’ve heard many horror stories but all upgrades I have done in the past years went flawless.

      Maybe other readers have seen this issue.

      Things I found: StackOverflow, QNAP Forum

      The QNAP Forum link, even though a little old, covers missing binaries … hope this helps …

      Reply

      hans

  • Dec 8, 2014 - 4:15 AM - yodann Comment Link

    Hi Hans,

    Yes, I guess you right. I shouldn’t upgrade hastily. I’m downgrading to ver 3.8.3 now and hopefully this issue doesn’t occur anymore. I let u know as well. Thanks.

    Reply

    yodann

    • Dec 8, 2014 - 4:28 AM - hans - Author: Comment Link

      Thanks Yodann! 

      I can imagine that you’re not the only one running into this issue! Keeping my fingers crossed that it works! 

      Reply

      hans

  • Feb 6, 2018 - 1:03 PM - Arya Comment Link

    how to download qnap

    Reply

    Arya

    • Feb 16, 2018 - 10:58 AM - hans - Author: Comment Link

      You don’t … QNAP is a company that builds NAS devices. 
      If you mean downloading the OS a QNAP uses; I’m not sure how useful that would be without the QNAP hardware.
      As I recall, the QNAP OS will not run without its original hardware.

      Reply

      hans

  • Mar 5, 2018 - 2:27 AM - Nosek Comment Link

    Hello Hans,

    I have a qnap TS-453A and enabled MySQL and WebServer. I have also installed PhpMyAdmin and created a database.

    While I can successfully create functioning web pages hosted on the qnap,  I cannot connect to the mysql database. Whether I use ‘localhost’, ‘127.0.0.1’ or ‘NAS_name’ I receive a cannot connect response.

    My question here is: Where are my databases being saved when created with PhpMyAdmin on the qnap?

    I have searched for them but cannot find them and believe the connection problem is from the host database location.

    Thanks for any info

    Reply

    Nosek

    • Mar 7, 2018 - 3:15 AM - hans - Author: Comment Link

      Hi Nosek,

      Question 1; Can’t access the database

      – Is this in PHPMyAdmin (running on the QNAP)?

      Default PHPMyAdmin (if installed through the QNAP web interface) should work straight out of the box.
      If you did a manual install, you’ll have to tinker with the config file (which I would not recommend).

      – Or is this from another device (not the QNAP) for example through KODI?

      In this case 127.0.0.1 and localhost will not work.

      If you try the IP address of the QNAP or the network name;
      MySQL does set the allowed connection with each user. For example %.%.%.% means from any computer, or 192.168.1.% from any computer in the 192.168.1.xxx range. Sometimes we need to add this to a user account to allow access.

      Question2; Where is the database saved

      I do not have my QNAP handy right now (in the middle of a move from the US to Europe), but I did find a post in the QNAP forum mentioning:

      /share/MDO0_DATA/.@mysql/

      The “MDO0_DATA” may be different depending on your QNAP hardware and QTS version.

      Hope this is helpful, feel free to ask if you have more questions or in case this was not answering your question 

      Reply

      hans

      • Mar 7, 2018 - 8:06 AM - Nosek Comment Link

        PhpMyAdmin is installed and running on the Qnap with everything working fine there. I can create a database without any problems. However I have no idea where PhpMyAdmin saves the database on the Qnap. There is nothing I can find in the /web/phpmyadmin folder that refers to my created database.

        Essentially I want to create the initial database through PhpMyAdmin and then allow intranet users through a web interface to modify, visualize and print. Thus using example: //192.168.1.100/database/index.php to connect and access content.  My php calls for:

        <?php

        $dbname = ‘MyName’;
        $dbuser = ‘root’;
        $dbpass = ‘myPass’;
        $dbhost = ‘192.168.1.100’;
        $link = mysqli_connect($dbhost, $dbuser, $dbpass) or die(“Unable to Connect to ‘$dbhost'”);
        mysqli_select_db($link, $dbname) or die(“Could not open the db ‘$dbname'”);
        $test_query = “SHOW TABLES FROM $dbname”;
        $result = mysqli_query($link, $test_query);
        $tblCnt = 0;
        while($tbl = mysqli_fetch_array($result)) {
          $tblCnt++;
          #echo $tbl[0].”<br />\n”;
        }
        if (!$tblCnt) {
          echo “There are no tables<br />\n”;
        } else {
          echo “There are $tblCnt tables<br />\n”;
        }

        Always returning ‘Unable to Connect to …..’ No matter what I use. From my ignorance this may be a php question but I am guessing that the database created in PhpMyAdmin is not being properly located or directed to. And upon looking for the database ‘file’ created I can’t seem to find it.

        Thanks again for your advice..

        Reply

        Nosek

        • Mar 11, 2018 - 3:08 AM - hans - Author: Comment Link

          Hi Nosek!

          First off, the actual “database” is stored in a file on your QNAP, and you’ll have to either find the my.ini file to determine the path or look in the shares (start with /share/MDO0_DATA/.@mysql/). However, you would not necessarily need the location of the database file for what you’re trying to do.

          Assuming the PHP script is stored on the QNAP, th following should theoretically work:

          <?php
          $dbname = ‘MyName’;
          $dbuser = ‘root’;
          $dbpass = ‘myPass’;
          $dbhost = ‘127.0.0.1’;
          $link = mysqli_connect($dbhost, $dbuser, $dbpass) or die(“Unable to Connect to ‘$dbhost'”);
          mysqli_select_db($link, $dbname) or die(“Could not open the db ‘$dbname'”);
          $test_query = “SHOW TABLES FROM $dbname”;
          $result = mysqli_query($link, $test_query);
          $tblCnt = 0;
          while($tbl = mysqli_fetch_array($result)) {
            $tblCnt++;
            #echo $tbl[0].”<br />\n”;
          }
          if (!$tblCnt) {
            echo “There are no tables<br />\n”;
          } else {
            echo “There are $tblCnt tables<br />\n”;
          }

          The user “root” with password “myPass” should have access to all the databases – in PHPMyAdmin you’ll find an option “Privileges” where you can create (another) user called “root” with password “myPass” that has access to everything (access from 127.0.0.1, for all tables that are needed, if unsure start with setting access to everything for testing).

          Unfortunately, I’m unable to walk through this right now, since my QNAP is on a boat on it’s way to Europe 

          Reply

          hans



Your Comment …

Friendly request to not post large files here (like source codes, log files or config files). Please use the Forum for that purpose.

Please share:
*
*
Notify me about new comments (email).
       You can also use your RSS reader to track comments.


Tweaking4All uses the free Gravatar service for Avatar display.
Tweaking4All will never share your email address with others.