How to Install MySQL on Ubuntu: Secure Configuration, Remote Access, and Backups

MySQL on Ubuntu: From a Bare Server to a Working Database

Welcome, dear reader!

Today, we will cover one of the most common tasks involved in preparing a server-side application: installing and performing the initial configuration of MySQL on Ubuntu.

Consider a fairly typical scenario: we have a VPS that will soon host a website, API, online store, or another service that needs somewhere to store its data.

This data may include:

• User accounts;
• Orders;
• Settings;
• Messages;
• Products;
• Transaction history.

The application needs a database to store all of this.

In our case, this role will be performed by MySQL Server.

However, simply installing the package and seeing an active status is not enough. A database is not a test service that can be deleted and set up again without consequences.

Mistakes involving access or backups can have considerably more serious consequences.

Therefore, rather than following this approach:

we will go a little further:

By the end, we should have more than just an installed DBMS—we should have a database that can serve as the foundation for a real-world application.

Why Installation Alone Is Not Enough

Installing MySQL on Ubuntu is straightforward.

In the simplest case, only a few APT commands are needed. The database server then starts and is ready to accept connections.

But this raises an important question: who exactly will be able to connect to it, and what will that user be able to do?

For a test environment on an isolated network, we could grant a single account all privileges and leave it at that.

This approach is unsuitable for a production project.

Suppose our application needs only one database: shop_db

It makes sense to create a separate user for it, for example: shop_app

and allow it to work only with this database.

The result looks like this:

The difference is much like giving an employee a key to their office rather than a set of keys to the entire building.

Both options allow the employee to access their workplace, but the second clearly grants more privileges than necessary.

The same principle applies to databases: users should be granted only the access their applications actually require.

Remote access is another matter.

Sometimes MySQL must be accessed not only from the VPS itself, but also from another server, an administrator’s workstation, or a backend service.

You could simply expose 3306 to the entire internet.

Technically, this works.

In practice, it is best avoided.

If remote access is genuinely required, we will restrict it at multiple levels:

Finally, even perfectly configured privileges cannot protect against accidental table deletion, a failed application update, or human error.

That is why we need one more essential component: a backup.

What We Will Have by the End

By the end of this process, we will have a working MySQL Server with a clear and controlled access model.

Conceptually:

We will not, however, leave the server in a state where anyone can attempt to connect.

We will also create a backup using mysqldump and verify the reverse operation—restoring from the backup.

This is important.

By the end, the workflow will look like this:

As a result, we will cover the entire basic lifecycle: from a clean Ubuntu server to a MySQL instance that an application can connect to with the required permissions and that can be restored from a backup when necessary.

We will start with the foundation: checking Ubuntu and installing MySQL Server.

Preparing Ubuntu and Installing MySQL Server

Checking the System

First, check the Ubuntu version: cat /etc/os-release

The output will include lines such as:

NAME=”Ubuntu”
VERSION=”24.04 LTS (Noble Numbat)”
VERSION_ID=”24.04″ If you are using Ubuntu 26.04 LTS, these values will reflect that version.

This check helps us identify the system we are working with and the repository from which the packages will be installed.

You can also check the architecture: dpkg --print-architecture

On most standard VPS instances, the output will be: amd64

Now check whether the server has enough disk space and memory for a basic installation:

df -h /

free -h

MySQL does not require extensive resources for a small test environment, but actual resource requirements will depend on the database size, number of queries, configuration, and the application itself.

At this stage, what matters is that the system is operational and the disk is not completely full.

If everything looks fine, you can proceed with the installation.

Installing MySQL via APT

First, update the package index: sudo apt update

APT retrieves up-to-date information about available software versions from the configured Ubuntu repositories.

Now let’s install MySQL Server: sudo apt install -y mysql-server

The -y option automatically confirms the installation.

APT downloads MySQL Server and the required dependencies, then adds a system service used to start the database on Ubuntu.

Here is a diagram illustrating the overall process:

Once the installation is complete, the database is physically present on the server.

Now, let’s make sure the service has started successfully.

Checking the service status

Let’s check MySQL via systemd: sudo systemctl status mysql --no-pager

We are interested in the line: Active: active (running)

This means that the MySQL process is running and ready to accept local connections.

For a quick check, you can use: systemctl is-active mysql

Expected response: active

Let’s also check whether the service is enabled to start automatically: systemctl is-enabled mysql

Under normal circumstances, we will see: enabled

In other words, MySQL will start automatically after the VPS reboots.

If the service failed to start for some reason, first try: sudo systemctl start mysql

