Jump to content
TrinityCore
FiftyTifty

Granting global MySQL database access to user, + installing HeidiSQL?

Recommended Posts

Right, bear with me now. Still a complete novice to anything Linux.

According to the guide, I'm to run worldserver on my Ubuntu server to install the downloaded databases. When I try that, the trinity user that I was prompted to make in the tutorial does not have privileges to do so. I've got access to root, but the info on StackOverflow on doing this is either outdated or too terse. How exactly do I grant the user "trinity" access to all the TrinityCore databases through MySQL?

Another part I'm stuck on, is allowing remote access to the MySQL databases with HeidiSQL. I've got it installed on Windows, changed the /etc/mysql/mysql.conf.d/mysql.cnf file to have bind-address        = 0.0.0.0. And when I use HeidiSQL to connect, I get refused, even though I entered the password for the server machine's root account in, with an error popping up saying Host 'Not-Sharing-Because-Security-Reasons' is not allowed to connect to this MySQL server'. How do I go about solving this?

Thanks in advance. Sorry if these questions are rudimentary, this is my first foray into working with both Linux, and servers.

Share this post


Link to post
Share on other sites
59 minutes ago, firefird said:

 

I hate mysql, only problems and freezes from it, remove it.

I'm following the guide for now, and it says to get MySQL, so that's what I'm going with. The problem at the moment is connecting to it with HeidiSQL as per the guide, but it doesn't say how to actually do that. Just says to install it, without any detail.

Share this post


Link to post
Share on other sites

There is absolutely nothing wrong with mySQL.

Have you switched off Ubuntu firewall?

Often the user root is not planned to have remote access and has rights only on localhost. You can try to add another user and give them proper rights. Both (mysql server and user) must have the right to access from external (means setting the host ip to 0.0.0.0. or to your local network)

If you have a GUI on your Ubuntu you also can install MySQL Workbench server which is similar to HeidiSql to manage the SQL server.

Share this post


Link to post
Share on other sites
7 minutes ago, Magnuss said:

There is absolutely nothing wrong with mySQL.

Have you switched off Ubuntu firewall?

Often the user root is not planned to have remote access and has rights only on localhost. You can try to add another user and give them proper rights. Both (mysql server and user) must have the right to access from external (means setting the host ip to 0.0.0.0. or to your local network)

If you have a GUI on your Ubuntu you also can install MySQL Workbench server which is similar to HeidiSql to manage the SQL.

There is no Ubuntu firewall at the moment. Currently using Hetzner's, and it's port functionality works just fine, and am currently allowing all connections from any port and IP. I can connect with other programs, namely NoMachine, FileZilla, and PuTTY, so that's not the issue.

Currently talking with the developer of HeidiSQL, and he's been helpful: https://www.heidisql.com/forum.php?t=27266#p27270

So now I've allowed root to be accessed externally, but the connection to MySQL is refused with a slightly different error:  Access denied for user 'root'@'host##-###-##-###.range86-164.btcentralplus.com'

And I do have a GUI, installed LXDE from the terminal so I could use NoMachine. I'll take a look at MySQL Workbench.

Also, as per the guide, I created a new user called "trinity", but installed MySQL with root. Is there a way to grant trinity full access to MySQL?

Share this post


Link to post
Share on other sites
SELECT Host, User from mysql.user

 - It seems you don't have added a user trinity (following the posting in the other forum). Therefore you can't access to mysql with this username, even you have a local account on Ubuntu with the same name. And of course you can give every rights to an existing mysql user, but you shouldn't. You shout give only rights really needed.

- There are good reasons to NOT allow external access for mysql user root ;-)

- And as you was told it seem a problem with password.

 

Share this post


Link to post
Share on other sites
1 minute ago, Magnuss said:
SELECT Host, User from mysql.user

 - It seems you don't have added a user trinity (following the posting in the other forum). Therefore you can't access to mysql with this username, even you have a local account on Ubuntu with the same name. And of course you can give every rights to an existing mysql user, but you shouldn't. You shout give only rights really needed.

- There are good reasons to NOT allow external access for mysql user root ;-)

- And as you was told it seem a problem with password.

 

I have, I can log into the user on Ubuntu. Created the user as instructed here: https://trinitycore.atlassian.net/wiki/spaces/tc/pages/10977309/Linux+Core+Installation

It's not a problem with the password. Got a text file with them, and when I input the root user's password on root, when logging into MySQL, it works just fine. But when I use the "trinity" user, and try to enter MySQL through the terminal with root and use the root password, the password is rejected.

Share this post


Link to post
Share on other sites
2 minutes ago, Magnuss said:

What's the output of

SELECT Host, User from mysql.user

mysql> SELECT Host, User from mysql.user
    -> ;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql>

 

Share this post


