4

In Debian/Ubuntu, after running apt install mariadb-server there is only a root user in the database, and one can only log in locally with e.g. sudo mariadb using socket authentication as root.

To actually use MariaDB for an app, one needs to create a new database, and a new user for the app and grant it permissions for the database. Additionally, if app is running on a remote host, one needs to allow MariaDB to accept connections over the network, and the user configuration needs to be aligned with it allowing remote connections.

What are the exact SQL commands and/or /etc/mysql/mariadb.conf.d/*.cnf configs a sysadmin should put in place with a freshly installed MariaDB server instance?

Otto
  • 444
  • 10

2 Answers2

3

In Debian/Ubuntu, what are the exact SQL commands to run to create a new database and user for your app after running apt install mariadb-server?

Start by creating the database and user with proper permission.

CREATE DATABASE

CREATE DATABASE myAppDB;

CREATE USER and GRANT privileges

In this case you might have two scenarios:

  1. The database is hosted locally
CREATE USER 'myAppUser'@'localhost' IDENTIFIED BY 'mySecurePassword';
  1. The database is hosted on a remote server
CREATE USER 'myAppUser'@'MyRemoteIpAddress' IDENTIFIED BY 'mySecurePassword';

I would not suggest using 'myAppUser'@'%' ---> It allows connection from any IP

As per the GRANTS it's up to you, if all changes/modification/creation would be done by the app , allow all privileges. If schema creation/modification/.. would be done by the DBA then allow only INSERT,SELECT,DELETE,UPDATE to the app.

GRANT ALL PRIVILEGES ON myAppDB.* TO 'myAppUser'@'localhost';
GRANT ALL PRIVILEGES ON myAppDB.* TO 'myAppUser'@'MyRemoteIpAddress';

To allow remote connections (if needed) to MariaDB you must change bind-address on /etc/mysql/mariadb.conf.d/50-server.cnf

Change

bind-address = 127.0.0.1

To

bind-address = MyRemoteIpAddress

Additionally, you could use bind-address = 0.0.0.0.0 or skip-bind-address, but carefully because MariaDB can listen on all network interfaces.

Open firewall port for the remote IP address

sudo iptables -A INPUT -p tcp -s <remote.ip.address> --dport 3306 -j ACCEPT

Some Server System Variables

[mysqld]
skip-name-resolve 
read_only = 0 # This is mostly useful when the server is a replica in that case it should be 1
innodb_buffer_pool_size = 1GB        # ~65-80% of available RAM on DB-only server
max_connections = 200             # Increase if you have to many clients/connections
connect_timeout = 10 
wait_timeout = 600 
max_allowed_packet = 128M

Logging


log_error = /var/log/mysql/error.log


I suggest keeping this on just for the starting days to monitor and tune any slow query, do not keep it on during production.


slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_slow_verbosity = query_plan,explain


log_bin=mariadb-bin
binlog_format =mixed    #choose best for your case


server-id   = 1

There are a lot more system variables , but with those above I think your are good to start.

Note some of the variables might be higher/lower for your particular case. Adjust accordingly.

I would prefer having a replica in which you can save backups and do all the selects to have a better performance. You can create a function to verify replica status, if it is up and running an 0 seconds behind source then use that connection. Something like I have done https://github.com/basha0110/PHP-Projects/blob/main/masterSlaveConnection.php

Ergest Basha
  • 5,369
  • 3
  • 7
  • 22
2

After some research I ended on these exact SQL commands. Sharing here for comments and in case others find them useful.

CREATING DATABASES AND USERS FOR APPS

On Debian/MariaDB running apt install mariadb-server will set up a sane and secure MariaDB server by default. The commands mariadb-install-db and mariadb-upgrade run automatically when needed.

The primary tasks for the administrator is to create new users and databases, and add custom settings in /etc/mysql/mariadb.conf.d/.

To add a custom database for an app, and create a custom password-authenticated user that has full access to that database over the network, run the following commands using the mariadb client (e.g., via sudo mariadb):

-- Create the database
CREATE DATABASE app_db;

-- Create the user 'app_user' with a secure password, allowing connections from any IP address
CREATE USER 'app_user'@'%' IDENTIFIED BY 'your_secure_password';


-- Grant full privileges to the 'app_user' user on the 'app_db' database
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'%';


-- Reload the privilege tables to apply changes
FLUSH PRIVILEGES;

Remember to replace 'your_secure_password' with a strong, unique password. Using '%' for the host allows connections from any IP address, which is less secure than restricting connections to a specific IP address or subnet if possible.

REMOTE CONNECTIONS OVER THE NETWORK AND TLS

For security reasons, the Debian package has enabled networking only on the loop-back device using bind-address in /etc/mysql/mariadb.conf.d/50-server.cnf. Check current setting by running:

SHOW VARIABLES LIKE 'bind_address';

Alternatively, check with sudo netstat -tlnp on what ports services are listening. If your connection is aborted immediately, check your firewall rules or network routes.

To allow remote connections from the network, the easiest way is to create a new configuration file and allow the MariaDB Server to listen for remote connections. To ensure passwords don't leak on the network, enforce that connections must use TLS encryption.

Create file /etc/mysql/mariadb.conf.d/99-server-customizations.cnf with contents:

[mariadbd]
# Listen for connections from anywhere
bind-address = 0.0.0.0
# Only allow TLS encrypted connections
require-secure-transport = on

For settings to take effect, restart the server:

systemctl restart mariadb

Note that in MariaDB 11.8 the TLS certificates are generated automatically, so there is no need to create them manually.

Test connections with e.g.

mariadb --user=app_user --password=your_secure_password  --host=192.168.1.66 -e '\s'
mariadb --user=root --password=your_secure_password  --host=192.168.1.66 --ssl
Otto
  • 444
  • 10