If it is in the failed state, check the log: sudo journalctl -u mysql --no-pager -n 50

This is usually where you can find out why the database server failed to start.

In most cases, the problem is one of several fairly straightforward issues:

• An error in the MySQL configuration—for example, after manually editing mysqld.cnf;
• Port 3306 is already in use by another process;
• The disk has run out of free space;
• MySQL cannot access its files because of incorrect permissions;
• A failed update has left issues with packages or dependencies.

For example, if MySQL reports that it cannot bind to port 3306, you can check which process is already using it: sudo ss -lntp | grep 3306

Available disk space is checked using the familiar command: df -h /

In this situation, the log serves as a guide: instead of blindly restarting the service and changing settings, first check the specific error and then address its cause.

If MySQL is in the active state, the first stage is complete.

Performing Initial MySQL Hardening

MySQL is already installed and running, but for now it is functional only in the technical sense.

Before creating users, granting privileges, and especially enabling remote access, it makes sense to clean up the basic configuration.

MySQL provides a dedicated wizard for this: mysql_secure_installation

It will not make the server impenetrable at the click of a button, but it will guide you through several important initial steps and help remove anything that is clearly unnecessary.

Why mysql_secure_installation Is Needed

After MySQL is installed, some settings remain configured for initial setup and local use.

The mysql_secure_installation wizard helps you review and change several settings related to database server access.

Depending on the installed MySQL version, it may prompt you to:

• Configure password requirements;
• Change how the administrative account is secured;
• Remove anonymous users;
• Block unwanted remote logins as root;
• Remove the test database;
• Apply changes to the privilege tables.

First, we remove anything that will not be needed during normal operation, and then we create our own access control scheme.

Think of it as preparing a new apartment before moving in. First, we replace the temporary keys and close off unnecessary entry points. Then we decide who should have access to which rooms.

However, it is important to understand that mysql_secure_installation is only the first layer.

It does not replace:

• A dedicated application user;
• Proper privilege restrictions;
• A firewall;
• Backups;
• Secure remote access configuration.

We will cover these next.

Run the security wizard

Run: sudo mysql_secure_installation

The wizard will then ask a series of questions.

The exact set of questions and their wording may vary slightly depending on the MySQL version and how the administrator account is configured.

If the password strength validation component is available, MySQL may ask whether you want to use it.

For a production server, this provides a useful additional safeguard: it prevents you from accidentally setting an extremely weak password such as 123456.

The wizard may then offer to remove anonymous users.

They are generally unnecessary on a typical server, so it is best to disable this access.

The next important item concerns the root administrator account.

In MySQL, root is the user with the broadest privileges within the DBMS itself. It is not the same as the Linux root user, even though they share the same name.

Using MySQL root as an application’s day-to-day account is therefore an extremely bad idea.

We will later create a dedicated user with limited privileges for the application.

The wizard may also offer to remove the test database and access to it.

If you do not need it, there is no reason to keep it.

As you work through the wizard, it is best to read each question rather than automatically answering Y to everything. After all, this is not just another video game user agreement that we are used to accepting without reading. Although those are sometimes worth reading more carefully too—the story surrounding the Subnautica 2 terms clearly showed just how interesting the clauses hidden in an EULA can be.

Players were particularly outraged by language stating that purchasing the game did not actually grant ownership of it, but merely a license to use it.

The section on user-generated content raised even more concerns. Under the agreement, content submitted through the game or related services—such as player-created modifications and other creative works—could either become the company’s property or be licensed to it under an extremely broad, perpetual license, including the right to modify, distribute, and commercially use that content.

Given how much time people can invest in mods, artwork, and other fan-created content, it is unsurprising that the EULA quickly became a separate topic of discussion within the gaming community.

What Exactly Changes After Configuration

After mysql_secure_installation is complete, MySQL itself continues to operate much as before: the service remains running, the databases continue to exist, and the port remains available.

First and foremost, what changes is the access model.

Before configuration, the state can be roughly represented as follows:

In other words, we have not made the server “secure forever.” This is not the end of the process.

We have only laid the groundwork.

Going forward, database security will be built in several layers:

After completing the wizard, you can verify once again that MySQL is still running: systemctl is-active mysql

Expected: active

Creating a Database and a Dedicated User

We have completed the initial security setup.

The next step is to create a working database and a dedicated user account for the application.

It is important not to take shortcuts here.

You could connect the application to MySQL as root, grant it full access, and forget about it until the first problem arises.

But that would be like giving a cashier the keys not only to the register, but also to the accounting office, the server room, and the director’s office. You can imagine what would happen.

