Setting Up a Standard MySQL or MariaDB Database for an Application



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



  • 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


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


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



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