How to create new user in mysql

To create a new user in MySQL, you can use the CREATE USER statement. Here is the basic syntax:

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

Here:

• username is the name of the new user.
• host is the host from which the user can connect to the MySQL server. You can specify a specific host, such as 'localhost' or '192.168.1.100', or use the wildcard character '%' to allow connections from any host.
• password is the password for the new user.

For example, to create a new user named john who can connect from any host with the password mypassword, you would use the following command:

CREATE USER 'john'@'%' IDENTIFIED BY 'mypassword';

You can also specify additional options when creating a new user, such as:

• GRANT OPTION: allows the user to grant privileges to other users.
• PASSWORD EXPIRE: sets the password expiration date for the user.
• PASSWORD HISTORY: sets the number of previous passwords stored for the user.

Here is an example of creating a new user with additional options:

CREATE USER 'john'@'%' IDENTIFIED BY 'mypassword' GRANT OPTION PASSWORD EXPIRE INTERVAL 30 DAY PASSWORD HISTORY 5;

This command creates a new user named john who can connect from any host with the password mypassword, and grants the user the ability to grant privileges to other users. The password will expire after 30 days, and the user's previous 5 passwords will be stored.

You can also use the CREATE USER statement with the INFORMATION_SCHEMA database to create a new user with a specific set of privileges. For example:

CREATE USER 'john'@'%' IDENTIFIED BY 'mypassword' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0;

This command creates a new user named john who can connect from any host with the password mypassword, and sets the maximum number of queries, connections, and updates per hour to 0, which means there is no limit.

After creating a new user, you can grant privileges to the user using the GRANT statement. For example:

GRANT SELECT, INSERT, UPDATE, DELETE ON database_name.* TO 'john'@'%';

This command grants the user john the ability to select, insert, update, and delete data in the database_name database.

Note: You can also use the mysql command-line tool to create a new user. For example:

mysql -u root -p<password> -e "CREATE USER 'john'@'%' IDENTIFIED BY 'mypassword';"

This command creates a new user named john who can connect from any host with the password mypassword, using the mysql command-line tool.