CresignSys Learn

CresignSys Learn — Lesson 061

Written by

cresignsys

in

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.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

More posts