MySQL Database Management

Contents

Introduction to MySQL

MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL). It's widely used for web applications, data warehousing, and embedded applications.

Key Features:

  • ACID compliance
  • Replication support
  • Partitioning
  • Stored procedures
  • Views and triggers
  • Full-text indexing

Why MySQL?

  • High performance
  • Proven reliability
  • Large community support
  • Cross-platform compatibility
  • Easy integration with popular technologies

Installation and Setup

Installation Steps

# Ubuntu/Debian sudo apt update sudo apt install mysql-server # Start MySQL service sudo systemctl start mysql sudo systemctl enable mysql # Secure installation sudo mysql_secure_installation # Access MySQL mysql -u root -p

Basic Configuration

# my.cnf configuration [mysqld] bind-address = 127.0.0.1 port = 3306 max_connections = 150 key_buffer_size = 16M max_allowed_packet = 16M innodb_buffer_pool_size = 1G character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

Basic Concepts

Database Structure

-- Create database CREATE DATABASE bookstore; USE bookstore; -- Create table CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(100), price DECIMAL(10,2), published_date DATE, category_id INT, FOREIGN KEY (category_id) REFERENCES categories(id) );

Table Relationships

-- One-to-Many relationship CREATE TABLE categories ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL ); -- Many-to-Many relationship CREATE TABLE book_authors ( book_id INT, author_id INT, PRIMARY KEY (book_id, author_id), FOREIGN KEY (book_id) REFERENCES books(id), FOREIGN KEY (author_id) REFERENCES authors(id) );

SQL Queries

Basic Queries

-- Select data SELECT * FROM books WHERE price > 20.00; -- Insert data INSERT INTO books (title, author, price) VALUES ('Database Design', 'John Smith', 29.99); -- Update data UPDATE books SET price = 24.99 WHERE author = 'John Smith'; -- Delete data DELETE FROM books WHERE id = 1;

Advanced Queries

-- Joins SELECT b.title, c.name as category, a.name as author FROM books b JOIN categories c ON b.category_id = c.id LEFT JOIN book_authors ba ON b.id = ba.book_id LEFT JOIN authors a ON ba.author_id = a.id WHERE b.price > 20.00; -- Aggregations SELECT c.name, COUNT(*) as book_count, AVG(price) as avg_price FROM books b JOIN categories c ON b.category_id = c.id GROUP BY c.name HAVING book_count > 5;

Data Types and Schema Design

Common Data Types

-- Numeric Types INT -- Whole numbers DECIMAL(10,2) -- Precise decimal numbers FLOAT -- Approximate decimal numbers -- String Types CHAR(10) -- Fixed-length strings VARCHAR(255) -- Variable-length strings TEXT -- Long text -- Date and Time DATE -- YYYY-MM-DD DATETIME -- YYYY-MM-DD HH:MM:SS TIMESTAMP -- Automatic timestamp -- Others ENUM('S','M','L') -- Enumerated values BOOL -- True/False values JSON -- JSON data

Schema Design Best Practices

  • Use appropriate data types for columns
  • Normalize database to reduce redundancy
  • Create indexes for frequently queried columns
  • Use foreign keys to maintain referential integrity
  • Consider denormalization for performance when needed

Database Administration

User Management

-- Create user CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'password123'; -- Grant privileges GRANT SELECT, INSERT, UPDATE ON bookstore.* TO 'webuser'@'localhost'; -- Show user privileges SHOW GRANTS FOR 'webuser'@'localhost';

Backup and Recovery

# Backup database mysqldump -u root -p bookstore > backup.sql # Restore database mysql -u root -p bookstore < backup.sql # Point-in-time recovery mysqlbinlog --start-datetime="2025-01-01 00:00:00" \ --stop-datetime="2025-01-02 00:00:00" \ /var/log/mysql/mysql-bin.* | mysql -u root -p

Performance Optimization

Indexing

-- Create indexes CREATE INDEX idx_author ON books(author); CREATE UNIQUE INDEX idx_isbn ON books(isbn); -- Show indexes SHOW INDEX FROM books; -- Analyze query performance EXPLAIN SELECT * FROM books WHERE author = 'John Smith';

Query Optimization

-- Use EXPLAIN to analyze queries EXPLAIN FORMAT=JSON SELECT b.title, c.name FROM books b JOIN categories c ON b.category_id = c.id WHERE b.price > 20.00; -- Optimize table OPTIMIZE TABLE books; -- Analyze table ANALYZE TABLE books;

Security Best Practices

Access Control

-- Principle of least privilege GRANT SELECT ON bookstore.books TO 'reader'@'localhost'; GRANT SELECT, INSERT, UPDATE ON bookstore.* TO 'editor'@'localhost'; -- Remove privileges REVOKE ALL PRIVILEGES ON bookstore.* FROM 'user'@'localhost';

Security Measures

  • Regular security updates
  • Strong password policies
  • Network security configuration
  • Regular security audits
  • Data encryption at rest and in transit
-- Enable SSL ALTER USER 'username'@'localhost' REQUIRE SSL; -- Rotate encryption keys ALTER INSTANCE ROTATE INNODB MASTER KEY;