[Ubuntu Server Setup] Lesson 6: Installing MySQL and Building a Web Service Database
![[Ubuntu Server Setup] Lesson 6: Installing MySQL and Building a Web Service Database](/uploads/editor/Ubuntu-서버-구축-6강-MySQL-설치와-웹서비스-데이터베이스-구축하기-20260708-905714.webp)
Where Do Websites Store Data?
Hello.
Last time, we learned how to install PHP and PHP-FPM to integrate with Nginx.
Now you can create HTML files and run PHP scripts.
But one crucial feature is still missing:
The space to store data.
If users sign up but there's nowhere to save their information, or if they post on a bulletin board without a place to record the content, you can't run a real web service.
This is where a database (Database) comes in. In this lesson, we'll install MySQL—the most commonly used database—and create a basic database from scratch.
What Is a Database?
In simple terms, a database is a structured space for storing data.
For example, if you run a blog, the following information would be stored:
User information
Posts
Comments
Categories
Announcements
Visit logs
If it's an online store, you'd store:
Product information
Order history
Purchase details
User data
All of this is managed in the database.
In short, it's the central hub for managing all critical information in a web service.
What Is MySQL?
MySQL is one of the most widely used relational databases (RDBMS) globally.
It powers many popular platforms including:
WordPress
Laravel
CodeIgniter
PHP-based bulletin boards
E-commerce sites
It's free to use and offers excellent performance, making it a popular choice for both personal servers and enterprise environments.
Installing MySQL
First, update the package list:
sudo apt update
Next, install the MySQL Server:
sudo apt install mysql-server -y
The installation may take a few minutes.
Once complete, the MySQL service will start automatically.
Checking MySQL Service Status
After installation, verify that the service is running properly:
sudo systemctl status mysql
If everything is working correctly, you'll see active (running).
This confirms MySQL is functioning as expected.
Setting Up Auto-Start for MySQL
Configure MySQL to start automatically after a reboot:
sudo systemctl enable mysql
To disable auto-start, use:
sudo systemctl disable mysql
For production servers, it's recommended to keep auto-start enabled.
Securing MySQL
After installation, it's essential to complete security settings:
sudo mysql_secure_installation
This process lets you configure:
Password policies
Removing anonymous users
Deleting test databases
Restricting remote root login
We strongly recommend running this on production servers.
Connecting to MySQL
Now, let's connect to the MySQL console:
sudo mysql
If successful, you'll see the mysql> prompt.
You can now enter SQL commands.
Creating a Database
Let's create a database for our blog:
CREATE DATABASE blogdb;
You can name your database anything you like, such as:
mysite
wordpress
homepage
Viewing Database List
To check existing databases, run:
SHOW DATABASES;
If your new database appears in the list, it was created successfully.
Selecting a Database
Select the database you'll work with:
USE blogdb;
All subsequent tables will be created within this database.
Creating Your First Table
Let's create a simple table for a bulletin board:
CREATE TABLE board (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
created_at DATETIME
);
Your blog post storage is now ready.
Verifying Tables
To view tables in the current database, run:
SHOW TABLES;
You should see your new board table listed.
Creating a User Account
For production environments, it's safer to create a dedicated user account rather than using the root account directly.
Create a new user with:
CREATE USER 'bloguser'@'localhost'
IDENTIFIED BY 'password';
This creates a dedicated account for your website.
Granting Permissions
Assign database permissions to the new user:
GRANT ALL PRIVILEGES
ON blogdb.*
TO 'bloguser'@'localhost';
Finally, apply the changes:
FLUSH PRIVILEGES;
Your PHP application can now connect to the database using this account.
What Is SQL?
MySQL uses SQL (Structured Query Language) for data management.
The core SQL commands you'll use are:
CREATE: Create objects
SELECT: Retrieve data
INSERT: Add new records
UPDATE: Modify existing records
DELETE: Remove records
Mastering these five commands is enough to develop most web applications.
Exiting MySQL
To exit the MySQL console:
EXIT;
Or use:
QUIT;
This will end your session.
How PHP and MySQL Work Together
Previously, our setup was:
Browser → Nginx → PHP
Now we've completed the structure:
Browser
↓
Nginx
↓
PHP
↓
MySQL
PHP stores and retrieves data from MySQL for display to users.
This is the standard architecture used by most websites today.
Backups Are Essential
Databases store critical information, so regular backups are crucial.
You can easily back up your database using:
mysqldump
We'll cover backup procedures in detail in future practical lessons.
What We Learned Today
In this lesson, we installed MySQL on Ubuntu and created a database. We also set up user accounts, permissions, and tables to prepare for real web service operations.
You now have a fully configured server with Nginx, PHP, and MySQL ready for development.
Next, we'll connect a domain name and learn how to run multiple websites on one server using Virtual Hosts.
Key Takeaways
MySQL is the database system used to store data.
It can be easily installed on Ubuntu using apt commands.
Always run
mysql_secure_installationfor basic security setup.Create databases with
CREATE DATABASE.Use
CREATE USERandGRANTto create secure, dedicated accounts.PHP connects to MySQL for data storage and retrieval.
Regular database backups are essential for data protection.
Next Lesson Preview
7th Lesson: Connecting Domains, DNS Configuration, and Virtual Hosts (Running Multiple Sites on One Server)
In the next lesson, we'll connect a purchased domain to your Ubuntu server and use Nginx's Virtual Host feature to run multiple websites from a single server.
FAQ
Why is a database necessary for websites?
A database stores essential information like user accounts, posts, comments, and orders. Without it, websites couldn't save or retrieve data needed for functionality.
What are the basic SQL commands used with MySQL?
The core SQL commands are CREATE (to make new objects), SELECT (to view data), INSERT (to add records), UPDATE (to modify data), and DELETE (to remove data). Mastering these five is enough for most web development tasks.
How do I secure my MySQL installation?
Run the mysql_secure_installation command after installation. This sets password policies, removes anonymous users, deletes test databases, and restricts remote root access.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [Ubuntu 서버 구축] 6강. MySQL 설치와 웹서비스 데이터베이스 구축하기