Installing and Configuring MySQL on Ubuntu

A Database Management System (DBMS) is software designed to create, manage, and manipulate databases. A DBMS provides an interface for interacting with databases, enabling data creation, protection, reading, updating, and deletion. It also manages security, data integrity, and concurrent access, making it indispensable for modern applications ranging from small websites to large enterprise systems.

A DBMS plays a crucial role in data organization by ensuring consistency, minimizing redundancy, and simplifying access. Without a DBMS, managing large volumes of data would be complex and inefficient, especially in web applications requiring rapid data access. According to W3Techs data for July 2025, relational DBMSs like MySQL are used in approximately 43% of websites with known database technologies.

Overview of Popular DBMSs

• MySQL: An open-source relational DBMS widely used for web applications due to its speed, reliability, and support for multiple storage engines like InnoDB, which provides ACID compliance (Atomicity, Consistency, Isolation, Durability). MySQL is popular in web development, especially for platforms like WordPress.
• PostgreSQL: An open-source relational DBMS known for advanced features like support for complex queries, extended data types, and strict SQL standard compliance. PostgreSQL is often chosen for enterprise applications where data integrity is critical.
• SQLite: A lightweight, serverless DBMS ideal for embedded systems, mobile applications, and small projects. SQLite doesn't support multi-user access or high concurrency but is simple to use and requires no server setup.

Why MySQL?

MySQL was selected for this guide due to its superior compatibility with PHP and WordPress, ease of use, and extensive community support. MySQL is one of the most popular databases for web applications, offering a balance between performance and configuration simplicity. According to official WordPress requirements, it requires MySQL version 5.7 or higher (or MariaDB 10.3+). MySQL integrates with PHP through extensions like mysqli or mysqlnd, making it ideal for WordPress, which uses MySQL to store site content, settings, and user data.

MySQL is also supported by major companies including Google (Cloud SQL), LinkedIn, and Netflix (for specific services), confirming its reliability and scalability. Easy installation and extensive documentation make MySQL accessible to both beginners and experienced administrators.

Prerequisites

Before installing MySQL on Ubuntu, ensure the following conditions are met:

• Ubuntu operating system: This guide uses Ubuntu 24.04 LTS (supported until 2034), but instructions apply to other versions like 20.04 or 22.04. Ubuntu is a popular choice for servers due to its stability and community support.
• Root or sudo access: Installing packages and modifying system files require administrative privileges. Use an account with sudo or root access.
• Updated system: Update the system before installation to avoid dependency issues:
  sudo apt update && sudo apt upgrade
• Terminal access: Ensure terminal access, either locally or via SSH for remote servers.

It's also recommended to configure a firewall (e.g., UFW) for secure access to MySQL port (3306). Only allow connections from trusted IPs:

Installing MySQL on Ubuntu

MySQL installation on Ubuntu uses the apt package manager, which installs the latest available MySQL version from Ubuntu repositories. As of July 2025, Ubuntu 24.04 defaults to MySQL version 8.0.x (e.g., 8.0.38 or newer).

Installation Steps

1. Update package lists
   Update the package index to ensure you install the latest versions:
   sudo apt update
2. Install MySQL server
   Install the mysql-server package, which includes the MySQL server and required dependencies:
   sudo apt install mysql-server

   This command automatically installs and starts the MySQL service. After installation, MySQL will be configured to start automatically on system boot.

3. Verify MySQL version
   Confirm successful installation by checking the version:
   mysql --version

   Expected output, for example:

   mysql Ver 8.0.38-2ubuntu3 for Linux on x86_64 ((Ubuntu))
4. Run security script
   To enhance security, execute:
   sudo mysql_secure_installation

   This script guides you through several configuration steps:

   • VALIDATE PASSWORD plugin setup: Configure password complexity policy (recommended level 2).
   • Set root password: If no password is set, create a strong password for the MySQL root account.
   • Remove anonymous users: Answer Y (yes) to remove anonymous accounts.
   • Disallow remote root login: Answer Y to prevent root login from remote hosts.
   • Remove test database: Answer Y to remove demonstration databases.
   • Reload privilege tables: Answer Y to apply changes.

   Answering Y to all questions is recommended for maximum security.

Basic Configuration

After installing MySQL, configure it for optimal operation. The main configuration file is located at /etc/mysql/mysql.conf.d/mysqld.cnf, containing server-specific settings.

Configuration File Overview

The file /etc/mysql/mysql.conf.d/mysqld.cnf contains sections:

• [mysqld]: MySQL server settings (port 3306, data directory /var/lib/mysql).
• [client]: Settings for MySQL clients.

Use a text editor to modify the file:

Example modification: Increasing InnoDB buffer size:

After making changes, restart MySQL:

Configuring User Accounts

MySQL 8.0 defaults to the caching_sha2_password authentication plugin. For compatibility with older applications, change it:

1. Log into MySQL shell:
   sudo mysql
2. Create a user with compatible authentication:
   CREATE USER 'new_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure_password';
3. Grant necessary privileges:
   GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO 'new_user'@'localhost';
4. Apply changes:
   FLUSH PRIVILEGES;
5. Exit:
   EXIT;

Creating a WordPress Database and User

WordPress requires a database and user with appropriate privileges. Follow these steps:

1. Log into MySQL shell
   Log in as root:
   sudo mysql
2. Create database
   Create a database named wordpress:
   CREATE DATABASE wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Create user
   Create a WordPress user:
   CREATE USER 'wp_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'secure_password';

   Replace secure_password with a strong password.

4. Grant privileges
   Assign minimum necessary privileges for the wordpress database:
   GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES
   ON wordpress.* TO 'wp_user'@'localhost';
5. Apply changes
   Reload privilege tables:
   FLUSH PRIVILEGES;
6. Exit shell
   EXIT;

Testing Database Connection

To verify the user can connect to the database:

1. Log in with new user
   Connect to MySQL as wp_user:
   mysql -u wp_user -p

   Enter the password.

2. Check database access
   Select the database and check privileges:
   USE wordpress;
   SHOW TABLES;

   If commands execute without errors, the connection works.

3. Exit shell
   EXIT;

Troubleshooting

If MySQL doesn't work as expected, use these diagnostic methods:

1. Check MySQL service status
   sudo systemctl status mysql

   If inactive, start it:

   sudo systemctl start mysql
2. View error logs
   sudo tail -50 /var/log/mysql/error.log
3. Resolving common issues
   • MySQL 8.0 authentication error: Reset root password:
     sudo systemctl stop mysql
     sudo mysqld_safe --skip-grant-tables &
     mysql -u root

     In MySQL shell:

     FLUSH PRIVILEGES;
     ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_strong_password';
     EXIT;

     Restart server:

     sudo systemctl start mysql
   • Port 3306 occupied:
     sudo ss -tulpn | grep ':3306'
   • Compatibility issues: If applications don't support the new authentication method:
     ALTER USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Installing and configuring MySQL on Ubuntu is a straightforward process that prepares a reliable database for web applications like WordPress. By following these steps, you can install MySQL, configure a WordPress database and user, test connectivity, and resolve potential issues. MySQL remains popular due to its PHP compatibility, ease of use, and community support.

Table: Key Commands for MySQL Installation and Configuration