Database Management: Installing MySQL/MariaDB and Getting Started

Introduction

Managing relational databases effectively is a cornerstone of modern software infrastructure.
This article covers installation and initial configuration of
MySQL and its popular fork MariaDB, along with best practices
to get you started securely and efficiently. Whether you are on Linux, Windows or macOS,
you’ll find step-by-step instructions, practical tips and real-world examples.

1. Why Choose MySQL or MariaDB

Open Source amp Enterprise Editions: MySQL offers both free and paid versions MariaDB is fully open source.
Compatibility: MariaDB maintains drop-in compatibility with MySQL.
Performance: Both systems support high-performance storage engines (InnoDB, XtraDB).
Community amp Support: Large ecosystems are available for both.

2. Installing MySQL / MariaDB

2.1 On Linux (Ubuntu/Debian)

Update package index:
```
sudo apt update
```
Install server:
```
sudo apt install mysql-server
```
(For MariaDB, use sudo apt install mariadb-server)
Start and enable service:
```
sudo systemctl enable --now mysql
```
Run security script:
```
sudo mysql_secure_installation
```

2.2 On Windows

Download the MSI installer from the official site:
MySQL Installer
or MariaDB Downloads.
Run the installer, choose Developer Default or Server only.
Set the root password and default port (3306) when prompted.
Configure Windows Firewall to allow inbound TCP on port 3306 if remote access is needed.

2.3 On macOS

Using Homebrew:
```
brew install mysql
```
(or brew install mariadb)
Start service:
```
brew services start mysql
```
Secure installation:
```
mysql_secure_installation
```

3. Initial Security amp Hardening

Run mysql_secure_installation to remove anonymous users, test database and enforce strong root password.
Disable remote root login:

ALTER USER root@% IDENTIFIED WITH mysql_native_password BY YourStrongPassword

Use SSL/TLS for client-server encryption:
- Generate certificates (self-signed or CA).
- Enable in my.cnf:
```
[mysqld]
ssl-ca=/path/ca.pem
ssl-cert=/path/server-cert.pem
ssl-key=/path/server-key.pem
```
For remote administration, tunnel over a VPN such as
NordVPN,
ExpressVPN or
Surfshark.

4. Configuration Overview

The main configuration file (my.cnf or my.ini) is usually located in:

/etc/mysql/my.cnf (Linux)
C:ProgramDataMySQLMySQL Server X.Ymy.ini (Windows)
/usr/local/etc/my.cnf (macOS/Homebrew)

Parameter	Description	Recommended Value
max_connections	Max simultaneous client connections	200–500
innodb_buffer_pool_size	InnoDB buffer pool memory	70–80% of RAM
query_cache_size	Query cache memory (deprecated in MySQL 8.0)	0 (disable)

5. Getting Started with SQL

5.1 Connecting to the Server

mysql -u root -p

5.2 Creating Databases and Tables

CREATE DATABASE company
USE company
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  hired_date DATE
)

5.3 Managing Users amp Privileges

CREATE USER appuser@localhost IDENTIFIED BY AppPass123
GRANT SELECT, INSERT, UPDATE ON company. TO appuser@localhost
FLUSH PRIVILEGES

5.4 Basic CRUD Operations

INSERT:

INSERT INTO employees (name, email, hired_date) VALUES
(Alice Smith, alice@example.com, 2023-04-15)

SELECT:

SELECT  FROM employees WHERE hired_date > 2023-01-01

UPDATE:

UPDATE employees SET email = alice.smith@example.com WHERE id = 1

DELETE:
```
DELETE FROM employees WHERE id = 1
```

6. Backup amp Restore

6.1 Using mysqldump

-- Backup
mysqldump -u root -p company > company_backup.sql

-- Restore
mysql -u root -p company < company_backup.sql

6.2 Hot Backups (InnoDB)

Tools like Percona XtraBackup allow non-blocking, consistent snapshots of InnoDB tables,
ideal for production environments.

7. MySQL vs MariaDB: Key Differences

Feature	MySQL	MariaDB
License	GPL proprietary	GPL
Default Storage Engine	InnoDB	XtraDB / Aria
JSON Support	Native	Extended (faster)

8. Performance Tuning Tips

Monitor with mysqladmin and performance_schema.
Optimize slow queries using EXPLAIN.
Adjust buffer sizes (innodb_buffer_pool_size, key_buffer_size).
Partition large tables and archive old data.
Use connection pooling (ProxySQL, MySQL Router).

Conclusion

Installing and configuring MySQL or MariaDB is the first step toward a robust data layer.
By following the guidelines above, you’ll have a secure, high-performance database ready
for development and production. Continue exploring advanced features like replication,
sharding and high-availability setups to scale your environment further.

LINUXMIND.DEV