Lessons

MySQL Installation and Administration

Level: Intermediate Module: Database Services 9 min read Lesson 41 of 66

Overview

  • What you’ll learn: How to install MySQL Server on Ubuntu, run the security hardening script, manage users and privileges, create and manage databases, configure the server, and perform essential administration tasks.
  • Prerequisites: Lesson 40 (Relational Database Fundamentals), Module 1 (Linux Fundamentals)
  • Estimated reading time: 25 minutes

Introduction

MySQL is one of the most widely deployed relational databases in the world. It powers countless web applications, from small WordPress sites to large-scale platforms serving millions of users. On Ubuntu Server, MySQL is available as an official package and integrates seamlessly with the systemd service manager.

In this lesson you will walk through the complete process of installing MySQL Server on Ubuntu, securing the initial configuration, creating databases and users, managing privileges, understanding the configuration file structure, and performing routine administration tasks such as monitoring connections and managing logs.

By the end of this lesson, you will have a fully functional MySQL Server instance ready for development or production use, and the skills to manage it confidently from the command line.

Installing MySQL Server

MySQL Server is available in the default Ubuntu repositories. The installation process is straightforward using apt:

# Update package index
$ sudo apt update

# Install MySQL Server
$ sudo apt install mysql-server -y

# Verify the service is running
$ sudo systemctl status mysql

# Check the installed version
$ mysql --version
mysql  Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

After installation, MySQL starts automatically and listens on port 3306 by default. The root MySQL user is configured to authenticate via the auth_socket plugin, meaning you can connect as root only when running as the Linux root user (or via sudo).

Verifying the Installation

# Connect as root (requires sudo)
$ sudo mysql

# Inside the MySQL shell
mysql> SELECT VERSION();
mysql> SELECT USER(), CURRENT_USER();
mysql> SHOW DATABASES;
mysql> EXIT;

Securing the Installation

MySQL ships with a security script that walks you through hardening the initial configuration:

# Run the security script
$ sudo mysql_secure_installation

The script prompts you to:

  • Set up the VALIDATE PASSWORD component: Enforces password complexity policies (LOW, MEDIUM, STRONG).
  • Set a root password: If you want password-based root authentication instead of auth_socket.
  • Remove anonymous users: Anonymous accounts allow anyone to connect without a username.
  • Disallow root login remotely: Restricts root access to localhost only.
  • Remove the test database: The test database is accessible by any user and should be removed in production.
  • Reload privilege tables: Applies the changes immediately.

For production servers, answer “Yes” to all prompts and choose at least the MEDIUM password policy.

User Management and Privileges

Never use the root account for application connections. Instead, create dedicated users with minimal required privileges.

Creating Users

# Connect as root
$ sudo mysql

-- Create a user that connects from localhost
mysql> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- Create a user that connects from a specific IP
mysql> CREATE USER 'appuser'@'10.0.1.%' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- Create a user that connects from anywhere (use cautiously)
mysql> CREATE USER 'appuser'@'%' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

Granting Privileges

-- Grant all privileges on a specific database
mysql> GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';

-- Grant read-only access
mysql> GRANT SELECT ON myapp_db.* TO 'readonly'@'localhost';

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

-- Apply privilege changes
mysql> FLUSH PRIVILEGES;

-- View grants for a user
mysql> SHOW GRANTS FOR 'appuser'@'localhost';

Modifying and Removing Users

-- Change a user's password
mysql> ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'N3w_Str0ng_P@ss!';

-- Revoke privileges
mysql> REVOKE INSERT, UPDATE ON myapp_db.* FROM 'appuser'@'localhost';

-- Drop a user entirely
mysql> DROP USER 'appuser'@'localhost';

Database Creation and Management

-- Create a new database with UTF-8 support
mysql> CREATE DATABASE myapp_db
       CHARACTER SET utf8mb4
       COLLATE utf8mb4_unicode_ci;

-- List all databases
mysql> SHOW DATABASES;

-- Switch to the database
mysql> USE myapp_db;

-- Show tables in the current database
mysql> SHOW TABLES;

-- Show table structure
mysql> DESCRIBE users;

-- Drop a database (IRREVERSIBLE)
mysql> DROP DATABASE myapp_db;

MySQL Configuration

MySQL’s configuration on Ubuntu is organized under /etc/mysql/:

/etc/mysql/
├── my.cnf                    # Main config (includes conf.d/ and mysql.conf.d/)
├── conf.d/
│   └── mysql.cnf             # Client settings
└── mysql.conf.d/
    └── mysqld.cnf            # Server settings

Key Configuration Directives

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Network
bind-address            = 127.0.0.1      # Listen only on localhost
port                    = 3306
mysqlx-bind-address     = 127.0.0.1

# Storage engine
default-storage-engine  = InnoDB

# InnoDB settings
innodb_buffer_pool_size = 512M           # ~70% of RAM for dedicated DB server
innodb_log_file_size    = 256M
innodb_flush_log_at_trx_commit = 1       # Full ACID compliance

# Logging
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/mysql-slow.log
long_query_time         = 2              # Log queries taking > 2 seconds

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

# Connection limits
max_connections         = 150

Applying Configuration Changes

# Test the configuration file for errors
$ sudo mysqld --validate-config

# Restart MySQL to apply changes
$ sudo systemctl restart mysql

# Check some variables at runtime
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
mysql> SHOW VARIABLES LIKE 'max_connections';

Essential Administration Commands

Monitoring Connections and Status

-- Show active connections
mysql> SHOW PROCESSLIST;

-- Show server status variables
mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
mysql> SHOW GLOBAL STATUS LIKE 'Queries';
mysql> SHOW GLOBAL STATUS LIKE 'Uptime';

-- Show InnoDB engine status
mysql> SHOW ENGINE INNODB STATUSG

Working with the Binary Log

-- Show binary log files
mysql> SHOW BINARY LOGS;

-- Show current binary log position
mysql> SHOW MASTER STATUS;

-- Purge old binary logs
mysql> PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';

System Commands

# Check MySQL service status
$ sudo systemctl status mysql

# View recent MySQL error log entries
$ sudo tail -50 /var/log/mysql/error.log

# View slow query log
$ sudo tail -50 /var/log/mysql/mysql-slow.log

# Check disk usage for MySQL data directory
$ sudo du -sh /var/lib/mysql/

Enabling Remote Access

By default, MySQL listens only on localhost. To allow remote connections:

# 1. Edit the bind address
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Change: bind-address = 0.0.0.0

# 2. Restart MySQL
$ sudo systemctl restart mysql

# 3. Verify MySQL is listening on all interfaces
$ sudo ss -tlnp | grep 3306

# 4. Allow through firewall
$ sudo ufw allow from 10.0.1.0/24 to any port 3306

# 5. Ensure the user has a remote host entry
mysql> CREATE USER 'appuser'@'10.0.1.%' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

Security note: Never bind MySQL to 0.0.0.0 on a public interface without firewall rules. Prefer restricting access to specific subnets.

Key Takeaways

  • Install MySQL with apt install mysql-server and immediately run mysql_secure_installation to harden the default configuration.
  • Create dedicated application users with minimal privileges — never use root for application connections.
  • MySQL configuration on Ubuntu lives in /etc/mysql/mysql.conf.d/mysqld.cnf; key tuning parameters include innodb_buffer_pool_size, max_connections, and slow query logging.
  • Use SHOW PROCESSLIST, SHOW GLOBAL STATUS, and the slow query log for routine monitoring.
  • Restrict remote access to specific IP ranges using both MySQL user host patterns and firewall rules.
  • Always use utf8mb4 as the character set for full Unicode support including emoji.

What’s Next

In Lesson 42, you will learn MySQL replication and high availability, including primary-replica replication, GTID-based replication, Group Replication, and strategies for achieving high availability with MySQL.

繁體中文

概覽

  • 您將學到：如何在 Ubuntu 上安裝 MySQL Server、執行安全強化腳本、管理使用者和權限、建立和管理資料庫、設定伺服器，以及執行基本管理任務。
  • 先決條件：第 40 課（關聯式資料庫基礎）、模組 1（Linux 基礎）
  • 預估閱讀時間：25 分鐘

簡介

MySQL 是世界上部署最廣泛的關聯式資料庫之一。它為無數的 Web 應用程式提供動力，從小型 WordPress 網站到服務數百萬使用者的大規模平台。在 Ubuntu Server 上，MySQL 以官方套件形式提供，並與 systemd 服務管理器無縫整合。

在本課中，您將完整地了解在 Ubuntu 上安裝 MySQL Server、保護初始配置、建立資料庫和使用者、管理權限、了解設定檔結構，以及執行日常管理任務（如監控連線和管理日誌）的過程。

安裝 MySQL Server