Why the Application Should Not Use the root Account

The root account in MySQL has virtually unrestricted administrative access to the database server.

It can:

• Create and drop databases;
• Create users;
• Modify permissions;
• Drop tables;
• Change system settings;
• Access objects that are entirely unrelated to the application.

Up to €20,000 in EU-Based IaaS Credits

Frankfurt infrastructure, predictable pricing, and expert migration support.

Get

This is appropriate for an administrator.

For a regular backend application, this is excessive.

Suppose we have an online store. It needs a single database: shop_db

The application has no reason to access:

• analytics_db
• internal_tools
• another_project

if such databases are added to the same server later.

It is therefore better to separate roles:

One of the fundamental security principles applies here—the principle of least privilege.

The idea is quite simple: users receive exactly the permissions they need to perform their tasks, and no more.

This helps protect against more than just attacks.

Even an ordinary application error is less dangerous if the application’s account simply cannot delete a database belonging to another application or change settings for the entire MySQL Server.

That covers the theory. Now let’s implement this setup in practice.

Log in to MySQL

On Ubuntu, administrative access to MySQL is often configured so that a local system user with sudo privileges can log in without entering a separate MySQL password.

Let’s try: sudo mysql

If everything is working correctly, the terminal prompt will change to something like: mysql>

Commands are now executed inside MySQL rather than in the standard Linux shell.

You can easily tell the difference:

• ubuntu@server:~$ — we are in Ubuntu.
• mysql> — we are interacting directly with the database server.

First, you can view the existing databases: SHOW DATABASES;

You will see several system databases, such as:

• information_schema
• mysql
• performance_schema
• sys

These are best thought of as MySQL system databases. You should not store the project in them.

We will create a separate database for the application.

Creating a New Database

Let’s create it: CREATE DATABASE shop_db;

If the command is executed successfully, MySQL will respond with something like: Query OK

Let’s check: SHOW DATABASES;

It should now appear among the other databases: shop_db

For now, however, the database is simply an empty space.

Think of it as a separate room that we have built but have not yet given anyone the key to.

The next step is to create a user who can work with this specific database.

Create a dedicated user

Let’s create a user account:

   CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD';

Each part has a specific meaning:

For now, use ‘localhost’

This means that the user will only be able to connect from the VPS itself.

This is important: in MySQL, a user is identified not only by their username but also by the host they connect from.

In other words, ‘shop_app’@’localhost‘ and ‘shop_app’@’203.0.113.25’ are effectively two different user accounts in MySQL.

This may seem a little unusual if you are used to standard Linux users, but it provides an additional level of control.

This will be very useful later when we configure remote access.

Be sure to use your own sufficiently strong password.

Grant Only the Necessary Privileges

The user has been created, but currently can do almost nothing with shop_db.

Now let’s grant it privileges:

   GRANT ALL PRIVILEGES ON shop_db.* TO 'shop_app'@'localhost';

Let’s examine the expression: shop_db.*

The asterisk here means all objects within the shop_db database.

In other words, the privileges apply to tables and other objects in this specific database, not to the entire MySQL Server.

Here is a visual diagram for clarity:

For a simple application, granting ALL PRIVILEGES within its own database is often convenient.

However, even this is not always necessary.

For example, if an application only needs to read data, you can grant it a much narrower set of privileges.

For example: GRANT SELECT ON shop_db.* TO 'shop_app'@'localhost';

Or read and modify access:

   GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_app'@'localhost';

In other words, access can be assembled from individual privileges:

This is where the principle of least privilege goes from an elegant theory to concrete SQL commands.

If the application needs to run migrations and create tables on its own, it will require more privileges.

If it only reads analytics data, SELECT may be sufficient.

In modern versions of MySQL, privileges take effect immediately after GRANT. A separate FLUSH PRIVILEGES command is generally not required after regular CREATE USER and GRANT statements.

Verifying Access

Simply trusting that the commands ran successfully is no longer enough.

First, let’s see which privileges the user has been granted: SHOW GRANTS FOR 'shop_app'@'localhost';

The output should include a line granting access to: shop_db.*

Now let’s exit the administrative session: EXIT;

Now let’s try connecting as the application: mysql -u shop_app -p

MySQL will prompt you for a password.

After logging in, let’s check: SHOW DATABASES;

The user should see the databases available to them, including: shop_db

Let’s switch to it: USE shop_db;

For a final check, create a small test table:

CREATE TABLE test_items (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL

);

Add a record:

INSERT INTO test_items (name)

