Skip to main content

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

  1. Open file at /etc/mysql/maridadb.conf.d/50-server.cnf
  2. Edit line bind-address = 127.0.0.1 to -> bind-address = 0.0.0.0
  3.  Restart db service systemctl restart mariadb
  4. 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

 

 

 

 

 

 

 

 

Back to top