MySQL Server 可在預設 Ubuntu 儲存庫中取得。使用 apt 安裝過程非常簡單：

# 更新套件索引
$ sudo apt update

# 安裝 MySQL Server
$ sudo apt install mysql-server -y

# 驗證服務正在執行
$ sudo systemctl status mysql

# 檢查已安裝的版本
$ mysql --version

保護安裝安全

MySQL 附帶一個安全腳本，引導您完成初始配置的強化：

# 執行安全腳本
$ sudo mysql_secure_installation

該腳本提示您：

  • 設定 VALIDATE PASSWORD 元件
  • 設定 root 密碼
  • 移除匿名使用者
  • 禁止 root 遠端登入
  • 移除測試資料庫
  • 重新載入權限表

使用者管理和權限

切勿使用 root 帳戶進行應用程式連線。相反，建立具有最小必要權限的專用使用者。

# 以 root 身份連線
$ sudo mysql

-- 建立從 localhost 連線的使用者
mysql> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- 授予特定資料庫的所有權限
mysql> GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';

-- 套用權限變更
mysql> FLUSH PRIVILEGES;

-- 檢視使用者的授權
mysql> SHOW GRANTS FOR 'appuser'@'localhost';

資料庫建立和管理

-- 建立支援 UTF-8 的新資料庫
mysql> CREATE DATABASE myapp_db
       CHARACTER SET utf8mb4
       COLLATE utf8mb4_unicode_ci;

-- 列出所有資料庫
mysql> SHOW DATABASES;

-- 切換到資料庫
mysql> USE myapp_db;

-- 顯示目前資料庫中的表
mysql> SHOW TABLES;

MySQL 設定

MySQL 在 Ubuntu 上的設定組織在 /etc/mysql/ 下：

/etc/mysql/
├── my.cnf                    # 主設定檔
├── conf.d/
│   └── mysql.cnf             # 用戶端設定
└── mysql.conf.d/
    └── mysqld.cnf            # 伺服器設定

關鍵設定指令

[mysqld]
bind-address            = 127.0.0.1      # 僅在 localhost 上監聽
innodb_buffer_pool_size = 512M           # 專用 DB 伺服器約 70% 的 RAM
slow_query_log          = 1
long_query_time         = 2
character-set-server    = utf8mb4
max_connections         = 150

基本管理命令

-- 顯示活動連線
mysql> SHOW PROCESSLIST;

-- 顯示伺服器狀態變數
mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- 顯示 InnoDB 引擎狀態
mysql> SHOW ENGINE INNODB STATUSG

啟用遠端存取

# 1. 編輯綁定位址
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 變更：bind-address = 0.0.0.0

# 2. 重新啟動 MySQL
$ sudo systemctl restart mysql

# 3. 允許通過防火牆
$ sudo ufw allow from 10.0.1.0/24 to any port 3306

重點回顧

  • 使用 apt install mysql-server 安裝 MySQL，並立即執行 mysql_secure_installation 強化預設配置。
  • 建立具有最小權限的專用應用程式使用者——切勿使用 root 進行應用程式連線。
  • Ubuntu 上的 MySQL 設定位於 /etc/mysql/mysql.conf.d/mysqld.cnf。
  • 使用 SHOW PROCESSLIST、SHOW GLOBAL STATUS 和慢查詢日誌進行日常監控。
  • 使用 MySQL 使用者主機模式和防火牆規則將遠端存取限制在特定 IP 範圍。
  • 始終使用 utf8mb4 作為字元集以獲得完整的 Unicode 支援。

下一步

在第 42 課中，您將學習 MySQL 複寫和高可用性，包括主從複寫、基於 GTID 的複寫、群組複寫，以及使用 MySQL 實現高可用性的策略。

日本語

概要

  • 学習内容：Ubuntu への MySQL Server のインストール方法、セキュリティ強化スクリプトの実行、ユーザーと権限の管理、データベースの作成と管理、サーバーの設定、基本的な管理タスクの実行。
  • 前提条件：レッスン 40（リレーショナルデータベース基礎）、モジュール 1（Linux 基礎）
  • 推定読了時間：25 分

はじめに

MySQL は世界で最も広くデプロイされているリレーショナルデータベースの1つです。小規模な WordPress サイトから数百万のユーザーにサービスを提供する大規模プラットフォームまで、無数の Web アプリケーションを支えています。Ubuntu Server では、MySQL は公式パッケージとして利用可能で、systemd サービスマネージャとシームレスに統合されています。

