Creating a SQL database (and user) through SSH

This is how you create a database and corresponding user through secure shell. I am taking from the point of being connected to your server through SSH. First you log into your sql database on your server.

Note: [username], [password], [hostname] and [database-name] are all place-holders. This is where you insert your credentials without the square brackets [ ].

mysql -u[username] -p[password] -h[hostname]


You are now logged into your phpMyAdmin. You should see a mysql tag at the beginning of your prompt line. Now we create the database with the following:

create database [database-name];

You should see a “Query OK” message. Now that the database is created, you can create a user that only has access to the database you just created. The following line creates a user, defines their password, and gives them all privileges to the database we just created.

grant all privileges on [database-name].* to '[username]'@'localhost' identified by "[password]";

You now can log into MySQL with the user you just created, and they will only have access to the database you just created. You can also use the above line to grant privileges to an existing user, just remove ‘ identified by “[password]”‘.

3 thoughts on “Creating a SQL database (and user) through SSH”

  1. Hi, i am trying to create new database using ssh but it has been difficult to do so, it only able to use the existing databases, how to do so.
    Hope to hear from you soon.
    Thanks.

    1. Hi Amani,

      The user you use to access your MySQL databases must have administrative access to be able to create databases. Generally, this is much easier to do from within cPanel.

      Cheers,

      Eric

Leave a Reply

Your email address will not be published. Required fields are marked *