Install MySQL or MariaDB on Ubuntu: Setup and Database

curl -sSL https://cp.lv/scripts/ustanovit-mysql-mariadb.sh | sudo bash

wget -qO- https://cp.lv/scripts/ustanovit-mysql-mariadb.sh | sudo bash

#!/usr/bin/env bash
# Install MySQL, create a database and a user
set -e
export DEBIAN_FRONTEND=noninteractive

DB_NAME=app_db
DB_USER=app_user
DB_PASS='StrongPassword123!'

# 1. Install the server (use mariadb-server for MariaDB)
sudo apt-get update
sudo apt-get install -y mysql-server
sudo systemctl enable --now mysql

# 2. utf8mb4 database and a user with rights to that database only
sudo mysql << EOF
CREATE DATABASE IF NOT EXISTS ${DB_NAME} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASS}';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'localhost';
FLUSH PRIVILEGES;
EOF

echo "Done: database ${DB_NAME} and user ${DB_USER} created."

MySQL and the compatible MariaDB are the most widespread relational databases for web applications. The commands are almost identical, so this guide covers both. Below: installation on Ubuntu, hardening and creating a database for a site.

Step 1. Install the server

For MySQL:

sudo apt update
sudo apt install mysql-server -y

For MariaDB (a lighter alternative, fully compatible):

sudo apt install mariadb-server -y

Enable autostart:

sudo systemctl enable --now mysql   # for MariaDB: mariadb

Check the status:

sudo systemctl status mysql

Step 2. Harden the server

The mysql_secure_installation script closes the typical holes in the default configuration:

sudo mysql_secure_installation

Answer as follows:

• Validate password plugin — optional (turns on password strength checks).
• Set root password — set a strong root password.
• Remove anonymous users — Yes.
• Disallow root login remotely — Yes.
• Remove test database — Yes.
• Reload privilege tables — Yes.

Step 3. Open the console

On a fresh installation, root logs in through the socket without a password:

sudo mysql

If you have set a password and want to log in the usual way:

mysql -u root -p

Step 4. Create a database and a user

Create the database with the utf8mb4 character set (emoji and every language are supported) and a separate user:

CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Grant rights on the needed database only (app_db.*), not on everything (*.*) — that is the principle of least privilege.

Step 5. Test the connection

mysql -u app_user -p app_db

Enter the password. The mysql> prompt means the login succeeded. Check the list of databases:

SHOW DATABASES;

Step 6. Remote access (optional)

If the backend runs on another server, create a user bound to a specific IP and allow the server to listen on it. Open the config in an editor (nano, vim or mcedit):

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

In the [mysqld] section, set the listen address:

bind-address = 10.0.0.5

Or create a separate override file with a single command — copy it and paste it into the console (MySQL picks up every .cnf in that directory):

sudo tee /etc/mysql/mysql.conf.d/zz-bind-address.cnf > /dev/null << 'EOF'
[mysqld]
bind-address = 10.0.0.5
EOF

CREATE USER 'app_user'@'10.0.0.10' IDENTIFIED BY 'StrongPassword123!';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'10.0.0.10';
FLUSH PRIVILEGES;

Open the port for the trusted source only:

sudo ufw allow from 10.0.0.10 to any port 3306
sudo systemctl restart mysql

FAQ

MySQL or MariaDB — which one should I pick? For most sites the difference is invisible. MariaDB is slightly lighter and fully compatible with applications written for MySQL. Oracle’s MySQL offers some enterprise features. The SQL syntax is identical.

Why does root log in only through sudo mysql? Socket authentication (auth_socket) is used by default. To enable password login: ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';.

What should I check on an “Access denied for user” error? Compare the user name, the host ('user'@'localhost' is not 'user'@'%') and the password. The host in the user record must match the source of the connection.

Why is the utf8mb4 character set needed? The old utf8 in MySQL stores at most 3 bytes per character and does not support emoji. utf8mb4 is full UTF-8 — use it by default.

Databases need a fast disk and a stable resource. For MySQL and MariaDB, look at the plans on the VPS hosting page and the dedicated database VPS.