MySQL/MariaDB Setup

Webinoly provides comprehensive database management with support for both MySQL and MariaDB, including automatic configuration, security hardening, and optimization.

Database Engine Selection

Webinoly supports both MySQL and MariaDB:

MariaDB (Default)
MySQL

MariaDB is the default database engine, offering:

Drop-in MySQL replacement
Better performance
More storage engines
Enhanced security
Active development

Supported Versions

MariaDB Versions

MariaDB 10.11

LTS - Long-term support

MariaDB 11.4

Current stable release

MariaDB 11.8

Default - Latest stable

MySQL Versions

MySQL 8.0

LTS - Long-term support

MySQL 8.4

Default - Latest LTS

MySQL on ARM: MySQL does not support ARM processors. If you’re on ARM architecture, you must use MariaDB.

Installation

Full Database Server

Install complete MySQL/MariaDB server:

sudo stack -mysql

Client Only

Install only the database client:

sudo stack -mysql=client

The client installation is useful for connecting to external database servers without running a local database.

Choosing Database Engine

Set Before Installation

sudo webinoly -config-set=db-engine:mariadb
sudo webinoly -config-set=mysql-ver:11.8
sudo stack -mysql

Check Current Engine

sudo webinoly -config-list | grep db-engine

Installation Process

Webinoly automatically handles the complete setup:

Package Installation

Adds official repository
Installs database server packages
Configures systemd service

Security Configuration

Runs mysql_secure_installation equivalent:

Generates strong root password
Removes anonymous users
Disables remote root access
Drops test database
Reloads privilege tables

User Creation

Creates admin user with privileges:

Generates secure admin password
Grants full database access
Enables WITH GRANT OPTION

Optimization

Applies performance optimizations:

Configures buffer pools
Sets connection limits
Optimizes query cache

Password Security: Database passwords are automatically generated and encrypted. They are NO longer displayed in the terminal for security reasons.

Accessing Database Credentials

Webinoly stores database credentials securely:

View Configuration

sudo webinoly -dbdata

This displays:

Database root password
Admin user password
Connection details

Stored Location

Credentials are encrypted in:

/opt/webinoly/webinoly.conf

Automatic login configured at:

/etc/mysql/mariadb.conf.d/90-webinoly-login.cnf
# or for MySQL:
/etc/mysql/mysql.conf.d/xx-webinoly-login.cnf

Database Configuration

Configuration Files Structure

MariaDB
MySQL

/etc/mysql/
├── my.cnf                          # Main configuration
├── mariadb.conf.d/
│   ├── 50-server.cnf             # Server settings
│   ├── 90-webinoly.cnf           # Webinoly optimizations
│   └── 90-webinoly-login.cnf     # Login credentials
└── conf.d/                       # Additional configs

/etc/mysql/
├── my.cnf                          # Main configuration
├── mysql.conf.d/
│   ├── mysqld.cnf                # Server settings
│   ├── xx-webinoly.cnf           # Webinoly optimizations
│   └── xx-webinoly-login.cnf     # Login credentials
└── conf.d/                       # Additional configs

Webinoly Optimizations

Webinoly applies several database optimizations:

[mysqld]
# Connection Settings
max_connections = 100
connect_timeout = 10
wait_timeout = 600

# Buffer Pool (automatically sized)
innodb_buffer_pool_size = XXM

# Query Cache
query_cache_type = 1
query_cache_limit = 2M
query_cache_size = 64M

# Log Settings
log_error = /var/log/mysql/error.log
slow_query_log = 0

Managing Database Server

Service Control

sudo systemctl start mysql

Database Access

Admin User
Root User
External Connection

# Access as admin
mysql --user=admin

# or with MariaDB
mariadb --user=admin

# Access as root
sudo mysql --user=root

mysql -h hostname -P port -u username -p

User Management

Create Database and User

-- Create database
CREATE DATABASE myapp_db;

-- Create user
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant privileges
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- Flush privileges
FLUSH PRIVILEGES;

Remove User and Database

-- Drop user
DROP USER 'myapp_user'@'localhost';

-- Drop database
DROP DATABASE myapp_db;

View Users

-- List all users
SELECT User, Host FROM mysql.user;

-- View user privileges
SHOW GRANTS FOR 'username'@'localhost';

External Database Access

Enable Public Access

Only enable public access if absolutely necessary and use strong security measures.

# Enable public access
sudo webinoly -mysql-public-access=on

# Enable with specific bind address
sudo webinoly -mysql-public-access=on -bind=0.0.0.0

# Disable public access
sudo webinoly -mysql-public-access=off

Configure Firewall

# Allow MySQL port (3306)
sudo ufw allow 3306/tcp

# Or allow from specific IP
sudo ufw allow from 203.0.113.0 to any port 3306

Create Remote User

-- Allow user from specific host
CREATE USER 'remote_user'@'203.0.113.0' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'remote_user'@'203.0.113.0';

-- Allow from any host (not recommended)
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'remote_user'@'%';

FLUSH PRIVILEGES;

Version Management

Switch Database Version

Webinoly allows version upgrades:

Check Current Version

mysql -V
# or
sudo stack -info

Upgrade Version

sudo stack -mysql-ver=11.8

This will:

Back up with keep-data
Remove old version
Install new version
Upgrade system tables

Verify Upgrade

mysql -V
sudo systemctl status mysql

Important Notes:

