How to Create a MySQL or MariaDB Database in CMD, XAMPP and Linux

How to create a MySQL database using CMD, XAMPP, or the Linux terminal can be learned with a few basic SQL commands: create a database, select a database, create a table, and set up users. However, in modern XAMPP, the bundled database component is actually MariaDB, not always Oracle’s MySQL. Many core commands are compatible, but features and versions need to be differentiated.

This guide uses a new, safe local database example for learning. Destructive commands from the old tutorial, including CREATE OR REPLACE DATABASE, which could replace an existing database, have been moved to the archive with a warning. Never run such commands on a production database without understanding the consequences and ensuring backups have been tested.

Differences between MySQL, MariaDB, XAMPP, and phpMyAdmin

1. Preparation before creating a database

For Windows, download XAMPP from the official Apache Friends and start the Apache and MySQL services (button names on the panel, but the server is often MariaDB). Check out install XAMPP Windows and phpMyAdmin if you’re more comfortable using a graphical interface. For Linux, install the MySQL/MariaDB server from the system’s official repositories and use an authorized user’s terminal. Installing a database doesn’t require opening port 3306 to the rest of the internet.

If you’re new to web programming languages, start with database naming, data types, users, and backup methods. Learning to code can help you understand the differences between Windows, Linux, SQL, and PHP shells.

2. Login to MySQL or MariaDB via Windows CMD

Once the service is running, open the XAMPP Shell or Command Prompt. In a default installation, the client is located at C:\xampp\mysql\bin\mysql.exe. Adjust the directory to reflect your actual installation; modern versions may support the mariadb executable.

cd C:\xampp\mysql\bin
mysql -u root -p

Enter your password when prompted. Some older XAMPP development installations have a local root account without a password, which should not be considered safe for an internet-connected server. Use an administrative account only for setup, not for everyday applications.

3. Create a new database without overwriting old data

Choose a clear name, for example app_demo_2026. Run the following code on the server you own:

CREATE DATABASE IF NOT EXISTS app_demo_2026
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE app_demo_2026;

Order IF NOT EXISTS avoid error when name already exists, but does not confirm existing schema matches. Do not replace this command with CREATE OR REPLACE DATABASE, DROP DATABASE, or overwrite options on servers that store important data.

4. Create an application user with limited rights

Separating administrative and application users helps limit risk. For your own dev server, create an account with a strong password and grant it permissions to a specific database, not the entire system:

CREATE USER 'appdemo'@'localhost' IDENTIFIED BY 'GANTI_DENGAN_PASSWORD_KUAT_UNIK';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX
ON app_demo_2026.* TO 'appdemo'@'localhost';
SHOW GRANTS FOR 'appdemo'@'localhost';

Use a truly unique password and store it in a secret manager. The privileges above are just examples for learning; production applications require a custom-designed access matrix. Do not share passwords in tutorial screenshots.

5. Create a table and test a simple query

CREATE TABLE IF NOT EXISTS kontak_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL,
  dibuat_pada TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO kontak_demo (nama, email)
VALUES ('Contoh Pengguna', '[email protected]');
SELECT id, nama, email FROM kontak_demo;

Run only on sample databases. For production, use schema migrations, backups, and the principle of least privilege; do not include real personal data in the tutorial examples. Explanation of create a PHP table can help when you connect tables with PHP.

6. How to create a database from the Linux terminal

Once the database server is installed, the administrator can log in with sudo mysql or mysql -u root -p according to the authentication method set. SQL creates databases the same as in Windows, but the management of services, system users, and configuration files depends on the distribution. If you learn Linux through Ubuntu WSL, remember WSL networks are not identical to production servers.

For WordPress CMS, the database is stored separately from the PHP/WordPress files. The WordPress installation process requires a pre-created database and an account with adequate permissions.

7. General backup and troubleshooting

Before WordPress migration or server maintenance, create a backup and test the restore process. backup database otomatis explains database automation with mysqldump. If your website uses a dynamic website and multiple website plugins, document the PHP version, database, plugins, and maintenance schedule. IP address and Cloudflare are also relevant when restricting external server network access.

Safe practices before working with a production database

A local database for learning is different from a WordPress database serving real customers. Before running DDL commands like CREATE TABLE or ALTER TABLE, create a restoreable snapshot and confirm that you are connected to the correct host and database. Many incidents occur because administrators use a terminal on a production server when they think they are on a staging server.

Check connections and permissions

Commands like SELECT DATABASE(); can help determine the currently selected database. Use SHOW GRANTS to check account permissions. Separate backup, schema migration, and application credentials. In a server environment, host restrictions and database firewalls should be designed according to the architecture; the DB port should not be publicly accessible.

Don’t store passwords in screenshots and repositories

Older tutorials often show root logins, terminal prompts, or sample emails as illustrative data. For a real project, replace all examples with dummy data. Don’t commit passwords to a Git repository, don’t upload your customer database to a public debugging service, and don’t give everyone access to phpMyAdmin just because the URL is easy to find.

Example of a repeatable database test flow

Use versioned migration scripts and perform row count validation or integrity checks after risky tasks. In modern systems, automation jobs should log the schema version, operation results, and relevant error messages, without logging passwords.

