The imRAD use the MariaDB server[1]. We name it "mysql". In this wiki, you use both terms "MariaDB" and "mysql", but they are the same.
Verifying Database
You can verify the status, variables, and so on by using the show database {ARG} command.
| argument | Description |
|---|---|
| backup | It shows you the backup configuration and files. |
| connection | It shows you information about which protocol is used.[2] |
| process | It shows you which threads are running.[3]
|
| property | It shows you decrypted database connection properties. |
| replication | You can see the status of the MySQL replication. |
| status | It shows you the status of MySQL service. |
| variable | It shows you the MySQL global variables. |
If you run the show system mariadb command, it shows the version of the Database. You can also check the status of the database by entering the show service mysql or show service status mysql
LYSH@MyHostName# show system mariadb
Database server : 10.4.19-MariaDB, for debian-linux-gnu (x86_64)
LYSH@MyHostName# show service mysql
● mariadb.service - MariaDB 10.4.19 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Tue 2021-05-11 15:44:07 KST; 6 days ago
.....
Configuring Database backup
You can configure to have the system can backup the database and the imRAD is already enabled to backup the database. The default configuration is
- db_backup_interval=1
- db_backup_hour=3
- max_db_backup_file=5
The above configurations mean that the system backup the database at 3 o'clock every 1 day and remain only a recent 5 backup files. In other words, if there are more than 5 backup files after backup, the oldest one is removed.
LYSH@MyHostName# show system db_backup // view the current database backup configuration db_backup task : Active db_backup : enable db_backup_interval : 1 db_backup_hour : 3 max_db_backup_file : 5 last_db_backup_datetime : 2021-04-28 03:30:01 last_db_backup_state : success LYSH@MyHostName# configure configure# database backup enable // enable database backup configure# database backup disable // disable database backup configure# database backup hour 5 // Configure the starting hour of database backup configure# database backup interval 2 // Configure the database backup interval in days configure# database backup maxfile 3 // Configure the maximum number of database backup files to keep
You can verify the current database backup configuration and backup files by the show database backup command
LYSH@MyHostName# show database backup db_backup : enable db_backup_interval : 1 db_backup_hour : 3 max_db_backup_file : 5 last_db_backup_datetime : 2021-04-28 03:30:01 last_db_backup_state : success total 1.7M -rw-r--r-- 1 root root 20 Apr 25 03:30 backup_210425.sql.gz -rw-r--r-- 1 root root 20 Apr 26 03:30 backup_210426.sql.gz -rw-r--r-- 1 root root 20 Apr 27 03:30 backup_210427.sql.gz -rw-r--r-- 1 root root 20 Apr 28 03:30 backup_210428.sql.gz
Database password
If you want to change the database password, enter the database password command.
LYSH@MyHostName# configure LYSH@MyHostName(config) # database password New password: *********** Confirm password: *********** misc: The Database password was changed. LYSH@MyHostName(config)# exit LYSH@MyHostName# show system database Main Database : lyon/*****y@nu24@127.0.0.1:6710 Local Database : lyon/*****y@nu24@127.0.0.1:6710
If your system is configured the failover, you must change the password of the active database and then change the password of the passive(standby) database to the same password as the active database. The method of changing the database password of the active device is the same as mentioned above.
Changing password in the passive(standby) database
In an environment where the failover is operated, the password of the passive device must be the same as the password of the active device,
so if it is successful after attempting to access the active device, it will be changed to that password.
LYSH@MyHostName# configure LYSH@MyHostName(config) # database password Notice: This device is the failover passive(standby) and must be set the same password as the active database. Therefore, the database password will be changed to the same password as the active device. Do you want to continue? [y/n]y Input the password of active Database(a.b.c.d):*********** success misc: The database password of the failover passive device was changed. LYSH@MyHostName(config)# exit
Database Exporting
You can export one or all databases by entering the mysql export on user mode. It saves exported data in the specified directory.
LYSH@MyHostName# mysql export 1) lyon 2) lyon_dhcp 3) lyon_history 4) lyon_log 5) lyon_mailbox 6) lyon_rad 7) lyon_stat 8) all Select databases number: 1 Please enter a backup file name: backup Do you want to include the data?(Y/n default[Y]): y LYSH@MyHostName# show database backup db_backup=enable db_backup_interval=1 db_backup_hour=3 max_db_backup_file=5 last_db_backup_datetime=2022-01-27 03:30:02 last_db_backup_state=success -rw-r--r-- 1 root root 1115294 Jan 27 20:13 backup.gz -rw-r--r-- 1 root root 3440103 Jan 23 03:30 backup_220123.sql.gz ...
Database Importing
This command can recover the database from a backed-up file. If the current database is corrupted or other problems occur, you can recover a recent backup file.
LYSH@MyHostName# mysql import 1) backup.gz 2) backup_220123.sql.gz 3) backup_220124.sql.gz 4) backup_220125.sql.gz 5) backup_220126.sql.gz 6) backup_220127.sql.gz 7) update Select backup file number: 2
When data is recovered through the command, all current data is deleted and they are replaced with the data in the backup file, so use it only if there are problems with the database.
Connect to Database(mysql)
You can access a database directly if necessary by entering the mysql command on user mode.
If you want to access a database using "root", you must request a One Time Password (OTP)[5].
You should bear in mind that accessing the Database should be only for the purpose of maintenance or fixing problems.
TLS
If you want to have all applications connect securely to the database, you can enable MySQL TLS connection by entering the mysql_tls command on configuration mode.
After configuring it, you must restart all services to apply.
LYSH@MyHostName# configure configure# mysql_tsl yes configure# exit LYSH@MyHostName# show system mysql_tls mysql_tls : yes
This configuration does not affect the "radiusd" service. To enable the "radiusd" service TLS connection, you must configure it from the management interface(RADIUS> Settings> General> RADIUS Database Configuration).