MySQL/MariaDB Database Setup Guide

Table of Contents

Introduction

MySQL and MariaDB are popular open-source relational database management systems (RDBMS) that serve as the backbone for many web applications and enterprise solutions. MySQL was originally developed by Oracle Corporation, while MariaDB is a community-driven fork created by the original MySQL developers to ensure continued open-source development.

Key Differences Between MySQL and MariaDB

| Feature | MySQL | MariaDB | |---------|--------|---------| | License | GPL with commercial options | GPL | | Storage Engines | InnoDB, MyISAM, Memory | All MySQL engines plus Aria, TokuDB, Spider | | JSON Support | Native JSON data type | JSON functions with TEXT storage | | Performance | Optimized for general use | Enhanced performance features | | Compatibility | Standard MySQL | Fully compatible with MySQL | | Development | Oracle-driven | Community-driven |

System Requirements

| Component | Minimum | Recommended | |-----------|---------|-------------| | RAM | 512 MB | 4 GB or more | | Disk Space | 200 MB | 2 GB or more | | CPU | 1 Core | 2+ Cores | | Operating System | Linux, Windows, macOS | Linux preferred |

Installation

Ubuntu/Debian Installation

#### MySQL Installation `bash

Update package index

Install MySQL server

Check MySQL service status

Enable MySQL to start on boot

#### MariaDB Installation `bash

Update package index

Install MariaDB server

Check MariaDB service status

Enable MariaDB to start on boot

CentOS/RHEL/Rocky Linux Installation

#### MySQL Installation `bash

Install MySQL repository

Start MySQL service

Enable MySQL to start on boot

Check service status

#### MariaDB Installation `bash

Install MariaDB server

Start MariaDB service

Enable MariaDB to start on boot

Check service status

Windows Installation

#### MySQL on Windows 1. Download MySQL Installer from official MySQL website 2. Run the installer as administrator 3. Choose setup type (Developer Default, Server Only, etc.) 4. Configure MySQL Server with desired settings 5. Set root password during installation 6. Complete installation and start MySQL service

#### MariaDB on Windows 1. Download MariaDB MSI installer from official website 2. Run installer as administrator 3. Follow installation wizard 4. Set root password 5. Configure service startup options 6. Complete installation

Docker Installation

#### MySQL Docker Container `bash

Pull MySQL image

Run MySQL container

Connect to MySQL container

#### MariaDB Docker Container `bash

Pull MariaDB image

Run MariaDB container

Connect to MariaDB container

Initial Configuration

Secure Installation

Both MySQL and MariaDB provide security scripts to improve initial security:

#### MySQL Secure Installation `bash

Run MySQL secure installation script

#### MariaDB Secure Installation `bash

Run MariaDB secure installation script

The secure installation script will prompt for the following configurations:

| Configuration Option | Recommendation | Purpose | |---------------------|----------------|---------| | Set root password | Yes | Secure root account | | Remove anonymous users | Yes | Eliminate security risk | | Disallow root login remotely | Yes | Prevent remote root access | | Remove test database | Yes | Remove unnecessary test data | | Reload privilege tables | Yes | Apply security changes |

Configuration Files

#### MySQL Configuration File Locations

| Operating System | Configuration File Path | |------------------|------------------------| | Linux | /etc/mysql/mysql.conf.d/mysqld.cnf | | CentOS/RHEL | /etc/my.cnf | | Windows | C:\ProgramData\MySQL\MySQL Server 8.0\my.ini |

#### MariaDB Configuration File Locations

| Operating System | Configuration File Path | |------------------|------------------------| | Linux | /etc/mysql/mariadb.conf.d/50-server.cnf | | CentOS/RHEL | /etc/my.cnf.d/server.cnf | | Windows | C:\Program Files\MariaDB\data\my.ini |

Basic Configuration Parameters

`ini [mysqld]

Basic Settings

Character Set and Collation

Connection Settings

Buffer Settings

Log Settings

Binary Logging

Database Operations

Connecting to Database

#### Command Line Connection `bash

Connect as root user

Connect to specific database

Connect to remote server

Connect with specific port

#### Connection Parameters

| Parameter | Description | Example | |-----------|-------------|---------| | -u | Username | -u root | | -p | Password (prompt) | -p | | -h | Hostname | -h localhost | | -P | Port number | -P 3306 | | -D | Database name | -D mydb | | -e | Execute command | -e "SHOW DATABASES;" |

