govuk-puppet: Create MySQL database and user
How to create a MySQL database/user on RDS instance
You'll need to know three things:
- Database name (find this in the db.pp of your app, e.g.
collections_publisher_production) - Database user (find this in db.pp, or the config/database.yml of your app. Beware that some apps - Whitehall - have multiple users to create)
- Database user's password (you'll need to extract the value from govuk-secrets, e.g. govuk::apps::collections_publisher::db::mysql_password. Make sure you look at the same environment, i.e. look in integration_credentials.yaml when making the database/user on Integration)
Connect to the DB admin machine, e.g.
gds govuk connect ssh -e integration collections_publisher_db_admin
Export the three bits of data:
export TMP_MYSQL_DB_NAME='collections_publisher_production' ;
export TMP_MYSQL_DB_USER='collections_pub' ;
export TMP_MYSQL_DB_PASSWORD='THE_PASSWORD' ;
Create the user:
sudo -H mysql -e "CREATE USER IF NOT EXISTS '$TMP_MYSQL_DB_USER'@'%' IDENTIFIED WITH 'mysql_native_password' BY '${TMP_MYSQL_DB_PASSWORD}';"
Create the database:
sudo -H mysql -e "CREATE DATABASE IF NOT EXISTS ${TMP_MYSQL_DB_NAME};"
Grant the user access to the database:
sudo -H mysql -e "GRANT ALL ON ${TMP_MYSQL_DB_NAME}.* TO '${TMP_MYSQL_DB_USER}'@'%'"
Finally, clean up after yourself:
unset TMP_MYSQL_DB_NAME ;
unset TMP_MYSQL_DB_USER ;
unset TMP_MYSQL_DB_PASSWORD ;
You can sense-check what you've done by:
# See which databases there are
sudo -H mysql -e "SHOW DATABASES;"
# See which users there are
sudo -H mysql -e "SELECT host, user FROM mysql.user;"
# See which tables are in the database, e.g.
sudo -H mysql -e "USE collections_publisher_production; SHOW TABLES;"
# Note that this will only be populated after the first data sync has run.
How often does this need to be done?
Whenever we spin up a new RDS instance (i.e. rarely).
Why must this be done manually?
In #11353 and #11359, new DB admin machines were created for apps that use MySQL. This was done as part of RFC-143, which agrees that every app should have its own RDS instance.
The new RDS instances run MySQL version 8, where the previous shared RDS instance (blue-mysql-primary) is on version 5.6.
Traditionally, we've used Puppet to create the MySQL databases and users via the DB admin machine. However, this approach throws a SQL syntax error when applied to MySQL 8 environments:
Error: /Stage[main]/Govuk::Apps::Collections_publisher::Db/Mysql::Db[collections_publisher_production]/Mysql_user[collections_pub@%]/ensure: change from absent to present failed: Execution of '/usr/bin/mysql --defaults-extra-file=/root/.my.cnf --database=mysql -e CREATE USER 'collections_pub'@'%' IDENTIFIED BY PASSWORD '(omitted)'' returned 1: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PASSWORD '(omitted)'' at line 1
This is because version 5.6 allowed the IDENTIFIED BY PASSWORD syntax, whereas 8.0 is just IDENTIFIED BY.
This isn't overridable in the puppetlabs-mysql module, and whilst the issue has been fixed in later versions of the module, we use an old version of Puppet which does not allow us to upgrade the module.
A spike investigated how we could use Puppet to create the database and user(s) in a MySQL 8 environment, but it had a number of unresolved complexities, so it was decided we would manually create the databases instead, given how infrequently we spin up new RDS instances.