[Realmist shows server, but doesn't connect]

We're so damn close to getting this working.

I've loaded into the database with HeidiSQL with my remote root account, and changed auth\realmlist\address to have the value of my server's IP. Clicked "Save". Edited the Data\enUS\realmlist.wtf file in my client (on my desktop pc) to the server's IP (don't have a domain yet): set realmlist  *.*.***.***

With the *'s obviously being the numbers of my server's IP.

I can log into the account I created in the WorldServer terminal, and the realm list shows the Trinity realm. And when I click on Okay, a window pops up saying: logging into game server

That lasts for a couple seconds, before it returns back to the realm list.

 

Edit: Found the solution. You also have to edit the first row on the realmlist's data tab in HeidiSQL, not just the table itself.

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

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.

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

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.

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

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.

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

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)
	

 

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

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.

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

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

• For 3.3.x:
  • https://github.com/TrinityCore/TrinityCore/blob/3.3.5/sql/create/create_mysql.sql
• For master:
  • https://github.com/TrinityCore/TrinityCore/blob/master/sql/create/create_mysql.sql

 

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?

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

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.

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

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.

 

 

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

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>

 

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

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.

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

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?

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

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.

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

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.

[Databses Installation - Guide is unclear]

1 hour ago, Aokromes said:

what part of this is unclear?

 

 

Oh. I do apologize, I didn't see that, which makes me look like a dickhead. Thanks for the response.

[Databses Installation - Guide is unclear]

2 hours ago, abelard said:

If you run auth/bnet and then run world, the first thing it will ask is do you want to install dbase.  It will then populate the dbase and updates included in the downloaded dbase.

Ooh. And what do I do if I want to change the databases, or reimport the originals?

[Databses Installation - Guide is unclear]

I've been following the guide thus far, and the previous pages have been pretty good at telling the user exactly what to do. But now that I'm at the Databases Installation section, there are no longer any steps to follow, and just unspecific advice is given: https://trinitycore.atlassian.net/wiki/spaces/tc/pages/2130092/Databases+Installation

I've downloaded the SQL database and put it next to worldserver, and I've downloaded the MySQL commands file. But what exactly do I do from then on? The guide just says to create the databases and populate them, without explaining how to do that.

[Addition to Linux Core Installation guide]

At the end of the guide, where it says to install MySQL and check to see if MyISAM and InnoDB engines are enabled. But it doesn't tell you how to check that. Nor does it tell you that you have to make a new root account for MySQL.

The way that's done:

Open the terminal

Type in: MySQL

If it's the first time you're installing MySQL, you'll be given various prompts about default settings. The one we're interested in, is adding a password to the root MySQL account. Remember it, and you'll be given two prompts. One to input the password, and a second one to retype it.

Once that's done, you'll have access to the MySQL terminal.

In the new prompt, type:

SHOW ENGINES;

And that will show a list of the installed database engines. You may want to copy the text from the Linux terminal to a text editor, to have it formatted properly.