How to Install MySQL on Ubuntu

MySQL is one of the world’s most popular open-source relational database management systems. It powers countless websites, applications, and services across the internet. If you’re an Ubuntu user looking to set up a reliable database system, MySQL is an excellent choice thanks to its robust feature set, reliability, and seamless integration with the Ubuntu ecosystem.

In this comprehensive guide, I’ll walk you through everything you need to know about installing MySQL on Ubuntu. From understanding what MySQL is to advanced configuration options, you’ll have all the information you need to successfully set up and manage MySQL on your Ubuntu system.

Table of Contents

Introduction to MySQL and Its Importance

What is MySQL and Why It Matters

MySQL is a powerful relational database management system (RDBMS) that uses Structured Query Language (SQL) for adding, accessing, and managing content in a database. Originally developed by MySQL AB in 1995, it’s now owned by Oracle Corporation but remains open-source under the GNU General Public License.

MySQL organizes data into tables consisting of rows and columns, allowing for structured data storage and efficient querying. This structure enables complex data relationships while maintaining data integrity, making it ideal for applications ranging from simple blogs to large e-commerce platforms.

According to DB-Engines Ranking, MySQL consistently ranks among the top three most popular database management systems globally, underscoring its importance in modern computing infrastructure.

Benefits of MySQL for Ubuntu Users

Ubuntu and MySQL share an open-source philosophy, which makes them natural companions. Here’s why MySQL is particularly beneficial for Ubuntu users:

Native Integration: MySQL works seamlessly with Ubuntu’s package management system, making installation and updates straightforward.
Performance: MySQL is optimized for Linux-based systems like Ubuntu, offering excellent speed and reliability.
Community Support: Both Ubuntu and MySQL have large, active communities, meaning you can find solutions to common problems quickly.
Security Updates: Ubuntu’s regular security patches ensure your MySQL installation remains protected against vulnerabilities.
Cost-Effectiveness: Both technologies are free to use, making them an economical choice for projects of any size.

MySQL vs. Other Database Management Systems

While MySQL is excellent, it’s worth understanding how it compares to alternatives:

PostgreSQL: Generally considered more feature-rich than MySQL with better standards compliance, but MySQL often performs better in read-heavy workloads.
MariaDB: A fork of MySQL that maintains high compatibility while offering some additional features and improvements.
SQLite: Lighter weight than MySQL, ideal for embedded applications, but lacks MySQL’s client-server architecture and concurrency capabilities.
MongoDB: A NoSQL database using document storage instead of tables, preferable for unstructured data but lacking MySQL’s ACID compliance.

For most web applications and services on Ubuntu, MySQL provides the perfect balance of features, performance, and ease of use, which explains its continued popularity.

Prerequisites for MySQL Installation

System Requirements

Before installing MySQL on Ubuntu, ensure your system meets these minimum requirements:

Ubuntu Version: 18.04 LTS, 20.04 LTS, 22.04 LTS, or newer
RAM: 512MB minimum (1GB+ recommended for production use)
Disk Space: At least 2GB free space for installation and initial databases
Processor: Any modern CPU (multi-core recommended for production)
User Privileges: Administrator (sudo) access to install packages

For production environments or larger databases, consider these recommended specifications:

4+ CPU cores
8GB+ RAM
SSD storage with at least 20GB free space
Separate partitions for database storage

Checking Your Ubuntu Version

Before proceeding, verify which version of Ubuntu you’re running, as installation steps may vary slightly between releases.

Open a terminal window and run:

lsb_release -a

You should see output similar to this:

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 22.04.1 LTS
Release:        22.04
Codename:       jammy

Alternatively, you can use:

cat /etc/os-release

Make note of your Ubuntu version as you follow this guide.

Updating Your System

It’s always a good practice to update your system before installing new software. This ensures you have the latest security patches and dependencies. Run:

sudo apt update
sudo apt upgrade

After running these commands, your system will download and install the latest updates. This might take a few minutes depending on your internet connection and how many packages need updating.

Methods to Install MySQL on Ubuntu

Using APT Package Manager (Recommended Method)

The Advanced Package Tool (APT) is the standard package management system for Ubuntu. Installing MySQL using APT offers several advantages:

Automatic dependency resolution
Integration with Ubuntu’s update system
Simplified installation process
Verified packages from Ubuntu repositories

This is the recommended method for most users, especially beginners, as it’s straightforward and reliable.

Using Debian Package Files

For more advanced users who need specific MySQL versions that aren’t available in the standard Ubuntu repositories, you can install MySQL using .deb package files downloaded directly from the MySQL website.

This method gives you more control over which version you install but requires manual management of dependencies and updates.

Installing from Source Code

Compiling MySQL from source code provides the highest level of customization. This approach allows you to:

Enable or disable specific features
Apply custom patches
Optimize MySQL for specific hardware
Install the very latest development versions

However, this method is significantly more complex and time-consuming. It’s primarily recommended for advanced users with specific requirements or developers contributing to MySQL.

For this guide, we’ll focus on the APT installation method as it’s the most user-friendly and appropriate for most scenarios.

Installing MySQL with APT Package Manager

Updating Repository Information

Before installing MySQL, ensure your APT package repositories are up-to-date:

sudo apt update

This command refreshes your system’s knowledge of available packages and their versions.

Installing MySQL Server Package

To install MySQL Server, run:

sudo apt install mysql-server

During the installation process, APT will resolve and install all necessary dependencies. Depending on your internet connection and system speed, this might take a few minutes.

On some Ubuntu versions, you might be prompted to set a root password during installation. If prompted, create a strong password and make note of it for future use. If you’re not prompted, don’t worry-we’ll set up authentication in the post-installation section.

Verifying Installation Success

After installation completes, verify that MySQL was installed correctly and is running:

sudo systemctl status mysql

You should see output indicating that the MySQL service is “active (running)”. The output will look something like this:

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2023-04-10 12:34:56 UTC; 2min ago
   Main PID: 12345 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4915)
     Memory: 356.3M
        CPU: 2.252s
     CGroup: /system.slice/mysql.service
             └─12345 /usr/sbin/mysqld

Checking MySQL Service Status

If MySQL isn’t running for some reason, you can start it with:

sudo systemctl start mysql

To ensure MySQL starts automatically at system boot, enable the service:

sudo systemctl enable mysql

You can also check which MySQL version was installed:

mysql --version

This will display something like:

mysql  Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))

Congratulations! MySQL Server is now installed on your Ubuntu system. However, there are important security steps you should take before using it in a production environment.

Post-Installation Security Setup

Running the MySQL Secure Installation Script

MySQL comes with a security script that helps you improve the security of your installation. Run:

sudo mysql_secure_installation

This interactive script will guide you through several security-enhancing steps:

Setting Root Password

If you didn’t set a root password during installation, the script will prompt you to create one. Even if you did set a password, you might be asked if you want to change it.

Choose a strong password that includes:

At least 12 characters
A mix of uppercase and lowercase letters
Numbers and special characters
No dictionary words or personal information

The script will also ask if you want to configure the VALIDATE PASSWORD COMPONENT, which checks password strength. This is recommended for production systems.

Removing Anonymous Users and Test Database

The script will ask you several additional security questions. For a secure installation, it’s recommended to answer “Y” (yes) to all of these:

Remove anonymous users? Anonymous users can access your database without a username, creating a security risk.
Disallow root login remotely? Preventing remote root access is a basic security measure. You should create specific users for remote connections.
Remove test database and access to it? The test database isn’t needed for normal operation and could be exploited.
Reload privilege tables now? This ensures all your security changes take effect immediately.

After completing these steps, your MySQL installation will have a basic level of security. For production environments, additional security measures may be necessary.

Configuring MySQL Server

Understanding Configuration Files

MySQL’s configuration files control how the server operates. On Ubuntu, the main configuration files are located in the following directories:

/etc/mysql/my.cnf: The main configuration file (often a symbolic link)
/etc/mysql/mysql.conf.d/: Directory containing additional configuration files
/etc/mysql/conf.d/: Directory for custom configuration files

The primary configuration file for the MySQL server itself is usually:

/etc/mysql/mysql.conf.d/mysqld.cnf

