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.
Log in to the server as root through SSH or the web console.
Install MariaDB from the OpenBSD repository.
# pkg_add mariadb-server
Configure MariaDB to start at boot time.
# rcctl enable mysqld
Start the MySQL daemon.
# rcctl start mysqld
Verify that MariaDB has started.
# rcctl check mysqld
Run the mysql_install_db script to create the necessary system tables and binary files.
# mysql_install_db
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
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
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
Save and close the file.
If you intend to use MariaDB with a web server, install the required PHP modules to establish a connection with hosted applications.
Install the PHP MySQL modules.
# pkg_add php_mysqli php_pdo_mysql
Test the MySQL connection by running a simple PHP script in your webroot directory.
# nano /var/www/htdocs/test.php
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 ";
?>
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!!
Login to the MariaDB console as root.
# mysql -u root -p
Enter the root password you set earlier.
Create a new sample database.
MariaDB [(none)]> CREATE DATABASE sampledb;
Create a new standard user with a strong password.
MariaDB [(none)]> CREATE USER 'user2'@'localhost' IDENTIFIED BY 'STRONG-PASSWORD';
Grant the user full permissions to the sample database.
MariaDB [(none)]> use sampledb;
MariaDB [sampledb]> GRANT ALL PRIVILEGES ON sampledb.* TO 'user2'@'localhost';
Reload the privileges.
MariaDB [sampledb]> FLUSH PRIVILEGES;
Exit the console.
MariaDB [sampledb]> EXIT
Log in to the MySQL console again, this time as a standard user.
# mysql -u user2 -p
Check the current databases accessible by the user.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| sampledb |
+--------------------+
2 rows in set (0.002 sec)
Exit the console.
MariaDB [(none)]> EXIT
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.