Specifying password for MySQL on Ubuntu 20.04

Hi!

I already have Ghost set up and running but I’m a bit curious about this part of the installation guide for Ubuntu.

Specifically, this part:

# and login to your Ubuntu user again
su - <user>

Is this really needed? I think this may be a mistake. Because earlier in the guide it says:

# Then log in as the new user
su - <user>

Once I had created a new user and switched to it I was still signed in when I entered the MySQL client.

I also wonder about this part:

If you provided your root MySQL user, Ghost-CLI can create a custom MySQL user that can only access/edit your new Ghost database and nothing else.

Is this related to the default Ghost user (the welcome post author)? I ask because I wonder if I can safely delete the Ghost default user if I selected to let the installation script create the custom MySQL user.

I have deleted the Ghost user and everything seems to be OK.

No, this is talking about system user in the Ubuntu OS.

ghost:x:998:998::/home/ghost:/bin/sh

Indeed, I appear to have this system user. So the setup creates one MySQL user named “ghost” and a system user with the same name? Who or what is using these users? I don’t have a password for either one of them.

Update 1:
It appears I have a MySQL user called “ghost-294”.

Update 2:
I just had my heureka moment! :bulb: This is probably why the installation guide says:

Note: Using the user name ghost causes conflicts with the Ghost-CLI, so it’s important to use an alternative name.

Because then there would be two system users called “ghost”.

This brings up another question… :thinking: why did it append “294” to ghost in “ghost-294”… unlike the “ghost” system user, this one is a MySQL user (no need to uniquely name the two as they cannot conflict with each other as they don’t know about each others existence).

I ask too much. Sorry! I sometimes get carried away. :blush: I don’t need to know all the whys at this stage.

This allows multiple Ghost instances to use the same MySQL instance without having the MySQL user for each being able to read/write data to every Ghost instance’s database.

1 Like
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Coincidentally, in following another guide on setting up MySQL, I learned that the MySQL PHP library mysqlnd doesn’t support caching_sha2_authentication, the default authentication method for MySQL 8. So it’s recommended to use mysql_native_password instead when creating database users for PHP applications on MySQL 8.

Does this mean that my recent Ghost installation will be compatible with PHP 8? PHP 8 is scheduled for release on November 26, 2020 (in about 2 weeks from now).

I’m not sure what you mean by this. Ghost is not a PHP application, it’s a Node.js application.

Well of course it is… :man_facepalming:

It’s also a question of MySQL 8, not PHP 8. :blush:

What I meant is why do we use mysql_native_password instead of caching_sha2_authentication? Is it some kind of fail-safe? Regardless if it’s a Node.js or a PHP application, most installation guides for MySQL suggest that we use mysql_native_password for authentication.

I see now that caching_sha2_authentication is supported in PHP as of PHP 7.4.2 (December 27, 2019). Likewise, perhaps there are some libraries in Ghost that need to be updated to support caching_sha2_authentication in MySQL? A better question would be, does Ghost support the use of caching_sha2_authentication?

No, it’s not supported by the node mysql client library https://github.com/mysqljs/mysql/issues/2002

Once Ghost has been installed, is it safe to change the root MySQL password to something else without breaking anything related to Ghost? By what I understand now, providing the root password as described in the installation guide is only done so to enable the installation script to set up a dedicated MySQL user (ghost-294 in my case) for use with Ghost.

Should I run the mysql_secure_installation script to secure my database? I’m told this disables remote logins, among other things. This sounds like a good thing, but I’m afraid I will break the installation.