Link to post
Share on other sites
Just now, Magnuss said:

As you see, there is no user trinity ;-)

Shouldn't that not matter? I use the following command to access MySQL:

mysql -u root -p

When I pass the password for the Ubuntu root account, that I'm also logged into, it works just fine. But when I'm in the Ubuntu user "trinity", and do the same, it's denied. I'm also trying to access MySQL through HeidiSQL via the root account, not the trinity account.

This is getting rather confusing.

 

So to clarify, everything was done through the trinity user; getting the source code, compiling it, extracting the client files, generating the various client-server information things. It only started getting awry when the instructions for installing MySQL are merely:

Installing MySQL Server

When configuring MySQL make sure you remember the password you set for the default root account and that you enabled both MyISAM and InnoDB engines.

 

 

Share this post


Link to post
Share on other sites

The mySQL server is an independent piece of software. It even could run on a complete different hardware. And it has it's own user management separated from your OS Ubuntu.

The idea is to add different new users to the mysql server with fine graduated rights. A mysql user can have access to everything, or just only to specific databases, or only to a table.  If you have a webserver, you add a user just only for the webser. If you have a mail server, you add a mailserver user. If you have a trinity server, you add a trinity user.

For security reasons root f.ex. never should have set host=%. Better add a new user like adminfity and give admin rights and set host to %. Additionally add a user trinity and give only rights to auth, character and world database.

Example for such a 'superuser':

CREATE USER 'adminfity'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL ON *.* TO 'adminfity'@'%' WITH GRANT OPTION;

Example for trinity user:

CREATE USER 'trinity'@'localhost' IDENTIFIED BY 'anotherpassword';
GRANT
ALL ON trinity_auth.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_characters.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_world.* TO 'trinity'@'localhost';

As you  see I give the trinity user only access on localhost. You would remote access with your adminfity user.

Be aware: Examples are out of my brain. And my brain is not allways correct ^^

 

Edit:

Reading the Trinity wiki very exactly helps better:

Creating the Trinity databases in MySQL

 

Share this post


Link to post
Share on other sites
19 minutes ago, Magnuss said:

The mySQL server is an independent piece of software. It even could run on a complete different hardware. And it has it's own user management separated from your OS Ubuntu.

The idea is to add different new users to the mysql server with fine graduated rights. A mysql user can have access to everything, or just only to specific databases, or only to a table.  If you have a webserver, you add a user just only for the webser. If you have a mail server, you add a mailserver user. If you have a trinity server, you add a trinity user.

For security reasons root f.ex. never should have set host=%. Better add a new user like adminfity and give admin rights and set host to %. Additionally add a user trinity and give only rights to auth, character and world database.

Example for such a 'superuser':

CREATE USER 'adminfity'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL ON *.* TO 'adminfity'@'%' WITH GRANT OPTION;

Example for trinity user:

CREATE USER 'trinity'@'localhost' IDENTIFIED BY 'anotherpassword';
GRANT
ALL ON trinity_auth.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_characters.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_world.* TO 'trinity'@'localhost';

As you  see I give the trinity user only access on localhost. You would remote access with your adminfity user.

Be aware: Examples are out of my brain. And my brain is not allways correct ^^

That's a great help. I can now connect to the database remotely with the new super user. Thanks man.

Only problem now, is that I can't connect to my server with NoMachine anymore. Utterly bizarre, so I'll go and wipe the server and start all over, and do everything properly this time.

Share this post


Link to post
Share on other sites
23 hours ago, Magnuss said:

The mySQL server is an independent piece of software. It even could run on a complete different hardware. And it has it's own user management separated from your OS Ubuntu.

The idea is to add different new users to the mysql server with fine graduated rights. A mysql user can have access to everything, or just only to specific databases, or only to a table.  If you have a webserver, you add a user just only for the webser. If you have a mail server, you add a mailserver user. If you have a trinity server, you add a trinity user.

For security reasons root f.ex. never should have set host=%. Better add a new user like adminfity and give admin rights and set host to %. Additionally add a user trinity and give only rights to auth, character and world database.

Example for such a 'superuser':

CREATE USER 'adminfity'@'%' IDENTIFIED BY 'somepassword';
GRANT ALL ON *.* TO 'adminfity'@'%' WITH GRANT OPTION;

Example for trinity user:

CREATE USER 'trinity'@'localhost' IDENTIFIED BY 'anotherpassword';
GRANT
ALL ON trinity_auth.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_characters.* TO 'trinity'@'localhost';
GRANT ALL ON trinity_world.* TO 'trinity'@'localhost';

As you  see I give the trinity user only access on localhost. You would remote access with your adminfity user.

Be aware: Examples are out of my brain. And my brain is not allways correct ^^

 

Edit:

Reading the Trinity wiki very exactly helps better:

Creating the Trinity databases in MySQL

 

Alrighty, got back to where I was before. Now I've run the create_mysql.sql file, but running worldserver with the trinity user throws the following error:

	[email protected]:~/server/bin$ sudo ./worldserver
TrinityCore rev. d7375ad33f9d 2019-06-09 21:44:37 +0200 (3.3.5 branch) (Unix, RelWithDebInfo, Static) (worldserver-daemon)
<Ctrl-C> to stop.
	 ______                       __
/\__  _\       __          __/\ \__
\/_/\ \/ _ __ /\_\    ___ /\_\ \, _\  __  __
   \ \ \/\`'__\/\ \ /' _ `\/\ \ \ \/ /\ \/\ \
    \ \ \ \ \/ \ \ \/\ \/\ \ \ \ \ \_\ \ \_\ \
     \ \_\ \_\  \ \_\ \_\ \_\ \_\ \__\\/`____ \
      \/_/\/_/   \/_/\/_/\/_/\/_/\/__/ `/___/> \
                                 C O R E  /\___/
http://TrinityCore.org \/__/
	Using configuration file /home/trinity/server/etc/worldserver.conf.
Using SSL version: OpenSSL 1.1.1b  26 Feb 2019 (library: OpenSSL 1.1.1b  26 Feb 2019)
Using Boost version: 1.67.0
Could not connect to MySQL database at 127.0.0.1: Access denied for user 'trinity'@'localhost' (using password: YES)
	DatabasePool Login NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors. Read wiki at http://www.trinitycore.info/display/tc/TrinityCore+Home
	

I've even reissued the GRANT commands to ensure that trinity is given access to the databases, but nada. I'm thinking that it's a problem with worldserver trying to connect to 127.0.0.1, instead of localhost. How do I change that?

Share this post


Link to post
Share on other sites
On 6/10/2019 at 6:59 PM, FiftyTifty said:

Alrighty, got back to where I was before. Now I've run the create_mysql.sql file, but running worldserver with the trinity user throws the following error:

 

	[email protected]:~/server/bin$ sudo ./worldserver
TrinityCore rev. d7375ad33f9d 2019-06-09 21:44:37 +0200 (3.3.5 branch) (Unix, RelWithDebInfo, Static) (worldserver-daemon)
<Ctrl-C> to stop.
	 ______                       __
/\__  _\       __          __/\ \__
\/_/\ \/ _ __ /\_\    ___ /\_\ \, _\  __  __
   \ \ \/\`'__\/\ \ /' _ `\/\ \ \ \/ /\ \/\ \
    \ \ \ \ \/ \ \ \/\ \/\ \ \ \ \ \_\ \ \_\ \
     \ \_\ \_\  \ \_\ \_\ \_\ \_\ \__\\/`____ \
      \/_/\/_/   \/_/\/_/\/_/\/_/\/__/ `/___/> \
                                 C O R E  /\___/
http://TrinityCore.org \/__/
	Using configuration file /home/trinity/server/etc/worldserver.conf.
Using SSL version: OpenSSL 1.1.1b  26 Feb 2019 (library: OpenSSL 1.1.1b  26 Feb 2019)
Using Boost version: 1.67.0
Could not connect to MySQL database at 127.0.0.1: Access denied for user 'trinity'@'localhost' (using password: YES)
	DatabasePool Login NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors. Read wiki at http://www.trinitycore.info/display/tc/TrinityCore+Home
	

 

I've even reissued the GRANT commands to ensure that trinity is given access to the databases, but nada. I'm thinking that it's a problem with worldserver trying to connect to 127.0.0.1, instead of localhost. How do I change that?

1st thing: you don't need to run tc with sudo.

2nd thing run https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql and start worldserver without change config, if you change config, change https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql to match config.

Share this post


Link to post
Share on other sites
2 minutes ago, Aokromes said:

1st thing: you don't need to run tc with sudo.

2nd thing run https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql and start worldserver without change config, if you change config, change https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql to match config.

That's the thing, I ran create_mysql.sql, making sure to change the config before hand. The MySQL databases exist, but it throws that error when I run worldserver.

Share this post


Link to post
Share on other sites

What's now the output of

SELECT Host, User from mysql.user

and

SELECT Host, Db, User FROM mysql.db

Share this post


Link to post
Share on other sites
39 minutes ago, Magnuss said:

What's now the output of

SELECT Host, User from mysql.user

and

SELECT Host, Db, User FROM mysql.db

Sorry that I keep coming back with these questions. Trying to get to the bottom of this once and for all.

Alright, so:

	mysql> SELECT Host, User from mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | adminfiftytifty  |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
