Installing a database

This guide covers installing and securing MySQL and PostgreSQL on your BareMeta server, creating databases and users, and setting up backups.

MySQL or PostgreSQL?

Both are excellent choices. Here's a quick comparison to help you decide:

• MySQL — widely used, great for web applications, WordPress, and PHP apps. Very well documented with lots of tutorials available.
• PostgreSQL — more feature-rich, better for complex queries and data integrity. Preferred by many developers for modern applications.

If you're not sure, go with MySQL for web apps and WordPress, PostgreSQL for everything else.

sudo apt update
sudo apt install mysql-server -y

# Start MySQL and enable on boot
sudo systemctl enable mysql
sudo systemctl start mysql

# Check it's running
sudo systemctl status mysql

sudo mysql_secure_installation

# Log in to MySQL
sudo mysql

# Create a database
CREATE DATABASE myapp;

# Create a user with a strong password
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

# Grant permissions
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp_user'@'localhost';

# Apply changes
FLUSH PRIVILEGES;

# Exit
EXIT;

sudo apt update
sudo apt install postgresql postgresql-contrib -y

# Start and enable on boot
sudo systemctl enable postgresql
sudo systemctl start postgresql

# Check it's running
sudo systemctl status postgresql

# Switch to the postgres system user
sudo -u postgres psql

# Create a user
CREATE USER myapp_user WITH PASSWORD 'StrongPassword123!';

# Create a database owned by that user
CREATE DATABASE myapp OWNER myapp_user;

# Exit
\q

# Connect to your database
psql -U myapp_user -d myapp -h localhost

# List all databases
\l

# List tables in current database
\dt

# Exit
\q

Securing your database

A few important rules:

• Never expose your database port to the internet — keep port 3306 (MySQL) or 5432 (PostgreSQL) closed in your firewall. Applications should connect via localhost.
• Use strong, unique passwords for all database users
• Don't use root/postgres for your application — always create a dedicated user with minimal permissions
• Grant only necessary permissions — if your app only reads data, grant SELECT only

SSH tunnel for remote access

# On your local machine — creates a tunnel to the database
ssh -L 3306:localhost:3306 ubuntu@YOUR_SERVER_IP

# Then connect your local database client to localhost:3306

Backing up your database

Always back up your database regularly. A single command can dump the entire database to a file:

MySQL backup

# Create a backup
mysqldump -u myapp_user -p myapp > backup_$(date +%Y%m%d).sql

# Restore from backup
mysql -u myapp_user -p myapp < backup_20260405.sql

PostgreSQL backup

# Create a backup
pg_dump -U myapp_user myapp > backup_$(date +%Y%m%d).sql

# Restore from backup
psql -U myapp_user myapp < backup_20260405.sql

Automate daily backups with cron

# Edit crontab
crontab -e

# Add this line for daily backups at 2am
0 2 * * * mysqldump -u myapp_user -pYourPassword myapp > /home/ubuntu/backups/db_$(date +\%Y\%m\%d).sql