Installing MySQL 8 on Ubuntu 22.04

This guide describes the method for installing MySQL 8 on Ubuntu 22.04.

Before starting, it's recommended to switch to the root user:

# Enter the command below, then input the current user password to switch to root
sudo -i

1. Update Package Lists

# Network issues may cause some errors, which can be ignored
sudo apt update

2. Check Available Versions

sudo apt search mysql-server

3. One-Click Installation

3.1 Installation Commands

# Install the latest version; wait longer if network is slow
sudo apt install -y mysql-server
# Install a specific version; sometimes this may cause errors, so installing the latest version is recommended
sudo apt install -y mysql-server-8.0.39

3.2 Troubleshooting Installation Issues

If you encounter the following error, restart if possible, or identify and close any ongoing apt tasks, or wait for them to complete:

Waiting for cache lock: Could not get lock /var/lib/dpkg/lock-frontend. It is held by process 11444 (unattended-upgr)

If you see connection issues to ubuntu.com, change the source to Alibaba Cloud:

# Error example
After this operation, 242 MB of additional disk space will be used.
Ign:1 http://cn.archive.ubuntu.com/ubuntu jammy/main amd64 mysql-common all 5.8+1.0.8                                                                                                                                         
Ign:2 http://cn.archive.ubuntu.com/ubuntu jammy/main amd64 libaio1 amd64 0.3.112-13build1
Ign:3 http://cn.archive.ubuntu.com/ubuntu jammy/main amd64 libevent-core-2.1-7 amd64 2.1.12-stable-1build3
64% [Connecting to cn.archive.ubuntu.com]

Solution:

# Modify the source
sudo vi /etc/apt/sources.list
# In vi, perform a batch replacement with the following content
:%s/cn.archive.ubuntu.com/mirrors.aliyun.com/g
# After completion, exit vi (exit command not provided here)
# Update apt after exiting
sudo apt update
# Then re-run the installation command
sudo apt install mysql-server

4. Common Operations

4.1 Restart Commands

4.1.1 Check Service Status

sudo systemctl status mysql

4.1.2 Enable Service Autostart

sudo systemctl enable mysql

4.1.3 Manage Service

# Restart
sudo systemctl restart mysql
# Stop
sudo systemctl stop mysql
# Start
sudo systemctl start mysql

4.2 MySQL Commands

# Log in; initially press enter without a password
mysql -uroot -p
# Change password (execute after logging in; password cannot be too simple)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'SecurePassword123';
# Create databases
CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE DATABASE data_db CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
# Allow non-local access
USE mysql;
SELECT host, user FROM user;
# % indicates any IP
UPDATE user SET host='%' WHERE user='root';
FLUSH PRIVILEGES;

4.3 Modify Configurration File

vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Add the following content under [mysqld]
# Configuration for specific applications; other users can ignore
character_set_server=utf8mb4
collation-server=utf8mb4_bin
max_allowed_packet=256M
innodb_log_file_size=2GB
innodb_default_row_format=DYNAMIC
# innodb_redo_log_capacity=4G
#autoCommit=false
binlog_format=row
log_bin_trust_function_creators=1
transaction-isolation=READ-COMMITTED

# Note: Restart MySQL for changes to take effect

Installation is now complete.