Downgrades are NOT supported
Always backup data before upgrading
Switching between MySQL and MariaDB requires manual data migration
Version upgrades preserve your data

Database Backup and Restore

Manual Backup

mysqldump --user=admin database_name > backup.sql

Restore Database

mysql --user=admin database_name < backup.sql

Logging Configuration

Webinoly provides log management:

Error Log

Always enabled by default:

/var/log/mysql/error.log

Binary Log

Useful for replication and point-in-time recovery:

sudo log -mysql=binary -enable

Slow Query Log

Identify slow queries:

sudo log -mysql=slow -enable

General Query Log

Log all queries (use with caution):

sudo log -mysql=general -enable

General query log can significantly impact performance and consume disk space. Only enable for debugging.

phpMyAdmin Integration

Webinoly can install phpMyAdmin for web-based management:

# Install phpMyAdmin
sudo stack -pma

# Access at
http://your-server-ip:22222/pma

phpMyAdmin is automatically configured to work with your database and is protected by HTTP authentication.

Performance Optimization

Buffer Pool Size

Automatic optimization based on RAM:

# Webinoly calculates optimal size
# Generally 50-70% of available RAM for dedicated database servers
# 25-40% for shared servers

Connection Optimization

[mysqld]
max_connections = 100
max_connect_errors = 1000000
thread_cache_size = 8

Query Cache

[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

Note: Query cache is deprecated in MySQL 8.0+ and removed in MariaDB 10.10+

Removing Database

Complete Removal

sudo stack -mysql -purge

Keep Data

Remove server but preserve databases:

sudo stack -mysql=keep-data -purge

Removing the database will:

Delete all databases and users (unless keep-data is used)
Remove phpMyAdmin if installed
Delete all configurations
This action cannot be undone without backups

Troubleshooting

Cannot connect to database

Check service status and socket:

sudo systemctl status mysql
ls -la /run/mysqld/mysqld.sock
sudo tail -f /var/log/mysql/error.log

Too many connections error

Increase max_connections:

sudo nano /etc/mysql/mariadb.conf.d/90-webinoly.cnf
# Increase max_connections value
sudo systemctl restart mysql

Slow query performance

Enable and check slow query log:

sudo log -mysql=slow -enable -long-query-time=1
sudo tail -f /var/log/mysql/slow.log

Version upgrade failed

Check for sufficient disk space and try:

sudo stack -mysql=keep-data -purge
sudo webinoly -config-set=mysql-ver:11.8
sudo stack -mysql

Engine mismatch error

Webinoly detects incompatible data:

ERROR: You have MySQL set as database engine, 
but we found you have MariaDB data in your server.

Solution: Use the same engine as your existing data or migrate data properly.

Best Practices

Regular Backups

Schedule automated database backups using Webinoly’s backup tools

Secure Access

Use strong passwords and limit remote access to specific IPs only

Monitor Performance

Enable slow query log to identify performance bottlenecks

Keep Updated

Regularly update to latest stable version for security patches

Next Steps

Install phpMyAdmin

Web-based database management interface

Create WordPress Site

Deploy WordPress with automatic database setup

Database Backups

Configure automated database backups

External Database

Connect to external database servers

Get Started

Core Concepts

Managing Sites

Stack Management

Advanced Features

Operations

Documentation Index

​Database Engine Selection

​Supported Versions

​MariaDB Versions

MariaDB 10.11

MariaDB 11.4

MariaDB 11.8

​MySQL Versions

MySQL 8.0

MySQL 8.4

​Installation

​Full Database Server

​Client Only

​Choosing Database Engine

​Set Before Installation

​Check Current Engine

​Installation Process

​Accessing Database Credentials

​Database Configuration

​Configuration Files Structure

​Webinoly Optimizations

​Managing Database Server

​Service Control

​Database Access

​User Management

​Create Database and User

​Remove User and Database

​View Users

​External Database Access

​Enable Public Access

​Configure Firewall

​Create Remote User

​Version Management

​Switch Database Version

​Database Backup and Restore

​Manual Backup

​Restore Database

​Logging Configuration

​Error Log

​Binary Log

​Slow Query Log

​General Query Log

​phpMyAdmin Integration

​Performance Optimization

​Buffer Pool Size

​Connection Optimization

​Query Cache

​Removing Database

​Complete Removal

​Keep Data

​Troubleshooting

​Best Practices

Regular Backups

Secure Access

Monitor Performance

Keep Updated

​Next Steps

Install phpMyAdmin

Create WordPress Site

Database Backups

External Database

Build docs developers (and LLMs) love

Database Engine Selection

Supported Versions

MariaDB Versions

MySQL Versions

Installation

Full Database Server

Client Only

Choosing Database Engine

Set Before Installation

Check Current Engine

Installation Process

Accessing Database Credentials

Database Configuration

Configuration Files Structure

Webinoly Optimizations

Managing Database Server

Service Control

Database Access

User Management

Create Database and User

Remove User and Database

View Users

External Database Access

Enable Public Access

Configure Firewall

Create Remote User

Version Management

Switch Database Version

Database Backup and Restore

Manual Backup

Restore Database

Logging Configuration

Error Log

Binary Log

Slow Query Log

General Query Log

phpMyAdmin Integration

Performance Optimization

Buffer Pool Size

Connection Optimization

Query Cache

Removing Database

Complete Removal

Keep Data

Troubleshooting

Best Practices

Next Steps