MySQL Setup and Administration

Introduction to MySQL

MySQL is one of the world's most popular open-source relational database management systems (RDBMS). It's a key component of the LAMP (Linux, Apache, MySQL, PHP/Perl/Python) stack, which forms the foundation of countless web applications.

Why MySQL?

• Performance: Optimized for high-speed operations and handling large datasets
• Reliability: Proven track record in production environments for decades
• Scalability: Can grow from small applications to enterprise-level systems
• Ease of Use: Straightforward to set up, configure, and manage
• Cross-Platform: Runs on virtually all operating systems
• Community Support: Large community and extensive documentation
• PHP Integration: Excellent native support in PHP, making it ideal for web development

Installing MySQL

Platform-Specific Installation

# Install Homebrew if you don't have it
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Install MySQL
brew install mysql

# Start MySQL service
brew services start mysql

# Secure the MySQL installation
mysql_secure_installation
            

# Update package index
sudo apt update

# Install MySQL server
sudo apt install mysql-server

# Start MySQL service
sudo systemctl start mysql

# Enable MySQL to start on boot
sudo systemctl enable mysql

# Run the security script
sudo mysql_secure_installation
            

Verifying Installation

After installation, verify that MySQL is running correctly:

# Check MySQL version
mysql --version

# Connect to MySQL server
mysql -u root -p

# After entering password, you should see the MySQL prompt:
# mysql>

# Show databases
SHOW DATABASES;

# Exit MySQL client
EXIT;
          

MySQL Configuration Basics

Proper configuration is essential for security, performance, and functionality. Let's explore the key configuration aspects of MySQL.

The MySQL Configuration File

MySQL's main configuration is stored in a file named my.cnf or my.ini (on Windows). The location varies by platform:

• Windows: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
• Linux: /etc/mysql/my.cnf or /etc/my.cnf
• macOS: /usr/local/mysql/my.cnf or /etc/my.cnf

[mysqld]
# Network settings
port = 3306
bind-address = 127.0.0.1

# Character set and collation
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB settings (storage engine)
innodb_buffer_pool_size = 256M
innodb_log_file_size = 64M

# Connection settings
max_connections = 150
wait_timeout = 600

[mysql]
# Client settings
default-character-set = utf8mb4

[client]
# Client connection defaults
port = 3306
default-character-set = utf8mb4
          

Important Configuration Parameters

MySQL User Management

Proper user management is crucial for security. MySQL uses a privilege-based security model where each user is granted specific permissions.

Creating and Managing Users

-- Create a new user
CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'password123';

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

-- Or grant all privileges on a database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'webuser'@'localhost';

-- Apply the privilege changes
FLUSH PRIVILEGES;
          

-- Add additional privileges
GRANT CREATE, ALTER ON myapp_db.* TO 'webuser'@'localhost';

-- Revoke privileges
REVOKE DELETE ON myapp_db.* FROM 'webuser'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;
          

-- Show all users
SELECT user, host FROM mysql.user;

-- Show grants for a specific user
SHOW GRANTS FOR 'webuser'@'localhost';

-- Change a user's password
ALTER USER 'webuser'@'localhost' IDENTIFIED BY 'newpassword456';

-- Delete a user
DROP USER 'webuser'@'localhost';
          

The MySQL Privilege System

MySQL's privilege system is hierarchical and granular:

Creating and Managing Databases

Before connecting your PHP application to MySQL, you need to create and structure your database.

Creating a Database

-- Create a new database
CREATE DATABASE myapp_db;

-- Create with specific character set and collation
CREATE DATABASE myapp_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
          

-- Show all databases
SHOW DATABASES;

-- Switch to a database
USE myapp_db;

-- Check current database
SELECT DATABASE();

-- Delete a database (be careful!)
DROP DATABASE myapp_db;
          

Creating Tables and Structure

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

-- Create a posts table with a foreign key
CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
          

-- Add a new column
ALTER TABLE users ADD COLUMN last_login DATETIME;

-- Modify a column
ALTER TABLE posts MODIFY title VARCHAR(300) NOT NULL;

-- Add an index
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- View table structure
DESCRIBE users;
SHOW CREATE TABLE posts;

-- List all tables
SHOW TABLES;
          

MySQL Administration Tools

MySQL offers several tools for database administration, both command-line and graphical.

Command-Line Tools

• MySQL Command-Line Client: Interactive interface for executing SQL commands
• mysqladmin: Client for administrative operations
• mysqldump: Utility for creating database backups
• mysqlimport: Data import client
• mysqlcheck: Table maintenance utility

# Backup a database
mysqldump -u root -p myapp_db > myapp_backup.sql

# Restore from backup
mysql -u root -p myapp_db < myapp_backup.sql

# Check and repair tables
mysqlcheck -u root -p --check myapp_db
mysqlcheck -u root -p --repair myapp_db

# Import data from CSV
mysqlimport -u root -p --fields-terminated-by=',' myapp_db /path/to/data.csv
          

Graphical Administration Tools

