How to Create a MySQL Database: Complete Guide

A database is the foundation of any dynamic web application. WordPress sites, e-commerce stores, CRM systems, forums — they all store information in MySQL databases. Proper database creation and configuration from the start will save you from future headaches.

Let's explore all methods of creating MySQL databases: from command line to graphical interfaces, with practical examples and recommendations.

Connecting to MySQL

Before creating a database, connect to your MySQL server. There are two main approaches.

Via Command Line

mysql -u root -p

The system will prompt for the root user password. After successful login, you'll see the mysql> prompt.

If MySQL is installed on a remote server:

mysql -h hostname -u username -p

Where hostname is the server address, and username is a user with database creation privileges.

Through phpMyAdmin

phpMyAdmin is a web interface for managing MySQL. Typically accessible at:

http://your-domain/phpmyadmin

After authentication, all operations are performed through the graphical interface without entering SQL commands.

Creating a Database — The Basics

Basic database creation command:

CREATE DATABASE database_name;

Example:

CREATE DATABASE shop;

This command will create a database named shop with default settings.

Database Naming Rules

MySQL imposes certain restrictions on names:

• Maximum length — 64 characters
• Allowed: Latin letters, digits, underscore _ and dollar sign $
• Name cannot start with a digit
• Case matters on Linux/Unix, but not on Windows
• Avoid MySQL reserved words (SELECT, TABLE, INDEX, etc.)

Good naming examples:

CREATE DATABASE my_store;
CREATE DATABASE blog_production;
CREATE DATABASE analytics_2024;

Bad examples:

CREATE DATABASE 123store;        -- starts with digit
CREATE DATABASE my-database;     -- hyphen not allowed
CREATE DATABASE select;          -- reserved word

Creating Database with Character Set and Collation

Character set determines which symbols can be stored in the database. Collation defines sorting and comparison rules for those symbols.

Full syntax:

CREATE DATABASE database_name
CHARACTER SET charset_name
COLLATE collation_name;

Modern Recommendation — UTF-8

To support all world languages, including emoji, use:

CREATE DATABASE my_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Parameter breakdown:

• utf8mb4 — full UTF-8 support, including 4-byte characters (emoji, rare hieroglyphs)
• utf8mb4_unicode_ci — case-insensitive sorting, correct for most languages

Alternative Options

For specific language with case sensitivity:

CREATE DATABASE german_site
CHARACTER SET utf8mb4
COLLATE utf8mb4_german2_ci;

For maximum performance (when case matters):

CREATE DATABASE fast_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_bin;

Binary collation (_bin) compares characters byte-by-byte — faster, but A and a will be different characters.

Legacy utf8 variant:

CREATE DATABASE old_style
CHARACTER SET utf8
COLLATE utf8_general_ci;

⚠️ Important: utf8 in MySQL is not full UTF-8, but a 3-byte version without emoji support. Always use utf8mb4.

Verifying Created Databases

After creating a database, make sure everything went smoothly.

List All Databases

The result will show all databases your user has access to:

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

Filter by Pattern

SHOW DATABASES LIKE 'shop%';

Shows only databases starting with "shop".

View Database Parameters

SHOW CREATE DATABASE my_app;

Displays the exact command used to create the database, including character set and collation.

Selecting a Database for Work

Before performing operations with tables, select an active database:

USE database_name;

Example:

USE shop;

After executing this command, all subsequent SQL queries will run in the context of the shop database.

Check current active database:

SELECT DATABASE();

Creating Database with Condition

Sometimes you need to create a database only if it doesn't already exist:

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

If the my_app database already exists, the command won't execute and won't throw an error — convenient for automation scripts.

Dropping a Database

⚠️ Critical: database deletion is irreversible. All tables and data will be lost.

Basic command:

DROP DATABASE database_name;

With condition:

DROP DATABASE IF EXISTS old_database;

If the database doesn't exist, there will be no error.

Example:

DROP DATABASE test_db;

Always create backups before deleting production databases!

Practical Scenarios

Creating Database for WordPress

CREATE DATABASE wordpress_site
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Then create a user and assign permissions:

CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'secure_password_123';
GRANT ALL PRIVILEGES ON wordpress_site.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;

Creating Multiple Databases for Different Environments

