Create a database without PhpMyAdmin
Tags: linux, mysql, database
Published: 2020-09-27 11:19:18

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](https://dev.to/moopet), 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;`

<small><strong>Output you should see</small></strong>
<small>`Query OK, 1 row affected (0.00 sec)`</small>

---

## 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';`

<small><strong>Output you should see</small></strong>
<small>`Query OK, 0 rows affected (0.00 sec)`</small>

#### 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](https://www.lastpass.com/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;`
<small><strong>Output you should see</small></strong>
<small>`Query OK, 0 rows affected (0.00 sec)`</small>

`FLUSH PRIVILEGES;`
<small><strong>Output you should see</small></strong>
<small>`Query OK, 0 rows affected (0.00 sec)`</small>

#### 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 **s**ecure <strong>c</strong>o<strong>p</strong>y 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.