Database Configuration

Configure MySQL default security

We should exec:

$ sudo mysql_secure_installation

And reply respectively in the prompt

1. Blank passwords: N
2. Root password:
3. Remove anonymous user: Y
4. Disallow remote login of the root user: Y
5. Delete test database: Y
6. Reload privilege tables: Y (You should reload manually the privilege tables if you change something by your own, just for take it in account)

Create Radius Database

Here we will name the RADIUS database radius if you use any other you should change it the name not only here, in all scripts you should take a look because maybe you should change the name. I will try to advice but this could be a focus of failures after all configuration and installation.

To create the database just type:

$ sudo mysql -u root

And now in the SQL console type:

CREATE DATABASE radius;
exit

FreeRadius Tables

Download good schema for FR3

FreeRadius by default download a mysql tables that are from FreeRadius 2.0 and fails so we first download good sql file:

cd /etc/freeradius/3.0/mods-config/sql/main/mysql/
sudo mv schema.sql schema.sql.old
wget https://raw.githubusercontent.com/FreeRADIUS/freeradius-server/v3.0.x/raddb/mods-config/sql/main/mysql/schema.sql

Creating the FreeRadius SQL Schema (Tables setup)

To create the tables we should use previous downloaded schema and type:

sudo mysql -u root radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql
sudo mysql -u root radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/setup.sql

Creating the daloRadius SQL Schema

We will use the same database (radius) but feel free to use other knowing that you should change database param in daloRadius configuration.

sudo mysql -u root radius < /var/www/daloradius/contrib/db/mysql-daloradius.sql

Create a new database users

We will create a user for PHPMyAdmin and manage all databases. We also create a user for daloRadius.

You must change <password> text with your desired user password. Do not forget them because you will use them later.

First prompt to the MariaDB Console

$ sudo mysql -u root

Then enter this SQL sentence to create a user for daloRadius:

CREATE USER 'daloUser'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON radius . * TO 'daloUser'@'localhost';
FLUSH PRIVILEGES;

After that and before exit create a user to access through PHPMyAdmin. If you won't use PHPMyAdmin you can avoid this, but remember to came here and do this if you will use it:

CREATE USER 'phpmyadminuser'@'localhost' IDENTIFIED BY '<password>';
GRANT ALL PRIVILEGES ON *.* TO 'phpmyadminuser'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

Performance optimizations

If you have a lot of users you will need to optimize the MariaDB configuration, I did some steps here which reduces the load (please if you are expert on this, maybe you can provide some documentation here).

Database backup

You must do a complete backup firstly because it could be a mess... Then we need the backup to cleanup all databases and later restore them. To do the backup (I did in root home dir) by executing:

$ sudo mysqldump --all-databases > ~/all-dbs.sql

This is necessary because even if you did not used the database engine before. Please do it to later restore all of the databases.

You can also do this through PHPMyAdmin

InnoDB Engine Configuration, Cache and MariaDB Memory

This is the file commented, you should add, replace or personalize some of this params in InnoDB section:

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
innodb=ON
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 4G
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
 
 
innodb_log_files_in_group       = 2
innodb_log_file_size            = 256M
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = OFF
innodb_buffer_pool_size         = 3000M

Cleanup all databases

Just in case that when you try to run the database you receive an error, do this:

• https://stackoverflow.com/questions/15694168/error-tablespace-for-table-xxx-exists-please-discard-the-tablespace-before-imp

Restore all databases

sudo mysql -u root < ~/all-dbs.sql

Identify slow querys

After the database is running just check this file /var/log/mysql/mariadb-slow.log. It will help you to improve your SQL Queries.

You can also use scripts like https://github.com/major/MySQLTuner-perl

Automatically Database Backups

I installed a tool that do a database backup to /var/lib/automysqlbackup. This will use some space so configure this tool to do a backup in other mounted disk would be a great idea.

sudo apt intall automysqlbackup
sudo automysqlbackup
sudo vim /etc/default/automysqlbackup

Configure that file with your desired configuration... =)