I was trying to setup DNS based multitenancy. I already have a system up and running in erp1.example.com. For this a database named ‘erp1’ and mysql user named ‘erp1’ is already in use. While setting up multitenancy, I have one question and one problem.
Question: 1. Do I need a separate database and user for second site in the same bench ?
Problem:
I tried the command bench new-site erp2.example.com (A records are already set).
It returned the following error: Access denied for user 'root'@'localhost'
I had encountered same error while running other bench commands as well which was solved with the help of friendly forum members, found in this link.
However, it would not be practical to follow the suggestion in the above link for all commands. I don’t think bench commands work that way normally.
Question: 1. Do I need a separate database and user for second site in the same bench ?
When you create a new site a new database is automatically created as part of the new site creation process.
When you create the site, it’ll ask for MySQL root password. Once you enter the correct root password, it’ll create a new database instance for the new site.
To have multiple sub-domains here are the step-by-step instructions:
Create new site
Add new domain and assign it to the new site
Run bench setup nginx and sudo service nginx reload
Add A record in your domain DNS panel
Run certbot (in case you have SSL activated for both domains)
The only problem with new site creation is it says Access denied for user 'root'@'localhost' but I have not set any password for root. Pressing enter just does not work. FIY I have used this step by step guide to set up erpnext.
Yes I can login with mysql -u root pressed “enter” when asked for password.
I installed manually, not easy install.
I did run mysql_secure_installation but did not set up root password (just skipped by pressing enter). I had created a new mysql for erpnext use (say: erpuser) with a password.
But when running bench commands, it asks for root password which doesnot exist and doesnot accept pressing enter.
Not setting up a root password for MySQL is a really risky option.
What we can try is;
Setup a root password
Grant all privileges to root user
mysql --defaults-file=/etc/mysql/debian.cnf
UPDATE mysql.user SET authentication_string=PASSWORD('YOUR-PASSWORD-HERE'), plugin='mysql_native_password' WHERE User='root';
EXIT;
service mysql restart
Once you reset your root password trying granting all privileges using this command
sudo mysql -u root -p PASSWORD-HERE -Bse "GRANT ALL PRIVILEGES ON *.* TO root'@'localhost' IDENTIFIED BY 'PASSWORD-HERE' WITH GRANT OPTION;"