MySQL

Adding a new database and user

By 28th August 2013October 20th, 2021No Comments

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

  1. PHPMyAdmin
  2. Sonassi control panel
  3. MySQL CLI
  4. 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.

Creating a Database/User via Sonassi control panel

Visit the Sonassi control panel's Stacks page, at https://my.sonassi.com/stack.

From there, select your stack, then the Databases link under the Domain Management heading on the left hand side.

Enter a database name, and select Create Database.

The password for the database will be automatically generated and shown in the panel - please note that this password is not stored on the panel for security purposes.

A MySQL user will also be created with the same name as the new MySQL database for reference.

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