Remote MySQL issues

Post Reply
Messiah
Posts: 7
Joined: Fri Nov 12, 2021 8:28 pm

Good afternoon!
I'm not really sure should I write it in English because I speak Russian, but...
Many years ago I've used VesataCP with Remore MySQL feature. As I remember, it worked. Then I've used only local DBs for years.

Now I have
  1. Amazon EC2 instance
  2. Amazon RDS2 instance
(both free tier eligible)
I've installed myVesta with mysql and added v-add-database-host. Databases are created or deleted normally on both mysql servers.

But:
  1. I want [my]Vesta to actualize config-db.php in phpmyadmin, since default config only allows me to connect to localhost server;
  2. I can't connect to remote MySQL with user created from myVesta (Access denied);
  3. Amazon RDS' mysql - user table has only system users and admin user created with RDS, no my admin_*** user created with the database;
  4. When I call v-change-database-user either with password or not 2 records are added to remote user table: % and localhost with user name and EMPTY pass and auth string columns (btw Hestia behave exactly the same way);
  5. Adding users to remote MySQL manually is not the solution, if I wand to do it this way I don't need a panel ;)
  6. Default phpmyadmin and webmail URIs are located default amazon (internal) hostname and must be changed manually
Shoud I provide an access to this machine to developers for tests?

Also, IMO php-mysql, phpmyadmin etc MUST be installed regardless --mysql yes or no, or the separate parameter for these (and all related) packages should be implemented. For any multi-server configuration it would be useful to run one server with VestaCP as a frontend (with mysql client, phpmyadmin and all related) and MySQL server itself on different machine. Now phpmyadmin etc are not installed if --mysql no

P.S. I like that now v-backup-user does not fail if the localhost mysql server is down or disabled. It was the former issue that forced either to change scripts or to leave mysql server consuming RAM when it's not required at the machine.

P.S.2 is it possible to implement custom phpmyadmin and webmail path from the box as it was done with secret url in the panel? Bruteforce attacks are still performed by domain names, even if phpmyadmin is not available for http[s]://ip/ access
User avatar
myVesta
Site Admin
Posts: 928
Joined: Fri Jun 19, 2020 9:59 am
Has thanked: 8 times
Been thanked: 6 times

Check Firewall section.
On myVesta, port 3306 is not enabled by default.

Regarding API - check viewtopic.php?f=9&t=39

About other questions - sorry - I can't reply now, I'm too busy.
Messiah
Posts: 7
Joined: Fri Nov 12, 2021 8:28 pm

Honestly I think you replied some other post here.
The issues I mentioned are related to Amazon RDS mysql database or any remote mysql server configuration. The port is open. Vesta was able to create database at the remote host but failed to create user account. If you are busy and not able to check it, ok, I will try to log some debug information if I will be able to. I'm good in PHP and other web-related but not good in bash scripting so it will take me some time.
User avatar
myVesta
Site Admin
Posts: 928
Joined: Fri Jun 19, 2020 9:59 am
Has thanked: 8 times
Been thanked: 6 times

Sorry, I didn't read carefully.
Vesta create mysql username and database via Vesta API - not via SQL commands.
Since Amazon RSD has no support for Vesta API - nothing is created.
Messiah
Posts: 7
Joined: Fri Nov 12, 2021 8:28 pm

Теперь я не понял.
Amazon RDS это по сути обычный mysql сервер, имеющий ip адрес, имя root пользователя (по умолчанию - admin) и рут пароль, установленный при создании RDS инстанса. Чем он принципиально отличается от удаленного VPS сервера, на котором будет поднят mysql only - не понимаю. Добавил его через v-add-database-host. Базу панель создавать умеет, а пользователя - нет. Траблы именно с паролем, т.к. колонка пароля со второй попытки (когда пользователь все таки будет создан) оказывается пустой.
User avatar
myVesta
Site Admin
Posts: 928
Joined: Fri Jun 19, 2020 9:59 am
Has thanked: 8 times
Been thanked: 6 times

Looks like I'm wrong.
It create database via SQL command.
But looks like it has a bug with SQL statement.

What version of MySQL is on Amazon RDS ?

P.S. Please write in english, since I'm from Serbia and I don't know Russian language :)
Messiah
Posts: 7
Joined: Fri Nov 12, 2021 8:28 pm

I'm sorry, i thought you are russian.

The version is 10.6.7-MariaDB-log
I've added some debug to add_mysql_database() of db.sh and found following:

The command

Code: Select all

v-add-database admin admin_dbtest admin_dbutest qwerty mysql 172.xx.xx.xx
(ok, now I know I should not add admin_ prefix, but it does not matter)
The script makes a query

Code: Select all

GRANT ALL ON `admin_admin_dbtest`.* TO `admin_admin_dbutest`@localhost IDENTIFIED BY 'qwerty'
that fails with an error

Code: Select all

ERROR 1044 (42000): Access denied for user 'administrator'@'%' to database 'admin_admin_dbtest'
I've expected RDS administrator user 'administrator' has full privileges at the database. The database admin_admin_dbtest created successfully.
Any ideas how to fix it?
Post Reply