In this guide we’ll install and configure a MySQL database service on an Ubuntu server. Specifically, we’ll be setting up MariaDB which is a forked version of MySQL that has some benefits you can read about here. In terms of usage, MySQL and MariaDB are effectively the same.
This guide is designed with new servers in mind. If you have an existing server that already has MySQL installed and you’re looking to migrate to MariaDB, check out this guide: Migrating from MySQL to MariaDB.
Check for existing installations
Before we begin, let’s do a quick check to see if MySQL or MariaDB is already installed as it’s possible your server came pre-setup with it. To do this, run the following command:
> mysql --version
If this reports back Command not found or No such file or directory, neither MySQL or MariaDB are installed so continue to the installation steps below.
If it reports back with version details, MySQL or MariaDB is already installed.
Example output if you have MariaDB installed (version numbers may vary):
> mysql --version
mysql Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
If you already have MariaDB installed, you can skip the Installation section below and go straight to Starting the database service.
Example output if you have MySQL installed (version numbers may vary):
> mysql --version
mysql Ver 8.0.32-0ubuntu0.22.04.2 for Linux on x86_64 ((Ubuntu))
If you have MySQL instead of MariaDB installed, and this is a new server, I suggest deleting your existing MySQL installation before proceeding with the MariaDB installation.
Before I explain how to delete MySQL, though, note that this guide is designed with new servers in mind where you are installing or setting up a database service for the first time. If you have done any MySQL-related work in the past (e.g. created databases), be aware that the following deletion steps would destroy that work. That warning aside...
To delete MySQL, first run the following command to make sure it’s not running:
> sudo systemctl stop mysql
Then remove MySQL and any of its related packages:
> sudo apt remove --purge mysql-*
Remove the MySQL data directory, configuration file, and logs:
> sudo rm -rf /usr/bin/mysql /var/lib/mysql /etc/mysql /var/log/mysql
Remove any packages that were automatically installed because they were dependencies of MySQL:
> sudo apt -y autoremove
MySQL should now be uninstalled and you can proceed with installing MariaDB...
Installation
To install MariaDB, first run the following command to update the package index on your server:
> sudo apt update
Then, run this command to install MariaDB:
> sudo apt install mariadb-server
Starting the database service
With MariaDB installed, you can start the database service:
> sudo systemctl start mysql
The above command won’t have any output, so to confirm it worked you can check the status of the database service using the following command:
> sudo systemctl status mysql
Example output:
mariadb.service - MariaDB 10.6.12 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled)
Active: active (running) since Fri 2023-06-02 03:28:06 UTC; 39s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 2669 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
Process: 2670 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2672 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environme>
Process: 2713 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 2715 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
Main PID: 2701 (mariadbd)
Status: "Taking your SQL requests now..."
[...etc...]
Secure your installation
After installation, it’s suggested you secure your installation with the following command:
> sudo mysql_secure_installation
Here are a summary of responses for each prompt it presents you with:
- When it asks
Enter current password for root (enter for none):leave it blank and hit enter - When it asks you if you want to...
-
Change the root password?, enter No. -
Remove anonymous users?, enter Yes. -
Disallow root login remotely?, enter Yes. -
Remove test database and access to it?, enter Yes. -
Reload privilege tables now?, enter Yes.
-
Authentication
Next up, you’ll probably want to start to work with your database service via the mysql command prompt. This will allow you to create/manage databases as well as users. Before you do this, though, we have to talk about authentication...
MariaDB (as of v10.4.3) uses the unix_socket authentication plugin by default which means you can authenticate with your database service via your server’s systems credentials (ref).
In other words, if you’re acting on your server with root privileges, you will automatically be authenticated to interact with your database as the root user.
To demonstrate this, run the command mysql -u root with root privileges (either by being logged into the server as the root user, or prefixing the command with sudo so its executed with root privileges).
This should connect you to the mysql command prompt as the MySQL root user without requiring any additional authentication.
Example output:
> mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 31
Server version: 10.6.12-MariaDB-0ubuntu0.22.10.1 Ubuntu 22.10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
What next?
At this point you can start issuing SQL commands against your database service. For example, you’ll probably want to start creating databases and users to interact with those databases. For more on that, check out this guide: MySQL Command Line - Database and User Creation.
Related, if you’re working with a web framework like Laravel, I have a guide specific to MySQL setup there: Deploying Laravel - MySQL (or MariaDB) database setup (Ubuntu server)