Database Management Commands

#### Creating Databases `sql -- Create a new database CREATE DATABASE company_db;

-- Create database with character set CREATE DATABASE company_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create database if it doesn't exist CREATE DATABASE IF NOT EXISTS company_db; `

#### Database Information Commands `sql -- Show all databases SHOW DATABASES;

-- Select a database to use USE company_db;

-- Show current database SELECT DATABASE();

-- Show database creation statement SHOW CREATE DATABASE company_db; `

#### Dropping Databases `sql -- Drop a database DROP DATABASE company_db;

-- Drop database if it exists DROP DATABASE IF EXISTS company_db; `

Table Operations

#### Creating Tables `sql -- Create a basic table CREATE TABLE employees ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE, hire_date DATE, salary DECIMAL(10,2), department_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

-- Create table with foreign key CREATE TABLE departments ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, manager_id INT, budget DECIMAL(15,2) );

-- Add foreign key constraint ALTER TABLE employees ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id); `

#### Table Information Commands `sql -- Show all tables SHOW TABLES;

-- Describe table structure DESCRIBE employees; -- or DESC employees;

-- Show table creation statement SHOW CREATE TABLE employees;

-- Show table status SHOW TABLE STATUS LIKE 'employees'; `

#### Modifying Tables `sql -- Add a new column ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Modify existing column ALTER TABLE employees MODIFY COLUMN phone VARCHAR(25);

-- Rename a column ALTER TABLE employees CHANGE COLUMN phone phone_number VARCHAR(25);

-- Drop a column ALTER TABLE employees DROP COLUMN phone_number;

-- Add index CREATE INDEX idx_last_name ON employees(last_name);

-- Add unique constraint ALTER TABLE employees ADD CONSTRAINT uk_email UNIQUE (email); `

Data Manipulation

#### Inserting Data `sql -- Insert single record INSERT INTO departments (name, budget) VALUES ('Engineering', 500000.00);

-- Insert multiple records INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES ('John', 'Doe', 'john.doe@company.com', '2023-01-15', 75000.00, 1), ('Jane', 'Smith', 'jane.smith@company.com', '2023-02-01', 80000.00, 1), ('Mike', 'Johnson', 'mike.johnson@company.com', '2023-03-10', 70000.00, 1);

-- Insert with SELECT INSERT INTO employees (first_name, last_name, email, salary, department_id) SELECT first_name, last_name, email, salary, department_id FROM temp_employees WHERE active = 1; `

#### Querying Data `sql -- Basic SELECT SELECT * FROM employees;

-- SELECT specific columns SELECT first_name, last_name, salary FROM employees;

-- SELECT with WHERE clause SELECT * FROM employees WHERE salary > 75000;

-- SELECT with JOIN SELECT e.first_name, e.last_name, d.name as department_name FROM employees e JOIN departments d ON e.department_id = d.id;

-- SELECT with aggregation SELECT department_id, COUNT(*) as employee_count, AVG(salary) as avg_salary FROM employees GROUP BY department_id;

-- SELECT with ordering SELECT * FROM employees ORDER BY salary DESC, last_name ASC;

-- SELECT with LIMIT SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10; `

#### Updating Data `sql -- Update single record UPDATE employees SET salary = 85000.00 WHERE id = 1;

-- Update multiple records UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1;

-- Update with JOIN UPDATE employees e JOIN departments d ON e.department_id = d.id SET e.salary = e.salary * 1.10 WHERE d.name = 'Engineering'; `

#### Deleting Data `sql -- Delete specific records DELETE FROM employees WHERE id = 1;

-- Delete with condition DELETE FROM employees WHERE hire_date < '2022-01-01';

-- Delete with JOIN DELETE e FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Discontinued'; `

User Management

Creating Users

`sql -- Create user with password CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Create user for remote access CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';

-- Create user for specific IP CREATE USER 'api_user'@'192.168.1.100' IDENTIFIED BY 'secure_password'; `

User Information Commands

`sql -- Show all users SELECT User, Host FROM mysql.user;

-- Show current user SELECT USER(), CURRENT_USER();

-- Show user privileges SHOW GRANTS FOR 'app_user'@'localhost';

-- Show all grants for current user SHOW GRANTS; `

Granting Privileges

