Step 3: Set Up MySQL Database on Lightsail

Lecture Slides

Either click on the slide area below or click here to view it in fullscreen. Use your keypad to navigate the slides.

A PDF printable handout version of the slides is available here

Introduction

In this step, you’ll install and configure MySQL on your Lightsail instance to serve as the production database for your Spring Boot application. We’ll set up proper security, create the application database, and configure it for optimal performance.

Understanding Database Options on Lightsail

While AWS offers managed database services like RDS, installing MySQL directly on your Lightsail instance is often more cost-effective for smaller applications. This approach gives you:

Full control over database configuration
No additional monthly charges beyond your instance cost
Simplified networking (database and application on same server)
Easier backup and maintenance for small-scale deployments

When to Consider RDS

If your application grows to handle thousands of concurrent users or requires high availability, consider migrating to Amazon RDS for better scalability and automated backups.

Installing MySQL on Ubuntu

Step 1: Connect to Your Lightsail Instance

First, connect to your Lightsail instance using the browser-based SSH client:

In the Lightsail console, click on your instance name
Click the “Connect using SSH” button
Wait for the terminal to load

Step 2: Update System Packages

Always start by updating your system packages to ensure security and compatibility:

sudo apt update && sudo apt upgrade -y

Step 3: Install MySQL Server

Install MySQL server and client packages:

sudo apt install mysql-server mysql-client -y

Installation Progress

The MySQL installation process may take 2-3 minutes. The system will automatically start the MySQL service after installation.

Securing MySQL Installation

Step 1: Run MySQL Security Script

MySQL comes with a security script that removes insecure default settings:

sudo mysql_secure_installation

Step 2: Configure Security Settings

When prompted, configure the following settings:

VALIDATE PASSWORD COMPONENT: Choose Y for better security
Password validation policy: Select 2 (STRONG)
Set root password: Create a strong password and save it securely
Remove anonymous users: Choose Y
Disallow root login remotely: Choose Y
Remove test database: Choose Y
Reload privilege tables: Choose Y

Save Your Root Password

Write down your MySQL root password in a secure location. You’ll need it for database administration tasks.

Creating the Application Database

Step 1: Access MySQL as Root

Log into MySQL using the root account:

sudo mysql -u root -p

Enter the root password you created during the security setup.

Step 2: Create Application Database

Create a dedicated database for your Spring Boot application:

CREATE DATABASE myapp_prod CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Step 3: Create Application User

Create a dedicated MySQL user for your application with limited privileges:

CREATE USER 'springapp'@'localhost' IDENTIFIED BY 'your-secure-app-password';
GRANT ALL PRIVILEGES ON myapp_prod.* TO 'springapp'@'localhost';
FLUSH PRIVILEGES;

Password Security

Use a strong, unique password for your application user. Consider using a password manager to generate and store it securely.

Step 4: Verify Database Setup

Test your new database and user:

-- Switch to the new database
USE myapp_prod;

-- Show current user and database
SELECT USER(), DATABASE();

-- Exit MySQL
EXIT;

Configuring MySQL for Production

Step 1: Edit MySQL Configuration

Open the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Step 2: Optimize MySQL Settings

Add or modify these settings in the [mysqld] section for better performance:

# Performance tuning for small to medium applications
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2

# Character set configuration
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Connection limits
max_connections = 100
max_connect_errors = 10000

# Query cache (for MySQL 5.7 and earlier)
query_cache_type = 1
query_cache_size = 64M

Step 3: Restart MySQL Service

Apply the configuration changes:

sudo systemctl restart mysql
sudo systemctl status mysql

Service Status

The systemctl status mysql command should show the service as “active (running)” in green text.

Testing Database Connection

Step 1: Test Local Connection

Verify you can connect with your application user:

mysql -u springapp -p myapp_prod

Step 2: Create a Test Table

Once connected, test database operations:

-- Create a simple test table
CREATE TABLE test_connection (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert test data
INSERT INTO test_connection (message) VALUES ('Database connection successful!');

-- Verify the data
SELECT * FROM test_connection;

-- Clean up
DROP TABLE test_connection;
EXIT;

Configuring Firewall (Optional)

If you plan to connect to MySQL from external applications for testing, you can configure the firewall:

# Allow MySQL connections (only if needed for external access)
sudo ufw allow 3306/tcp

# Check firewall status
sudo ufw status

Security Consideration

Only open MySQL port 3306 if you absolutely need external database access. For production applications, keep the database local to the instance for better security.

Setting Up Automated Backups

Step 1: Create Backup Directory

sudo mkdir -p /backup/mysql
sudo chown mysql:mysql /backup/mysql

Step 2: Create Backup Script

Create a simple backup script:

sudo nano /usr/local/bin/mysql-backup.sh

Add the following content:

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="myapp_prod"
DB_USER="springapp"
DB_PASS="your-secure-app-password"

# Create backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/${DB_NAME}_${DATE}.sql

# Keep only last 7 days of backups
find $BACKUP_DIR -name "${DB_NAME}_*.sql" -mtime +7 -delete

echo "Backup completed: ${DB_NAME}_${DATE}.sql"

Step 3: Make Script Executable and Test

sudo chmod +x /usr/local/bin/mysql-backup.sh
sudo /usr/local/bin/mysql-backup.sh

Updating Spring Boot Configuration

Now that your database is ready, update your production profile configuration to match your MySQL setup:

# src/main/resources/application-prod.properties

# Production MySQL database configuration
spring.datasource.url=jdbc:mysql://localhost:3306/myapp_prod
spring.datasource.username=springapp
spring.datasource.password=your-secure-app-password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# Production-specific JPA settings
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.show-sql=false

# Production logging
logging.level.root=WARN
logging.level.org.springframework.web=INFO
logging.level.org.hibernate=ERROR

Environment Variables

In the next step, we’ll show you how to externalize these database credentials using environment variables for better security.

Summary

In this step, you’ve successfully:

Installed and secured MySQL on your Lightsail instance
Created a dedicated database and user for your Spring Boot application
Configured MySQL for optimal performance
Set up basic backup functionality
Prepared your database configuration for production deployment

Your Lightsail instance now has a properly configured MySQL database ready to support your Spring Boot application. The database is secured with appropriate user permissions and optimized for production workloads.

Key Takeaways

Security First: Always run mysql_secure_installation and use strong passwords
Dedicated Users: Create application-specific database users with minimal required privileges
Performance Tuning: Configure MySQL settings appropriate for your instance size
Regular Backups: Implement automated backup strategies from the beginning
Connection Testing: Always verify database connectivity before deploying your application

In the next step, you’ll learn how to deploy your Spring Boot application to the Lightsail server and connect it to this MySQL database.