Database MySQL / MariaDB

apt update && apt install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb

dnf install mariadb-server -y
systemctl start mariadb
systemctl enable mariadb

mysql_secure_installation

# As system root
mysql -u root -p

# As specific user
mysql -u username -p database_name

-- Show all databases
SHOW DATABASES;

-- Create a database
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Select a database
USE db_name;

-- Show tables
SHOW TABLES;

-- Delete a database
DROP DATABASE db_name;

-- Create a user
CREATE USER 'user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant all permissions on a database
GRANT ALL PRIVILEGES ON db_name.* TO 'user'@'localhost';

-- Apply permissions
FLUSH PRIVILEGES;

-- Remove a user
DROP USER 'user'@'localhost';

-- Change a user's password
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

# Backup of a single database
mysqldump -u root -p db_name > backup_db_name.sql

# Backup of all databases
mysqldump -u root -p --all-databases > backup_all.sql

# Compressed backup
mysqldump -u root -p db_name | gzip > backup_$(date +%Y%m%d).sql.gz

# From .sql file
mysql -u root -p db_name < backup.sql

# From .sql.gz file
gunzip < backup.sql.gz | mysql -u root -p db_name

[mysqld]
# Maximum packet size (useful for large imports)
max_allowed_packet = 256M

# Maximum connections
max_connections = 200

# Default charset
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

systemctl restart mariadb

systemctl status mariadb

# Installed version
mysql --version

# Active processes in MySQL
mysqladmin -u root -p processlist