Creating and Managing Databases in MySQL — Create, Show, Use, Drop, Rename, and Backup

SQL Databases — Creating, Managing, and Configuring

A database is a container that holds tables, views, procedures, and other objects. This tutorial covers how to create, select, rename, and delete databases, plus character sets and collations.

Table of Contents

• Create Database
• Show Databases
• Select a Database (USE)
• Rename Database
• Drop Database
• Database Best Practices
• Character Sets
• Collations

Create Database

CREATE DATABASE creates a new database.

Syntax:

CREATE DATABASE database_name;

Example:

CREATE DATABASE shop;

To avoid an error if the database already exists:

CREATE DATABASE IF NOT EXISTS shop;

You can also specify a character set and collation at creation time (more on those later):

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Show Databases

List every database on the server:

SHOW DATABASES;

Output looks like:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop               |
| sys                |
+--------------------+

The first four (information_schema, mysql, performance_schema, sys) are system databases that the server uses internally. Leave them alone.

To search for a specific database:

SHOW DATABASES LIKE '%shop%';

Select a Database (USE)

Before you can run queries against tables, you need to tell the server which database to use.

USE shop;

Every statement after this runs inside shop until you switch to another database.

Why this matters: Tables with the same name can exist in different databases. USE removes the ambiguity without you having to type database.table every time.

To check which database is currently selected:

SELECT DATABASE();

If none is selected, it returns NULL.

Rename Database

MySQL does not have a single RENAME DATABASE command. You have three workarounds:

1. Dump and reload (safest)

mysqldump -u root -p old_db > old_db.sql
mysql -u root -p -e "CREATE DATABASE new_db"
mysql -u root -p new_db < old_db.sql
mysql -u root -p -e "DROP DATABASE old_db"

2. Rename table by table (fastest for small databases)

Create the new database, then move every table into it:

CREATE DATABASE new_db;
RENAME TABLE old_db.table1 TO new_db.table1,
             old_db.table2 TO new_db.table2,
             old_db.table3 TO new_db.table3;
DROP DATABASE old_db;

Generate the RENAME statements automatically:

SELECT CONCAT('RENAME TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,
              ' TO new_db.', TABLE_NAME, ';')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'old_db';

Copy the output and run it, then DROP DATABASE old_db;.

3. InnoDB ALTER TABLESPACE (MySQL 8.0.13+)

Only works for individual InnoDB tables, not for renaming the database as a whole.

Drop Database

Deletes the database and everything inside it — tables, data, views, procedures. There is no undo.

DROP DATABASE database_name;

Safe version:

DROP DATABASE IF EXISTS database_name;

Before you drop, take a backup:

mysqldump -u root -p database_name > backup.sql

Database Best Practices

Naming

• Use lowercase letters, numbers, and underscores only: shop_v2, blog_platform.
• One word or underscore-separated, never spaces or hyphens.
• Keep names short but clear: crm not customer_relationship_management_database.

One application, one database

Don't dump multiple apps into the same database. Separate databases keep things clean, make backups easier, and let you grant permissions per app.

Character set

Always use utf8mb4 for new databases. It supports the full Unicode range including emoji.

CREATE DATABASE myapp
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Backups

Set up regular automated backups. For MySQL/MariaDB:

mysqldump -u root -p --all-databases > full_backup.sql

Character Sets

A character set defines which symbols (letters, digits, emoji, etc.) can be stored and how they are encoded as bytes.

Common character sets:

Always choose utf8mb4 unless you have a specific reason not to. The old utf8 in MySQL is actually utf8mb3 — it cannot store characters like 😀 or 🤦‍♂️.

Check the server's default character set:

SHOW VARIABLES LIKE 'character_set_server';

Check a database's character set:

SELECT DEFAULT_CHARACTER_SET_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'shop';

Collations

A collation is the set of rules that determines how strings are compared and sorted. It builds on top of a character set.

Example: In English, A and a are the same letter. In some collations they compare as equal; in others they don't.

Naming convention

utf8mb4_general_ci
│      │       │
│      │       └─ ci = case-insensitive / cs = case-sensitive / bin = binary
│      └───────── language or rule variant
└──────────────── character set it belongs to

Common collations for utf8mb4

Case sensitivity examples

With _ci (case-insensitive):

SELECT 'apple' = 'APPLE';  -- 1 (true)

With _cs (case-sensitive):

SELECT 'apple' = 'APPLE';  -- 0 (false)

With _bin (binary):

SELECT 'A' = 'a';  -- 0 (false) — different byte values

View and set collation

Check server collation:

SHOW VARIABLES LIKE 'collation_server';

Set at database level:

CREATE DATABASE blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Change an existing database:

ALTER DATABASE blog
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Note: Changing a database's character set or collation does not affect existing tables. You must alter each table separately:

ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Picking the right collation