If you need to create a database for your website there are multiple ways to do it. In this guide, you can learn how to create a MySQL database using the command line interface [CLI].
Don’t have time to read the article? Watch our walk-through video.
IMPORTANT: This guide is intended for use with our Cloud Server Hosting and other hosting plans that do not include cPanel. If your server includes cPanel, you should follow our guide on How to Create a MySQL Database Using the cPanel API.
If you are running a Linux server with MySQL but no cPanel, you can simply use the terminal to create a MySQL database, database user, and password, as well as, assign all privileges to the user for the database.
- SSH into your server as root.
- Log into MySQL as root:
mysql -u root
- Create a new database user:
GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'localhost' IDENTIFIED BY '[email protected]$w0rd123!';
- Log out of MySQL by typing:
\q
. - Log in as the new database user you just created:
mysql -u db_user -p
Then, type the new database user’s password and press Enter.
- Create a new database:
CREATE DATABASE db_name;
Congratulations! Now, you know how to create a MySQL database from the command line. Want to learn more about database management? Check out official MySQL documentation or our guide on How to Import MySQL Databases in Command Line.
If you don’t need cPanel, don't pay for it. Only pay for what you need with our Cloud VPS solutions.
CentOS, Debian, or Ubuntu No cPanel Bloat SSH Key Management
John-Paul Briones Content Writer II
John-Paul is an Electronics Engineer that spent most of his career in IT. He has been a Technical Writer for InMotion since 2013.
More Articles by John-Paul
13.1.12 CREATE DATABASE Statement
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
CREATE DATABASE
creates a database with the given name. To use this statement, you need the CREATE
privilege for the database.
CREATE
SCHEMA
is a synonym for CREATE
DATABASE
.
An error occurs if the database exists and you did not specify IF NOT EXISTS
.
CREATE DATABASE
is not permitted within a session that has an active LOCK
TABLES
statement.
Each create_option
specifies a database characteristic. Database characteristics are stored in the data dictionary.
The
CHARACTER SET
option specifies the default database character set. TheCOLLATE
option specifies the default database collation. For information about character set and collation names, see Chapter 10, Character Sets, Collations, Unicode.To see the available character sets and collations, use the the
SHOW CHARACTER SET
andSHOW COLLATION
statements, respectively. See Section 13.7.7.3, “SHOW CHARACTER SET Statement”, and Section 13.7.7.4, “SHOW COLLATION Statement”.The
ENCRYPTION
option, introduced in MySQL 8.0.16, defines the default database encryption, which is inherited by tables created in the database. The permitted values are'Y'
[encryption enabled] and'N'
[encryption disabled]. If theENCRYPTION
option is not specified, the value of thedefault_table_encryption
system variable defines the default database encryption. If thetable_encryption_privilege_check
system variable is enabled, theTABLE_ENCRYPTION_ADMIN
privilege is required to specify a default encryption setting that differs from thedefault_table_encryption
setting. For more information, see Defining an Encryption Default for Schemas and General Tablespaces.
A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the
CREATE DATABASE
statement creates only a directory under the MySQL data directory. Rules for permissible database names are given in Section 9.2, “Schema Object Names”. If a database name contains special characters, the name for the database
directory contains encoded versions of those characters as described in Section 9.2.4, “Mapping of Identifiers to File Names”.
Creating a database directory by manually creating a directory under the data directory [for example, with mkdir] is unsupported in MySQL 8.0.
When you create a database, let the server manage the directory and the files in it. Manipulating database directories and files directly can cause inconsistencies and unexpected results.
MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
You can also use the mysqladmin program to create databases. See Section 4.5.2, “mysqladmin — A MySQL Server Administration Program”.