MySQL and MariaDB Installation: Basic Management Guide

MySQL and MariaDB are the most widely used relational database management systems worldwide. The vast majority of web applications, e-commerce sites, and enterprise systems use one of these databases. This guide covers installation, basic configuration, and management of both systems.

MySQL vs MariaDB

MariaDB is a community-driven fork created by the original MySQL developers. Key differences:

Feature	MySQL	MariaDB
License	GPL + Commercial	GPL (fully open)
Developer	Oracle	MariaDB Foundation
Performance	Good	Generally faster
Compatibility	Reference	Compatible with MySQL
New features	Slower	Faster release cycle

MariaDB is recommended for new projects. It is fully compatible with MySQL and generally offers better performance. Migrating from MySQL to MariaDB is straightforward.

MySQL Installation (Ubuntu/Debian)

Update Package Repository and Install

hljs bash

# Update package list
apt update

# Install MySQL Server
apt install -y mysql-server

# Check service status
systemctl status mysql

# Enable auto-start
systemctl enable mysql

Verify MySQL Version

hljs bash

mysql --version
# Output: mysql  Ver 8.0.xx Distrib 8.0.xx, for Linux (x86_64)

MariaDB Installation (Ubuntu/Debian)

hljs bash

# Update package list
apt update

# Install MariaDB Server
apt install -y mariadb-server mariadb-client

# Check service status
systemctl status mariadb

# Enable auto-start
systemctl enable mariadb

# Check version
mysql --version
# Output: mysql  Ver 15.1 Distrib 10.x.x-MariaDB

MariaDB Installation (CentOS/AlmaLinux/Rocky Linux)

hljs bash

# Add MariaDB repository
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | bash

# Install MariaDB
dnf install -y MariaDB-server MariaDB-client

# Start and enable service
systemctl start mariadb
systemctl enable mariadb

Security Configuration: mysql_secure_installation

Always run the security wizard after installation:

hljs bash

mysql_secure_installation

The wizard asks the following questions:

Enter current password for root (enter for none): [Enter]
Switch to unix_socket authentication [Y/n]: n
Change the root password? [Y/n]: Y
New password: [Enter a strong password]
Re-enter new password: [Repeat the password]
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

Disable remote root access. Create separate users for remote connections. Use strong passwords (at least 12 characters with uppercase, lowercase, numbers, and special characters).

Connecting to MySQL/MariaDB

hljs bash

# Connect as root
mysql -u root -p

# Connect to a specific database
mysql -u user -p database_name

# Connect to remote server
mysql -h 192.168.1.100 -u user -p database_name

# Connect with specific port
mysql -h server -P 3306 -u user -p

Creating and Managing Databases

hljs sql

-- Create database
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- List all databases
SHOW DATABASES;

-- Select database
USE myapp;

-- Drop database (careful!)
DROP DATABASE old_database;

-- Check database size
SELECT table_schema AS 'Database',
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema;

Creating Users and Granting Privileges

hljs sql

-- Create user (localhost only)
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'StrongPass123!';

-- Create user (remote access)
CREATE USER 'appuser'@'%' IDENTIFIED BY 'StrongPass123!';

-- Access from specific IP
CREATE USER 'appuser'@'192.168.1.50' IDENTIFIED BY 'StrongPass123!';

-- Grant full privileges on database
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'localhost';

-- Grant read-only access
GRANT SELECT ON myapp.* TO 'readonly'@'localhost';

-- Grant privileges on specific table
GRANT SELECT, INSERT, UPDATE ON myapp.users TO 'appuser'@'localhost';

-- Apply privileges
FLUSH PRIVILEGES;

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

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

Basic SQL Commands

hljs sql

-- Create table
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Update data
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 5;

-- List tables
SHOW TABLES;

-- View table structure
DESCRIBE users;

Remote Access Configuration

By default, MySQL/MariaDB only accepts connections from localhost. For remote access:

1. Edit Configuration File

hljs bash

# For MySQL
nano /etc/mysql/mysql.conf.d/mysqld.cnf

# For MariaDB
nano /etc/mysql/mariadb.conf.d/50-server.cnf

hljs ini

# Change or comment out the bind-address line
# bind-address = 127.0.0.1
bind-address = 0.0.0.0

2. Restart Service

hljs bash

systemctl restart mysql
# or
systemctl restart mariadb

3. Add Firewall Rule

hljs bash

# With UFW
ufw allow 3306/tcp

# With iptables
iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

Exposing MySQL port (3306) to the internet is a security risk. Use SSH tunneling or VPN when possible. If remote access is required, restrict to specific IP addresses.

Performance Tuning

Basic Configuration Optimization

hljs bash

nano /etc/mysql/mysql.conf.d/mysqld.cnf

hljs ini

[mysqld]
# InnoDB buffer pool (70-80% of RAM)
innodb_buffer_pool_size = 1G

# Connection pool
max_connections = 150

# Temporary table size
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary log (for replication)
# log_bin = /var/log/mysql/mysql-bin.log
# expire_logs_days = 7

Apply Configuration

hljs bash

systemctl restart mysql

# Check variables
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Service Management

hljs bash

# Start service
systemctl start mysql

# Stop service
systemctl stop mysql

# Restart service
systemctl restart mysql

# Reload configuration (no restart needed)
systemctl reload mysql

# Check service status
systemctl status mysql

# View logs
journalctl -u mysql -f
tail -f /var/log/mysql/error.log

Conclusion

MySQL and MariaDB are powerful and reliable database systems. After installation, don't neglect security configuration, perform regular backups, and optimize performance settings according to your server's resources. Both systems run smoothly on REXE servers.

MySQL and MariaDB Installation: Basic Management Guide

Table of Contents