| localhost | trinity          |
+-----------+------------------+
6 rows in set (0.00 sec)
	

	mysql> SELECT Host, Db, User FROM mysql.db
    -> ;
+-----------+--------------------+---------------+
| Host      | Db                 | User          |
+-----------+--------------------+---------------+
| localhost | auth               | trinity       |
| localhost | characters         | trinity       |
| localhost | performance_schema | mysql.session |
| localhost | sys                | mysql.sys     |
| localhost | trinity_auth       | trinity       |
| localhost | trinity_characters | trinity       |
| localhost | trinity_world      | trinity       |
| localhost | world              | trinity       |
+-----------+--------------------+---------------+
8 rows in set (0.00 sec)
	

 

Edited by FiftyTifty
Text didn't paste.

Share this post


Link to post
Share on other sites

Now you have maybe more than you need ;-)

If your databases are named auth, characters and world you can revoke rights to trinity_auth and so on. These was only example names to group trinity databases together.

REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_auth.* FROM 'trinity'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_characters.* FROM 'trinity'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_world.* FROM 'trinity'@'localhost';

It seems you have a problem with your password or similar in your config file. Check this in your config file:

LoginDatabaseInfo     = "127.0.0.1;3306;trinity;trinity;auth"
WorldDatabaseInfo     = "127.0.0.1;3306;trinity;trinity;world"
CharacterDatabaseInfo = "127.0.0.1;3306;trinity;trinity;characters"

You have to replace the password with your own password for user trinity.

Share this post


Link to post
Share on other sites
1 hour ago, Magnuss said:

Now you have maybe more than you need ;-)

If your databases are named auth, characters and world you can revoke rights to trinity_auth and so on. These was only example names to group trinity databases together.

REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_auth.* FROM 'trinity'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_characters.* FROM 'trinity'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION ON trinity_world.* FROM 'trinity'@'localhost';

It seems you have a problem with your password or similar in your config file. Check this in your config file:

LoginDatabaseInfo     = "127.0.0.1;3306;trinity;trinity;auth"
WorldDatabaseInfo     = "127.0.0.1;3306;trinity;trinity;world"
CharacterDatabaseInfo = "127.0.0.1;3306;trinity;trinity;characters"

You have to replace the password with your own password for user trinity.

Yup, as per the guide, I did exactly that.

 

Edit: I did exactly that, and the error still occurs upon trying to run worldserver.

Edited by FiftyTifty
Added missing info

Share this post


Link to post
Share on other sites

Well, never thought people really following the wiki exactly xD. For security reason you may will change later the default password for user trinity or never ever give other host rights than localhost. If you would give host right like % it's likely you will get hacked early or later.

Atm, I don't know what the problem could be. You have a user trinity, that's good. The user trinity has permission to the 3 databases, that's also good. And as I understand you right you installed the mysql server and the trinity server on the same machine, so both are localhost.

Last thing you may can try is to connect from Ubuntu terminal as user trinity:

mysql -u trinity -p

Enter password trinity and check if you can access.

Share this post


Link to post
Share on other sites
1 minute ago, Magnuss said:

Well, never thought people really following the wiki exactly xD. For security reason you may will change later the default password for user trinity or never ever give other host rights than localhost. If you would give host right like % it's likely you will get hacked early or later.

Atm, I don't know what the problem could be. You have a user trinity, that's good. The user trinity has permission to the 3 databases, that's also good. And as I understand you right you installed the mysql server and the trinity server on the same machine, so both are localhost.

Last thing you may can try is to connect from Ubuntu terminal as user trinity:

mysql -u trinity -p

Enter password trinity and check if you can access.

I meant that I followed the guide, where it says to change the password in the .conf files. And it was as the user Trinity that I ran worldserver. I'll give that another go in a moment to see if it works now for some reason.

Share this post


Link to post
Share on other sites
22 minutes ago, Magnuss said:

Well, never thought people really following the wiki exactly xD. For security reason you may will change later the default password for user trinity or never ever give other host rights than localhost. If you would give host right like % it's likely you will get hacked early or later.

Atm, I don't know what the problem could be. You have a user trinity, that's good. The user trinity has permission to the 3 databases, that's also good. And as I understand you right you installed the mysql server and the trinity server on the same machine, so both are localhost.

Last thing you may can try is to connect from Ubuntu terminal as user trinity:

mysql -u trinity -p

Enter password trinity and check if you can access.

A'ight, gave it a go, now MySQL is refusing my password for trinity. Which is utterly bizarre. Changed the password to the exact same thing I set it to, now I can get into trinity.

And now it works. For some bizarre reason, the password changed even though I explicitly kept a text file of it. After changing it to the same password, it now works? This is just bananas.

Thanks for the help so far lads, I really appreciate it. Expect questions about accessing MySQL databases once I get started on making tools.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...