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

FiftyTifty · June 5, 2019, 9:05pm

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.

firefird · June 7, 2019, 8:35pm

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

FiftyTifty · June 7, 2019, 9:41pm

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.

Magnuss · June 9, 2019, 4:14pm

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.

FiftyTifty · June 9, 2019, 4:21pm

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?

Magnuss · June 9, 2019, 4:33pm

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.

FiftyTifty · June 9, 2019, 4:37pm

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.

Magnuss · June 9, 2019, 4:40pm

What’s the output of

SELECT Host, User from mysql.user

FiftyTifty · June 9, 2019, 4:42pm

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>

Magnuss · June 9, 2019, 4:46pm

As you see, there is no user trinity

Edit:

Here’s how to add users: https://dev.mysql.com/doc/refman/5.7/en/creating-accounts.html

FiftyTifty · June 9, 2019, 4:57pm

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.

Magnuss · June 9, 2019, 5:25pm

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

[ul][li]For 3.3.x:[/li]
https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql

	[/ul]

[li]For master:[/li]		[ul]
			[https://github.com/TrinityCore/TrinityCore/blob/master/sql/create/create_mysql.sql](https://github.com/TrinityCore/TrinityCore/blob/master/sql/create/create_mysql.sql)
		
	[/ul]

FiftyTifty · June 9, 2019, 6:00pm

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.

FiftyTifty · June 10, 2019, 4:59pm

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

[ul][li]For 3.3.x:[/li]
https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql
			[/ul]

		[li]For master:[/li]				[ul]
					[https://github.com/TrinityCore/TrinityCore/blob/master/sql/create/create_mysql.sql](https://github.com/TrinityCore/TrinityCore/blob/master/sql/create/create_mysql.sql)
				
			[/ul]

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:

trinity@Ubuntu-1904-disco-64-minimal:~/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?

Aokromes · June 12, 2019, 10:57am

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:

[CODE]
trinity@Ubuntu-1904-disco-64-minimal:~/server/bin$ sudo ./worldserver
TrinityCore rev. d7375ad33f9d 2019-06-09 21:44:37 +0200 (3.3.5 branch) (Unix, RelWithDebInfo, Static) (worldserver-daemon)
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
[/CODE]
	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.

FiftyTifty · June 12, 2019, 11:00am

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.

Magnuss · June 12, 2019, 12:37pm

What’s now the output of

SELECT Host, User from mysql.user

and

[FONT=‘Courier New’]SELECT Host, Db, User FROM mysql.db[/FONT]

FiftyTifty · June 12, 2019, 1:16pm

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

Alright, so:

Magnuss · June 12, 2019, 2:40pm

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.

[FONT=‘Courier New’]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’;[/FONT]

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

[FONT=‘Courier New’]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”[/FONT]

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

FiftyTifty · June 12, 2019, 4:02pm

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.