VALUES (‘MySQL works’);

And let’s query it: SELECT * FROM test_items;

The expected result should look roughly like this (see the screenshot in this chapter):

We now have a complete, functional workflow:

The root administrative account remains separate and is not used for normal application operations.

For now, however, our user can connect only from the VPS itself.

This is the simplest and most secure option if the application runs on the same server. However, the database and application may sometimes be hosted on separate machines, in which case remote access must be enabled.

Configuring Secure Remote Access

Everything is already working correctly locally: an application on the same VPS can connect to shop_db as the shop_app user, while MySQL itself is not exposed externally unless necessary.

For many projects, you can stop here.

If the backend and database are hosted on the same server, it is safer to keep this setup: the application connects to MySQL through localhost, and port 3306 does not need to be exposed to the internet.

However, there are situations where remote access is genuinely necessary.

For example:

• The application runs on a different VPS;
• A separate backend server connects to the database;
• An administrator accesses the database from a dedicated machine;
• Multiple services use the same MySQL Server.

This creates a new task: exposing MySQL externally, but not to everyone.

This is where it is easy to make a mistake such as binding to 0.0.0.0:3306, opening the firewall to everyone, and allowing the user to connect from any address.

Technically, remote access will work.

But anyone on the internet will also be able to at least attempt to connect to your MySQL Server—and they most certainly will.

We do not need that kind of trouble.

Why You Should Not Expose MySQL to the Entire Internet

MySQL typically runs on TCP port 3306

If you make it accessible from any IP address, the database server becomes another public entry point.

An open port does not automatically mean an immediate compromise. However, it allows outsiders to:

• Discover your MySQL server through automated scanning;
• Continuously try to guess usernames and passwords;
• Look for vulnerabilities specific to the server version;
• Generate unnecessary load;
• Take advantage of mistakenly granted privileges if another configuration error has already been made elsewhere.

This creates a somewhat odd situation: we have just carefully created a separate user with limited privileges, only to put MySQL’s door right out on the street and tell anyone who wants to, “Go ahead, try to open it.”

Remote access should therefore be secured with multiple layers from the outset:

The important point is that these restrictions do not needlessly duplicate one another.

They back each other up.

If you make a mistake in one place, the second layer can still block an unwanted connection.

First, let us examine which network addresses MySQL itself accepts connections on.

Configuring bind-address

By default, MySQL on Ubuntu is usually configured to accept local connections only.

You can check which interface it is listening on for port 3306 with the following command: sudo ss -lntp | grep 3306

If we see something like 127.0.0.1:3306, it means MySQL only accepts connections from the VPS itself.

This is ideal for a local application.

However, a remote machine cannot access 127.0.0.1 on our server. For the remote machine, this address refers to its own localhost.

Therefore, if remote access is required, you need to change the network address that MySQL listens on.

The main configuration file is usually located here: /etc/mysql/mysql.conf.d/mysqld.cnf

Let’s open it: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the line: bind-address = 127.0.0.1

There are several options.

You can specify the IP address of a particular VPS interface if MySQL should listen only on that interface.

Alternatively, use: bind-address = 0.0.0.0

0.0.0.0 means accepting IPv4 connections on all of the server’s network interfaces.

It is important not to confuse two things here.

0.0.0.0 does not meanthat anyone automatically gained access to the database.

This simply means that MySQL is now ready to accept network connections not only via localhost.

We will separately restrict who can actually connect using the MySQL user account and the firewall.

After making the change, save the file and restart MySQL: sudo systemctl restart mysql

Check: systemctl is-active mysql

And again: sudo ss -lntp | grep 3306

If you used 0.0.0.0, you may see something like: 0.0.0.0:3306

In other words, we have opened the network door.

Now you need to decide exactly who is allowed to log in through it.

Restricting a User by IP Address

Earlier, we created: ‘shop_app’@’localhost’

This user can connect only locally.

If the application is now hosted, for example, on another server with IP 198.51.100.25, you can create a separate user account:

   CREATE USER 'shop_app'@'198.51.100.25' IDENTIFIED BY 'STRONG_PASSWORD';

And grant it privileges only on our database:

   GRANT ALL PRIVILEGES ON shop_db.* TO 'shop_app'@'198.51.100.25';

Full Control Over Your Kubernetes Infrastructure

Deploy production-ready Kubernetes with full control over nodes, storage, and networking on predictable-cost EU infrastructure with direct access to your resources.

Get

MySQL now distinguishes between two accounts:

• shop_app@localhost
• [email protected]

Although the username is the same, the connection origins are different.

