How to Create a MySQL Database on Ubuntu

Creating a database is the first step when setting up a new app, WordPress site, or project on MySQL. This DevAnswers guide creates one from the Ubuntu command line with a proper utf8mb4 character set, then lists, selects, and safely removes it. Need a login for it too? See the companion guide on creating a user and granting access.

Prerequisites #

You will need:

• MySQL 8.0+ or MariaDB installed and running (see how to install MySQL)
• The MySQL root account or another admin user

Step 1: Create the Database #

Open the MySQL shell as an administrator:

sudo mysql

Create the database and set its character set in one statement. utf8mb4 (explained in Step 2) is the right default. CREATE SCHEMA is a synonym for CREATE DATABASE, and your account needs the CREATE privilege (the root user has it):

CREATE DATABASE devans_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

The bare form is just CREATE DATABASE devans_db;, but specifying the character set up front saves trouble later. Add IF NOT EXISTS to make the command safe to re-run, it skips the create (instead of erroring) when the database already exists:

CREATE DATABASE IF NOT EXISTS devans_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Keep the name lowercase with underscores and no spaces. On Linux, MySQL database names are case-sensitive by default (they map to directory names on disk), so devans_db and Devans_DB are two different databases, a common source of “unknown database” errors.

If the create fails with ERROR 1044 (Access denied), your account lacks the CREATE privilege, connect as root (or another admin) and try again. (Can’t get in as root because the password is lost? Reset the MySQL root password first.)

Step 2: Why utf8mb4? #

utf8mb4 stores up to four bytes per character and covers both the Basic Multilingual Plane and supplementary characters (emoji included), which is why MySQL recommends it for new applications.

Getting this right up front matters. The older utf8 alias stores only three bytes per character, so inserting a four-byte character (an emoji, or some CJK text) into a utf8 database fails with Incorrect string value. Starting with utf8mb4 avoids that class of bug entirely.

The collation decides how text is compared and sorted. utf8mb4_unicode_ci is a safe, case-insensitive general choice; MySQL 8.0 also offers the newer utf8mb4_0900_ai_ci (its own default), which does not exist on MariaDB, so utf8mb4_unicode_ci is the portable choice if you target both. Either is fine, just be consistent. Verify what the database actually got with:

SHOW CREATE DATABASE devans_db;

If you need to change the character set of an existing database, use ALTER DATABASE (note this sets the default for new tables; existing tables keep their own charset until you alter them too):

ALTER DATABASE devans_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Step 3: List and Select Your Database #

Confirm it was created by listing every database on the server:

SHOW DATABASES;

Database
devans_db
information_schema
mysql
performance_schema
sys

Alongside devans_db you will see the built-in system databases, mysql, information_schema, performance_schema, and sys. Leave those alone; MySQL needs them to run.

To run statements against it, select it as the default database with USE. It stays the default until you end the session or issue another USE:

USE devans_db;

At any point you can confirm which database is currently selected, useful in a long session where you have switched between several:

SELECT DATABASE();

Create a User for the Database #

A fresh database is only useful once an application can log in to it. Rather than connect as root, create a dedicated user and grant it access to devans_db only, following least privilege. That is a topic of its own, the full walkthrough (including the FLUSH PRIVILEGES myth) is in how to assign a user to a database.

Dropping a Database #

When you no longer need a database, remove it with DROP DATABASE. Before you do, it is worth selecting it and running SHOW TABLES; to be sure it holds nothing you still need, and double-checking the name, there is no undo. Use IF EXISTS so the command does not error if it is already gone:

DROP DATABASE IF EXISTS devans_db;

Summary #

Creating a MySQL database is a single CREATE DATABASE statement, made better by specifying utf8mb4 up front; from there you list with SHOW DATABASES, select with USE, and remove with a careful DROP DATABASE IF EXISTS. The usual next steps are creating a dedicated user for it (covered in the linked guide above) and setting up regular database backups.