Adding a new database and user

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

Click 'Add user'

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

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.%.%' 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.%.%';