Magento. How to Create New Database, Database User and Grant Permissions over SSH

Here you can find the information about  MySQL users, databases and user privileges management with the help of command line interface (CLI).
Use the instructions for VPS and Dedicated servers only.

[notice type=”warning”] You should have root privileges (administrative access) to be able to manipulate databases and users via SSH.

Create new database, user and grant permissions

1. Set up a connection with remote server over SSH.

2. Enter the following command to access MySQL:

3. Type in your MySQL password.

The password is specified in the  root/.my.cnf  file on the server (typically).

Use the following command to retrieve the password:

4. You will see MySQL greeting once logged in.

The command prompt now starts with mysql tag and angle bracket “>” instead of the dollar sign.

5. To create a new database, use the following command:

  • example_database is your new database name

6. To add a new user, type in the following:

  • username – the name of the MySQL user which will be created
  • user_password – the password which we want to assign to that user

The 'username'@'localhost' account can be used only when connecting from the local host.

7. Now we have to grant all permissions to the new user.

The commands will help to do that:

These privileges applies to ALL example_database tables to the username.

To list all your databases and users, do the following:

8. Quit MySQL by using the following command:

Importing SQL file

Upload sample data SQL file to any directory on your remote server, you need to do the next:

1. cd(change directory) to the Magento installation directory.

2. Run the following command to import the SQL file:

Use MySQL username next to -u flag.

* Upon issuing a command, you will be requested to type in user password.