MySQL 8.0 + phpMyAdmin AMI Guide

Getting Started

Quick Start Guide

Follow these steps to access your MySQL database and phpMyAdmin web interface.

SSH to Your Instance

Connect to your EC2 instance using SSH with your key pair.

ssh -i your-key.pem ec2-user@<your-instance-ip>

Find Your Credentials

View the auto-generated MySQL and phpMyAdmin passwords.

sudo cat /root/.mysql_credentials

Access phpMyAdmin

Open phpMyAdmin in your browser using your instance's public IP.

http://<your-instance-ip>/phpmyadmin

Connect to MySQL

Connect via command line or add a server in phpMyAdmin.

# On the instance:
mysql -u root -p

# From remote:
mysql -h <your-instance-ip> -u root -p

Authentication

Credentials & Connection Details

All credentials are automatically generated and stored securely on your instance.

MySQL

Username: root
Port: 3306
Password: See credentials file
SSL: Enabled (TLSv1.2+)

phpMyAdmin

URL: http://<your-ip>/phpmyadmin
Username: root
Password: See credentials file

Credentials File Location

sudo cat /root/.mysql_credentials

Connection String Format

mysql://root:<password>@<your-instance-ip>:3306/mysql?ssl-mode=REQUIRED

Web Interface

Using phpMyAdmin

phpMyAdmin is a powerful web-based administration tool for MySQL. Here's how to get started.

Login to phpMyAdmin

Open your browser and navigate to the phpMyAdmin URL.

http://<your-instance-ip>/phpmyadmin

Username: root
Password: (from credentials file)

Create a Database

Create a new database for your application.

1. Click Databases tab at the top
2. Enter database name in "Create database" field
3. Select utf8mb4_unicode_ci collation
4. Click Create

Run SQL Queries

Use the SQL tab to execute SQL commands.

1. Select your database from the left panel
2. Click the SQL tab
3. Type your SQL query in the editor
4. Click Go to execute
5. View results below the query editor

Create a User

Create application-specific database users.

1. Click User accounts tab
2. Click Add user account
3. Enter username and password
4. Set host to % for remote access
5. Grant privileges and click Go

Common phpMyAdmin Tasks

Create Table

Select database → Create table
Define columns, types, indexes, and constraints.

Import Data

Select database → Import tab
Upload SQL, CSV, or other supported formats.

Export/Backup

Select database → Export tab
Choose SQL format for full backup.

User Management

Click User accounts tab
Add, modify, or remove database users.

View Table Data

Select table → Browse tab
View, edit, and delete rows directly.

Server Status

Click Status tab
Monitor connections, queries, and performance.

phpMyAdmin Tips

Use localhost when connecting from phpMyAdmin since it runs on the same server.
Always use utf8mb4 character set for full Unicode support including emojis.
Access Query history via the SQL tab to re-run previous queries.
Use Export with "Custom" option for fine-grained backup control.

Best Practices

Security Recommendations

Follow these steps to secure your MySQL deployment.

Important: Change the default passwords immediately after your first login. The pre-generated passwords should only be used for initial access.

Change Passwords

Update MySQL root password via command line or phpMyAdmin interface.

Security Groups

Restrict port 3306 access to only your IP addresses or VPC CIDR range.

SSL/TLS Enabled

SSL is enabled by default. All connections use TLS encryption.

Change MySQL Password

mysql -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'your-new-secure-password';"

Need Help?

Troubleshooting

Common issues and how to resolve them.

Can't Connect Remotely

Symptom: Connection refused or timeout when connecting from your machine.

Solution:

1. Check your EC2 Security Group allows inbound on port 3306
2. Verify your IP is whitelisted in the security group
3. Ensure MySQL is running: sudo systemctl status mysqld

phpMyAdmin Login Fails

Symptom: Can't log in to phpMyAdmin web interface.

Solution:

1. Verify credentials: sudo cat /root/.mysql_credentials
2. Check Apache is running: sudo systemctl status httpd
3. Restart services: sudo systemctl restart httpd mysql

phpMyAdmin Blank Page

Symptom: phpMyAdmin shows a blank white page.

Solution:

1. Check PHP errors: sudo tail -50 /var/log/httpd/error.log
2. Verify PHP is installed: php -v
3. Restart Apache: sudo systemctl restart httpd

Access Denied Error

Symptom: "Access denied for user 'root'@'localhost'"

Solution:

1. Check the password from credentials file
2. Reset password if needed:
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';"
3. Flush privileges: FLUSH PRIVILEGES;

Quick Answers

Frequently Asked Questions

Common questions and quick solutions.

How do I reset my MySQL password?

sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';"

How do I check MySQL logs?

sudo tail -100 /var/log/mysql/error.log

How do I restart MySQL?

sudo systemctl restart mysqld

How do I restart Apache/phpMyAdmin?

sudo systemctl restart httpd

Where is MySQL data stored?

Data is stored on a separate EBS volume:

/var/lib/mysql

How do I check service status?

sudo systemctl status mysqld httpd

Performance

Instance Sizing Guide

Choose the right EC2 instance type for your workload.

Use Case	Instance Type	vCPUs	RAM	Recommended For
Development	`t3.small`	2	2 GB	Testing, learning, small dev projects
Small Production	`t3.medium`	2	4 GB	Small apps, low traffic websites
Medium Production	`m6i.large`	2	8 GB	Medium traffic, business applications
Large Production	`m6i.xlarge`	4	16 GB	High traffic, enterprise workloads
High Performance	`r6i.xlarge`	4	32 GB	Memory-intensive, analytics, large datasets

Auto-Tuning Note

MySQL automatically tunes itself on first boot based on your instance's RAM and CPU.
You can resize your instance at any time - MySQL will re-tune on next reboot.
For production, use m6i or r6i instances for consistent performance (not burstable).

Data Protection

Backup & Recovery

Protect your data with regular backups.

EBS Snapshots (Recommended)

The easiest way to back up your entire database.

Go to EC2 Console → Volumes
Select the data volume
Actions → Create Snapshot
Enable automated snapshots via AWS Backup

mysqldump (Logical Backup)

Export databases to SQL files.

                        # Backup single database
mysqldump -u root -p mydb > mydb_backup.sql

# Backup all databases
mysqldump -u root -p --all-databases > all_databases.sql

# Compressed backup
mysqldump -u root -p mydb | gzip > mydb.sql.gz
                    

Restore from Backup

Restore databases from SQL files.

                        # Restore from SQL
mysql -u root -p mydb < mydb_backup.sql

# Restore from compressed
gunzip < mydb.sql.gz | mysql -u root -p mydb

# Create db and restore
mysql -u root -p -e "CREATE DATABASE newdb;"
mysql -u root -p newdb < mydb_backup.sql
                    

Backup Schedule

Recommended backup frequency:

Daily: EBS snapshots (automated)
Weekly: mysqldump for off-site storage
Before changes: Manual snapshot before upgrades
Retention: Keep 7 daily, 4 weekly snapshots

Performance

Auto-Tuning

MySQL is automatically configured based on your instance size.

How It Works

On first boot, the auto-tuning service detects your instance's RAM and CPU count, then calculates optimal MySQL settings.

innodb_buffer_pool_size: 50-70% of RAM
innodb_log_file_size: Based on buffer pool
max_connections: Based on available memory

Configuration Files

Key configuration and log file locations:

Config: /etc/my.cnf
Data Dir: /var/lib/mysql
Error Log: /var/log/mysql/error.log
phpMyAdmin: /etc/phpmyadmin