How to create a database in MySQL

Creating a MySQL database is one of the first things you do when setting up a WordPress site, a web application, or any project that stores data. The process itself takes one command, but doing it correctly means choosing the right character set, creating a user with appropriate privileges, and verifying that everything works before pointing your application at it.

This guide covers database creation from the command line, from phpMyAdmin, and through hosting control panels, with practical context for WordPress installations throughout.

Connecting to MySQL#

Before you can create anything, you need to connect to the MySQL server. How you connect depends on your access level.

Command line (SSH)

If you have SSH access to the server, connect to MySQL with:

mysql -u root -p

Enter the root password when prompted. You will see the MySQL prompt:

mysql>

If your server uses a non-default socket path or port:

mysql -u root -p --socket=/var/run/mysqld/mysqld.sock
mysql -u root -p --port=3306 --host=127.0.0.1

Without entering the MySQL shell

You can run individual SQL commands without entering the interactive shell by using the -e flag:

mysql -u root -p -e "CREATE DATABASE mydb"

This is useful for scripting and automation. The command runs, prints any output, and returns you to the bash prompt.

MySQL client only

If you are on a machine that does not run MySQL server (a development laptop, a jump host), you need the MySQL client package:

# Rocky Linux / RHEL / CentOS
sudo dnf install mysql

# Ubuntu / Debian
sudo apt install mysql-client

Then connect to a remote server:

mysql -u username -p -h db.example.com

For a complete walkthrough of remote MySQL connectivity including user grants, firewall rules, and SSL requirements, see How to allow remote MySQL connections.

Creating a database#

Basic syntax

CREATE DATABASE database_name;

That is the minimum. MySQL creates a database with the server’s default character set and collation. On MySQL 8.0+, the default is utf8mb4 with utf8mb4_0900_ai_ci collation.

With character set and collation

Always specify the character set explicitly rather than relying on the server default. This prevents problems when migrating between servers with different defaults:

CREATE DATABASE wordpress_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Why utf8mb4? The utf8mb4 character set supports the full Unicode range including emoji, Chinese/Japanese/Korean characters, and mathematical symbols. MySQL’s utf8 is actually utf8mb3 , which only supports characters up to 3 bytes and cannot store emoji or some less common Unicode characters. WordPress has required utf8mb4 since version 4.2.

Which collation? Collation determines how strings are compared and sorted. The two most common choices for WordPress:

• utf8mb4_unicode_ci  – works on MySQL 5.7 and 8.0+. Case-insensitive, accent-insensitive. The safe choice for compatibility.
• utf8mb4_0900_ai_ci  – MySQL 8.0+ only. Uses the Unicode 9.0 algorithm for more accurate sorting. Default on MySQL 8.0+.

If your site may ever be migrated to a server running MySQL 5.7, use utf8mb4_unicode_ci . If you know the server will always be MySQL 8.0+, either collation works.

Using IF NOT EXISTS

If you are writing a script or automation that may run multiple times, use IF NOT EXISTS to prevent errors when the database already exists:

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

Without IF NOT EXISTS , running the command a second time returns: ERROR 1007 (HY000): Can't create database 'wordpress_db'; database exists .

Verifying the database was created#

List all databases

SHOW DATABASES;

Output:

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

The first four are MySQL system databases. Your new database should appear in the list. For a deeper guide on listing and navigating databases, see How to show databases in MySQL.

Check character set and collation

Verify the database was created with the correct character set:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'wordpress_db';

Output:

+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_unicode_ci     |
+----------------------------+------------------------+

Switch to the new database

USE wordpress_db;

The prompt changes to confirm:

Database changed

From this point, all SQL commands operate on wordpress_db unless you specify another database explicitly. You can check which database is currently selected:

SELECT DATABASE();

Creating a user for the database#

A database without a user is useless to an application. WordPress needs a MySQL user with credentials to connect to the database. Never use the MySQL root account for WordPress or any web application.

Create the user

CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'strong_password_here';

The @'localhost' part means this user can only connect from the same server where MySQL is running. This is correct for WordPress installations where PHP and MySQL are on the same machine, which is the standard setup.

If you need the user to connect from a different server:

CREATE USER 'wp_user'@'10.0.1.50' IDENTIFIED BY 'strong_password_here';

Replace 10.0.1.50 with the IP address of the connecting server. Never use @'%' (any host) in production – it allows connections from any IP address.

Grant privileges

Give the user full privileges on the WordPress database only:

GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';

The wordpress_db.* means all tables in the wordpress_db database. The user has no access to any other database on the server.

Apply the privilege changes:

FLUSH PRIVILEGES;

FLUSH PRIVILEGES reloads the grant tables from the MySQL system database. In MySQL 8.0+, it is technically not required after GRANT statements (the changes take effect immediately), but running it is harmless and ensures consistency.

Verify the grants

SHOW GRANTS FOR 'wp_user'@'localhost';

Output:

+----------------------------------------------------------------+
| Grants for wp_user@localhost                                   |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wp_user`@`localhost`                    |
| GRANT ALL PRIVILEGES ON `wordpress_db`.* TO `wp_user`@`localhost` |
+----------------------------------------------------------------+

USAGE on *.* means the user can connect to MySQL but has no global privileges. ALL PRIVILEGES on wordpress_db.* means full access to that specific database. This is the correct configuration.

For a detailed guide on MySQL privilege management including the principle of least privilege, see How to show and manage MySQL users.

Complete workflow in one block

Here is the entire database and user creation process as a single script:

-- Create the database
CREATE DATABASE IF NOT EXISTS wordpress_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Create the user
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'strong_password_here';

-- Grant privileges
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';

-- Apply changes
FLUSH PRIVILEGES;

From the command line without entering the MySQL shell:

mysql -u root -p -e "
CREATE DATABASE IF NOT EXISTS wordpress_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wp_user'@'localhost' IDENTIFIED BY 'strong_password_here';
GRANT ALL PRIVILEGES ON wordpress_db.* TO 'wp_user'@'localhost';
FLUSH PRIVILEGES;
"

Creating a database from phpMyAdmin#

If you do not have SSH access, phpMyAdmin provides a web interface for database management.

1. Log in to phpMyAdmin (usually accessible through your hosting control panel)
2. Click the Databases tab at the top
3. Under “Create database,” enter the database name
4. Select  utf8mb4_unicode_ci  from the collation dropdown
5. Click Create

The database appears in the left sidebar. To create a user and assign it to the database:

1. Click the new database in the sidebar
2. Go to the Privileges tab
3. Click Add user account
4. Enter a username and password
5. Under “Database for user account,” check “Grant all privileges on database [your database]”
6. Click Go

phpMyAdmin generates and executes the same SQL commands described above.

Database naming conventions#

MySQL database names have a few constraints:

• Maximum 64 characters
• Can contain letters, numbers, underscores, and dollar signs
• Case sensitivity depends on the operating system (case-sensitive on Linux, case-insensitive on Windows and macOS)
• Cannot be a MySQL reserved word (like  database ,  table ,  select )

If you need to use a reserved word or special characters, wrap the name in backticks:

CREATE DATABASE `my-database`;

Practical naming conventions:

• WordPress sites: Use the domain name or a short identifier:  example_com ,  blog_db ,  shop_db
• Multiple environments: Include the environment:  myapp_production ,  myapp_staging
• Multi-tenant setups: Use a prefix:  client_acme ,  client_globex

Avoid spaces and hyphens even though backtick-quoting allows them. Many tools and scripts do not handle them correctly.

Configuring WordPress to use the database#

After creating the database and user, update wp-config.php with the connection details:

define('DB_NAME', 'wordpress_db');
define('DB_USER', 'wp_user');
define('DB_PASSWORD', 'strong_password_here');
define('DB_HOST', 'localhost');
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

Leave DB_COLLATE empty – WordPress will use the database’s default collation, which you already set to utf8mb4_unicode_ci during creation.

The $table_prefix variable (default wp_ ) prepends a string to all table names. If you are running multiple WordPress installations on the same database (not recommended but sometimes done), change the prefix for each installation.

Test the connection:

wp db check

If the connection is correct, WP-CLI reports the status of each table. If the credentials are wrong, you see “Error establishing a database connection” – see Error establishing a database connection in WordPress: how to fix it for the diagnostic walkthrough.

Dropping a database#

To delete a database and all its contents:

DROP DATABASE wordpress_db;

This is irreversible. All tables, data, stored procedures, and views in the database are permanently deleted. There is no undo. Always create a backup before dropping:

mysqldump -u root -p wordpress_db > wordpress_db_backup.sql

To prevent errors if the database does not exist:

DROP DATABASE IF EXISTS wordpress_db;

Security considerations#

Do not use root for applications

The MySQL root account has global privileges on every database. If a WordPress vulnerability exposes the database credentials, an attacker with root access can read, modify, or delete every database on the server – not just the WordPress database. Always create a dedicated user per application with privileges limited to that application’s database.

Strong passwords

MySQL passwords should be at least 16 characters, mixing uppercase, lowercase, and digits. Avoid special characters in MySQL passwords if possible – some characters conflict with shell escaping or PHP string parsing in wp-config.php, causing hard-to-debug connection failures.

One database per WordPress installation

Each WordPress site should have its own database with its own user. Sharing a database between WordPress installations (using different table prefixes) means a vulnerability in one site exposes the other site’s data. The isolation benefit of separate databases is worth the minimal overhead.

Localhost connections only

If PHP and MySQL are on the same server (which is the standard WordPress setup), the MySQL user should be restricted to @'localhost' . This means even if the credentials leak, they cannot be used from a remote machine.

How Hostney handles database creation#

On Hostney, databases are created through the control panel under Hosting > MySQL Databases. The process is streamlined:

Database creation. Click “Create database” and the system generates a unique database name automatically. The character set is always utf8mb4 with utf8mb4_unicode_ci collation – no manual selection needed, no risk of choosing the wrong encoding.

User creation. Users are created separately under Hosting > MySQL Users with auto-generated usernames and strong passwords (minimum 16 characters, alphanumeric). You then assign users to databases through the access management interface.

Per-account isolation. Each hosting account’s databases and users are isolated from other accounts on the same server. One account cannot see or access another account’s databases, even though they share the same MySQL server. This is enforced at both the application level and the MySQL privilege level.

Size management. Each database has a configurable size limit (default 1024 MB). The platform monitors database sizes in real time. If a database exceeds its limit, it is switched to read-only mode (SELECT and DELETE only) so you can clean up data without the site going completely offline. Once the size is back under the limit, full access is restored automatically.

phpMyAdmin access. Single sign-on access to phpMyAdmin is available from the control panel. You do not need to know the database credentials to browse or manage your data.

Summary#

Creating a MySQL database is a three-step process: create the database with the correct character set ( utf8mb4 ), create a user, and grant that user privileges on the database. Use utf8mb4_unicode_ci collation for WordPress compatibility across MySQL versions. Never use the root account for application connections, and restrict users to localhost when PHP and MySQL are on the same server.

The commands are simple. The important part is doing it correctly from the start – wrong character sets cause data corruption, overly broad privileges create security risks, and missing the FLUSH PRIVILEGES step can leave you debugging connection failures that should not exist. For related MySQL operations, see How to show databases in MySQL and How to show and manage MySQL users.