Databases

Databases are essentials to organize data linked to each others.

As example, a blog engine needs to know who commented which article, at which date and by whom. This comment has a link, the author may have left a mail address to get an alert when there is an answer... All those are tied together.

When you self host, you may not need a huge database engine. In this scenario, SQLite is a better choise : easy to backup and light even if there are less features.

SQlite

SQlite is amazing.

It is easy and light. To backup, just copy a file. It is more than enough in mosts cases.

To install, just : 

# pkg_add sqlite3.

To use with PHP, add "php-pdo_sqlite-*" and "php-sqlite3-*".

MariaDB (MySQL)

Very well known database engine, MySQL or MariaDB is often required in webapps. Make sure to understand how to secure your installation as it is a sensitive software.

Read "/usr/local/share/doc/pkg_readmes/*" related to mariadb install.

To use with PHP, install php-mysqli-* and php-pdo_mysql-* then enable extensions as explained in PHP's part.

Afin d'installer MariaDB, il faut lancer les commandes suivantes :

To install MariaDB : 

# pkg_add mariadb-server
# /usr/local/bin/mysql_install_db

The second command install a default database.

To start mysql :

# rcctl enable mysqld
# rcctl start mysqld

Finally, use the following command to improve mysql safety : 

# /usr/local/bin/mysql_secure_installation

To let httpd talk with MariaDB (it is chrooted), launch thos command to reproduce root structure with appropriate permissions : 

# install -d -m 0711 -o _mysql -g _mysql /var/www/var/run/mysql

Add those lines to /etc/my.cnf to change MariaDB socket location so it is accessible to httpd : 

[client]
    socket = /var/www/var/run/mysql/mysql.sock
[mysqld]
    socket = /var/www/var/run/mysql/mysql.sock

At last, restart mysql :

# rcctl restart mysqld

Now you can add users and databases.

Create a database

As example, we will show how to create a database for Wordpress.

Enter "mysql -u root -p" To get to the MariaDB shell. Below see a log of inputs and ouputs.

# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.23-MariaDB-log openBSD port: mariadb-server-10.0.23p0v1
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE wordpress_base;
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> CREATE USER 'wp'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON wordpress_base.* TO 'wp'@'localhost';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> exit
Bye

That's it 😊.

PostgreSQL

PostgreSQL is another database engine. Install postgresql-server to use it.

With PHP, you'll need php-pgsql-* and php-pdo_pgsql-*.

Read /usr/local/share/doc/pkg-readmes/postgresql* carefully.

Create a default database : 

# su - _postgresql
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data -U postgres -A scram-sha-256 -E UTF8 -W
$ exit

Default user is postgres.

Edit /var/postgresql/data/postgresql.conf to suit your needs.

To let httpd access postgresql, you should have : 

unix_socket_directories = '/var/www/tmp/postgresql, /tmp'

You have to edit permissions on this directory : 

# mkdir -p /var/www/tmp/postgresql
# chown _postgresql:www /var/www/tmp/postgresql

To start postgresql, as usual : 

# rcctl enable postgresql
# rcctl start postgresql

To get the postgresql shell : 

# su _postgresql -c psql

Below a few examples to dael with postgresql :

Change admin password : 

# psql -U postgres -c "ALTER USER postgres WITH PASSWORD 'new_password'";

Add "toto" user : 

# psql -U postgres -c "CREATE USER toto WITH PASSWORD 'password';"

Créer une nouvelle base et donner à toto tous les droits dessus :

Create a new database and let toto do everything he wants with : 

# psql -U postgres 
\connect template1
CREATE DATABASE "new_db" WITH ENCODING 'UTF-8';
GRANT ALL PRIVILEGES ON DATABASE "new_db" TO toto;
ALTER DATABASE "new_db" OWNER TO toto;
\q

Backup / Restore databases

With SQLite

Just copy the database file. That's it 😊.

With MariaDB

To get a db-name dump : 

# mysqldump -u root -p db-name > /var/backup/db_backup

Of course, edit user "root" and "db-name".

To restore the database :

Notice the "<" backwards.

With PostgreSQL

To save a database with postgresql, you actually save all the instructions to recreate the database : 

# pg_dump db-name > /var/backup.db

To restore the base : 

# psql -U postgres db-name < /var/backup.db