CREATE DATABASE myapp_production CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE myapp_staging CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE myapp_development CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This allows data isolation between production, staging, and development environments.

Database for Multilingual Application

CREATE DATABASE multilang_cms
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

utf8mb4_unicode_ci correctly handles text in any language: Cyrillic, Chinese characters, Arabic script.

Working Through phpMyAdmin

Creating a Database

1. Open phpMyAdmin
2. On the main page, find the "Databases" section
3. In the "Create database" field, enter the name
4. Select character set from dropdown (recommended: utf8mb4_unicode_ci)
5. Click "Create"

Deleting a Database

1. In the left panel, find the needed database
2. Click on it to open
3. Go to the "Operations" tab
4. In the "Remove database" section, click the corresponding button
5. Confirm the action

Exporting a Database

1. Select database in the left panel
2. Go to the "Export" tab
3. Choose method: "Quick" or "Custom"
4. Format: usually SQL
5. Click "Go"

The .sql file will be downloaded to your computer.

Importing a Database

1. Create an empty database
2. Select it in the left panel
3. Go to the "Import" tab
4. Click "Choose File" and specify the .sql file
5. Click "Go"

phpMyAdmin will load and execute all commands from the file.

Backup and Restore

Creating Backup via Command Line

Single database:

mysqldump -u username -p database_name > backup.sql

All databases:

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

With compression:

mysqldump -u username -p database_name | gzip > backup.sql.gz

Restoring from Backup

Regular file:

mysql -u username -p database_name < backup.sql

Compressed file:

gunzip < backup.sql.gz | mysql -u username -p database_name

Important: the database must exist before restoration. If you need to create it fresh:

mysql -u root -p -e "CREATE DATABASE new_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u username -p new_database < backup.sql

Modifying Existing Database Parameters

If a database already exists but with incorrect character set, you can change settings:

ALTER DATABASE my_app
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

⚠️ Warning: this changes default settings only for new tables. Existing tables need separate conversion:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Checking Database Sizes

Find out how much space each database occupies:

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

This command will output a list of databases with their size in megabytes.

Optimization and Maintenance

Checking Tables for Errors

USE my_database;
CHECK TABLE table_name;

Repairing Corrupted Tables

REPAIR TABLE table_name;

Optimizing Tables

OPTIMIZE TABLE table_name;

This command defragments the table and frees unused space.

Common Errors and Solutions

Error: ERROR 1007 (HY000): Can't create database 'dbname'; database exists

Cause: a database with this name already exists.

Solution: use CREATE DATABASE IF NOT EXISTS or choose a different name.

Error: ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'dbname'

Cause: user doesn't have database creation privileges.

Solution: log in as root and grant permissions:

GRANT CREATE ON *.* TO 'user'@'localhost';
FLUSH PRIVILEGES;

Error: after import, Cyrillic displays as "�����"

Cause: character set mismatch between database, tables, and client.

Solution: ensure database is created with utf8mb4. Before import, execute:

SET NAMES utf8mb4;

Error: ERROR 1064 (42000): You have an error in your SQL syntax

Cause: typo in command or use of reserved word without backticks.

Solution: check syntax. If database name is a reserved word, wrap it in backticks:

CREATE DATABASE `select`;

Though it's better to simply choose a different name.

Setting Default Character Set

To have all new databases created with proper character set, edit the MySQL configuration file.

For Linux: /etc/mysql/my.cnf or /etc/my.cnf

Add to the [mysqld] section:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

For Windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini

After changes, restart MySQL:

sudo systemctl restart mysql

Check current settings:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Automation Through Scripts

For automatic database creation in scripts, use:

#!/bin/bash
DB_NAME="my_new_database"
DB_USER="root"
DB_PASS="password"

mysql -u $DB_USER -p$DB_PASS <<EOF
CREATE DATABASE IF NOT EXISTS $DB_NAME
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
EOF

echo "Database $DB_NAME created successfully"

Such scripts are useful when deploying applications or migrating between servers.

Conclusion

Creating a MySQL database is a straightforward operation, but proper configuration of character set and collation from the start will save future headaches. Always use utf8mb4 for new projects — it guarantees compatibility with any languages and modern standards.

Core principles: choose meaningful database names, use proper character sets, regularly create backups, and don't forget about user permissions. The database is your application's heart, and it deserves careful attention.