Summary: in this tutorial, you will learn how to use the MySQL
CREATE USER statement to create a new user in the database server.
CREATE USER syntax
CREATE USER statement creates a new user in the database server.
Also: Some versions of MySQL set their passwords to expire by default, even the root password! If the password expires, Kodi cannot access the database and the TV Series and Movies libraries will disappear. To avoid this and/or fix it after it happens, make sure the account's password does not expire. So for my user email protected mysql while installing assigned a plugin called authsocket, which let Unix user “root” log in as a mysql user “root” without password, but don’t allow login as another Unix user. So to fix that you should turn off this plugin and set usual authentication: open Linux terminal; enter “sudo mysql“. After you have set up a remote MySQL connection, you can use a MySQL client application to manage your databases. For more information, please see this article. Method #1: Set up an SSH tunnel. The procedure you follow to set up an SSH tunnel between your local computer and the A2 Hosting server depends on the local computer's operating system.
Here is the basic syntax of the
CREATE USER statement:
In this syntax:
First, specify the account name after the
CREATE USER keywords. The account name has two parts:
hostname, separated by the
username is the name of the user. And
hostname is the name of the host from which the user connects to the MySQL Server.
MySQLTutorial.org is a website dedicated to MySQL database. We regularly publish useful MySQL tutorials to help web developers and database administrators learn MySQL faster and more effectively. All MySQL tutorials are practical and easy-to-follow, with SQL script and screenshots available. Open the command-line mysql client on the server using the root account. Then you will want to run the following two commands, to see what the root user host is set to already: use mysql; select host, user from user; Here’s an example of the output on my database, which is pretty much the default settings.
hostname part of the account name is optional. If you omit it, the user can connect from any host.
An account name without a hostname is equivalent to:
hostname contains special characters such as space or
-, you need to quote the username and hostname separately as follows:
Besides the single quote (
'), you can use backticks (
`) or double quotation mark (
Second, specify the password for the user after the
IDENTIFIED BY keywords.
IF NOT EXISTS option conditionally create a new user only if it does not exist.
Note that the
CREATE USER statement creates a new user without any privileges. To grant privileges to the user, you use the
CREATE USER example
First, connect to the MySQL Server using the mysql client tool:
Enter the password for the
root account and press
Second, show users from the current MySQL Server:
Here is the output:
Third, create a new user called
Fourth, show all users again:
The output will be:
bob has been created successfully.
Fifth, open a second session and log in to the MySQL as
Input the password for
bob and press
Sixth, show the databases that
bob has access:
Here is the list of databases that
bob can access:
Seventh, go to the session of the user
root and create a new database called
bobdb: Mac boot from usb windows.
Eight, select the database
Ninth, create a new table called
Notice that when you press
Enter, instead of showing the
mysql> command, the mysql tool shows the
-> that accepts new clause of the statement.
Tenth, grant all privileges on the
Note that you will learn how to grant privileges to a user in the
Eleventh, go to the bob’s session and show databases:
bob can see the
Twelfth, select the database
Thirteenth, show the tables from the
bob can see the
Fourteenth, insert a row into the
Fifteenth, query data from the
This is the output:
So the user
bob can do everything in the
Finally, disconnect from the MySQL Server from both sessions:
In this tutorial, you have learned how to use the MySQL
CREATE USER to create a new user in the database server.
- Was this tutorial helpful?
By default, when you install XAMPP in your windows machine or mac, the
root password for the MySQL is set to empty. But this is not recommended, as the MySQL database without a password will be accessible to everyone. To avoid this, a proper/secure password must be set to the user
root. To do it in XAMPP, there are two ways.
Method 1: reset XAMPP MySQL root password through web interface:
After you started your XAMPP server, go to the browser and type the URL:
(incase you’ve modified XAMPP server port, you need to include that port number also in previous URL). The security page will be shown where you can change the
password for MySQL. This will update the phpMyAdmin config also.
Method 2: reset XAMPP MySQL root password through SQL update:
- Start the Apache Server and MySQL instances from the XAMPP control panel.
- After the server started, open any web browser and give
(if you are running XAMPP on 8090 port). This will open the phpMyAdmin interface. Using this interface we can manager the MySQL server from the web browser.
- In the phpMyAdmin window, select SQL tab from the right panel. This will open the SQL tab where we can run the SQL queries.
- Now type the following query in the textarea and click GoUPDATE mysql.user SET Password=PASSWORD('password')WHERE User='root';FLUSH PRIVILEGES;
- Now you will see a message saying that the query has been executed successfully.
- If you refresh the page, you will be getting a error message. This is because the phpMyAdmin configuration file is not aware of our newly set root passoword. To do this we have to modify the phpMyAdmin config file.
- Open the file