DbDeployer - MySQL instance - Grants for new databases (or root user)

Question

This question relates to Dbdeployer

The section in question:

Users:

root, with the default grants as given by the server version being installed.

I have an instance installed on port 5730 and port 5731 respectively. (Corresponds to MySQL 5.7.30 and 5.7.31).

I can connect like this:

mysql -u msandbox -p -h 127.0.0.1 -P 5730
mysql -u msandbox -p -h 127.0.0.1 -P 5731
mysql -u mycustomusername -p -h 127.0.0.1 -P 5730

I created a file for grants like shown in the article:

use the option --post-grants-sql-file to load the instructions.

> cat << EOF > orchestrator.sql

CREATE DATABASE IF NOT EXISTS orchestrator;
CREATE USER orchestrator IDENTIFIED BY 'msandbox';
GRANT ALL PRIVILEGES ON orchestrator.* TO orchestrator;
GRANT SELECT ON mysql.slave_master_info TO orchestrator;

EOF

$ dbdeployer deploy single 5.7 \
  --post-grants-sql-file=$PWD/orchestrator.sql

This works fine for a new empty database deployed by the SQL script (and its grants), but I now have an existing instance, and want to create a new database from within the mysql instance.

The article claims that root should be available, but:

mysql -u root -p -h 127.0.0.1 -P 5731
Access denied for user 'root'@'localhost' (using password: YES)

I have the local instance installed on 3306, but this is not supposed to be the user I need to login with.

When I do this:

mysql -u root -p -h localhost -P 5731

I am able to login, _however this seems to ignore the port (when connecting as localhost) because I see different databases (those on port 3306 and not those from 5730/5731)!

This also confirms my suspicion that port gets ignored :

SHOW GRANTS FOR mycustomusername;
ERROR 1141 (42000): There is no such grant defined for user 'mycustomusername' on host '%'

SHOW GLOBAL VARIABLES LIKE '%port%';
.... truncated ....
port   | 3306

I need to use root@host5731 and root@host5730 but there does not seem a way to use root here?

I need to do one (either) of the following:

• Use root user at these ports,
• Get a way to let msandbox or mycustomusername to be able to have ability to do GRANT ALL PRIVILEGES on a new database.

Why?

I cannot remove/recreate a new MySQL instance to add new databases (using the SQL file method) --post-grants-sql-file when I already have existing databases.

Answer 1

Dbdeployer instances and setup installed and configures the password for root to be the same password as the username specified (default username msandbox).

You cannot do this (even though some answers on the github repo claim you can)

dbdeployer deploy single 5.7.31 -u root -p somepassword

Rather what happens (and not clearly mentioned anywhere easily accessible) is that you can do the following:

dbdeployer deploy single 5.7.31 -u someuser -p somepassword

Dbdeployer setup then deploys this someuser AND root to have the same password (somepassword).

More information:

I found that I could do this:

cd /var/dbdeployer/instance/location/of/mysql.5.7.31/
./use -u root

(Not specifying the password here.) Inspecting the ./use script, it greps the password from your configuration (which is the password for someuser.

This then gives us the ability to login via root to change grants:

mysql -u root -p -h 127.0.0.1 -P 5731

I have now changed the password from inside:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new-password';

This prevents you from externally using ./use -u root as the password is now different than the other user.