MySQL for Web Hosting — Databases, Users, Privileges & WordPress
We now move to the database layer.
Your WordPress request currently travels like this:
Browser
↓
DNS
↓
IP
↓
Nginx
↓
PHP-FPM
↓
WordPress
↓
MySQL
The purpose of MySQL is to store and retrieve the structured data WordPress needs.
1. What Is MySQL?
MySQL is a relational database management system.
It stores information in structured tables.
For WordPress, that includes things such as:
posts
pages
users
settings
comments
categories
menus
plugin data
theme data
metadata
2. WordPress Is Not the Database
This distinction is important.
WordPress
=
application
MySQL
=
database
The relationship is:
WordPress
↓
SQL queries
↓
MySQL
↓
data
3. What Is a Database?
A database is a logical collection of related data.
For example:
wordpress_learn
might contain tables such as:
wp_posts
wp_users
wp_options
wp_terms
wp_comments
4. Database Server vs Database
These are different.
MySQL server
The running database service.
Database
A logical collection inside that server.
Conceptually:
MySQL Server
│
├── database1
├── database2
├── database3
└── database4
5. Your Hosting Server
You may have:
Ubuntu VPS
│
├── Nginx
├── PHP-FPM
├── MySQL
├── WordPress site 1
├── WordPress site 2
└── WordPress site 3
All websites can use the same MySQL server while having separate databases.
6. One Database Per Website
For hosting, a good model is:
site1.com
↓
database_site1
site2.com
↓
database_site2
site3.com
↓
database_site3
This makes management and isolation easier.
7. Why Not One Database for Everything?
You could technically put many websites into one database.
But it creates problems:
harder backup
harder restore
harder migration
harder isolation
harder troubleshooting
Therefore, for your hosting platform:
One website → one database is a useful standard.
8. Database User
A database should also have a dedicated database user.
For example:
Website:
learn.cresignsys.com
Database:
learn_cresignsys
User:
learn_user
The application uses that user to access its database.
9. Why Dedicated Users?
Suppose:
site1
site2
both use the same MySQL account.
If site1 is compromised, the attacker may potentially gain access to site2’s database as well.
Instead:
site1 → db_user1 → database1
site2 → db_user2 → database2
provides better isolation.
10. MySQL Architecture
Conceptually:
WordPress
│
│ SQL
▼
MySQL Server
│
├── Database 1
│
├── Database 2
│
└── Database 3
11. Check MySQL
On Ubuntu:
sudo systemctl status mysql
You want:
Active: active (running)
12. Check MySQL Version
Run:
mysql --version
You may see something similar to:
mysql Ver 8.0...
The exact version depends on your installation.
13. Enter MySQL
On a server using local administrative authentication, you may use:
sudo mysql
You should then see:
mysql>
14. Exit
To leave MySQL:
EXIT;
or:
\q
15. List Databases
Inside MySQL:
SHOW DATABASES;
You may see:
information_schema
mysql
performance_schema
sys
plus your WordPress databases.
16. System Databases
MySQL includes databases used internally.
For example:
mysql
information_schema
performance_schema
sys
Don’t delete these casually.
17. Create a Database
Example:
CREATE DATABASE learn_cresignsys;
Now:
learn_cresignsys
exists.
18. Better Character Set
For WordPress, Unicode support is important.
A commonly used configuration is:
CREATE DATABASE learn_cresignsys
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
The exact collation choice can vary by MySQL version and application requirements.
19. Why utf8mb4?
It supports a much broader range of Unicode characters than the older MySQL utf8 implementation.
For example:
English
Malayalam
Hindi
Arabic
Chinese
emoji
can all be represented properly when the application and database configuration are compatible.
20. Database User
Create a dedicated user:
CREATE USER 'learn_user'@'localhost'
IDENTIFIED BY 'STRONG_PASSWORD';
Use a strong, unique password.
Do not reuse your Linux password.
21. Grant Privileges
Give the user access to its own database:
GRANT ALL PRIVILEGES
ON learn_cresignsys.*
TO 'learn_user'@'localhost';
Now:
learn_user
can operate on:
learn_cresignsys
22. What Does *.* Mean?
This is important.
database.*
means:
All tables in this database.
For example:
learn_cresignsys.*
means:
all tables inside learn_cresignsys
23. Why Not Give Global Privileges?
Avoid doing this for normal WordPress users:
GRANT ALL PRIVILEGES ON *.* ...
That could give access to every database.
Instead:
learn_user
↓
learn_cresignsys.*
This follows least privilege.
24. Least Privilege
A core security principle:
Give an application only the permissions it needs.
For example:
Site 1
↓
Database 1
not:
Site 1
↓
Every database
25. WordPress Database Credentials
WordPress stores database connection information in:
wp-config.php
Typically:
DB_NAME
DB_USER
DB_PASSWORD
DB_HOST
26. Example
Conceptually:
define( 'DB_NAME', 'learn_cresignsys' );
define( 'DB_USER', 'learn_user' );
define( 'DB_PASSWORD', 'STRONG_PASSWORD' );
define( 'DB_HOST', 'localhost' );
Do not publish real credentials.
27. What Happens During a Request?
Suppose:
https://learn.cresignsys.com/about/
is requested.
The flow is:
Browser
↓
Nginx
↓
PHP-FPM
↓
WordPress
↓
wp-config.php
↓
MySQL
WordPress reads the database connection settings and connects to MySQL.
28. MySQL Query
WordPress may execute queries conceptually similar to:
SELECT *
FROM wp_posts
WHERE ID = 123;
MySQL processes the query.
Then returns the result to PHP.
29. PHP Receives Data
The flow becomes:
MySQL
↓
database result
↓
WordPress/PHP
↓
HTML
↓
Nginx
↓
Browser
30. Tables
A relational database stores data in tables.
For example:
wp_users
may contain:
ID
user_login
user_email
user_pass
and other fields.
31. Rows
A row represents one record.
For example:
ID = 15
user_login = john
is one user record.
32. Columns
Columns describe the fields.
Example:
wp_users
ID
user_login
user_email
user_registered
33. Primary Key
A table normally has a way to uniquely identify each record.
For example:
ID
can act as a primary key.
Conceptually:
ID
--
1
2
3
4
Each identifies a particular record.
34. WordPress Tables
A standard WordPress installation commonly creates tables such as:
wp_posts
wp_postmeta
wp_users
wp_usermeta
wp_options
wp_terms
wp_term_taxonomy
wp_term_relationships
wp_comments
wp_commentmeta
Plugins can create additional tables.
35. Table Prefix
Notice:
wp_
is a common table prefix.
WordPress can use another prefix.
For example:
abc_posts
abc_users
abc_options
36. Why Prefix Exists
The prefix can help distinguish WordPress tables.
It can also allow multiple WordPress installations to use the same database in some configurations, although separate databases are generally cleaner for independent hosted sites.
37. Never Treat Prefix as Complete Security
Changing:
wp_
to:
abc_
does not provide meaningful protection by itself.
Real security comes from:
database privileges
Linux permissions
application security
updates
authentication
network controls
38. Check WordPress Tables
Inside MySQL:
USE learn_cresignsys;
Then:
SHOW TABLES;
You should see the WordPress tables.
39. Inspect a Table
For example:
DESCRIBE wp_options;
This shows the columns.
40. Count Posts
You could query:
SELECT COUNT(*)
FROM wp_posts;
This is a simple example of how WordPress data is stored.
41. Don’t Modify WordPress Directly Without Understanding It
You can technically run:
UPDATE ...
but be careful.
WordPress plugins and themes can have relationships and serialized/structured data.
Changing database records manually can break the application.
For normal content management:
WordPress Admin
is usually safer.
42. MySQL User Authentication
MySQL identifies users by:
username
+
host
For example:
'learn_user'@'localhost'
is different from:
'learn_user'@'%'
43. localhost
For:
'learn_user'@'localhost'
the account is intended for connections from the local host under MySQL’s account matching rules.
This is appropriate when:
WordPress
+
MySQL
are on the same VPS.
44. Why Avoid %?
You may see:
'learn_user'@'%'
which broadly permits connections from hosts matching %.
For a local-only WordPress database connection, you generally don’t need to expose the database user this broadly.
Prefer the narrowest host requirement that works.
45. Database Port
MySQL commonly listens on:
3306
But don’t assume the port alone makes MySQL accessible.
Check:
sudo ss -ltnp | grep 3306
46. MySQL Should Usually Not Be Public
For your single VPS:
Internet
✕
↓
MySQL 3306
You generally don’t want arbitrary Internet users connecting directly to your WordPress MySQL service.
Instead:
Internet
↓
Nginx
↓
PHP-FPM
↓
MySQL
MySQL remains internal to the server.
47. Check MySQL Bind Address
Inspect MySQL configuration.
For example:
sudo grep -R "bind-address" /etc/mysql/ 2>/dev/null
You may find something such as:
127.0.0.1
This means MySQL is bound to the local interface.
The exact configuration depends on your installation.
48. Why This Is Secure
If MySQL listens only locally:
Internet
↓
Nginx
↓
PHP
↓
localhost
↓
MySQL
external systems cannot directly reach the MySQL service through the network.
49. If You Need Remote MySQL
There are legitimate cases where:
Application Server
↓
network
↓
Database Server
is required.
Then you need:
TLS
firewall rules
restricted source IPs
database privileges
authentication
Don’t simply open:
3306
to the whole Internet.
50. WordPress Database Architecture
Your current hosting architecture should be:
INTERNET
│
▼
NGINX
│
▼
PHP-FPM
│
▼
WORDPRESS
│
▼
MYSQL
│
┌─────────┼─────────┐
▼ ▼ ▼
DB1 DB2 DB3
51. One Website
For:
learn.cresignsys.com
use:
Linux user:
learn
Website:
learn.cresignsys.com
Database:
learn_cresignsys
Database user:
learn_user
PHP-FPM pool:
learn
PHP socket:
learn.sock
Conceptually:
learn.cresignsys.com
│
├── files
├── Nginx
├── PHP-FPM
└── MySQL
│
└── learn_cresignsys
52. Another Website
For:
shop.cresignsys.com
use:
shop
shop_cresignsys
shop_user
shop PHP-FPM pool
shop.sock
Now the two sites are logically separated.
53. Database Backup
Database backup is essential.
A common tool is:
mysqldump
For example:
mysqldump -u learn_user -p learn_cresignsys > learn_cresignsys.sql
This exports database contents to a SQL file.
54. What Is a SQL Dump?
A dump can contain SQL statements that recreate database objects and data.
Conceptually:
Database
↓
mysqldump
↓
backup.sql
Later:
backup.sql
↓
mysql
↓
Database
55. Restore Example
Create the target database first, then import:
mysql -u learn_user -p learn_cresignsys < learn_cresignsys.sql
The exact restore procedure depends on what is contained in the dump and how the target database/user is configured.
56. Database Backup Is Not Website Backup
This is critical.
A WordPress backup needs at least:
Database
+
Website files
because:
database
contains content/settings, while:
wp-content
themes
plugins
uploads
WordPress files
are stored on disk.
57. Full WordPress Backup
Conceptually:
WordPress Backup
│
├── Database
│ └── .sql
│
└── Files
└── website directory
58. Why wp-content Is Important
Among WordPress files:
wp-content/
usually contains important site-specific data such as:
plugins
themes
uploads
The uploads directory is particularly important because it contains media files.
59. Backup Strategy for Hosting
For each hosted website:
site1
├── database backup
└── files backup
site2
├── database backup
└── files backup
site3
├── database backup
└── files backup
60. Database Size
Check database size:
SELECT
table_schema AS database_name,
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = 'learn_cresignsys'
GROUP BY table_schema;
This helps monitor growth.
61. Why Database Size Matters
A WordPress database can grow due to:
posts
revisions
logs
plugin tables
transients
analytics
comments
metadata
Poorly configured plugins can create very large tables.
62. MySQL Performance
MySQL consumes:
RAM
CPU
disk I/O
WordPress performance therefore depends on:
Nginx
+
PHP-FPM
+
MySQL
+
storage
not only PHP.
63. Request Flow With Database
A dynamic request may look like:
Browser
↓
Nginx
↓
PHP-FPM
↓
WordPress
↓
MySQL
↓
data
↓
WordPress
↓
PHP
↓
Nginx
↓
Browser
Every layer can become a bottleneck.
64. Database Bottleneck
Suppose PHP is fast but MySQL takes:
5 seconds
Then the page can still take roughly:
5+ seconds
before other processing and network overhead.
Therefore:
Faster PHP cannot completely compensate for a slow database.
65. Query
A database query asks MySQL to retrieve or modify information.
For example:
SELECT *
FROM wp_posts
WHERE post_status = 'publish';
The database parses and executes it.
66. Index
Indexes help MySQL find records efficiently.
Conceptually:
Without index
↓
scan many rows
With appropriate index
↓
find relevant rows faster
WordPress core tables have indexes designed for common operations, and plugins may add their own.
67. Too Many Queries
A page can trigger many database queries.
For example:
WordPress
↓
100 queries
↓
MySQL
If each query is expensive, page generation becomes slow.
Caching can reduce the need to regenerate the page.
68. Object Cache
WordPress can also use object caching.
Conceptually:
WordPress
↓
Object cache
↓
data
instead of querying MySQL repeatedly for the same information.
Common technologies include Redis and Memcached, but these are later lessons.
69. Database Security Model
Your hosting platform should follow:
Website
↓
Dedicated database user
↓
Dedicated database
↓
Minimum required privileges
Not:
Website
↓
root MySQL account
↓
all databases
70. Never Use MySQL Root in WordPress
Do not configure:
define( 'DB_USER', 'root' );
for a normal WordPress site.
Instead:
site-specific database user
should be used.
71. Why Root Is Dangerous
If WordPress is compromised and it has MySQL root privileges, the attacker could potentially perform actions far beyond that site’s database.
Least privilege limits the potential impact.
72. Your Hosting Creation Workflow
A proper automated site creation process should eventually do:
DOMAIN
↓
Linux user
↓
Website directory
↓
PHP-FPM pool
↓
Nginx server block
↓
Database
↓
Database user
↓
Privileges
↓
WordPress
↓
SSL
73. Example: learn.cresignsys.com
Conceptual configuration:
DOMAIN
learn.cresignsys.com
LINUX USER
learn
PATH
/storage/websites/learn.cresignsys.com/public
DATABASE
learn_cresignsys
DATABASE USER
learn_user
PHP POOL
learn
SOCKET
/run/php/learn.sock
Then:
DNS
↓
Nginx
↓
learn PHP-FPM
↓
learn WordPress
↓
learn database
74. This Is Multi-Tenant Hosting
When multiple independent websites share one server, you are operating a form of:
Multi-tenant hosting
Example:
Server
│
├── Customer A
├── Customer B
├── Customer C
└── Customer D
Each tenant needs isolation.
75. Isolation Layers
You can isolate at several levels:
Linux user
+
filesystem permissions
+
PHP-FPM pool
+
database
+
database user
+
Nginx configuration
The more layers are correctly separated, the stronger the hosting architecture.
76. Important Distinction
Separate:
database
does not automatically mean:
full security isolation
You also need:
filesystem isolation
process isolation
correct PHP-FPM users
correct permissions
secure Nginx configuration
77. MySQL Commands to Memorize
Check databases:
SHOW DATABASES;
Select database:
USE database_name;
List tables:
SHOW TABLES;
Describe table:
DESCRIBE table_name;
Create database:
CREATE DATABASE database_name;
Create user:
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
Grant access:
GRANT ALL PRIVILEGES
ON database_name.*
TO 'user'@'localhost';
78. Linux Commands to Memorize
Check MySQL:
sudo systemctl status mysql
Enter MySQL:
sudo mysql
Check listening port:
sudo ss -ltnp | grep 3306
Check version:
mysql --version
Backup:
mysqldump -u USER -p DATABASE > backup.sql
Restore:
mysql -u USER -p DATABASE < backup.sql
79. Complete Architecture
You now understand:
INTERNET
│
▼
DNS
│
▼
PUBLIC IP
│
▼
OCI NETWORKING
│
▼
NGINX
│
▼
PHP-FPM
│
▼
WORDPRESS
│
▼
MYSQL
And for multiple websites:
NGINX
│
┌─────────────┼─────────────┐
▼ ▼ ▼
Site 1 Site 2 Site 3
│ │ │
PHP 1 PHP 2 PHP 3
│ │ │
DB 1 DB 2 DB 3
80. Lesson 061 — Core Principle
The most important principle is:
Each hosted website should have its own application identity and database identity whenever practical.
For your CresignSys hosting architecture:
Domain
↓
Linux User
↓
Website Files
↓
Nginx Server Block
↓
PHP-FPM Pool
↓
PHP Socket
↓
WordPress
↓
Dedicated Database
↓
Dedicated Database User
This gives you a clean foundation for automated multi-domain hosting.
Next Lesson — 062
Linux Users, Groups & Permissions for Multi-Website Hosting
Next we will connect MySQL security with the filesystem and PHP-FPM security:
Linux User
↓
Group
↓
UID
↓
GID
↓
file ownership
↓
r / w / x
↓
chmod
↓
chown
↓
umask
↓
setgid
↓
ACL
Then we will design the correct permissions for:
/storage/websites/
├── site1.com/
├── site2.com/
└── site3.com/
so that Site 1 cannot modify Site 2, even though both run on the same VPS.