How to Create a Database and a User in MySQL

In this instruction, we will look at creating a user and a database in MySQL, deleting a database, and granting user privileges. 

By default, you will have access to the root user. Use it to perform the initial setup: create the first database and other users.

These commands are suitable for managing MySQL deployed on the server. If you are using a cloud database at Hostman, the user and the database itself will be created automatically when you create the database in the control panel.

Creating a database

Connect to MySQL under the root account:

mysql -u root -p

Check what databases already exist on the server:

SHOW DATABASES;

The table won't be empty even if you haven't done anything yet. It will show the service databases. There is no need to delete them.

To create a MySQL database, execute:

CREATE DATABASE my_hostman;

Instead of my_hostman, specify the name you want to give the new database.

To make sure that the new database appears on the server, run again:

SHOW DATABASES;

The table will display the name of the database you just created.

Database names should be unique. If you try to create the my_hostman database again, an error message with code 1007 will appear. 

To prevent the error from appearing, you can create a new database in MySQL using the extended syntax:

CREATE DATABASE IF NOT EXISTS my_hostman;

In this case, the DBMS will check the condition. If there is no database with this name, MySQL will create it. If such a database already exists, nothing will change.

Deleting a database

To delete a database, execute:

DROP DATABASE my_hostman;

Instead of my_hostman, specify the name of the database to be deleted.

DROP DATABASE also has an extended syntax that helps avoid errors:

DROP DATABASE IF EXISTS my_hostman;

The query will only be executed if there is a database with that name.

DROP DATABASE will delete the database and its associated data. 

Use DROP DATABASE with caution. The MySQL command line interface will not prompt you to confirm the action.

Creating a database user

MySQL offers a convenient system for managing users and their privileges. You can change access rights and allow and deny actions on the server. 

To create a user in MySQL, log in as root and execute in the console:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

After executing this command, the user named user will access the MySQL instance from the local machine (localhost). Direct access from other hosts will be denied. A password will be used for identification.

If you want to specify a pool of hosts, use the % symbol. For example:

CREATE USER 'sub_user'@'10.0.%' IDENTIFIED BY 'password;

The entry 10.0.% indicates that MySQL can be accessed from any client whose IP address starts with 10.0..

You can use % at any octet of the IP address when defining the host. For the user to have access from all hosts, use the 'user'@'%' entry. 

To see a list of accounts, execute:

SELECT * FROM mysql.user;

Deleting an account

You can delete a MySQL account with the DROP USER command:

DROP USER 'user'@'localhost';

Be careful; the command is executed without additional confirmation.

Granting privileges

Without privileges, a user can connect to MySQL server, but cannot work with data or perform other actions. 

This table contains privileges that are often granted to users. For a more detailed list, see the documentation.

To grant rights, you must execute the GRANT command. For example:

GRANT SELECT, INSERT ON my_hostman.* TO 'user'@'localhost';

This command should be run under the root account, as it has the GRANT privilege. After applying this query, user will be able to read and add rows to the my_hostman database tables.

To grant the user all privileges other than those of root, execute:

GRANT ALL PRIVILEGES ON my_hostman.* TO 'user'@'localhost';

Revoking privileges

To revoke privileges, use the REVOKE command. It has the same syntax as GRANT. But the result will be the opposite.

For example, if you want to revoke the SELECT and INSERT privileges of  user in the my_hostman database, type the following:

REVOKE SELECT, INSERT ON my_hostman.* FROM 'user'@'localhost';

If the account did not have any privileges for this database, nothing will change.

Key commands you need to know

How to create a database in MySQL:

CREATE DATABASE IF NOT EXISTS [database_name];

How to delete a database:

DROP DATABASE IF EXISTS [database_name]

How to create a new MySQL user:

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

How to delete a user:

DROP USER 'user'@'host';

This is the minimum set of queries that will help to create a MySQL database and a user. The specifics of using these and other DBMS features are described in detail in the MySQL documentation.

You can also use tools with a graphical interface for database management, such as phpMyAdmin or MySQL Workbench. Their documentation describes in detail how to create and work with the databases and users.