Backup and Recovery

Regular backups are essential for data safety. MySQL provides several methods for backing up and restoring databases.

Types of MySQL Backups

• Logical Backups: SQL statements that recreate the database objects and data
• Physical Backups: Copies of the database files, typically faster but less portable
• Full Backups: Complete copy of the database
• Incremental Backups: Only changes since the last backup
• Online Backups: Performed while the database is running
• Offline Backups: Require shutting down the database

Using mysqldump for Backups

The most common method for logical backups is using the mysqldump utility:

# Back up a single database
mysqldump -u root -p --opt myapp_db > myapp_backup.sql

# Back up multiple databases
mysqldump -u root -p --databases db1 db2 db3 > multiple_dbs_backup.sql

# Back up all databases
mysqldump -u root -p --all-databases > full_backup.sql

# Create a compressed backup
mysqldump -u root -p myapp_db | gzip > myapp_backup.sql.gz

# Backup only specific tables
mysqldump -u root -p myapp_db users posts > selected_tables_backup.sql
          

# Restore a database
mysql -u root -p myapp_db < myapp_backup.sql

# Restore from a compressed backup
gunzip < myapp_backup.sql.gz | mysql -u root -p myapp_db

# Restore multiple databases
mysql -u root -p < multiple_dbs_backup.sql
          

Backup Best Practices

1. Regular Schedule: Implement automated, scheduled backups
2. Off-site Storage: Store backups in multiple locations, including off-site
3. Test Restores: Regularly test the backup restoration process
4. Retention Policy: Define how long to keep different backups
5. Documentation: Document the backup and restore procedures
6. Monitoring: Verify that backups complete successfully

#!/bin/bash
# Simple MySQL backup script

# Configuration
DB_USER="backup_user"
DB_PASS="backup_password"
DB_NAME="myapp_db"
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME-$DATE.sql.gz"

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Create backup
mysqldump -u $DB_USER -p$DB_PASS --opt $DB_NAME | gzip > $BACKUP_FILE

# Check if backup was successful
if [ $? -eq 0 ]; then
  echo "Backup created successfully: $BACKUP_FILE"
else
  echo "Error creating backup" >&2
  exit 1
fi

# Remove backups older than 30 days
find $BACKUP_DIR -name "$DB_NAME-*.sql.gz" -mtime +30 -delete
          

MySQL Performance Basics

Understanding MySQL performance fundamentals will help you build more efficient PHP applications.

Key Performance Factors

• Proper Indexing: Essential for query performance
• Query Optimization: Writing efficient SQL queries
• Server Configuration: Allocating appropriate resources
• Hardware: Adequate CPU, memory, and storage
• Caching: Reducing database load

Basic Indexing

Indexes speed up data retrieval but can slow down writes. They're essential for large tables.

-- Add an index to a single column
CREATE INDEX idx_username ON users(username);

-- Create a composite index on multiple columns
CREATE INDEX idx_first_last_name ON users(first_name, last_name);

-- Add a UNIQUE index
CREATE UNIQUE INDEX idx_email ON users(email);

-- View existing indexes
SHOW INDEX FROM users;

-- Remove an index
DROP INDEX idx_username ON users;
          

Query Optimization Basics

Using EXPLAIN to Analyze Queries

The EXPLAIN statement shows how MySQL executes a query, helping you identify performance issues.

-- Analyze a SELECT query
EXPLAIN SELECT u.username, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE p.created_at > '2023-01-01';
          

Practical Activities

Activity 1: MySQL Installation and Configuration

1. Install MySQL on your development machine
2. Secure the installation by running mysql_secure_installation
3. Connect to the MySQL server using the command-line client
4. Check the MySQL version and status
5. Locate and review the MySQL configuration file on your system

Activity 2: Database and User Setup

1. Create a new database named "web_dev_course"
2. Create a new user named "course_user" with access to the database
3. Grant the appropriate privileges to the user (SELECT, INSERT, UPDATE, DELETE)
4. Verify the user's privileges
5. Connect to the database using the new user account

Activity 3: Create a Blog Database Schema

Design and implement a database for a simple blog application with:

1. A users table with id, username, email, password_hash, and registration date
2. A posts table with id, user_id, title, content, created_at, and published status
3. A comments table with id, post_id, user_id, content, and created_at
4. A categories table with id and name
5. A post_categories junction table to establish a many-to-many relationship
6. Create appropriate indexes for the tables
7. Ensure proper foreign key relationships

Key Takeaways

• MySQL is a powerful, open-source relational database ideal for web applications
• Installation steps vary by platform but follow a similar pattern
• Proper configuration is essential for security and performance
• Creating appropriate users with least-privilege permissions enhances security
• Database and table design forms the foundation of your application
• Regular backups are critical for data protection
• Performance optimization starts with proper indexing and query design
• Various administration tools are available for managing MySQL

Next Steps

In our next lecture, we'll dive into PHP and MySQL integration, exploring how to connect your PHP applications to MySQL databases and execute queries from your PHP code.