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:
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_databaseis your new database name
6. To add a new user, type in the following:
username– the name of the MySQL user which will be createduser_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.
