Deploying Laravel - MySQL / MariaDB database setup (Ubuntu)

In some of my recent guides, I covered the steps for deploying an existing Laravel application to an Ubuntu server running either Nginx or Apache.

As a follow-up, this guide will show how to set up and configure your application’s MySQL or MariaDB database connection. The instructions are the same for Apache or Nginx.

Confirm database service is installed

This guide assumes you already have MySQL or MariaDB installed on your server. To see if this is the case, run the following command:

> sudo systemctl status mysql

If this reports back that the mysql command is not found you need to first follow this guide: Setting up MySQL / MariaDB on an Ubuntu server.

Example output if you have MySQL installed:

 mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2023-06-01 18:08:59 UTC; 8h ago
    Process: 39549 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 39557 (mysqld)
     Status: "Server is operational"
[...etc...]

As an alternative to the above output you may see mariadb.service - MariaDB database server instead of mysql.service - MySQL Community Server. MariaDB is an alternative version of MySQL but in terms of usage, they are effectively the same. Either MySQL or MariaDB will work for the purposes of this guide.

Confirm database service is running

Note from the above output that line 3 reports Active: active (running), indicating your database server is running. If it’s not, start it with this command:

> sudo systemctl start mysql

The above command will not produce any output, so you can run sudo systemctl status mysql again to confirm it worked and your database service is actively running.

Create a new database

Next, lets enter the mysql command prompt in order to create a new database for our application.

In the video, I do that with the following command and I will automatically be connected to the prompt without having to enter a password.

> sudo mysql

If this does not work for you as shown, refer to the video as I discuss several different scenarios you might face when attempting this step.

New database

Once you’re at the mysql command prompt you can run the following SQL command to create a new database for your Laravel application, swapping in demo with whatever database name you want:

mysql> CREATE DATABASE demo;

Note how every statement we’ll run should end with a ; to terminate the command. Without it, when you hit Enter, the command will not actually run.

Also, if you need to exit the mysql command prompt, run the command exit;.

You can confirm your database was created by running SHOW DATABASES:

mysql> SHOW DATABASES;

Example output:

+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

New user

Next, we’ll create a new MySQL user that will connect to this database. Do that with the following command swapping in demoAdmin and your-password-here with whatever username/password you want.

mysql> CREATE USER 'demoAdmin'@'localhost' IDENTIFIED BY 'your-password-here';

It’s a good idea to create separate users for each database you create, so that if one user is compromised, not all your databases will be compromised.

Next, we need to grant the demoAdmin user privileges to manage the demo database. The following command will grant all privileges to all tables (*) within the demo database:

mysql> GRANT ALL PRIVILEGES ON demo . * TO 'demoAdmin'@'localhost';

You can read more about privileges here.

In terms of database setup, that’s all we need to do from the mysql command prompt. We’ll now switch gears to our Laravel application to connect to our database and build our tables via Laravel migrations.

Configure Laravel

Within your Laravel application, open your .env file and update the following environment variables:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=demo
DB_USERNAME=demoAdmin
DB_PASSWORD=your-password-here

DB_CONNECTION should stay at the default value of mysql as the database driver we’ll be using (This is true even if you’re using MariaDB)
DB_HOST can typically be left to 127.0.0.1 when your database service is operating on the same server as your web server. If you’re using an external database server, you would want to update the host address as appropriate.
DB_DATABASE should match the name of the database you created in the above steps.
DB_USERNAME and DB_PASSWORD should match the info for the mysql user you created in the above steps.

Test it - Run migrations and seeds

To confirm your connection is set up, you can run your migrations to build your application’s database tables:

> php artisan migrate

If set up, and if working in a development context, you can also run your seeds to fill your tables with sample data:

> php artisan db:seed

Returning to the MySQL command prompt, you can run some queries to check that the tables and data were set up as expected:

mysql> USE demo;
mysql> SELECT * FROM users;

And of course, you can visit your application in the browser and confirm any database interactions are working as expected.