Database Server — MySQL, PostgreSQL, Redis — DevOps & Server Engineering

🐬 MySQL / MariaDB

# Install
sudo apt install mysql-server -y
# OR MariaDB (drop-in replacement)
sudo apt install mariadb-server -y

# Secure setup (mandatory!)
sudo mysql_secure_installation
# - Set root password
# - Remove anonymous user
# - Disallow remote root
# - Remove test DB
# - Reload privilege

# Login
sudo mysql                          # via socket
mysql -u root -p                    # password

👤 User & Database

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER "appuser"@"localhost" IDENTIFIED BY "StrongPass123!";
GRANT ALL PRIVILEGES ON myapp.* TO "appuser"@"localhost";
FLUSH PRIVILEGES;

# Remote (only specific IP!)
CREATE USER "appuser"@"1.2.3.4" IDENTIFIED BY "...";

# Show
SHOW DATABASES;
SHOW GRANTS FOR "appuser"@"localhost";

⚙️ MySQL Tuning

# /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 127.0.0.1           # local only!
max_connections = 200
innodb_buffer_pool_size = 1G       # 70% of RAM (DB-only server)
innodb_log_file_size = 256M
slow_query_log = 1
long_query_time = 2

sudo systemctl restart mysql

🐘 PostgreSQL

sudo apt install postgresql postgresql-contrib -y
sudo systemctl enable --now postgresql

# Login
sudo -u postgres psql

# Create DB & user
CREATE DATABASE myapp;
CREATE USER appuser WITH ENCRYPTED PASSWORD "StrongPass123!";
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;
\c myapp
GRANT ALL ON SCHEMA public TO appuser;

# Connect
psql -U appuser -d myapp -h localhost

# Configure
# /etc/postgresql/16/main/postgresql.conf
listen_addresses = "localhost"
shared_buffers = 1GB
work_mem = 16MB

# /etc/postgresql/16/main/pg_hba.conf (auth)
host    all   all   127.0.0.1/32   md5

sudo systemctl restart postgresql

🔴 Redis

sudo apt install redis-server -y

# /etc/redis/redis.conf
bind 127.0.0.1                    # never public!
requirepass "StrongRedisPass!"     # password mandatory
maxmemory 512mb
maxmemory-policy allkeys-lru

sudo systemctl restart redis-server

# Test
redis-cli
> AUTH StrongRedisPass!
> PING
PONG
> SET key value
> GET key

💾 Backup

# MySQL
mysqldump -u root -p myapp > backup.sql
mysqldump --all-databases --single-transaction -u root -p > all.sql
# With gzip
mysqldump -u root -p myapp | gzip > backup.sql.gz
# Restore
mysql -u root -p myapp < backup.sql

# PostgreSQL
pg_dump -U postgres myapp > backup.sql
pg_dumpall -U postgres > all.sql
# Restore
psql -U postgres myapp < backup.sql

# Redis
# RDB snapshot in /var/lib/redis/dump.rdb (auto)
# AOF log enable for durability
appendonly yes

⏰ Auto Backup Cron

0 2 * * * mysqldump -u root -p$MYSQL_PASS --all-databases | gzip > /backup/db_$(date +\%Y\%m\%d).sql.gz && find /backup -mtime +7 -delete

🛡️ Database Security

Bind 127.0.0.1 — never public
Strong password (16+ char)
Per-app user with limited privilege
Regular backup + offsite (S3)
SSL for remote connection (RDS-style)
Audit log enable (compliance)

🎓 মূল কথা ⭐

DB server bind to localhost — public exposure = hack guaranteed। Daily backup automate। Redis password set mandatory। Buffer pool tuning = 10x performance।