Knowledgebase

MariaDB is a popular open-source database management software commonly used as the drop-in replacement to MySQL that uses the same command syntax. This guide explains how to install and configure MariaDB on a server running OpenBSD 7.

1. Install MariaDB Server

1. Log in to the server as root through SSH or the web console.

2. Install MariaDB from the OpenBSD repository.

    # pkg_add mariadb-server

3. Configure MariaDB to start at boot time.

    # rcctl enable mysqld

4. Start the MySQL daemon.

    # rcctl start mysqld

5. Verify that MariaDB has started.

    # rcctl check mysqld

6. Run the mysql_install_db script to create the necessary system tables and binary files.

    # mysql_install_db

2. Configure MariaDB

1. Run the mysql_secure_installation script to remove the test database and insecure default settings that may put your production server at risk of attack. Choose your desired settings when prompted and set a strong root password.

    # mysql_secure_installation 

2. Enable the mysql socket at /var/run/mysql.sock and allow the MariaDB to listen on port 3306 for client connections. Open /etc/my.cnf in your favorite editor.

    # nano /etc/my/cnf

3. Uncomment the socket and port entries below[client-server]. When finished, that section should look like this:

    [client-server]
    socket=/var/run/mysql/mysql.sock
    port=3306

4. Save and close the file.

Optional: Configure MariaDB to work with a Web Server

If you intend to use MariaDB with a web server, install the required PHP modules to establish a connection with hosted applications.

1. Install the PHP MySQL modules.

    # pkg_add php_mysqli php_pdo_mysql

2. Test the MySQL connection by running a simple PHP script in your webroot directory.

    # nano /var/www/htdocs/test.php

3. Paste the following code. Replace username and password with your actual credentials:

    <?php
   
    $servername = "127.0.0.1";
    $user = "username";
    $pass = "password";
   
    // Create connection to MariaDB
   
    $conn = new mysqli($servername, $user, $pass);
   
    // Test connection to MariaDB
   
    if ($conn->connect_error) {
    die("Database Connection failed: " . $conn->connect_error);
    }
    echo "Database connected successfully, Congratulations ";
   
    ?>

4. Visit your server IP in a web browser and load test.php.

    http://your.server.ip.address/test.php

   Your output should be:

    Database connected successfully, Congratulations!!

3. Test MariaDB

1. Login to the MariaDB console as root.

    # mysql -u root -p

   Enter the root password you set earlier.

2. Create a new sample database.

    MariaDB [(none)]> CREATE DATABASE sampledb;

3. Create a new standard user with a strong password.

    MariaDB [(none)]> CREATE USER 'user2'@'localhost' IDENTIFIED BY 'STRONG-PASSWORD';

4. Grant the user full permissions to the sample database.

    MariaDB [(none)]> use sampledb; 
   
    MariaDB [sampledb]> GRANT ALL PRIVILEGES ON sampledb.* TO 'user2'@'localhost';

5. Reload the privileges.

    MariaDB [sampledb]> FLUSH PRIVILEGES;

6. Exit the console.

    MariaDB [sampledb]> EXIT 

7. Log in to the MySQL console again, this time as a standard user.

    # mysql -u user2 -p

8. Check the current databases accessible by the user.

    MariaDB [(none)]> show databases;
   
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | sampledb           |
    +--------------------+
    2 rows in set (0.002 sec)

9. Exit the console.

    MariaDB [(none)]> EXIT

Conclusion

You have successfully installed and configured MariaDB on OpenBSD 7, and the database server can connect with other applications installed on the server, including web stacks like Nginx, httpd, and apache2-httpd through its run time socket and PHP.