How to Install and Configure the MySQL Database Server on Ubuntu

MySQL is one of the most popular open-source relational database management systems in the world. It serves as the data backbone for countless web applications, including the widely used LAMP stack (Linux, Apache, MySQL, PHP/Python). Installing and properly securing MySQL on an Ubuntu server is a fundamental skill for any systems administrator or backend developer.

Step 1: Install the MySQL Server Package

The MySQL server package is available directly from the default Ubuntu repositories.

1. Update your package index to ensure you get the latest version:
   sudo apt update
2. Install the MySQL server package:
   sudo apt install mysql-server
3. Press Y and hit Enter when prompted to confirm the installation.

Once the installation is complete, the MySQL service will start automatically. You can verify this by checking the systemd service status: systemctl status mysql.

Step 2: Run the Security Script

By default, a fresh MySQL installation is highly insecure. It allows anonymous users, remote root logins, and includes a test database that anyone can access. To fix this, MySQL includes a built-in security script.

Run the script using the following command:

sudo mysql_secure_installation

The script will guide you through several interactive prompts:

1. VALIDATE PASSWORD COMPONENT: You can choose to enforce strict password policies. (Press y to enable, or any other key to skip).
2. Remove anonymous users? Press y.
3. Disallow root login remotely? Press y (It is a major security risk to allow root to log in from anywhere except localhost).
4. Remove test database and access to it? Press y.
5. Reload privilege tables now? Press y to apply the changes immediately.

Step 3: Accessing the MySQL Shell

On modern Ubuntu systems, the MySQL root user is configured to authenticate using the auth_socket plugin by default. This means you do not need to enter a MySQL password to log in as root, provided you invoke the command using system sudo privileges.

To access the database shell, type:

sudo mysql

Your prompt will change to mysql>, indicating you are now interacting directly with the database engine.

Step 4: Creating a Dedicated User and Database

Best practices dictate that applications (like a WordPress site or a custom Node.js backend) should never connect to the database using the root account. You must create a dedicated database and a user with restricted privileges.

While inside the mysql> shell, execute the following SQL commands (don’t forget the semicolons at the end of each line!):

1. Create a new database:
   CREATE DATABASE app_database;
2. Create a new user and set a strong password:
   CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'Strong_Password_123!';
3. Grant the new user full permissions, but only on the database you just created:
   GRANT ALL PRIVILEGES ON app_database.* TO 'app_user'@'localhost';
4. Reload the internal cache to enforce the new permissions:
   FLUSH PRIVILEGES;
5. Exit the MySQL shell:
   exit

Your Ubuntu server is now running a secure, optimized MySQL instance, ready to host your application’s data.