MIDAS Knowledge Base

How to manually create a new MySQL / MariaDB Database

This article outlines how to manually create a MariaDB or MySQL database, along with a database user and the privileges it needs, from the command line.

If you're setting up a self-hosted MIDAS scheduling system, you'll need to create an empty database in this way before you install MIDAS.

If you haven't yet installed your database server, see our guides to installing MariaDB on Windows or installing MySQL on Windows first.

If your server runs cPanel, follow our How to create a database in cPanel guide instead, otherwise carry on reading...

Colored text in the commands below denote details you may need to modify for your particular server setup

Step 1: Login to your database server

From your server's command line, login to your MariaDB or MySQL server.

On MariaDB, use the "mariadb" client:

$ mariadb -u root -p

On MySQL, use the "mysql" client:

$ mysql -u root -p

(Most MariaDB installations also still accept the "mysql" command. Omit "-p" if your root account has no password set, for example when using socket authentication on Linux.)

On Windows, you can skip this step and access the command line via the "MariaDB Command Prompt" shortcut (MariaDB) or the "MySQL Command Line Client" shortcut (MySQL) in your Start Menu instead.

The examples below show the "mysql>" prompt. On MariaDB, your prompt will instead read "MariaDB [(none)]>" (The (none) portion of the prompt indicates that no database is currently selected). The SQL commands themselves are identical on both.

Step 2: Create a MariaDB (or MySQL) database

Enter a desired name for your MIDAS database:

mysql> CREATE DATABASE `MIDAS`;

You will need the name of this database when installing MIDAS - also, please note that SQL queries outlined here may contain "backtick" characters ( ` ) which are not the same as regular apostrophes ( ' )

Step 3: Create a database user

Once you've created a database, create a new database user:

mysql> CREATE USER `username`@`localhost` IDENTIFIED BY 'password';

You will need this username/password when installing MIDAS

If your database and web servers reside on different physical hardware to each other, you will need to substitute localhost in the above code to reflect the IP/domain of your web server.

If unsure of your web server's IP/domain, you can instead substitute `localhost` for `%`. This will allow the database server to accept incoming connections from any host. However for security this approach is not recommended, and you should always endeavor to use localhost or the exact IP/domain name of your web server instead.

Step 4: Assign the user to the database & set user privileges

Once you've created a database and a database user, you will need to associate the user with that database and grant the necessary privileges:

mysql> GRANT ALTER, CREATE, DELETE, DROP, INSERT, LOCK TABLES, SELECT, UPDATE ON `MIDAS`.* TO `username`@`localhost`;

Step 5: Flush Privileges

Finally, to ensure that the privileges you set in the previous step are applied, issue the following command:

mysql> FLUSH PRIVILEGES;

Step 6: Verify your database

You can confirm your new database exists by listing all databases:

mysql> SHOW DATABASES;

Your newly created database should appear in the list. To confirm the privileges granted to your user, you can run:

mysql> SHOW GRANTS FOR `username`@`localhost`;

Finally, type "exit" to leave the client:

mysql> exit

Troubleshooting

"ERROR 1045 (28000): Access denied for user..."

The username or password used to log in is incorrect, or the account doesn't have permission to perform the action. Log in with an account that has sufficient privileges (such as root). On Linux, you may need to run the client with "sudo".

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

This usually means a command was mistyped. The most common causes are using regular apostrophes ( ' ) in place of backticks ( ` ) around names, or omitting the trailing semicolon ( ; ) at the end of the command.

"ERROR 1396 (HY000): Operation CREATE USER failed..."

A user with that name and host already exists. Either choose a different username, or drop the existing user first before recreating it.

"'mariadb'/'mysql' is not recognized" (Windows) or "command not found" (Linux)

The client isn't on your PATH. On MariaDB the client is named "mariadb" (use that instead of "mysql"). On Windows, use the database client shortcut in your Start menu, or add the database server's "bin" folder to your system PATH.

Frequently asked questions

How do I create a database in MariaDB from the command line?

Log in to your server with "mariadb -u root -p", then run "CREATE DATABASE `yourdbname`;". The same applies to MySQL using the "mysql" client.

Are the MariaDB and MySQL commands the same?

Yes. The SQL used to create a database, create a user, and grant privileges is identical on both. The only practical differences are the client used to connect ("mariadb" for MariaDB, "mysql" for MySQL) and the prompt each one displays.

Do I need to be root?

You need to connect with an account that has permission to create databases and users. The "root" account is the simplest choice, but any suitably privileged account will work.

How do I list the databases I've created?

Connect to your server and run "SHOW DATABASES;" to see all databases currently on the server.

I haven't installed my database server yet

See our guides to installing MariaDB on Windows or installing MySQL on Windows first.