database Configuration File

Database configuration file

Location: `/batm/config/database`

Mandatory entries with default values

db_name=batm
db_username=batm
db_password=batm
db_host=127.0.0.1
db_port=3306

Optional DB query entries with default values

Available from 20210808 release.

Note: the two entries below are different for Master and Admin (CAS GUI)

db_connection_params_master=useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&autoReconnect=true&useSSL=false
db_connection_params_admin=useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8&autoReconnect=true&serverTimezone=UTC&useCursorFetch=true&enabledTLSProtocols=TLSv1,TLSv1.1,TLSv1.2,TLSv1.3

You can specify these entries when you need to provide different or additional query (connection URL) parameters (e.g. SSL settings).

Don’t change the value in standalone.xml’s <connection-url> tag, use this config file instead.

MySQL Command Line Options

When in a need to customize mysql command line options (e.g. in batm-manage backup/restore command) please refer to https://dev.mysql.com/doc/refman/5.7/en/option-files.html to use Global options to define your custom parameters.

Available options: https://dev.mysql.com/doc/refman/5.7/en/connection-options.html

Root passwords

The MySQL root password is required for database restores. Ubuntu (by default) doesn’t set a password for the MySQL “root” user during installation. The schema used by MySQL is not as insecure as you might think, but it could still be hardened nonetheless. A missing root password may cause issues during restores, depending upon the version you use.

The MySQL “root” user and your Ubuntu “root” user passwords are not the same thing.

Creating a root password is recommended for improved security.

If you currently don’t have a root password:

mysql -u root -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpass'; FLUSH PRIVILEGES;"

replace rootpass with any memorable root password.

If you have a root password and wish to change it:

mysql -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpass'; FLUSH PRIVILEGES;"

replace rootpass with any memorable root password, then
enter the old password when requested.

If you’ve lost or forgotten your old root password, please refer to this excellent article from Digital Ocean:

https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password-on-ubuntu-20-04

remember, you are using MySQL (not MariaDB). Follow the correct instructions.

How to examine your password configuration:

To test if you have a MySQL password set, use:

mysql -u root -e "select user, host, plugin from mysql.user where user = 'root';"

This is an example of an unset (or improperly set) password:

the insecure authorization plugin is using “auth_socket” (which is native to newer Ubuntu versions and the default on DO droplets).

This is what should happen:

mysql -u root -p

Enter the (now required) password, and then type (at the mysql prompt):

select user, host, plugin from mysql.user where user = 'root';

Perfect.

Executing MySQL from the command line

Display your CAS db credentials:

sudo cat /batm/config/database

You may use this info in the next step.

Source the file (makes the next step easier, but not absolutely required)

sudo bash
source /batm/config/database
echo $db_name

If you encounter an error, then you’ll have to type the fields manually (below).
If you choose to skip this step, then you’ll have to enter the mysql manually (below).
The last command should report the same information as contained in the database config file.

Assemble the MySQL statement

sql="INSERT THE SQL COMMAND HERE AND INCLUDE THE QUOTATIONS AS SHOWN"

Example: sql="delete from flyway_schema_history where version = '1.202502180000';"

Execute the MySQL statement

mysql --database=$db_name -u $db_username -p$db_password -e "$sql"

If the command doesn’t generate an error, then the execution was successful.

Clean up

unset db_name db_username db_password db_host db_port sql
exit

This removes the variables from memory, erasing any security vulnerability from their existence.
Exit the shell.

database Configuration File