ML
    • Recent
    • Categories
    • Tags
    • Popular
    • Users
    • Groups
    • Register
    • Login

    Setting Up a Standard MySQL or MariaDB Database for an Application

    IT Discussion
    database mysql mariadb rdbms how to dba system administration fedora linux centos 7 rhel 7 ubuntu centos
    4
    5
    1.2k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • scottalanmillerS
      scottalanmiller
      last edited by 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.

      1 Reply Last reply Reply Quote 3
      • 1
        1337
        last edited by 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
        
        1 Reply Last reply Reply Quote 1
        • JaredBuschJ
          JaredBusch
          last edited by 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;"
          
          black3dynamiteB 1 Reply Last reply Reply Quote 3
          • black3dynamiteB
            black3dynamite @JaredBusch
            last edited by

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

            JaredBuschJ 1 Reply Last reply Reply Quote 0
            • JaredBuschJ
              JaredBusch @black3dynamite
              last edited by

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

              1 Reply Last reply Reply Quote 0
              • 1 / 1
              • First post
                Last post