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).

[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:

mysql -p

view raw SSH hosted with by GitHub

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:

cat /root/.my.cnf

view raw gistfile1.txt hosted with by GitHub

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:

mysql>CREATE DATABASE example_databse;

view raw gistfile1.txt hosted with by GitHub

• example_database is your new database name

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

mysql>CREATE USER 'username'@'localhost' IDENTIFIED BY 'user_password';

view raw gistfile1.txt hosted with by GitHub

• 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:

	mysql>GRANT ALL PRIVILEGES ON example_database.* TO 'username'@'localhost';
	mysql>FLUSH PRIVILEGES;

view raw gistfile1.txt hosted with by GitHub

These privileges applies to ALL example_database tables to the username.

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

	mysql>SHOW DATABASES;
	mysql>select user,host from mysql.user;

view raw gistfile1.txt hosted with by GitHub

8. Quit MySQL by using the following command:

quit;

view raw gistfile1.txt hosted with by GitHub

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:

mysql -umysqluser -p example_databse < dump.sql

view raw gistfile1.txt hosted with by GitHub

Use MySQL username next to -u flag.

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