This is a very useful feature of the MySQL access control model.

Put simply, this user exists, but can log in only from a specific address.

The variant ‘shop_app’@’%’ allows connections from any host.

It is convenient, but too permissive for a public server unless it is genuinely needed.

Therefore, instead of CREATE USER 'shop_app'@'%' …, it is better to use a specific IP address: CREATE USER 'shop_app'@'198.51.100.25' ...

Let’s check the privileges: SHOW GRANTS FOR 'shop_app'@'198.51.100.25';

MySQL now knows who is allowed to connect.

However, the request still needs to reach port 3306.

The next layer handles this.

Open port 3306 only to the required IP address

If the server uses UFW, do not run the following command: sudo ufw allow 3306

This command allows MySQL connections from anywhere.

We need to be much more precise.

Suppose the remote server has the following IP address: 198.51.100.25

Allow only this IP address to access port 3306:

   sudo ufw allow from 198.51.100.25 to any port 3306 proto tcp

Let’s check the rules: sudo ufw status

Conceptually, the result should be:

The restrictions now apply at two levels:

• Firewall → allows traffic only from the required IP address
• MySQL → accepts connections for shop_app only from this IP address

If the VPS is hosted by a cloud provider that also uses Security Groups or an external firewall, the rule must be checked at that level as well.

This is important: UFW may be configured perfectly, but the cloud firewall might still block port 3306 entirely.

Conversely, opening the port to the entire internet in the Security Group could inadvertently undo some of our careful configuration.

Testing the Remote Connection

Now switch to the remote machine that has been granted access.

The connection command is: mysql -h 203.0.113.10 -u shop_app -p

Where 203.0.113.10 is the public IP address of the MySQL server.

After entering the password, you should see the mysql> prompt.

Let’s check the database: USE shop_db;

And let’s try to query the test table we created earlier: SELECT * FROM test_items;

If our record is returned:

This confirms that the connection chain is working:

For completeness, you can also perform the reverse test by attempting to connect from an IP address that has not been granted access.

The connection should be blocked before normal MySQL authentication can even take place.

One important caveat should be noted here.

Although restricting access by IP is far better than exposing port 3306 to the entire internet, remote MySQL connections to sensitive production systems are often further secured with a private network, VPN, or SSH tunnel, or by configuring an SSL connection with certificate verification.

The reason is simple: IP filtering controls, who can reach the port, but does not by itself address all the security concerns associated with traffic between different networks. By default, the connection is not encrypted, and data is transmitted in plaintext.

For our basic configuration, the key safeguards are now in place: MySQL is not exposed to the entire internet, the user is restricted to a specific source, and the firewall allows connections only from the specified address.

If the remote connection still does not work after completing these steps, do not immediately revert the settings and open the port to everyone.

In the next chapter, we will examine the main causes, from bind-address and firewall settings to the classic Access denied error.

If the remote connection is not working

We will troubleshoot the connection layer by layer:

In other words, start with the network and work your way toward the database itself, checking only one thing at each step.

MySQL Listens Only on localhost

Let’s start with the most common case.

On the client machine, we run mysql -h 203.0.113.10 -u shop_app -p, but the connection cannot be established.

Let’s check which interface port 3306 is listening on on the MySQL server: sudo ss -lntp | grep 3306

If we see 127.0.0.1:3306, it means MySQL accepts only local connections.

A remote machine cannot reach this address.

Let’s review why.

127.0.0.1 always means this same computer.

Therefore: 127.0.0.1 on the VPS ≠ 127.0.0.1 on your computer

Each machine interprets localhost as referring to itself.

Open the configuration file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

And check: bind-address = 127.0.0.1

If remote access is required, change the value to the appropriate address. In our basic example: bind-address = 0.0.0.0

After making the change: sudo systemctl restart mysql

Check: systemctl is-active mysql

And again: sudo ss -lntp | grep 3306

We should now see, for example: 0.0.0.0:3306

If MySQL is now listening on an external interface, proceed to the next link in the chain.

The User Is Not Authorized for the Remote IP Address

Suppose the network is configured correctly, but MySQL still rejects the login attempt.

It is worth recalling a detail we discussed earlier: ‘shop_app’@’localhost’ and ‘shop_app’@’198.51.100.25’ are different MySQL accounts.

Therefore, the existence of a user named shop_app does not mean that the user is allowed to connect from any host.

Log in to MySQL: sudo mysql

And let’s check the user accounts:

   SELECT User, Host FROM mysql.user WHERE User = 'shop_app';

For example, if we see only:

