MySQL 101

MySQL is one of the most popular open-source relational database management systems (RDBMS) in the world. It is a cornerstone of the LAMP stack (Linux, Apache, MySQL, PHP) and is known for its reliability, performance, and ease of use.

Overview

MySQL stores data in tables, which are organized into databases. Each table consists of rows and columns, similar to a spreadsheet. It uses Structured Query Language (SQL) to manage and query data. As a relational database, it is excellent for applications that require structured data and strong transactional consistency, such as e-commerce platforms, financial systems, and content management systems.

Installation

The installation process varies by operating system. Here are the general steps for a Debian-based Linux distribution like Ubuntu.

Ubuntu/Debian

1. Update Package Index:

   sudo apt update

2. Install the MySQL Server package:

   sudo apt install mysql-server

3. Run the Security Script: After installation, it's recommended to run the included security script to remove insecure default settings.

   sudo mysql_secure_installation

   This script will guide you through setting a root password, removing anonymous users, and other security-related configurations.

Basic Concepts and Commands

Connecting to MySQL

To connect to the MySQL server from the command line, use the mysql client:

sudo mysql -u root -p

You will be prompted to enter the root password you set during the secure installation.

Common SQL Commands

• Show Databases: See a list of all databases on the server.

  SHOW DATABASES;

• Create a Database: Create a new database.

  CREATE DATABASE my_new_database;

• Use a Database: Select a database to work with.

  USE my_new_database;

• Create a Table: Create a new table within the selected database.

  CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(50) NOT NULL,
      email VARCHAR(100) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );

• Insert Data: Add a new row of data to a table.

  INSERT INTO users (username, email) VALUES ('johndoe', 'john.doe@example.com');

• Select Data: Query data from a table.

  SELECT * FROM users;

• Update Data: Modify existing data in a table.

  UPDATE users SET email = 'john.d@example.com' WHERE username = 'johndoe';

• Delete Data: Remove data from a table.

  DELETE FROM users WHERE username = 'johndoe';

Docker Installation

Running MySQL in a Docker container is a popular choice for development and testing environments.

1. Pull the MySQL Image:

   docker pull mysql:latest

2. Run the MySQL Container:

   docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysql:latest

   This command starts a new container named some-mysql, sets the root password, and runs it in detached mode.

3. Connect to the Container:

   docker exec -it some-mysql mysql -uroot -p

Kubernetes Deployment

Deploying MySQL on Kubernetes can be done using a Deployment and a PersistentVolume to ensure data persistence.

1. Create a Secret for the Password:

   apiVersion: v1
   kind: Secret
   metadata:
     name: mysql-pass
   type: Opaque
   data:
     password: <your-base64-encoded-password>

2. Create a PersistentVolumeClaim:

   apiVersion: v1
   kind: PersistentVolumeClaim
   metadata:
     name: mysql-pv-claim
   spec:
     accessModes:
       - ReadWriteOnce
     resources:
       requests:
         storage: 10Gi

3. Create a Deployment:

   apiVersion: apps/v1
   kind: Deployment
   metadata:
     name: mysql
   spec:
     replicas: 1
     selector:
       matchLabels:
         app: mysql
     template:
       metadata:
         labels:
           app: mysql
       spec:
         containers:
           - name: mysql
             image: mysql:latest
             env:
               - name: MYSQL_ROOT_PASSWORD
                 valueFrom:
                   secretKeyRef:
                     name: mysql-pass
                     key: password
             ports:
               - containerPort: 3306
             volumeMounts:
               - name: mysql-persistent-storage
                 mountPath: /var/lib/mysql
         volumes:
           - name: mysql-persistent-storage
             persistentVolumeClaim:
               claimName: mysql-pv-claim

Configuration and Tuning

• Configuration File: The main configuration file for MySQL is my.cnf. The location of this file varies by operating system, but it is typically found in /etc/mysql/my.cnf or /etc/my.cnf.
• Key Configuration Options:
  • innodb_buffer_pool_size: This is one of the most important settings for performance. It should be set to 50-70% of the available RAM on a dedicated database server.
  • max_connections: This setting controls the maximum number of concurrent connections.
  • query_cache_size: This setting controls the size of the query cache. It can be useful for read-heavy workloads, but it is disabled by default in MySQL 8.0.
• Tuning:
  • Indexing: Ensure that your tables are properly indexed to improve query performance.
  • Query Optimization: Use the EXPLAIN command to analyze your queries and identify potential bottlenecks.
  • Monitoring: Use tools like mysqltuner or Percona Monitoring and Management (PMM) to monitor your database and identify performance issues.