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
mysql -u root -p
Check what databases already exist on the server:
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;
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:
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;
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.
DROP DATABASEwith 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;
10.0.% indicates that MySQL can be accessed from any client whose IP address starts with
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
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.
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.
What the user can do
Change the table or database structure
Create new databases and tables
Delete rows in a table
Add rows to a table
Read data from a table
Update data in the table
Delete the database
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
GRANT ALL PRIVILEGES ON my_hostman.* TO 'user'@'localhost';
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
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.