Modifying MySQL Configuration

To modify MySQL’s configuration, edit the appropriate file using a text editor like nano:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Some common settings you might want to adjust include:

bind-address: By default, MySQL binds to 127.0.0.1 (localhost), meaning it only accepts local connections. Change this to your server’s IP address or 0.0.0.0 to allow remote connections.
port: The default port is 3306. You can change this for security through obscurity.
max_connections: The default (151) may be too low for busy servers.
innodb_buffer_pool_size: Controls how much memory is allocated to caching data and indexes. For dedicated database servers, set this to 70-80% of available RAM.

After making changes, save the file and restart MySQL:

sudo systemctl restart mysql

Setting MySQL to Start on Boot

MySQL should start automatically when your system boots. Verify this with:

sudo systemctl is-enabled mysql

If it returns “enabled,” MySQL will start on boot. If not, enable it with:

sudo systemctl enable mysql

Creating and Managing MySQL Users

Creating New Database Users

For security reasons, you should avoid using the root user for regular database operations. Instead, create dedicated users with appropriate permissions:

First, log in to MySQL as root:

sudo mysql

Then create a new user:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

Replace ‘newuser’ and ‘password’ with your chosen username and a secure password.

Setting User Permissions

After creating a user, grant the appropriate permissions. For example, to grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

Or for read-only access:

GRANT SELECT ON database_name.* TO 'newuser'@'localhost';

After granting permissions, always flush the privileges:

FLUSH PRIVILEGES;

To see the permissions granted to a user:

SHOW GRANTS FOR 'newuser'@'localhost';

Managing User Authentication Methods

MySQL 8.0+ uses caching_sha2_password as the default authentication plugin, which is more secure but may not be compatible with older clients. If you need to use older clients, you can create users with the legacy authentication method:

CREATE USER 'legacyuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

For existing users, you can change the authentication method:

ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

To exit the MySQL prompt, type:

EXIT;

Creating and Managing Databases

Creating Your First Database

To create a new database, log in to MySQL:

mysql -u root -p

Then create the database:

CREATE DATABASE mydatabase;

To list all databases:

SHOW DATABASES;

To select a database for use:

USE mydatabase;

Basic Database Operations

Once your database is created, you can perform various operations:

Creating tables:

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

Inserting data:

INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]');

Querying data:

SELECT * FROM users;

Updating records:

UPDATE users SET email = '[email protected]' WHERE username = 'john_doe';

Deleting records:

DELETE FROM users WHERE username = 'john_doe';

Working with Tables and Data

You can modify existing tables:

Adding columns:

ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

Creating indexes for better performance:

CREATE INDEX idx_username ON users(username);

Viewing table structure:

DESCRIBE users;

For database backup and export, you’ll use the mysqldump utility, which we’ll cover in a later section.

Connecting to MySQL Server

Using the MySQL Command Line Client

The simplest way to connect to your MySQL server is using the command-line client:

mysql -u username -p

After entering your password, you’ll see the MySQL prompt (mysql>), where you can execute SQL commands.

You can also execute SQL commands directly from the terminal:

mysql -u username -p -e "SELECT * FROM database_name.table_name;"

For ease of use, consider creating a configuration file in your home directory named .my.cnf with your connection details:

[client]
user=username
password=password

Set the file permissions to be readable only by you:

chmod 600 ~/.my.cnf

Now you can connect simply by typing mysql.

Remote Connections to MySQL

To allow remote connections:

Edit the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Change the bind-address line from 127.0.0.1 to:
- Your server’s IP address to allow connections only to that IP
- 0.0.0.0 to allow connections on all server network interfaces
Save and restart MySQL:
```
sudo systemctl restart mysql
```

Create a user that can connect from remote hosts:

CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'remoteuser'@'%';
FLUSH PRIVILEGES;

The % wildcard allows connections from any host. For better security, replace it with specific IP addresses.

Configure your firewall to allow connections to port 3306:
```
sudo ufw allow from remote_IP_address to any port 3306
```

Connection Troubleshooting

If you’re having trouble connecting to MySQL:

Check MySQL status:
```
sudo systemctl status mysql
```
Verify MySQL is listening on the expected address and port:
```
sudo netstat -tuln | grep 3306
```
Check firewall settings:
```
sudo ufw status
```
Review MySQL error logs:
```
sudo tail -f /var/log/mysql/error.log
```
Test connectivity with a basic client like telnet:
```
telnet your_server_ip 3306
```

Common connection issues include incorrect user credentials, IP restrictions, firewall rules, and MySQL configuration problems.

MySQL Performance Optimization

Basic Performance Tuning

MySQL’s default configuration is designed for compatibility rather than optimal performance. Here are some basic tuning tips:

Optimize server variables based on workload:
- For OLTP (transactional) workloads, prioritize query cache and buffer pools
- For OLAP (analytical) workloads, prioritize sort buffers and join buffers
Use appropriate storage engines:
- InnoDB for transactional data with frequent updates
- MyISAM for read-heavy, rarely updated tables
Implement proper indexing for frequently queried columns

Monitor query performance using the slow query log:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- log queries taking more than 1 second

Configuring Query Cache

Note: MySQL 8.0+ has removed the query cache feature. For MySQL 5.7 and earlier:

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

For MySQL 8.0+, consider application-level caching solutions instead.

Buffer Pool and Memory Settings

The InnoDB buffer pool is critical for performance. Set it based on your available RAM:

innodb_buffer_pool_size = 4G  # Adjust based on your server RAM
innodb_log_file_size = 512M
innodb_flush_method = O_DIRECT

For production systems, you might also adjust:

innodb_flush_log_at_trx_commit = 2  # Slightly less durability, better performance
innodb_read_io_threads = 8  # Increase for read-heavy workloads
innodb_write_io_threads = 8  # Increase for write-heavy workloads

After making configuration changes, restart MySQL:

sudo systemctl restart mysql

Monitor performance impact after each change to find the optimal configuration for your workload.

Backing Up and Restoring MySQL Databases

Using mysqldump for Backups

The mysqldump utility is the most common way to back up MySQL databases:

To back up a single database:

mysqldump -u root -p database_name > database_name_backup.sql

To back up all databases:

mysqldump -u root -p --all-databases > all_databases_backup.sql

For large databases, consider these optimizations:

mysqldump -u root -p --single-transaction --quick --lock-tables=false database_name > database_name_backup.sql

The options mean:

--single-transaction: Creates a consistent snapshot without locking tables
--quick: Retrieves rows one at a time rather than caching the whole result set
--lock-tables=false: Avoids locking tables (use with –single-transaction)

Automating Database Backups

Set up a cron job to automate regular backups:

sudo crontab -e

Add a line like this to run a backup every day at 2 AM:

0 2 * * * mysqldump -u backup_user -p'password' --all-databases | gzip > /path/to/backup/mysql_backup_$(date +\%Y\%m\%d).sql.gz

For security, create a dedicated backup user with minimal privileges:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, SHOW VIEW, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Restoring from Backup Files

To restore a database from a backup:

mysql -u root -p database_name < database_name_backup.sql

If restoring a compressed backup:

zcat mysql_backup_20230410.sql.gz | mysql -u root -p database_name

For large database restores, you can use the pv tool to monitor progress:

pv database_name_backup.sql | mysql -u root -p database_name

Always test restore procedures regularly to ensure your backup strategy works as expected.

Upgrading MySQL

Checking Current Version

Before upgrading, check your current MySQL version:

mysql --version

or from within MySQL:

SELECT VERSION();

Upgrade Procedures and Best Practices

The safest approach to upgrading MySQL is:

Back up all your data:

mysqldump -u root -p --all-databases > pre_upgrade_backup.sql

Document your current configuration:

mysqld --verbose --help > mysql_config.txt

Check upgrade compatibility:
Review the MySQL documentation for your target version to identify any deprecated features or syntax changes.
For minor version upgrades (e.g., 8.0.21 to 8.0.30):
```
sudo apt update
sudo apt upgrade mysql-server
```

For major version upgrades (e.g., 5.7 to 8.0), add the MySQL APT repository:

wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.22-1_all.deb
sudo apt update
sudo apt upgrade mysql-server

