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,RELOADandALTERinside 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 themysqlcommand which user to log into MySQL CLI with, in my case I'll be usingroot. -
-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@localhostto theCREATE USERstatement as a means of telling the system that we want to create a user whose name isarnoldand who can only connect from the same server he is on, i.e.localhost. - The
IDENTIFIED BYpart 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
arnoldall 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 PRIVILEGESstatement makes it so that ourGRANT ALLstatement 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.scpstands 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.
Top comments (4)
You don't need root permissions at the server level to perform MySQL commands with the
mysqlcommand line client.You need a MySQL user with the create database permission, exactly the same as if you were using phpmyadmin. You also need direct access to the socket or port the server's listening on.
Hi Ben, thanks for the response. I genuinely didn't know that, I was always taught to use either the server's root user or a sudo user so thanks for that!
As for the port, I was writing under the presumption that the port would be the default of 3306 but you're right, there should be a mention of that.
If you're running a local MySQL server that's only supposed to be accessed from the same *nix machine you don't need to bind it to a port at all. You can make it listen on a socket like
/var/run/mysqld/mysqld.sockand it'll be fine. It's common to run on either or both depending how the server is set up.There can be odd permissions set up and you can get around not knowing the MySQL root password by running things like
mysqladminas root if I remember correctly, but it's been a long time since I did that and I'm not 100% sure. I know if you search for things like "reset mysql admin password" you can do that as a sudo user without needing any other info.That is very useful to know, appreciate the info. Going to try those things out for myself.