q35-database-server

Énoncé§

Solve this question on: data-001

1. Install MariaDB and start it.
2. Run the secure installation procedure (set a root password, drop anonymous users).
3. Create a database appdb and a user app@'%' with password appsecret, granting only SELECT/INSERT/UPDATE on appdb.
4. Configure MariaDB to listen on the LAN interface (192.168.50.10) so the app server can connect.
5. Write a backup of appdb into /opt/course/35/appdb.sql.

Solution§

Install§

Debian:

sudo apt install mariadb-server mariadb-client
sudo systemctl enable --now mariadb

RHEL:

sudo dnf install mariadb-server mariadb
sudo systemctl enable --now mariadb

Step 2 — Secure installation§

sudo mysql_secure_installation

Answers the prompts about:

• root password (set one)
• remove anonymous users
• disallow root login remotely
• remove test database
• reload privilege tables

Step 3 — Database, user, grants§

sudo mysql -u root -p

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app'@'%' IDENTIFIED BY 'appsecret';
GRANT SELECT, INSERT, UPDATE ON appdb.* TO 'app'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'app'@'%';
\q

Host syntax:

• 'app'@'localhost' — Unix socket only
• 'app'@'192.168.50.%' — subnet
• 'app'@'%' — any host
• 'app'@'app-srv1.lfcs.lan'

Revoke / drop:

REVOKE INSERT ON appdb.* FROM 'app'@'%';
DROP USER 'app'@'%';
DROP DATABASE appdb;

Step 4 — Network binding§

Edit:

• Debian: /etc/mysql/mariadb.conf.d/50-server.cnf
• RHEL: /etc/my.cnf.d/server.cnf

[mysqld]
bind-address = 192.168.50.10
port         = 3306

Restart and verify:

sudo systemctl restart mariadb
ss -tlnp | grep 3306

Open the firewall if needed (see q07-network-packet-filtering):

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Test from the client side:

mysql -h 192.168.50.10 -u app -p appdb -e "SELECT NOW();"

Step 5 — Backup and restore§

Logical dump:

sudo mysqldump --single-transaction --routines --triggers appdb \
  > /opt/course/35/appdb.sql

# all databases
sudo mysqldump --all-databases --single-transaction > full.sql

# restore
sudo mysql appdb < /opt/course/35/appdb.sql

For large or hot databases, prefer mariabackup (physical backup):

sudo mariabackup --backup --target-dir=/var/backups/mariadb/$(date +%F) --user=root --password=...

PostgreSQL quick equivalents§

sudo apt install postgresql
sudo systemctl enable --now postgresql
sudo -u postgres psql

# inside psql:
CREATE DATABASE appdb;
CREATE USER app WITH PASSWORD 'appsecret';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app;

Network binding:

• /etc/postgresql/<ver>/main/postgresql.conf → listen_addresses = '*'
• /etc/postgresql/<ver>/main/pg_hba.conf → add host appdb app 192.168.50.0/24 md5

Dump:

sudo -u postgres pg_dump appdb > appdb.sql
sudo -u postgres pg_dumpall > full.sql

Useful day-to-day commands§

mysql -e "SHOW DATABASES;"
mysql -e "SHOW PROCESSLIST;"
mysql -e "SHOW VARIABLES LIKE 'bind%';"
mysql -e "SHOW STATUS LIKE 'Threads%';"
sudo mysqladmin status
sudo mysqladmin shutdown