#### Database-Level Privileges `sql -- Grant all privileges on specific database GRANT ALL PRIVILEGES ON company_db.* TO 'app_user'@'localhost';

-- Grant specific privileges GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_user'@'localhost';

-- Grant read-only access GRANT SELECT ON company_db.* TO 'readonly_user'@'localhost'; `

#### Table-Level Privileges `sql -- Grant privileges on specific table GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'hr_user'@'localhost';

-- Grant column-specific privileges GRANT SELECT (first_name, last_name, email) ON company_db.employees TO 'limited_user'@'localhost'; `

#### Global Privileges `sql -- Grant global privileges (use carefully) GRANT CREATE, DROP ON . TO 'admin_user'@'localhost';

-- Grant replication privileges GRANT REPLICATION SLAVE ON . TO 'repl_user'@'%'; `

Privilege Types

| Privilege | Description | Level | |-----------|-------------|-------| | ALL PRIVILEGES | All available privileges | Global, Database, Table | | SELECT | Read data | Global, Database, Table, Column | | INSERT | Insert new records | Global, Database, Table, Column | | UPDATE | Modify existing records | Global, Database, Table, Column | | DELETE | Remove records | Global, Database, Table | | CREATE | Create databases and tables | Global, Database | | DROP | Drop databases and tables | Global, Database | | ALTER | Modify table structure | Global, Database, Table | | INDEX | Create and drop indexes | Global, Database, Table | | GRANT OPTION | Grant privileges to other users | Global, Database, Table |

Revoking Privileges

`sql -- Revoke specific privileges REVOKE INSERT, UPDATE ON company_db.* FROM 'app_user'@'localhost';

-- Revoke all privileges REVOKE ALL PRIVILEGES ON company_db.* FROM 'app_user'@'localhost';

-- Revoke grant option REVOKE GRANT OPTION ON company_db.* FROM 'app_user'@'localhost'; `

User Management

`sql -- Change user password ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'new_password';

-- Rename user RENAME USER 'old_user'@'localhost' TO 'new_user'@'localhost';

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

-- Lock user account ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;

-- Unlock user account ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

-- Apply privilege changes FLUSH PRIVILEGES; `

Security Configuration

Password Policies

#### MySQL 8.0 Password Validation `sql -- Install password validation plugin INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Check password validation settings SHOW VARIABLES LIKE 'validate_password%';

-- Set password policy SET GLOBAL validate_password.policy = STRONG; SET GLOBAL validate_password.length = 12; SET GLOBAL validate_password.mixed_case_count = 1; SET GLOBAL validate_password.number_count = 1; SET GLOBAL validate_password.special_char_count = 1; `

SSL/TLS Configuration

#### Enabling SSL `ini [mysqld]

SSL Configuration

#### Creating SSL Certificates `bash

Generate CA private key

Generate CA certificate

Generate server private key

Generate server certificate

Firewall Configuration

#### UFW (Ubuntu/Debian) `bash

Allow MySQL port

Allow from specific IP

Allow from subnet

#### Firewalld (CentOS/RHEL) `bash

Allow MySQL service

Allow from specific IP

Audit Logging

#### Enable Audit Plugin `sql -- Install audit log plugin INSTALL PLUGIN audit_log SONAME 'audit_log.so';

-- Configure audit logging SET GLOBAL audit_log_policy = ALL; SET GLOBAL audit_log_format = JSON; SET GLOBAL audit_log_file = '/var/log/mysql/audit.log'; `

Performance Optimization

Key Configuration Parameters

#### Memory Settings `ini [mysqld]

InnoDB Buffer Pool (70-80% of available RAM)

Query Cache (MySQL 5.7 and earlier)

Key Buffer (for MyISAM tables)

Sort and Join Buffers

#### Connection Settings `ini [mysqld]

Connection Limits

Thread Settings

#### InnoDB Settings `ini [mysqld]

InnoDB Log Settings

InnoDB I/O Settings

InnoDB Lock Settings

Index Optimization

#### Creating Effective Indexes `sql -- Single column index CREATE INDEX idx_last_name ON employees(last_name);

-- Composite index CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- Unique index CREATE UNIQUE INDEX idx_email ON employees(email);

-- Partial index (MySQL 8.0+) CREATE INDEX idx_active_employees ON employees(department_id) WHERE status = 'active';