このレッスンでは、Ubuntu に MySQL Server をインストールし、初期設定のセキュリティを確保し、データベースとユーザーを作成し、権限を管理し、設定ファイルの構造を理解し、接続の監視やログの管理などの日常的な管理タスクを実行する完全なプロセスを学びます。

MySQL Server のインストール

MySQL Server はデフォルトの Ubuntu リポジトリで利用可能です。apt を使用したインストールは簡単です：

# パッケージインデックスの更新
$ sudo apt update

# MySQL Server のインストール
$ sudo apt install mysql-server -y

# サービスが実行中であることを確認
$ sudo systemctl status mysql

# インストールされたバージョンの確認
$ mysql --version

インストールのセキュリティ確保

MySQL にはセキュリティスクリプトが同梱されており、初期設定の強化を案内します：

# セキュリティスクリプトの実行
$ sudo mysql_secure_installation

スクリプトは以下を確認します：

  • VALIDATE PASSWORD コンポーネントの設定
  • root パスワードの設定
  • 匿名ユーザーの削除
  • root のリモートログインの禁止
  • テストデータベースの削除
  • 権限テーブルの再読み込み

ユーザー管理と権限

アプリケーション接続に root アカウントを使用しないでください。代わりに、最小限の必要な権限を持つ専用ユーザーを作成します。

# root として接続
$ sudo mysql

-- localhost から接続するユーザーを作成
mysql> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- 特定のデータベースにすべての権限を付与
mysql> GRANT ALL PRIVILEGES ON myapp_db.* TO 'appuser'@'localhost';

-- 権限変更を適用
mysql> FLUSH PRIVILEGES;

-- ユーザーの権限を表示
mysql> SHOW GRANTS FOR 'appuser'@'localhost';

データベースの作成と管理

-- UTF-8 対応の新しいデータベースを作成
mysql> CREATE DATABASE myapp_db
       CHARACTER SET utf8mb4
       COLLATE utf8mb4_unicode_ci;

-- すべてのデータベースを表示
mysql> SHOW DATABASES;

-- データベースに切り替え
mysql> USE myapp_db;

-- 現在のデータベースのテーブルを表示
mysql> SHOW TABLES;

MySQL の設定

Ubuntu での MySQL の設定は /etc/mysql/ 以下に編成されています：

/etc/mysql/
├── my.cnf                    # メイン設定ファイル
├── conf.d/
│   └── mysql.cnf             # クライアント設定
└── mysql.conf.d/
    └── mysqld.cnf            # サーバー設定

主要な設定ディレクティブ

[mysqld]
bind-address            = 127.0.0.1      # localhost のみでリッスン
innodb_buffer_pool_size = 512M           # 専用 DB サーバーでは RAM の約 70%
slow_query_log          = 1
long_query_time         = 2
character-set-server    = utf8mb4
max_connections         = 150

基本的な管理コマンド

-- アクティブな接続を表示
mysql> SHOW PROCESSLIST;

-- サーバーステータス変数を表示
mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';

-- InnoDB エンジンステータスを表示
mysql> SHOW ENGINE INNODB STATUSG

リモートアクセスの有効化

# 1. バインドアドレスを編集
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# 変更：bind-address = 0.0.0.0

# 2. MySQL を再起動
$ sudo systemctl restart mysql

# 3. ファイアウォールで許可
$ sudo ufw allow from 10.0.1.0/24 to any port 3306

重要ポイント

  • apt install mysql-server で MySQL をインストールし、すぐに mysql_secure_installation を実行してデフォルト設定を強化する。
  • 最小権限の専用アプリケーションユーザーを作成する——アプリケーション接続に root を使用しない。
  • Ubuntu での MySQL 設定は /etc/mysql/mysql.conf.d/mysqld.cnf にある。
  • SHOW PROCESSLIST、SHOW GLOBAL STATUS、スロークエリログで日常的な監視を行う。
  • MySQL ユーザーホストパターンとファイアウォールルールの両方でリモートアクセスを特定の IP 範囲に制限する。
  • 完全な Unicode サポートのために常に utf8mb4 を文字セットとして使用する。

次のステップ

レッスン 42 では、MySQL レプリケーションと高可用性について学びます。プライマリ・レプリカレプリケーション、GTID ベースのレプリケーション、グループレプリケーション、MySQL で高可用性を実現するための戦略を学びます。

You Missed