Page 1 of 1

QNAP – Installing MySQL and phpMyAdmin

QNAP – Installing MySQL and phpMyAdmin
   22

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 … 

Support Us ...


Your support is very much appreciated, and can be as easy as sharing a link to my website with others, or on social media.

Support can also be done by sponsoring me, and even that can be free (e.g. shop at Amazon).
Any funds received from your support will be used for web-hosting expenses, project hardware and software, coffee, etc.

Thank you very much for those that have shown support already!
It's truly amazing to see that folks like my articles and small applications.

Please note that clicking affiliate links, like the ones from Amazon, may result in a small commission for us - which we highly appreciate as well.

Comments


There are 22 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

  • Feb 10, 2021 - 2:26 PM - Jose Mario Gomes Comment Link

    Hi

    I have a QNAP TS-253D and I’ve tried to use it as an SQL server.

    I’ve followed the steps shown in your post, and I’ve managed to open phpMyAdmin and manage the SQL server and databases.

    The problem is accessing the SQL server. I’m using Power BI to connect to the SQL server on QNAP. For now, I’m trying a local access to the SQL server, but I can’t get it to work.

    The error I get is the following:

    “Microsoft SQL: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 – An existing connection was forcibly closed by the remote host.)”

    I’ve looked for the error online but I can’t get an answer. Do you have any suggestion?

    I also would like to know if there is the need to create a config.ini file in the “Web” folder on QNAP.

    Thank you in advance for your attention, kind regards,

    Jose Mario Gomes

    Reply

    Jose Mario Gomes

    • Feb 11, 2021 - 6:30 AM - Hans - Author: Comment Link

      Hello Jose Mario Gomes 😊 

      Since it says “pre-login”, I assume this can be caused by a few things;

      1) Make sure that you add the right port number (3306 is the default port number, eg. 192.168.1.100:3306).

      2) Make sure there are no firewall (on your PC) rules blocking port 3306. Default this should not be blocked, unless you specifically did set this.

      3) You’ll also have to make sur that the user you’re using to login, has access rights in the database which allows for a user to connect from another computer.

      When looking in phpMyAdmin, under “User account“, you can see a column “Host name“, make sure this is set to “%” (meaning: originating from anywhere in the network).
      This may be a little too open for security reasons, but can be refined to a specific IP address or IP address range.
      For testing, start with setting “Host name” to “%” for that user (if this is at home, then this is probably not a security concern).

      Config file;
      As far as I can see on my system, there is only “config.inc.php” in the phpMyAdmin directory on my QNAP.
      I never had to tinker with it, as the setup already did it correctly.

      Reply

      Hans

      • Feb 11, 2021 - 6:47 AM - Jose Mario Gomes Comment Link

        Hi Hans,

        Thank you for your promp response.

        I will try to implement your sugestions. The problem is that I little knowlege of the configuration of web servers, and ports, and stuff like that. I’m trying to configure this through try and error, since all the tutorials I find are very advanced.

        Kind regards,

        Reply

        Jose Mario Gomes

      • Feb 11, 2021 - 8:59 AM - Hans - Author: Comment Link

        Hi Jose,

        you’re most welcome. This is one of those things one usually “set and forget” 

        It’s been a while since I last used Power BI, but as far as I recall, it connects through ODBC (see this guide – you can download the ODBC driver here) or directly (maybe this guide is helpful – see the “Steps to Connect MySQL to Power BI” section). ODBC is probably the most convenient, as you can use it for other tools as well.

        When using ODBC, you will first need to install the ODBC driver, and follow these steps.
        Import aspects:

        1. Datasource name – a name you like to use yourself to identify this database connection
        2. Check the TCP/IP server option
        3. Enter the IP address of your QNAP as the TCP/IP Server
        4. Set port to 3306
        5. Enter username and password
        6. Select the database from the dropdown list.
        You should now be able to click the “Test” button.

        Reply

        Hans

        • Feb 11, 2021 - 10:18 AM - Jose Mario Gomes Comment Link

          Thank you. Your answers were extremely helpful.

          The problem was that I was using the SQL connector in Power BI, not the mySQL connector.

          Now I’ve managed to connect to my database.

          Also, using the ODBC administrator in Windows after installing the mySQL driver, I was able to connect to the database.

          Now, what I am trying to do is accessing the server remotely. I’ve tried to enter [my domain on qnap].myqnapcloud.com and set the port to 3306, but I’m not being able to access the database.

          But this will probably be for another try.

          Kind regards,

          Reply

          Jose Mario Gomes

        • Feb 11, 2021 - 12:49 PM - Hans - Author: Comment Link

          Good to hear! Glad you’ve got it to work. 

          As for access from the Internet … ehm … I do not use myqnapcloud.com, and I do not know it they support traffic through port 3306 or how they even redirect your data to your home. So for that you may have to contact QNAP support.

          Having said that, if you’d connect straight to home (through the IP address of your Internet connection – a.k.a. your public IP address), you’d have to open port 3306 in your firewall. This is usually done in your modem or router, where you can set port forwarding.
          Now .. I would never open port 3306 like that, since it is a well known port and attackers/malicious people may easily find access to your database.

          Another option is using a VPN connection to connect to your home.

          With this I mean that you have a VPN server running at your home. You can run this also on your QNAP, it’s called QVPN in the QNAP App Center on your QNAP, so you should not need any special hardware. 

          However, I’ve never done this on a QNAP (my firewall handles it for me) – here is QNAP’s support article on it.
          Keep in mind that VPN setup can be confusing and challenging at times. This more to-the-point article may be helpful with as well.

          After setup, you will need to setup a VPN Client on the computer that wants to access the database remotely.

          Reply

          Hans



Your Comment …

Do 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.