Verify the new version:
```
mysql --version
```
Check for any post-upgrade tasks in the MySQL documentation.

Handling Version Compatibility Issues

Major version upgrades may introduce compatibility issues. Common problems include:

Authentication plugin changes: MySQL 8.0 uses caching_sha2_password by default, which may affect older clients. Fix with:
```
ALTER USER 'username'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
```
Reserved keywords: New MySQL versions may add reserved keywords that conflict with your table or column names. Check the MySQL keyword list before upgrading.
Deprecated features: Features marked as deprecated in your current version may be removed in the newer version.

Always test upgrades in a development environment before applying them to production systems.

Troubleshooting Common MySQL Issues

Connection Problems

If you can’t connect to MySQL:

Verify MySQL is running:
```
sudo systemctl status mysql
```
Check MySQL is listening on the expected address and port:
```
sudo ss -tap | grep mysql
```
Try connecting from localhost first:
```
mysql -u root -p
```
Check for connection limits:
```
SHOW VARIABLES LIKE 'max_connections';
```
Look for errors in the MySQL logs:
```
sudo tail -f /var/log/mysql/error.log
```

Permission Errors

Common permission issues include:

Access denied errors: Ensure the user exists and has appropriate permissions:

SELECT user, host FROM mysql.user WHERE user = 'username';
SHOW GRANTS FOR 'username'@'host';

File permission problems: MySQL needs read/write access to its data directory:
```
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod -R 755 /var/lib/mysql
```
AppArmor restrictions: If you’ve moved MySQL’s data directory, you may need to update AppArmor profiles:
```
sudo nano /etc/apparmor.d/usr.sbin.mysqld
sudo systemctl restart apparmor
```

Database Corruption Issues

If you suspect database corruption:

Check for errors in the MySQL logs:

sudo grep -i error /var/log/mysql/error.log

Use the mysqlcheck utility:

mysqlcheck -u root -p --check --all-databases

Repair corrupted tables:

mysqlcheck -u root -p --repair --all-databases

For InnoDB tables, you might need to:

Enable innodb_force_recovery (temporarily) in my.cnf:
```
innodb_force_recovery = 1
```
Restart MySQL:
```
sudo systemctl restart mysql
```
Export your data and then reimport it into a fresh database.

Always keep regular backups to recover from corruption issues more easily.

Frequently Asked Questions

How do I reset the MySQL root password if I forget it?

To reset the MySQL root password, stop the MySQL service, restart it in safe mode, connect without a password, reset the password, and restart MySQL normally:

sudo systemctl stop mysql
sudo mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root

Then, in the MySQL prompt:

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

Finally, stop the safe mode instance and restart MySQL normally:

sudo killall mysqld
sudo systemctl start mysql

Can I run MySQL and MariaDB on the same Ubuntu system?

It’s not recommended to run MySQL and MariaDB simultaneously on the same system as they use the same port (3306) and file locations by default. This would cause conflicts. If you need both, consider running one of them in a Docker container or virtual machine to isolate the environments.

How much memory should I allocate to MySQL on my server?

The ideal memory allocation depends on your workload and available system resources. For dedicated database servers, allocate about 70-80% of available RAM to MySQL’s buffer pool. For servers running other applications alongside MySQL, allocate 30-40% of RAM. Start conservative and monitor performance before increasing memory allocation.

Is MySQL secure enough for production use?

Yes, when properly configured, MySQL is secure enough for production use. Key security measures include: running the mysql_secure_installation script, using strong passwords, creating specific users with limited privileges, binding MySQL to appropriate interfaces, implementing a firewall, keeping MySQL updated, and regularly backing up your data.

How do I migrate my MySQL database to a different server?

To migrate a MySQL database to a different server:

Back up the database on the source server:

mysqldump -u root -p database_name > database_backup.sql

Transfer the backup file to the new server:

scp database_backup.sql username@new_server:/path/to/destination/

Create the database on the new server:

mysql -u root -p -e "CREATE DATABASE database_name;"

Import the data on the new server:

mysql -u root -p database_name < database_backup.sql

For large databases, consider using tools like Percona XtraBackup or MySQL’s replication features for less downtime.