Setting Up a Standard MySQL or MariaDB Database for an Application

[scottalanmiller]

Creating a database for use by an application is a pretty standard and common system administration or DBA task. MariaDB and MySQL are probably the most common platforms for doing this. Countless applications require this same process to be followed using these tools (MariaDB and MySQL are compatible so the directions apply to either.)

---

Installing MariaDB or MySQL

Installing MariaDB on Fedora Linux

Assuming that you are using Fedora Linux, this is how you install MariaDB.

dnf -y install mariadb mariadb-server
systemctl start mariadb
systemctl enable mariadb
/usr/bin/mysql_secure_installation

When running the final script, simply accept all defaults. Be sure to store your root database admin password somewhere safe.

Installing MariaDB on CentOS 7 or RHEL 7

Assuming that you are using CentOS (or RHEL) Linux 7, this is how you install MariaDB.

yum -y install MariaDB MariaDB-server
systemctl start mariadb
systemctl enable mariadb
/usr/bin/mysql_secure_installation

When running the final script, simply accept all defaults. Be sure to store your root database admin password somewhere safe.

installing MySQL on Ubuntu 18.04 or 18.10

Assuming that you are using Ubuntu Linux 18.XX, this is how you install MySQL.

sudo apt-get update
sudo apt-get install mysql-server mysql
sudo systemctl start mysql
sudo systemctl enable mysql
sudo mysql_secure_installation

When running the final script, simply accept all defaults. Be sure to store your root database admin password somewhere safe.

---

Creating Your Application Database

Create the Database Itself

First, we need to connect to our running database server with the mysql command line utility. We will use the local root account and the password that we just set above during our installation step.

mysql -uroot -pyoursecretpasswordthatyoujustset

Now that we are connected, we need to create our new database at the mysql prompt.

mysql> CREATE DATABASE yourdatabasename;

Create a Local Database User Account

Next we create a user that has full access to manage that database (but no others). We are using "localhost" here because we are assuming that we are creating a local user for use by a locally installed application. For remote use, we will need a few more steps.

mysql> GRANT ALL PRIVILEGES ON yourdatabasename.* TO "yourusername"@"localhost" IDENTIFIED BY "yourpassword";

Finally we want to ensure that we make these new changes take effect immediately.

mysql> FLUSH PRIVILEGES;
mysql> EXIT

That is it, any locally installed application will simply need to know to connect to localhost, yourdatabasename, yourusername, and yourpassword. Those four things are all that are needed for your locally installed application.

---

Example applications that use installs of this nature are WordPress and osTicket.

[1337]

Installing MariaDB on Debian 9.x

$ su
# apt install mariadb-server
# mysql_secure_installation

To check the installed version:

# mysqladmin version

Or to check that MariaDB is running:

# systemctl status mariadb

[JaredBusch]

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;"

[black3dynamite]

@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.

[JaredBusch]

@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.