the situation becomes clear.

The user exists, but only for local connections.

For the client with IP address 198.51.100.25 a corresponding user account is required: CREATE USER 'shop_app'@'198.51.100.25' IDENTIFIED BY 'STRONG_PASSWORD';

And the privileges:

   GRANT ALL PRIVILEGES ON shop_db.* TO 'shop_app'@'198.51.100.25';

Check: SHOW GRANTS FOR 'shop_app'@'198.51.100.25';

A common but tempting mistake here is to create the following instead of specifying a specific IP address: ‘shop_app’@’%’

This can indeed instantly “fix” the connection.

However, % means any host.

In other words, this is like someone who could not unlock a door with their key and therefore decided to take the door off its hinges.

The connection will work, but the chosen solution is questionable.

If the client’s static IP address is known, it is better to restrict access to that address.

The firewall is blocking port 3306

The next layer is the network.

MySQL may be listening correctly on 0.0.0.0:3306 and the user may be configured correctly, but the request simply does not reach the server.

Check UFW: sudo ufw status

For our authorized client, there should be a rule along these lines: 198.51.100.25 → 3306/tcp → ALLOW

If it is not present:

   sudo ufw allow from 198.51.100.25 to any port 3306 proto tcp

After that, run again: sudo ufw status

However, UFW is not the only firewall that may be in use.

If the VPS is hosted by a cloud provider, there may also be a Security Group or network firewall.

This can result in an interesting situation:

This is because the packet is blocked before it even reaches Ubuntu.

Therefore, when troubleshooting network issues, check both layers:

From an authorized remote machine, you can also check whether the TCP port itself is reachable: nc -vz 203.0.113.10 3306

If nc is installed, a successful connection indicates that the port is at least reachable.

If the port is completely unreachable, it is too early to troubleshoot SQL privileges.

We get an Access denied error.

But if the client can reach MySQL and receives a message such as: ERROR 1045 (28000): Access denied for user ‘shop_app’@’198.51.100.25’ — that is already a useful diagnostic indicator.

The network is working.

MySQL received the request.

The problem now lies with either authentication or user permissions.

Note the error message itself: ‘shop_app’@’198.51.100.25’

MySQL explicitly shows which user and source address it is currently trying to authenticate.

This is very useful.

If you expected: [email protected], but the error showed a different IP address, the server is not seeing the client as you expected. This can happen, for example, because of NAT or intermediary network infrastructure.

Let’s check the existing users:

   SELECT User, Host FROM mysql.user WHERE User = 'shop_app';

Then check the privileges: SHOW GRANTS FOR 'shop_app'@'198.51.100.25';

And, of course, make sure that we are entering the password for this specific account.

It is useful to distinguish between two types of errors here:

This distinction significantly narrows the troubleshooting scope.

Checking the MySQL Logs

If the previous checks did not reveal anything obvious, it is time to stop guessing and see what MySQL itself is reporting.

First, you can check the service using systemd: sudo journalctl -u mysql --no-pager -n 100

To view messages in real time: sudo journalctl -u mysql -f

Then retry the remote connection and check whether any new log entries appear.

Depending on the MySQL configuration, a separate error log may also be located at /var/log/mysql/error.log

View the last few lines: sudo tail -n 50 /var/log/mysql/error.log

Logs are especially useful when the issue involves more than just an incorrect password, such as service startup, network configuration, TLS, an authentication plugin, or other internal MySQL errors.

The entire troubleshooting process can be summarized in a simple map:

The key is not to try to fix every layer at once.

If the port is unreachable, user permissions are not relevant yet. Conversely, if MySQL returns Access denied, the network has already done its part.

Once the remote connection is working, the next task is to ensure data recoverability. Restricting access to the database is only half of database security. You must also be able to restore the data if it is accidentally deleted or corrupted.

Next, we will create a backup using mysqldump.

Creating a Backup Using mysqldump

Backing Up a Single Database

For our test database shop_db, the command is:

   mysqldump --no-tablespaces -u shop_app -p shop_db > shop_db_backup.sql

After you run the command, MySQL will prompt you for the user’s password.

The –no-tablespaces option excludes tablespace information from the dump. This allows a user with limited privileges to create a dump without being granted the global PROCESS privilege.

Without this parameter, MySQL 8 may return the following error: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

In our case, there is no need to grant shop_app the global PROCESS privilege solely for backup purposes, so we use –no-tablespaces.

Let’s break down the command:

High-Performance Virtual Machines

Deploy in seconds with full root access, reserved resources, built-in high availability, and predictable monthly pricing.

