MySQL¶

Overview¶

MySQL provides relational database services for applications requiring structured data and ACID transactions.

Deployment¶

Kubernetes Resources¶

Resource	Name	Namespace
Deployment	mysql	databases
Service	mysql	databases
PVC	mysql-data	databases

Configuration¶

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql
  namespace: databases
spec:
  replicas: 1
  template:
    spec:
      containers:
      - name: mysql
        image: mysql:8.0
        ports:
        - containerPort: 3306
        env:
        - name: MYSQL_ROOT_PASSWORD
          valueFrom:
            secretKeyRef:
              name: mysql-secret
              key: root-password
        volumeMounts:
        - name: data
          mountPath: /var/lib/mysql

Access¶

Connection Details¶

Property	Value
Host	mysql.databases.svc
Port	3306
Protocol	MySQL

Connection String¶

mysql://user:[email protected]:3306/database

Databases¶

Configured Databases¶

Database	Purpose
app_data	Application data
information_schema	System metadata
mysql	User accounts
performance_schema	Performance data

Schema Management¶

Creating Tables¶

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Migrations¶

Use application framework migrations or tools like Flyway/Liquibase.

Users and Permissions¶

Creating User¶

CREATE USER 'appuser'@'%' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_data.* TO 'appuser'@'%';
FLUSH PRIVILEGES;

User Roles¶

User	Access
root	Full access
appuser	App database only
readonly	SELECT only

Queries¶

Basic Operations¶

-- Select
SELECT * FROM users WHERE status = 'active';

-- Insert
INSERT INTO users (email) VALUES ('[email protected]');

-- Update
UPDATE users SET status = 'inactive' WHERE id = 1;

-- Delete
DELETE FROM users WHERE id = 1;

Performance Query¶

-- Check slow queries
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Show running processes
SHOW PROCESSLIST;

-- Explain query plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Indexes¶

Creating Indexes¶

-- Single column
CREATE INDEX idx_email ON users(email);

-- Composite
CREATE INDEX idx_status_created ON users(status, created_at);

-- Full-text
CREATE FULLTEXT INDEX idx_content ON articles(content);

Backup¶

mysqldump¶

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  app_data > backup.sql

Restore¶

mysql -u root -p app_data < backup.sql

Monitoring¶

Key Metrics¶

Metric	Description
Threads_connected	Active connections
Questions	Queries/sec
Slow_queries	Slow query count
Innodb_buffer_pool_reads	Buffer misses

Status Commands¶

SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW VARIABLES LIKE '%buffer%';

Performance Tuning¶

Key Parameters¶

Parameter	Recommended
innodb_buffer_pool_size	70% of RAM
max_connections	100-200
query_cache_size	Disabled (8.0+)
innodb_log_file_size	256M-2G

Configuration¶

[mysqld]
innodb_buffer_pool_size = 1G
max_connections = 150
slow_query_log = 1
long_query_time = 2

Troubleshooting¶

Common Issues¶

Issue	Cause	Resolution
Too many connections	Connection leak	Check pool settings
Slow queries	Missing index	Add index
Lock wait timeout	Long transaction	Optimize transaction
Disk full	Binary logs	Purge old logs

Logs¶

kubectl logs -n databases deployment/mysql