Ghost blog : resolving the "Access denied for user" problem

When you are self hosting with Ghost you can create as many blogs as you want. You do that by following these steps on your linux box :

cd /var/www # cd where the files reside
sudo mkdir www.example.com # create a new dir for this new site
chown ghost.ghost www.example.com # dir must be owned by the ghost user (or whatever you named it)
cd www.example.com
ghost setup

Ghost will output something like :

? Enter your blog URL: https://www.example.com
? Enter your MySQL hostname: localhost
? Enter your MySQL username: ghost-222
? Enter your MySQL password (skip to keep current password): [hidden]
? Enter your Ghost database name: www_example_com_prod

That will work ONLY if you have the user ghost-222 and the www_example_com_prod already present in the mysql database.

Here are the steps to create the user and database in mysql :

1. Connect to the database with the admin account (probably named root) :
   mysql -u root -h localhost -p
2. (optional) List the existing users :
   SELECT * FROM mysql.user;
3. (optional) List the databases
   SHOW SCHEMAS
4. Create the database :
   CREATE DATABASE www_example_com_prod;
5. Create the user (ghost-222) and assign a long password :
   CREATE USER 'ghost-222'@'localhost' IDENTIFIED BY 'a long pass phrase';
6. Give permissions to this user :
   GRANT ALL PRIVILEGES ON www_example_prod.* TO 'ghost-222'@'localhost';
7. Commit the changes
   FLUSH PRIVILEGES;
8. Exit from mysql
   \q
9. Check with steps 2 and 3.

Now that the user is created, you can run ghost setup and answer with the above info, username, database name and password.

Errors

If you don't create the user/database, you will get the following errors :

✖ Starting Ghost
One or more errors occurred.
1) GhostError
Message: Ghost was able to start, but errored during boot with: Access denied for user 'ghost-xxx'@'localhost' (using password: YES)
Help: Unknown database error
Suggestion: journalctl -u ghost_www-example-com -n 50
Debug Information:
  OS: Ubuntu, v22.04.4 LTS
  Node Version: xxx
  Ghost Version: xxx
  Ghost-CLI Version: xxx
  Environment: production
  Command: 'ghost setup'

Additional log info available in: /home/ghost/.ghost/logs/ghost-cli-debug-2024-11-11T01_11_11_111Z.log

Try running ghost doctor to check your system for known issues.

You can always refer to https://ghost.org/docs/ghost-cli/ for troubleshooting.