MariaDB 10.6 On Ubuntu 22.04 Hardening

Start MariaDB service: sudo systemctl start mariadb

Stop MariaDB service: sudo systemctl stop mariadb

Restart MariaDB service: sudo systemctl restart mariadb

Check service status: sudo systemctl status mariadb

Enable auto-start on boot: sudo systemctl enable mariadb

Connect as root user: sudo mysql -u root -p

Connect with specific user: mysql -u username -p

Connect to specific database: mysql -u username -p database_name

Exit MySQL prompt: EXIT;

Show all databases: SHOW DATABASES;

Create new database: CREATE DATABASE database_name;

Use a database: USE database_name;

Show tables in current database: SHOW TABLES;

Describe table structure: DESCRIBE table_name;

Create new user: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Grant all privileges on database: GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

Grant specific privileges: GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

Apply privilege changes: FLUSH PRIVILEGES;

Show user privileges: SHOW GRANTS FOR 'username'@'localhost';

Remove user: DROP USER 'username'@'localhost';

Backup single database: mysqldump -u username -p database_name > backup.sql

Backup all databases: mysqldump -u root -p --all-databases > full_backup.sql

Backup with compression: mysqldump -u username -p database_name | gzip > backup.sql.gz

Restore database: mysql -u username -p database_name < backup.sql

Restore compressed backup: gunzip < backup.sql.gz | mysql -u username -p database_name

Check MariaDB version: SELECT VERSION();

Show active processes: SHOW PROCESSLIST;

Kill a process: KILL process_id;

Check table status: SHOW TABLE STATUS;

Repair a table: REPAIR TABLE table_name;

Optimize a table: OPTIMIZE TABLE table_name;

View error logs: sudo tail -f /var/log/mysql/error.log

View slow query logs: sudo tail -f /var/log/mysql/slow.log

Main configuration file: /etc/mysql/my.cnf

Custom configuration directory: /etc/mysql/conf.d/

After config changes, restart: sudo systemctl restart mariadb

Run security script: sudo mysql_secure_installation

Change user password: ALTER USER 'username'@'localhost' IDENTIFIED BY 'newpassword';

Remove anonymous users: DELETE FROM mysql.user WHERE User='';

Remove remote root access: DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

Check if service is running: sudo systemctl is-active mariadb

Check listening port: sudo netstat -tlnp | grep 3306

Reset root password if locked out: sudo systemctl stop mariadb sudo mysqld_safe --skip-grant-tables & mysql -u root Then run: ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

Check database sizes: SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

Show storage engines: SHOW ENGINES;

Check connection count: SHOW STATUS LIKE 'Threads_connected';