There are a number of instructions for creating a MySQL database via the command line online. As with many instructions, many are overcomplicated, under-explained, or just plain confusing. This is a simple guide for someone who wants to simply set up a new database and user in MySQL.
This guide is written with WordPress in mind but could be used for a number of applications.
Use the following instructions to create a new database in MySQL using the command line.
Step 1: Connect to Your Server via SSH
Step 2: Log in to MySQL
mysql -u root -p
Step 3: Create the New Database
Replace database_name in the example below with a name for your database.
CREATE DATABASE database_name DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
You can confirm that the database was created by running this command:
Step 4: Create a New Database User
In this step, you are going to create a new user and grant all permissions for the database to that user. I usually choose to grant all permissions to the user that I am creating to manage my WordPress websites. It is important to create a new database user for every WordPress install on your server as an added security measure.
In the example below, replace database_name with the name of your database, database_user with the name of the user that you would like to create, and user_password with the password that you would like to use for the new user.
GRANT ALL ON database_name.* TO 'database_user'@'localhost' IDENTIFIED BY 'user_password';
Step 5: Flush Privileges
Step 6: Exit MySQL
Now that your new database is setup and ready to use, you can import an existing database from a MySQL dump file. Instructions on how to do this can be found in my guide: How to Easily Import and Export WordPress MySQL Databases Using the Command Line (Linux).