Adding a MySQL Database, User, and Assigning Privileges through the MySQL command line

From Acenet Knowledgebase
Jump to: navigation, search

In the absence of a control panel, MySQL databases and users will need to be added to your MySQL service through the MySQL command line. A database and user are often the first pieces of information popular web scripts like Joomla and Wordpress require for installation. If you're installing a popular script for your web site, this will likely be your first place to start. During this process, be sure to take note of the database, username, and password you setup. You'll need to supply this information when you install a script for your site.

[1] Login to your server via SSH as the root user.

[2] Access the MySQL command line using the username 'root' and the root MySQL password. Note that the MySQL root password is set during MySQL installation and is different from your server's root SSH password. Enter the following command and provide the MySQL root password when prompted:
[root@ /]  mysql -u root -p
 Enter password:

[3] Once you've authenticated, this will drop you to a MySQL prompt. From here, we'll be issuing MySQL commands or queries to add the user and database. Let's first add a database, using the CREATE DATABASE query to create a database named 'mynewdatabase'. 

mysql> CREATE DATABASE mynewdatabase;
 Query OK, 1 row affected (0.00 sec)

[4] We'll now add a MySQL user using the CREATE USER query. The example below adds the user 'mynewuser' with a password 'mypassword'. As always, be sure to use a strong password consisting of letters and numbers. The password should also not contain words that occur in a dictionary. The password 'mypassword' is not a strong password. 

mysql> CREATE USER 'mynewuser'@localhost IDENTIFIED BY 'mypassword';
 Query OK, 0 rows affected (0.00 sec)

[5] Now that we have both a database and user, we need to grant the user permissions on the database. Unless you know which specific permissions you wish to grant, it will suffice to grant your user all permissions on the database. We'll use the GRANT query to grant privileges for our user. 

mysql> GRANT ALL PRIVILEGES ON mynewdatabase.* TO 'mynewuser'@localhost;
 Query OK, 0 rows affected (0.00 sec)

You're now ready to provide the database name, user, and password to your script's installer.

Retrieved from "https://kb.acenet.us/index.php?title=Adding_a_MySQL_Database,_User,_and_Assigning_Privileges_through_the_MySQL_command_line&oldid=224"