Get

Important: The user running mysqldump must have sufficient permissions to read the required database objects.

If shop_app is used exclusively by the application and its privileges have been deliberately restricted, it may be more convenient to create a separate account with privileges limited to backups.

For this example, we will keep things simple and use the current user.

Once the command completes, the file will appear in the current directory.

For example: ls -lh shop_db_backup.sql

This file now contains the SQL statements that describe the contents of the database at the time the backup was created.

Checking the Created File

The mere existence of the file does not guarantee that the backup was created correctly.

Sometimes you may end up with an empty file, a dump containing errors, or something entirely different from what you intended to save.

So, let’s first check the file size: ls -lh shop_db_backup.sql

For a database containing data, the file size should be greater than zero.

You can also inspect the beginning of the file: head -n 20 shop_db_backup.sql

It will contain internal SQL statements and mysqldump comments.

And to verify that our test table is actually present in the dump: grep -n "test_items" shop_db_backup.sql

If the table name is present, the table was included in the backup.

However, there is an important caveat.

Checking that the file exists is not the same as fully validating the backup.

The proper way to validate a backup is to restore it and confirm that the data can actually be recovered.

That is exactly what we will do in the next chapter.

For now, we have the dump itself.

Keep the Password Out of Command History

You might be tempted to write it like this:

   mysqldump --no-tablespaces -u shop_app -pMY_SECRET_PASSWORD shop_db > shop_db_backup.sql

The command is shorter, and you do not have to enter the password.

However, that is precisely why you generally should not do this.

If you specify the password directly on the command line, it may end up in:

• The shell history;
• The process list while the command is running;
• Logs or scripts;

The potential consequences are not difficult to imagine.

It’s better to leave it simply as:

   mysqldump --no-tablespaces -u shop_app -pMY_SECRET_PASSWORD shop_db > shop_db_backup.sql

Then enter the password when prompted: Enter password:

Characters are usually not displayed as you type—this is normal, so do not worry.

If backups run automatically on a schedule, entering the password manually each time is impractical. In this case, use a separate secure configuration file or another credential storage mechanism instead of putting the password directly in the cron command.

And another important point: the file itself: shop_db_backup.sql may also contain sensitive data.

If the database contains users, email addresses, orders, tokens, or other internal information, the SQL dump effectively becomes a copy of that data.

Therefore, you should not store the backup in an arbitrary public directory or grant read access to every user on the server.

For example, you can restrict access to the file: chmod 600 shop_db_backup.sql

Now only the owner can read and modify it.

A backup is therefore more than just a “safety file.”

It should be protected almost as carefully as the database itself.

Restoring the Database from a Backup

We already have a backup. However, the shop_db_backup.sql file alone does not prove that it will actually save our data in a critical situation.

So now we will do what many people put off until later: try restoring the dump.

It is best to restore it to a separate test database rather than over the production database.

Why? Because restoration involves writing data, not just reading it. If you accidentally import the dump into the wrong database, you could end up with duplicates or conflicts, or even overwrite data that you never intended to modify.

Preparing the Database for Recovery

Let’s create a separate database: CREATE DATABASE shop_db_restore;

To do this, log in to MySQL using an administrative account: sudo mysql

Next, run: CREATE DATABASE shop_db_restore;

Let’s check: SHOW DATABASES;

The following should appear in the list: shop_db_restore

Now let’s grant our user privileges on the test database:

   GRANT ALL PRIVILEGES ON shop_db_restore.* TO 'shop_app'@'localhost';

After a standard GRANT command, the privileges take effect immediately.

Exit: EXIT;

We now have a safe environment for testing:

In other words, we do not touch the production database at all.

Importing an SQL Dump

Now let’s restore the contents of the dump to the new database.

The command is as follows:

   mysql -u shop_app -p shop_db_restore < shop_db_backup.sql

This time, input redirection is used:

• Previously, we used: mysqldump → file
• Now we do the reverse: file → mysql

Let’s break down the command:

After you enter the password, the command may finish without displaying a reassuring message such as: Restore completed successfully

And that’s normal.

If no errors were displayed, MySQL simply executed the SQL statements from the file.

Essentially, the following happens:

In other words, mysql reads the dump from top to bottom and executes the statements saved by mysqldump.

But we won’t take the absence of errors at face value. Let’s verify the result ourselves.

Verifying the Restored Data

Let’s connect to the restored database: mysql -u shop_app -p shop_db_restore

Let’s view the tables: SHOW TABLES;

If our backup did indeed contain the test table, we will see: test_items

