Setup MySQL Database on VPS - Complete Guide

MySQL Installation and Configuration

Step 1: Install MySQL

sudo apt update
sudo apt install mysql-server -y

Step 2: Secure MySQL

sudo mysql_secure_installation
# Answer: Y to all questions
# Set strong root password

Step 3: Create Database and User

sudo mysql

CREATE DATABASE myapp_db;
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Step 4: Allow Remote Access (Optional)

# Edit MySQL config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Change bind-address
bind-address = 0.0.0.0

# Restart MySQL
sudo systemctl restart mysql

# Create remote user
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON myapp_db.* TO 'remote_user'@'%';

Common MySQL Commands

# Show databases
SHOW DATABASES;

# Show tables
USE myapp_db;
SHOW TABLES;

# Backup database
mysqldump -u root -p myapp_db > backup.sql

# Restore database
mysql -u root -p myapp_db < backup.sql

WebHostWare VPS includes MySQL with automated backups.