Adding a new database and user
Table of Contents
Creating a database and user can be completed via a number of means:
- PHPMyAdmin
- MySQL CLI
- 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.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.%';