Now let’s look at the data: SELECT * FROM test_items;

We expect to see the same record we created earlier:

The backup can now be considered fully verified.

We have completed the full cycle:

This is why testing the restore process is just as important as creating the backup itself.

A file may exist, be a reasonable size, and even contain familiar SQL commands. However, only a successful restore confirms that it can actually be used to recover working data.

For a real production database, verification is typically more thorough: it includes checking the number of tables, key records, relationships between data, and the operation of the application itself using the restored copy.

For our test environment, however, it is enough to confirm that the structure and data have been restored.

We now have not only a working MySQL Server with restricted access, but also a verified restore procedure.

It is also worth noting that a locally stored backup protects against failures of the database itself, but not of the entire VPS. Therefore, in a production environment, the backup must also be copied and stored elsewhere. An .sql file is essentially plain text and compresses well in an archive, but that is beyond the scope of this article.

All that remains is to perform a quick final check of the entire configuration to make sure nothing has been overlooked.

Final Check

Let’s start with the service itself:

Now let’s verify access and backups:

If all these checks pass, it means we have assembled a fully functional configuration:

At this point, MySQL is not merely installed but ready for normal operation.

Conclusion

The setup is now complete.

We installed MySQL Server on Ubuntu, completed the initial security hardening, created a dedicated database and user, restricted the user’s privileges, and configured remote access without exposing port 3306 to the entire internet.

We also created a backup using mysqldump and, just as importantly, restored it to a test database. The result is a basic but fully functional setup that can serve as the foundation for a real-world application.

FAQ

Can a single MySQL user be used for multiple applications?

Technically, yes, but it is generally better to use separate accounts.

For example:

• shop_app → shop_db
• analytics_app → analytics_db
• admin_panel → admin_db

This makes it easier to manage permissions and understand what each service is allowed to do and what it actually does.

If all applications use a single account with broad privileges, an error in or compromise of one project automatically provides access to the other data as well.

MySQL allows accounts to be distinguished not only by username but also by the connection source, using the ‘user’@’host’ format.

Do I need to open port 3306 if the application is running on the same VPS?

No.

If the application and MySQL are running on the same server, you can restrict database access to localhost: 127.0.0.1:3306

The application will then connect to MySQL within the VPS, so there will be no need to expose the port externally.

Remote access should be enabled only when it is actually needed.

Which is more secure: ‘shop_app’@’%’ or a user restricted to a specific IP address?

A specific IP address provides tighter access control.

The entry ‘shop_app’@’%’, allows this account to match connections from any host, whereas ‘shop_app’@’198.51.100.25’ restricts the source to a specific address. 

If the client’s IP address is known and static, it is better to use it and enforce the same restriction in the firewall as an additional safeguard.

Is restricting access to MySQL with UFW enough?

UFW is an important security layer, but it should not be your only line of defense.

A sound setup looks like this:

Ubuntu lets you create a firewall rule for a specific IP address and TCP port, so port 3306 does not need to be open to the entire internet.

Why is mysqldump called a logical backup?

Because it does not copy the physical MySQL files byte for byte.

mysqldump generates a set of SQL statements that can be used to recreate the database object definitions and data.

In simple terms:

This makes the dump easy to read, transfer, and reimport using the mysql client.

Why doesn’t the existence of a backup.sql file necessarily mean that the backup is usable?

Because the file may be empty, incomplete, or contain a dump of the wrong database.

Even an apparently valid SQL file should at least be periodically restored to a separate test database.

In our case, the verification process was as follows:

Only then can we be certain that the data can actually be restored from the backup.

Do I need to run FLUSH PRIVILEGES after every GRANT?

No, not when privileges are changed using standard SQL statements such as CREATE USER, GRANT, ALTER USER, and others.

MySQL updates the privilege information automatically.

FLUSH PRIVILEGES is needed in other scenarios, such as after manually modifying the grant tables directly. Therefore, there is no need to run it after every GRANT “just in case.”

What exactly does mysql_secure_installation do?

It is not an automatic “make MySQL secure” button, but rather an initial configuration wizard.

Among other things, it helps secure administrative accounts and remove anonymous accounts, unwanted remote root access, and the test database.

Even after running it, you still need to create users, assign permissions correctly, and configure networking, the firewall, and backups.

Sources

1. MySQL 8.4 Reference Manual — mysql_secure_installation
2. MySQL 8.4 Reference Manual — Specifying Account Names / CREATE USER
3. MySQL 8.4 Reference Manual — mysqldump
4. Ubuntu Server Documentation — Firewall