Adding a new database and user

Table of Contents

  1. Best Practice
  2. Creating a Database/User via PHPMyAdmin
  3. Creating a Database/User via Script
  4. Creating a Database/User via MySQL CLI

Creating a database and user can be completed via a number of means:

  1. PHPMyAdmin
  2. MySQL CLI
  3. MySQL GUI Desktop Client

MageStack comes with PHPMyAdmin and MySQL CLI installed as standard, but you can also install a desktop application to manage your databases if you wish.

Best Practice

Its always a good idea for consistency to use the same name for the database and user, this keeps permissions tight and ensures clarity of user/db associations.

Naming the database name/user after the purpose of the site helps identify the purpose of the DB.

You should also prefix the database name/user with the domain group in question.

Eg.

example_exaliv    - 'example' domain group, for domain example.com, with database name/user 'exaliv' (ie. Magento Live)
example_exasta    - 'example' domain group, for domain example.com, with database name/user 'exasta' (ie. Magento Staging)

Creating a Database/User via PHPMyAdmin

Log into PHPMyAdmin as root, and select 'Users' from the top menu

createdbuser_1

Click 'Add user'

createdbuser_2

Enter the details for the user, ensuring you select the checkbox to create a database with the same name.

createdbuser_3

Creating a Database/User via Script

MageStack includes a quick script that allows you to create a new database and user automatically, you can find it in /microcloud/scripts_ro/create_db_user.sh.

Running the script without arguments will show the usage/help,

create_db_user.sh Usage:

create_db_user.sh ([args]) -u [username]

  -u username     Create database and user with this name
  -h hostname     Database server hostname (eg. db1.i) [optional]
  -n              Verbose dry run (print all commands, but don't run them)

To create a new user, just specify the hostname of your DB server and the desired username/database name.

Eg. To create a new DB/user called example_newuser

/microcloud/scripts_ro/create_db_user.sh -h db1.i -u example_newuser

You will be prompted to enter your root MySQL password,

Please enter the root MySQL password:

Then on successfully entering this, your new user details will be printed.

Database Host:     db1.i
Database Name:     example_newuser
Database Username: example_newuser
Database Password: ff400a194cc6d243f424473d0ae0c31c

Creating a Database/User via MySQL CLI

Using mysql CLI, you can execute the following commands to create a user, replace domaingroup_name and password as necessary. Ensure that you escape the special wildcard character (_) by prefixing it with a backslash (\).

You can also set connection limits, we've left them as unlimited for the purpose of this example.

CREATE USER 'domaingroup\_name'@'%' IDENTIFIED BY 'mypassword';

GRANT USAGE ON * . * TO 'domaingroup\_name'@'172.16.0.%' IDENTIFIED BY 'mypassword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

CREATE DATABASE IF NOT EXISTS `domaingroup\_name`;

GRANT ALL PRIVILEGES ON `domaingroup\_name` . * TO 'domaingroup\_name'@'172.16.0.%';