When a database is used for WordPress or a business application, every change requires a change management process: backup, staging, approval, deployment schedule, monitoring, and rollback. This is more important than simply being able to run the CREATE DATABASE command in a terminal.

Archive of 3 screenshots and 26 SQL snippets from the original tutorial

The original 2020 example shows a MariaDB database and testing on a real server. The snippet, results table, and example identity are preserved for historical documentation. No Recommended commands for production. Some legacy commands may replace the database or use outdated date formats and data types.

View screenshots and 26 historical snippets (don’t run without auditing)

Windows Key + R

Ketik CMD

Klik OK

Login SSH dengan menggunakan terminal seperti Putty

mysql -u root -p

SHOW DATABASES;

MariaDB [(none)]> SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oauthv2            |
| performance_schema |
| postfix            |
| root_cwp           |
| roundcube          |
+--------------------+
7 rows in set (0.00 sec)

CREATE DATABASE datapengunjung;

Query OK, 1 row affected (0.00 sec)

+--------------------+
| Database           |
+--------------------+
| datapengunjung     |
| information_schema |
| mysql              |
| oauthv2            |
| performance_schema |
| postfix            |
| root_cwp           |
| roundcube          |
+--------------------+
8 rows in set (0.00 sec)

USE datapengunjung;

CREATE DATABASE dbpengunjungtest;
Query OK, 1 row affected (0.18 sec)

CREATE DATABASE dbpengunjungtest;
ERROR 1007 (HY000): Can't create database 'dbpengunjungtest'; database exists

CREATE OR REPLACE DATABASE dbpengunjungtest;
Query OK, 2 rows affected (0.02 sec)

CREATE DATABASE IF NOT EXISTS dbpengunjungtest2;
Query OK, 1 row affected, 1 warning (0.01 sec)

SHOW WARNINGS;
+-------+------+-----------------------------------------------------------+
| Level | Code | Message                                                   |
+-------+------+-----------------------------------------------------------+
| Note  | 1007 | Can't create database 'dbpengunjungtest'; database exists |
+-------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

exit

exit
Bye

USE datapengunjung;

CREATE TABLE TamuSaya (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
namadepan VARCHAR(30) NOT NULL,
namabelakang VARCHAR(30) NOT NULL,
email VARCHAR(50),
tanggaldaftar TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

show tables;

+--------------------------+
| Tables_in_datapengunjung |
+--------------------------+
| TamuSaya                 |
+--------------------------+
1 row in set (0.00 sec)

describe TamuSaya;

+---------------+-----------------+------+-----+-------------------+-----------------------------+
| Field         | Type            | Null | Key | Default           | Extra                       |
+---------------+-----------------+------+-----+-------------------+-----------------------------+
| id            | int(6) unsigned | NO   | PRI | NULL              | auto_increment              |
| namadepan     | varchar(30)     | NO   |     | NULL              |                             |
| namabelakang  | varchar(30)     | NO   |     | NULL              |                             |
| email         | varchar(50)     | YES  |     | NULL              |                             |
| tanggaldaftar | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+---------------+-----------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

insert into TamuSaya values ('', 'Buat', 'Database', '[email protected]',DEFAULT);

Query OK, 1 row affected, 1 warning (0.01 sec)

select * from TamuSaya;

+----+-----------+--------------+-------------------------+---------------------+
| id | namadepan | namabelakang | email                   | tanggaldaftar       |
+----+-----------+--------------+-------------------------+---------------------+
|  1 | Belajar   | Database     | [email protected] | 0000-00-00 00:00:00 |
|  2 | Buat      | Database     | [email protected] | 2020-07-22 06:11:48 |
+----+-----------+--------------+-------------------------+---------------------+
2 rows in set (0.00 sec)

FAQ: how to create a MySQL and MariaDB database

1. What is the difference between MySQL and MariaDB?

Both are relational database systems, but their development and some features are different.

2. Does XAMPP still use MySQL?

Modern versions of XAMPP bundle MariaDB, although the panel button often says MySQL.

3. What is the function of CREATE DATABASE?

Creates a new database on the server accessed by a permitted account.

4. What is the use of IF NOT EXISTS?

Prevents errors when the database name already exists, not validates the structure.

5. Is CREATE OR REPLACE DATABASE safe?

It risks replacing an existing database and should be avoided for beginner tutorials.

6. How to open a database CLI on Windows?

Use the XAMPP Shell or the mysql executable in the appropriate bin folder.

7. Is phpMyAdmin required?

No. Databases can be managed via the CLI or other client applications.

8. How to view all databases?

Use SHOW DATABASES according to account privileges.

9. How to change the active database?

Use USE database_name; in the correct SQL session.

10. Does the WordPress application need to be root?

No. A dedicated account with the necessary database permissions is preferable.

11. Does port 3306 need to be public?

Generally not for applications on the same server; restrict network access.

12. When is a backup necessary?

Before schema changes, migrations, and on a scheduled basis during operation.

External references

• MariaDB — CREATE DATABASE
• MySQL — CREATE DATABASE
• Apache Friends — XAMPP FAQ Windows
• MySQL — GRANT

Please leave this field empty

Oh hi there 👋
It’s nice to meet you.

Sign up to receive awesome content in your inbox, every month.

We don’t spam! Read our privacy policy for more info.

Check your inbox or spam folder to confirm your subscription.