Create a New MySQL User and Database

Creating users and databases

The standard tool for interacting with MySQL is the mysql client which installs with the mysql-server package. The MySQL client is used through a terminal.

To create a MySQL database and user, follow these steps:

1. At the command line, log in to MySQL as the root user: 
   
   

   # mysql -u root -p



2. Type the MySQL root password, and then press Enter. 


3. To create a database user, type the following command. Replace username with the user you want to create, and replace password with the user's password:
   
   

   mysql> create database testdb; mysql> create user 'testuser'@'localhost' identified by 'password'; mysql> grant all privileges on testdb.* to 'testuser' identified by 'password';

   
   You can shorten this process by creating the user while assigning database permissions:
   
   

   mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';

   
   

   This command grants the user all permissions. However, you can grant specific permissions to maintain precise control over database access. For example, to explicitly grant the SELECT permission, you would use the following command:     GRANT SELECT ON *.* TO 'username'@'localhost'; For more information about setting MySQL database permissions, please visit https://dev.mysql.com/doc/refman/8.0/en/grant.html.



4. Type \q or exit to exit the mysql program.


5. To log in to MySQL as the user you just created, type the following command. Replace username with the name of the user you created in step 3:
   
   

   mysql> mysql -u username -p



6. Type the user's password, and then press Enter.


7. To create a database, type the following command. Replace dbname with the name of the database that you want to create:
   
   

   mysql> CREATE DATABASE dbname;



8. To work with the new database, type the following command. Replace dbname with the name of the database you created in step 7:


9. You can now work with the database. For example, the following commands demonstrate how to create a basic table named example, and how to insert some data into it:
   
   

   mysql> CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) ); mysql>  INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );

Deleting tables and databases

To delete a table, type the following command from the mysql> prompt. Replace tablename with the name of the table that you want to delete:

mysql>  DROP TABLE tablename;

This command assumes that you have already selected a database by using the USE statement.

Similarly, to delete an entire database, type the following command from the mysql> prompt. Replace dbname with the name of the database that you want to delete:

mysql>  DROP DATABASE dbname;

The mysql program does not ask for confirmation when you use this command. As soon as you press Enter, MySQL deletes the database and all of the data it contains..

Deleting users

To view a list of all users, type the following command from the mysql> prompt:

mysql>  SELECT user FROM mysql.user GROUP BY user;

To delete a specific user, type the following command from the mysql> prompt. Replace username with the name of the user that you want to delete:

mysql>  DELETE FROM mysql.user WHERE user = 'username';