Create a database without PhpMyAdmin

Tomek Pryjma — Sun, 27 Sep 2020 11:19:18 +0000

Sometimes you may find that you have to create a MySQL/MariaDB database without the use of a nice GUI provided by PhpMyAdmin.
This is the 10th circle of hell dedicated to sysadmins and unlucky souls who just happened to be at the wrong place at the wrong time.
Good news is that this is the only circle you can actually leave and redeem yourself.
I will show you how.

What you will need

Server SSH access
Either the MySQL root username and password or a MySQL user with the ability to CREATE, INSERT, RELOAD and ALTER inside the MySQL command line interface (CLI).
And as pointed out by Ben Sinclair, you may also need to know which port you have to connect on. MySQL typically uses 3306 by default but always best to check to avoid mishaps.

Let's begin

After you access the server you must get yourself into MySQL's own command line interface (CLI).

Do that by running: mysql -u root -p

Breakdown of command

mysql - tells the server we want to log into MySQL's CLI.
-u root - tells the mysql command which user to log into MySQL CLI with, in my case I'll be using root.
-p - tells the command to prompt us for a password after we run it.
As mentioned before if MySQL is using a port other than the default 3306 you will need to provide the command with that port by adding --port= followed by the port number.

In the CLI

What you should see after running the command in the step above is a variation of the following:

MySQL

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 265976
Server version: 5.5.62-0+deb8u1 (Debian)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

MariaDB

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Creating a database

This is simple enough, we do this:

MariaDB 10.1.3+ & MySQL 5.5+
CREATE DATABASE t800;

Output you should see
Query OK, 1 row affected (0.00 sec)

Every database needs a user

All databases need their own user so we create one like so:

MariaDB 10.1.3+ & MySQL 5.5+
CREATE USER arnold@localhost IDENTIFIED BY 'dAs2WnZD^j3D1Nxpu%ES';

Output you should see
Query OK, 0 rows affected (0.00 sec)

Breakdown of command

We provide arnold@localhost to the CREATE USER statement as a means of telling the system that we want to create a user whose name is arnold and who can only connect from the same server he is on, i.e. localhost.
The IDENTIFIED BY part lets us set a password for our user. I strongly suggest you use a password generator to help out with this.

Granting privileges

Once arnold is created we will need to set some ground rules about how arnold is allowed to interact with the t800 database.

We give him a set of privileges, like this:

MariaDB 10.1.3+ & MySQL 5.5+
GRANT ALL PRIVILEGES ON t800.* TO arnold@localhost;
Output you should see
Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;
Output you should see
Query OK, 0 rows affected (0.00 sec)

Breakdown of command

We tell the system to give arnold all the permissions he may need to properly interact with the database. That includes reading from it, writing to it, deleting from it etc.
The FLUSH PRIVILEGES statement makes it so that our GRANT ALL statement has an effect. Essentially telling the system to refresh everything it currently knows about its current users and their privileges.

Finishing touches

Chances are, if you're creating a database you probably don't want it to be empty.
So considering we don't have PhpMyAdmin access, we will need to import a database via the command line.

You can quit the MySQL/MariaDB CLI by typing quit.

You will need to upload the database file onto your server into the directory you are currently in (for ease of typing later on) you can do this in two ways:

Drag and drop the file via FTP
Run scp /your/local/computer/database.sql user@server-name-or-ip:/path/to/directory/database.sql. scp stands for secure copy and will transfer the file via SSH if FTP is not available to you.

Now that the file is on you server, the next command assumes the .sql file is in the same directory that you are in on the command line:

mysql -u root -p t800 < database.sql

And that's about it! We have successfully escaped from the 10th circle of hell.

Forem: Tomek Pryjma

Create a database without PhpMyAdmin

What you will need

Let's begin

Breakdown of command

In the CLI

Creating a database

Every database needs a user

Breakdown of command

Granting privileges

Breakdown of command

Finishing touches