SQL

After install 

 ensure secure access  sudo mysql_secure_installation 

 

  Logging in  

 mysql -u USERNAME -p 

 add  -h HOSTNAME IP when logging in remotely 

 

 Creating a user 

 CREATE USER 'NEW_USERNAME'@'localhost' IDENTIFIED BY 'NEW_PASSWORD'; 

 

 Creating a database 

 CREATE DATABASE DATABASE_NAME 

 

 Allow user to modify Database 

 GRANT ALL PRIVILEGES ON DATABASE_NAME.* TO 'USERNAME'@'localhost'; 

 

 Update permissions 

 FLUSH PRIVILEGES; 

 

 Open Database to the world 

 

 Open file at  /etc/mysql/maridadb.conf.d/50-server.cnf 

 Edit line  bind-address = 127.0.0.1 to ->  bind-address = 0.0.0.0 

  Restart db service  systemctl restart mariadb 

 Allow port through firewall with  ufw allow 3306 

 

 

 Automated backup script 

 #!/bin/bash

# === Config ===

BACKUP_DIR="/var/backups/mysql"

DATE=$(date +"%Y-%m-%d_%H-%M")

DB_USER="backupuser"

DB_PASS="yourpassword"

# Create backup dir if not exist

mkdir -p "$BACKUP_DIR"

# Dump all databases

mysqldump -u "$DB_USER" -p"$DB_PASS" --all-databases --single-transaction > "$BACKUP_DIR/mariadb-$DATE.sql"

# Optional: Compress the backup

gzip "$BACKUP_DIR/mariadb-$DATE.sql"

# Delete backups older than 7 days

find "$BACKUP_DIR" -name "*.sql.gz" -type f -mtime +7 -delete