-- Functional index (MySQL 8.0+) CREATE INDEX idx_email_domain ON employees((SUBSTRING_INDEX(email, '@', -1))); `

#### Index Analysis `sql -- Show indexes for a table SHOW INDEX FROM employees;

-- Analyze index usage EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

-- Show index statistics SELECT table_name, index_name, column_name, cardinality FROM information_schema.statistics WHERE table_schema = 'company_db'; `

Query Optimization

#### Using EXPLAIN `sql -- Basic EXPLAIN EXPLAIN SELECT * FROM employees WHERE salary > 75000;

-- Extended EXPLAIN EXPLAIN FORMAT=JSON SELECT e.first_name, e.last_name, d.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE e.salary > 75000;

-- EXPLAIN ANALYZE (MySQL 8.0+) EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 75000; `

#### Query Optimization Techniques

| Technique | Description | Example | |-----------|-------------|---------| | Use Indexes | Create appropriate indexes | CREATE INDEX idx_salary ON employees(salary) | | Limit Results | Use LIMIT clause | SELECT * FROM employees LIMIT 100 | | Select Specific Columns | Avoid SELECT * | SELECT id, name FROM employees | | Use EXISTS | Instead of IN with subqueries | WHERE EXISTS (SELECT 1 FROM...) | | Optimize JOINs | Use proper JOIN conditions | ON e.id = d.employee_id | | Use UNION ALL | Instead of UNION when duplicates allowed | SELECT ... UNION ALL SELECT ... |

Monitoring Performance

#### Key Performance Metrics `sql -- Show global status SHOW GLOBAL STATUS;

-- Show process list SHOW PROCESSLIST;

-- Show engine status SHOW ENGINE INNODB STATUS;

-- Query performance metrics SELECT schema_name, digest_text, count_star, avg_timer_wait/1000000000000 as avg_time_sec, sum_rows_examined, sum_rows_sent FROM performance_schema.events_statements_summary_by_digest ORDER BY avg_timer_wait DESC LIMIT 10; `

#### Performance Schema Configuration `ini [mysqld]

Enable Performance Schema

Backup and Recovery

Logical Backups

#### Using mysqldump `bash

Backup single database

Backup multiple databases

Backup all databases

Backup with compression

Backup specific tables

Backup structure only

Backup data only

#### Advanced mysqldump Options

| Option | Description | Usage | |--------|-------------|--------| | --single-transaction | Consistent backup for InnoDB | --single-transaction | | --routines | Include stored procedures | --routines | | --triggers | Include triggers | --triggers | | --events | Include scheduled events | --events | | --master-data | Include binary log position | --master-data=2 | | --where | Backup subset of data | --where="date > '2023-01-01'" | | --ignore-table | Skip specific tables | --ignore-table=db.table |

Physical Backups

#### Using mysqlbackup (MySQL Enterprise) `bash

Full backup

Incremental backup

Restore from backup

#### Using Percona XtraBackup `bash

Install Percona XtraBackup

Full backup

Prepare backup

Restore backup

Binary Log Backups

#### Configuring Binary Logging `ini [mysqld]

Enable binary logging

#### Managing Binary Logs `sql -- Show binary logs SHOW BINARY LOGS;

-- Show current binary log position SHOW MASTER STATUS;

-- Purge old binary logs PURGE BINARY LOGS TO 'mysql-bin.000010'; PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

-- Reset binary logs (use carefully) RESET MASTER; `

#### Point-in-Time Recovery `bash

Extract SQL from binary logs

Apply binary log to specific point

Apply from specific position

Automated Backup Scripts

#### Daily Backup Script `bash #!/bin/bash

daily_backup.sh

Configuration

Create backup directory

Perform backup

Remove old backups

Log backup completion

#### Cron Job Configuration `bash

Edit crontab

Add daily backup at 2 AM

Add weekly full backup on Sunday at 1 AM

Monitoring and Maintenance

System Monitoring

#### Key Metrics to Monitor

| Metric Category | Metrics | Description | |-----------------|---------|-------------| | Connections | Threads_connected, Max_used_connections | Active connections | | Performance | Questions, Queries per second | Query throughput | | InnoDB | Innodb_buffer_pool_reads, Innodb_buffer_pool_read_requests | Buffer pool efficiency | | Locks | Table_locks_waited, Innodb_row_lock_waits | Lock contention | | Replication | Seconds_behind_master, Slave_lag | Replication health |

