Installing MySQL on Ubuntu: Configuration And Setup

In this in-depth tutorial, we’ll walk through every step needed to install, configure, secure, and optimize MySQL on an Ubuntu server. Whether you’re setting up a development environment on Ubuntu Desktop or provisioning a production-grade database server on Ubuntu Server, you’ll find clear explanations, practical examples, and troubleshooting tips. By the end of this article, you’ll understand how to:

• Prepare your system
• Install MySQL using APT
• Verify and manage the MySQL service
• Secure your installation
• Configure remote access
• Create users and databases with best practices
• Perform basic backups and restores
• Tweak performance settings
• Troubleshoot common issues

Prerequisites and Environment Setup

Before diving into installation, ensure you have the following:

• Ubuntu Version: This guide targets Ubuntu 20.04 LTS and Ubuntu 22.04 LTS, but steps are similar on later releases.
• User with sudo Privileges: You need a non-root user in the sudo group.
• Basic Familiarity with the Command Line: We’ll be executing commands in Bash.
• Stable Internet Connection: To fetch packages from Ubuntu repositories.

Tip: Always update your package lists and upgrade existing packages before installing new software. This prevents version conflicts.

sudo apt update
sudo apt full-upgrade -y

Installing MySQL via APT

Ubuntu’s default repositories include MySQL packages maintained by the Ubuntu team. You can also use the official MySQL APT repository for the latest GA (Generally Available) releases, but for most cases, the Ubuntu repo version is sufficient.

1. Installing from Ubuntu Repositories

1. Install the MySQL Server package:

2. sudo apt install mysql-server -y

3. Install client utilities (optional but recommended):

4. sudo apt install mysql-client -y

5. Check installed versions:

6. mysqld --version
   mysql --version

7. Example output:

8. mysqld  Ver 8.0.33 for Linux on x86_64 (Ubuntu)
   mysql  Ver 8.0.33 for Linux on x86_64 (Ubuntu)

2. Installing the Latest GA Version via Official MySQL APT Repository

If you need the very latest MySQL version (e.g., 8.1 series), follow these steps:

1. Download the MySQL APT config package:

2. wget https://dev.mysql.com/get/mysql-apt-config_0.8.28-1_all.deb

3. Install the package:

4. sudo dpkg -i mysql-apt-config_0.8.28-1_all.deb

5. During the configuration prompt, select MySQL 8.0 (or 8.1) under “MySQL Server & Cluster”.
6. Update package lists and install:

7. sudo apt update
   sudo apt install mysql-server -y

Note: After installation, the repository package remains on your system. You can remove it if you no longer need to manage MySQL APT settings:

sudo apt remove mysql-apt-config -y

Verifying the Installation

Once installation completes, ensure that the MySQL service is running.

1. Check service status:

2. sudo systemctl status mysql

3. You should see:

4. ● mysql.service - MySQL Community Server
      Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
      Active: active (running) since Thu 2025-07-02 10:15:12 UTC; 2min ago

5. Start, stop, and restart commands:

6. sudo systemctl start mysql
   sudo systemctl stop mysql
   sudo systemctl restart mysql

7. Enable on boot (usually enabled by default):

8. sudo systemctl enable mysql

9. Log in to the MySQL shell:

10. sudo mysql

11. You should see the mysql> prompt, indicating you’re connected as the root user via Unix socket.

Securing the MySQL Installation

MySQL includes a utility to perform basic security hardening: mysql_secure_installation. This step is crucial especially for production environments.

1. Run the security script:

2. sudo mysql_secure_installation

3. Follow prompts:
   • Validate password plugin: Choose a password policy level (0 = low, 1 = medium, 2 = high).
   • Set root password: If using Unix socket auth, you may skip.
   • Remove anonymous users:
   • Disallow root login remotely: Recommended for production.
   • Remove test database: Recommended to prevent misuse.
   • Reload privilege tables: Apply changes immediately.
4. Example interaction:

5. Securing the MySQL server deployment.
   
   Enter password for user root: (Press ENTER)
   Set root password? [Y/n] y
   New password: *****
   Re-enter new password: *****
   Remove anonymous users? [Y/n] y
   Disallow root login remotely? [Y/n] y
   Remove test database and access to it? [Y/n] y
   Reload privilege tables now? [Y/n] y

After completion, the installation is locked down against many common attacks.

Configuring Remote Access

By default, MySQL binds only to localhost (127.0.0.1). To allow remote connections:

1. Edit the MySQL configuration:

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

3. Locate the bind-address directive and change:

4. #bind-address = 127.0.0.1
   bind-address = 0.0.0.0

5. This listens on all interfaces. You can also specify a particular IP address.
6. Restart MySQL:

7. sudo systemctl restart mysql

8. Create a remote user with appropriate privileges:

9. -- Log in as root
   sudo mysql
   
   -- Create user
   CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPassword!123';
   
   -- Grant privileges (e.g., full access on a specific database)
   GRANT ALL PRIVILEGES ON sampledb.* TO 'remote_user'@'%';
   
   -- Apply changes
   FLUSH PRIVILEGES;

10. Open port 3306 in the firewall (if using UFW):

11. sudo ufw allow 3306/tcp
    sudo ufw reload

Security Tip: Instead of opening port 3306 to the world, restrict it to specific IP addresses:

sudo ufw allow from 203.0.113.5 to any port 3306 proto tcp

Creating Users and Databases

Let’s walk through creating a sample database and users for development or production.

1. Connect as root:

2. sudo mysql -u root -p

3. Create a new database:

4. CREATE DATABASE blogdb
       CHARACTER SET utf8mb4
       COLLATE utf8mb4_unicode_ci;

5. Create a dedicated user:

6. CREATE USER 'blog_user'@'localhost' IDENTIFIED BY 'BlogUserPass!2025';

7. Grant minimal privileges:

8. GRANT SELECT, INSERT, UPDATE, DELETE
     ON blogdb.*
     TO 'blog_user'@'localhost';

9. Flush privileges:

10. FLUSH PRIVILEGES;
    EXIT;

11. Test access:

12. mysql -u blog_user -p blogdb

13. Inside the shell, try creating a table:

14. CREATE TABLE posts (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      content TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

Backups and Restores

Regular backups are essential. We’ll cover simple SQL dumps and point-in-time recovery basics.

1. Logical Backup with mysqldump

• Full backup of blogdb:

• mysqldump -u root -p blogdb > ~/backups/blogdb_$(date +%F).sql

• All databases:

• mysqldump -u root -p --all-databases > ~/backups/all_dbs_$(date +%F).sql

• Compressing on the fly:

• mysqldump -u root -p blogdb | gzip > ~/backups/blogdb_$(date +%F).sql.gz

2. Restoring from a Dump

• Uncompressed backup:

• mysql -u root -p blogdb < ~/backups/blogdb_2025-07-02.sql

• Compressed backup:

• gunzip < ~/backups/blogdb_2025-07-02.sql.gz | mysql -u root -p blogdb

3. Binary Logs for Point-in-Time Recovery

Enable binary logging by editing /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
log_bin         = /var/log/mysql/mysql-bin.log
binlog_format   = row
expire_logs_days= 10

Restart MySQL, then you can replay changes up to a specific timestamp.

Basic Performance Tuning

MySQL performs well out of the box, but a few tweaks can improve performance.

1. Adjust innodb_buffer_pool_size to ~70–80% of available RAM on a dedicated DB server:

2. [mysqld]
   innodb_buffer_pool_size = 2G

3. Enable query cache (for read-heavy workloads, though deprecated in later MySQL versions):

4. query_cache_type = 1
   query_cache_size = 64M

5. Configure connections and thread pool:

6. max_connections = 200
   thread_cache_size = 50

7. Slow Query Logging:

8. slow_query_log = 1
   slow_query_log_file = /var/log/mysql/mysql-slow.log
   long_query_time = 1

9. Analyze slow queries with mysqldumpslow:

10. sudo mysqldumpslow -a /var/log/mysql/mysql-slow.log

After making changes, restart MySQL:

sudo systemctl restart mysql

Conclusion

You now have a fully functional MySQL installation on your Ubuntu system, secured, configured for remote access, and primed for performance. You know how to create databases and users, perform backups and restores, and tune key parameters. Although this guide covers foundational topics, real-world deployments often require additional layers—high availability, replication, monitoring, and advanced security hardening.

Next Steps

• Explore MySQL replication for read scaling and failover.
• Implement automated backup scripts via cron.
• Monitor performance with tools like Percona Monitoring and Management (PMM).
• Harden your server by integrating with Vault for secret management.

With this knowledge, you can confidently manage MySQL on Ubuntu—whether for development projects, personal blogs, or production applications. Happy databasing!