ReadyNAS Pro 4: Setting up MySQL properly.

Hello,

I've been running into some troubles getting MySQL to run properly on my ReadyNAS Pro 4. But before I start explaining, I'd like to mention that I did search this and other forums for possible solutions. Most of them didn't work for me, whilst some others got me a step further, I will try to include other threads that helped me a bit, but ultimately, I still have problems running a MySQL server, and hopefully someone experienced in the subject is able to help me.

General information

Model: ReadyNAS Pro 4 (RNDP4000)

Firmware: 4.2.28

Installed AddOns: EnableRootSSH 1.0 PHP5-5.6.16 PHPMyAdmin-4.2.2-x86-1.6.0

Yesterday I decided to do a factory reset on my NAS, starting from scratch, my goal is to have a secure, functioning web server. After the reset, I only installed the addons mentioned above. Fair enough, I'm able to ssh into the machine. 

login as: root
root@10.0.0.10's password:
Last login: Tue Apr 26 12:54:13 2016 from demis-pc.fritz.box
Last login: Tue Apr 26 13:13:03 2016 from demis-pc.fritz.box on pts/0
Linux Smurfbay 2.6.37.6.RNx86_64.2.4 #1 SMP Thu May 28 16:29:46 PDT 2015 x86_64 GNU/Linux
Smurfbay:~#

Now, as far as I understood, the mysql server is installed by default, but it's not running unless I install the Photos Addon or I start it manually - is that correct? However, if I do a ps ax | grep mysql, this is the output

Smurfbay:~# ps ax | grep mysql
 4720 pts/0    S+     0:00 grep mysql

Well to my knowledge, that means a mysql server is running, right? so when I try to get into it, this happens

Smurfbay:~# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run                                       /mysqld/mysqld.sock' (2)
Smurfbay:~# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run                                       /mysqld/mysqld.sock' (2)
Smurfbay:~# mysql -u root --host 127.0.0.1
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)
Smurfbay:~# mysql -u root --host 127.0.0.1 --port 3306
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111)

As you can see, I just tried to tinker around with it, however, I can't get into it. My next step was to stop and start the mysql service again

Smurfbay:~# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.
Smurfbay:~# ps ax | grep mysql
 5303 pts/0    S+     0:00 grep mysql
Smurfbay:~# /etc/init.d/mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . .logger: invalid option -- i
BusyBox v1.18.4 (2011-03-28 19:08:01 PDT) multi-call binary.

Usage: logger [OPTIONS] [MESSAGE]

Write MESSAGE (or stdin) to syslog

Options:
        -s      Log to stderr as well as the system log
        -t TAG  Log using the specified tag (defaults to user name)
        -p PRIO Priority (numeric or facility.level pair)

 failed!

So first thing I noticed is, mysql is still running after I stopped the daemon, but well, I'm a newbie, so you tell me what that means.

I googled this issue quite abit, but most threads stop there. I eventually found this thread where super_poussin suggest this command to start the mysql server

start-stop-daemon -S -q -b -x /usr/sbin/mysqld -- \
          --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/run/mysqld/mysqld.sock

If I do that, I get no output, another ps ax | grep mysql reveals

Smurfbay:~# ps ax | grep mysql
 2846 ?        Sl     0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/run/mysqld/mysqld.sock
 2861 pts/0    S+     0:00 grep mysql

that the mysql daemon is running now. Am I correct? fair enough, if I type in mysql now

Smurfbay:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.32-Debian_7etch5 Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

it works. So.. my troubles don't end here, but lets just stop for a second, if this is the right command to start the mysql server, how do I set it up automatically after a reboot, because when I reboot, I have to type it in again.

Moving on I'd like to either use PHPMyAdmin from the browser or MySQL workbench from my PC to access MySQL, both don't work. It is likely I'm doing something silly wrong, but if I use the same credentials I use for sshing into the machine, it won't work, output from PHPMyAdmin:

#1045 Cannot log in to the MySQL server

As far as my knowledge goes, this could be due to the fact that the mysql server can only be accessed from the localhost, here is my my.cnf

Smurfbay:~# cat /etc/mysql/my.cnf
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = admin
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/english
skip-external-locking
skip-name-resolve
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#skip-networking

innodb_buffer_pool_size = 6M
key_buffer_size = 16k
myisam_sort_buffer_size = 16k
max_connection = 20
#
# * Fine Tuning
#
key_buffer              = 16k
max_allowed_packet      = 16M
thread_stack            = 128K
#
# * Query Cache Configuration
#
query_cache_limit       = 1048576
query_cache_size        = 32768
query_cache_type        = 1
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log            = /var/log/mysql.log
#log            = /var/log/mysql/mysql.log
#
# The following can be used as easy to replay backup logs or for replication.
#server-id              = 1
#log-bin                        = /var/log/mysql/mysql-bin.log
# See /etc/mysql/debian-log-rotate.conf for the number of files kept.
max_binlog_size         = 16384
#binlog-do-db           = include_database_name
#binlog-ignore-db       = include_database_name
skip-bdb
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

If that's the case, I want to access it from localhost as well as my remote PC. But again, I don't know if that is even the issue here.

So there you have it, that's how far I've come. In the end, I'd like to have a secure web server to run some of my laravel applications. I have never set up a secure web server before, but assuming I'm able to get MySQL to work eventually, would anyone of you be able to provide some information as to how a secure setup would look like? By secure I basically mean a web server setup that is as secure as possible from the outside (Maybe setting up a dedicated user to only have the rights to administrate the web components instead of root, groups, ect ect..). However, I'll try to ask Google to answer that one, for now, I'd just like you to help me set up the mysql server properly, and maybe some info on how to operate it properly as well.

Thanks in advance,

Demis.