Author: Humphrey Mpairwe
Last Updated: Fri, Jan 7, 2022MariaDB 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.