#### Monitoring Queries `sql -- Connection information SHOW PROCESSLIST; SHOW STATUS LIKE 'Threads_%'; SHOW STATUS LIKE 'Max_used_connections';

-- Query performance SHOW STATUS LIKE 'Questions'; SHOW STATUS LIKE 'Slow_queries'; SHOW STATUS LIKE 'Uptime';

-- InnoDB metrics SHOW STATUS LIKE 'Innodb_buffer_pool_%'; SHOW STATUS LIKE 'Innodb_rows_%'; SHOW STATUS LIKE 'Innodb_data_%';

-- Lock information SHOW STATUS LIKE '%lock%'; SELECT * FROM information_schema.INNODB_LOCKS; SELECT * FROM information_schema.INNODB_LOCK_WAITS; `

Log Management

#### Error Log Analysis `bash

View recent errors

Search for specific errors

Analyze error patterns

#### Slow Query Log `sql -- Enable slow query log SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL log_queries_not_using_indexes = 'ON';

-- View slow query log settings SHOW VARIABLES LIKE 'slow_query%'; SHOW VARIABLES LIKE 'long_query_time'; `

#### Analyzing Slow Queries `bash

Use mysqldumpslow to analyze slow query log

Sort by query time

Sort by count

Sort by lock time

Maintenance Tasks

#### Regular Maintenance Commands `sql -- Analyze tables ANALYZE TABLE employees;

-- Optimize tables OPTIMIZE TABLE employees;

-- Check table integrity CHECK TABLE employees;

-- Repair corrupted tables REPAIR TABLE employees;

-- Update table statistics ANALYZE TABLE employees UPDATE HISTOGRAM ON salary, department_id; `

#### Automated Maintenance Script `bash #!/bin/bash

maintenance.sh

Configuration

Get list of tables

Analyze and optimize each table

echo "Maintenance completed at $(date)" `

Troubleshooting

Common Issues and Solutions

#### Connection Issues

| Issue | Possible Causes | Solutions | |-------|----------------|-----------| | Can't connect to server | Service not running | systemctl start mysql | | Access denied | Wrong credentials | Reset password, check user privileges | | Too many connections | Connection limit reached | Increase max_connections, kill idle connections | | Host not allowed | Host restrictions | Update user host or bind-address |

#### Performance Issues

| Issue | Symptoms | Solutions | |-------|----------|-----------| | Slow queries | High response time | Add indexes, optimize queries | | High CPU usage | Load average > cores | Optimize queries, increase resources | | Memory issues | Swapping, OOM killer | Adjust buffer sizes, add RAM | | Disk I/O bottleneck | High iowait | Use faster storage, optimize queries |

#### Replication Issues

| Issue | Symptoms | Solutions | |-------|----------|-----------| | Slave lag | Seconds_behind_master > 0 | Check network, optimize slave | | Replication stopped | Slave_IO_Running = No | Check connection, restart slave | | Duplicate key errors | Error 1062 | Skip error or fix data inconsistency |

Diagnostic Commands

#### System Information `sql -- Show MySQL version SELECT VERSION();

-- Show system variables SHOW VARIABLES;

-- Show global status SHOW GLOBAL STATUS;

-- Show engine information SHOW ENGINES;

-- Show plugins SHOW PLUGINS; `

#### Performance Diagnostics `sql -- Show running processes SHOW PROCESSLIST;

-- Show InnoDB status SHOW ENGINE INNODB STATUS;

-- Show table locks SHOW OPEN TABLES WHERE In_use > 0;

-- Show index usage SELECT object_schema, object_name, index_name, count_read, count_write, count_fetch, count_insert, count_update, count_delete FROM performance_schema.table_io_waits_summary_by_index_usage ORDER BY count_read DESC; `

Recovery Procedures

#### Recovering from Corruption `sql -- Check for corrupted tables CHECK TABLE table_name;

-- Repair MyISAM tables REPAIR TABLE table_name;

-- For InnoDB corruption SET GLOBAL innodb_force_recovery = 1; -- Restart MySQL and dump data -- Recreate database and restore data `

#### Emergency Recovery `bash

Start MySQL in safe mode

Reset root password

Restart MySQL normally

This comprehensive guide provides a thorough foundation for setting up, configuring, and maintaining MySQL or MariaDB database systems. Regular practice with these commands and concepts will help you become proficient in database administration and troubleshooting.