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 is straight forward; your QNAP NAS already comes with MySQL pre-installed.
Note: MySQL 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
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.
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/
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
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 …
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
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!
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
After login:
QNAP -PHPMyAdmin after login
If this all worked out just fine; then we’re good to go … 
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 24 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.
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?
Raj
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.
hans
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.
Raj
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?
hans
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.
Raj
You;re welcome Raj … wish I could be more helpful
hans
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?
yodann
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 …
hans
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.
yodann
Thanks Yodann!
I can imagine that you’re not the only one running into this issue! Keeping my fingers crossed that it works!
hans
how to download qnap
Arya
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.
hans
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
Nosek
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:
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
hans
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..
Nosek
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:
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
hans
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
Jose Mario Gomes
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.
Hans
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,
Jose Mario Gomes
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:
Hans
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,
Jose Mario Gomes
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.
Hans
G’day,
I am sorry, I did not write down your name, but I shall look it up later.
Thank you for your well-written article. I have succeeded in enabling MySQL on my QNAP TS-210.
The version of MySQL is 5.1.7.3.
I shall build a database using phpMyAdmin and then attempt to connect MS Access 365 as a front end.
I intend to REPLICATE to a friend’s laptop, with MySQL running. Very little data is updated, but I need to be able to make small changes occasionally.
Thank you for the help! Enjoy your coffee, Terry
Terry
Hi Terry,
Thanks again for the coffee! And nice to hear the article was useful for you.
If you’re ever interested in a newer version of MySQL (or want to use the in-place-replacement named “MariaDB”), the I can highly recommend taking a look at QNAP Club.
TIP:
When using with Office products: consider getting the ODBC MySQL driver.
Also make sure TCP port 3306 can be accessed on your QNAP (from within your LAN).
Hans