I'm just learning how to handle the magento plattform, this is all happening in a test environment and my hoal is to learn about IT and see if I can host some small e-commerce websites on my own (if I start to process payment I will have infosec people have a look at the server and for sure I wouldn't go for dollars per year machine, like these one who just got me started). I'm having privileges trouble accessing the remote db and I don't know what's wrong since it seems I have them (please see at the end of the post)
All of the machines are VPS (OpenVz/SolusVM) behind a NAT with 20ports each being forwarded from the public IPv4 and 1 IPv6 each.
I was planning to test a setup where I use one webnode for magento, 1 node for mysql and 1 node for backup.
All the machines are reachable from the public NAT IPv4 address on their forwarded port and they can ping each other behind the NAT on their local addresses (10.10.10.xxx).
Webnode
Ubuntu 14.04 minimal server install, I added just a couple packages on top of it and all magento system prerequisites. it has 1GB RAM and no swap, slow but sufficient for testing purpose only.
$ uname -a
Linux 2.6.32-042stab120.16 #1 SMP Tue Dec 13 20:58:28 MSK 2016 x86_64 x86_64 x86_64 GNU/Linux
$ apache2 -v
Server version: Apache/2.4.7 (Ubuntu)
$ php -v
PHP 7.0.20-2~ubuntu14.04.1+deb.sury.org+1 (cli) (built: Jun 14 2017 05:55:23) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
with Zend OPcache v7.0.20-2~ubuntu14.04.1+deb.sury.org+1, Copyright (c) 1999-2017, by Zend Technologiesin regards to php I also set memory_max to 1GB and wait-execution to an enormous value.
This is the mysql node
CentOS 6.5, it has very limited RAM (128MB) but I could get mysql-server-5.6 to install and start, it's cent0S minimal install with really only mysql-5.6 on it, I had to change heavily the my.cnf file to allow for the lowmem I'm posting it in case some of these options are the ones causing the problems.
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address = 0.0.0.0
port = hidden for public post
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# LOW MEMORY CONF
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# deprecated skip-innodb
default-storage-engine=myisam
default-tmp-storage-engine=myisam
innodb=OFF
ignore-builtin-innodb
max-connections = 25
query-cache-size = 2M
query-cache-limit = 1M
thread-stack = 256K
thread-cache-size = 2M
slow-query-log
long_query_time = 5
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
So I created the database instance on the mysql node and told it to listen on a certain port, it all seems to work because if I do this on the webnode it works:
$ mysql -u 'current username' -h 10.10.10.xxx (IP address of the mysql node on the "internal network", which I suppose it's the virtual network run by the hostOS) --port=xxxxx -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.6.36-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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.
mysql> SHOW GRANTS FOR username;
+-----------------------------------------------------------------------+
| Grants for username@% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD <secret> |
| GRANT ALL PRIVILEGES ON `dbname`.* TO 'username'@'%' |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
could this be the reason? this is on the mysql node
# free -m
total used free shared buffers cached
Mem: 128 115 12 7 0 35
-/+ buffers/cache: 79 48
Swap: 0 0 0
So as you can see it seems that my webnode can connect to my mysql node, (username and database name, ip and ports were all changed to post this publicly), but when I start the web setup wizard and I connect to the database I get this:
Database user does not have enough privileges. Please make sure SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER privileges are granted to table 'dbname'.
what can I do now?
(besides getting more ram and decent VPS, that's an option for the future, for now I'd love to get this working)
