@black3dynamite said in Setting Up a Standard MySQL or MariaDB Database for an Application:
@JaredBusch said in Setting Up a Standard MySQL or MariaDB Database for an Application:
I like my approach to setting this up.
Obviously, install MySQL/MariaDB first as noted above.
Then do the following. This all needs done in the same SSH session, but otherwise things are simple.
Choose once of these exports for your DB root password.
The first one is for you to specify, the second generates a random one and echo's it back to you.
# Specify your own password for MariaDB root user
export DB_ROOT_PASS="somebigpasswordgoeshere"
# Generate a random password for MariaDB root user
export DB_ROOT_PASS="$(head /dev/urandom | tr -dc A-Za-z0-9 | head -c 30)"
echo "This is your MariaDB root password: $DB_ROOT_PASS"
Specify the application database name and application user name
# Database user to use for application
export DB_USER='yourusername'
# Database name to use for application
export DB_NAME='yourdatabasename'
Generate or specify a random password for the database user
# Specify your own password for the application's database user
export DB_PASS="somebigpasswordgoeshere"
# Generate a random password for the application's database user
export DB_PASS="$(head /dev/urandom | tr -dc A-Za-z0-9 | head -c 30)"
echo "This is your password for the application user: $DB_PASS"
Then create the application database, use, and grant access.
mysql -e "CREATE DATABASE $DB_NAME;"
mysql -e "CREATE USER '$DB_USER'@'localhost' IDENTIFIED BY '$DB_PASS';"
mysql -e "GRANT ALL ON $DB_NAME.* TO '$DB_USER'@'localhost';"
mysql -e "FLUSH PRIVILEGES;"
Finally, lock down the system without the interactive requirement of mysql_secure_installation
# Secure MariaDB (this does what mysql_secure_installation performs without interaction)
mysql -e "UPDATE mysql.user SET Password=PASSWORD('$DB_ROOT_PASS') WHERE User='root';"
mysql -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
mysql -e "DELETE FROM mysql.user WHERE User='';"
# Beginning on some version of MariaDB after Fedora 29 was released, the test DB is no longer there by defualt.
mysql -e "DROP DATABASE test;"
mysql -e "FLUSH PRIVILEGES;"
Your approach makes it easier to use as part of a script.
It also generates random passwords, which I prefer.
