Signup to the waiting list!
Right after you install MySQL, you will have the
root user available.
This is the user you might use to try and see if MySQL is working, but it should not be the user for other kinds of usages.
Why? Because it’s too powerful.
With great power comes great responsibility (said Spider-Man’s uncle). And in particular, great danger if you make mistakes.
Instead, you should create ad-hoc users that only have permissions to perform their job, and no more. Same for using Linux or any Unix system, for example: you don’t want to be using the
root user, but your own user account.
To create a new user, connect to MySQL using the
mysql -u root -p
then use the command
CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>';
For example, to create a user named
test_user with password
test_password12A run the command:
CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'test_password12A';
The command should return a line saying
Query OK, 0 rows affected (X.XX sec):
If you used an invalid password, the system would tell you something like
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements. In this case the error says that the password is not complex enough, because when I installed MySQL I told it to use a certain policy on passwords.
That’s great! Now the user has been created, and we can connect using that user to MySQL. From the command line you can exit by typing
QUIT, and typing:
mysql -u test_user -p
Now if I try to create a database, I’d get an error saying
ERROR 1044 (42000): Access denied for user 'test_user'@'localhost' to database 'testing':
Why? Because the user does not have the permission to create a new database.
We’ll see how to work with permissions in another tutorial.
It’s worth mentioning the
@'localhost' string we used to create the user. This tells MySQL that the user can only connect from localhost. Which is fine when testing things out and when any application that will connect to MySQL is running on the same computer that runs the DBMS.
When this is not true, you either need to manually enter the IP the user will connect from, or use the
CREATE USER 'test_user'@'%' IDENTIFIED BY 'test_password12A';
I want to close this tutorial by showing how to remove the user we created:
DROP USER 'test_user'@'localhost';
More database tutorials:
- The MongoDB basics tutorial
- How MongoDB is different from a SQL database
- Information systems, data and information
- Data models
- Introduction to the ER Data Model
- The Relational Model
- Relational Algebra
- Relational Databases
- What is a Database? And a DBMS?
- Introduction to SQL
- How to install PostgreSQL on macOS
- Do you always need a database for your app?
- How to install SQLite on macOS
- Introduction to PostgreSQL
- PostgreSQL User Permissions
- How to list all users in PostgreSQL
- How to switch database using PostgreSQL
- How to list all databases using PostgreSQL
- How to list tables in the current database using PostgreSQL
- How to install MySQL on macOS
- Creating a user on MySQL
- PostgreSQL vs MySQL, a comparison
- SQLite User Permissions
- MySQL User Permissions
- SQL, creating a table
- SQL, adding data to a table
- SQL, how to use SELECT
- SQL, Handling empty cells
- SQL, Unique and Primary keys
- SQL, how to update data
- SQL, how to update a table structure
- SQL, how to delete data and tables
- SQL Joins
- SQL Views
- How to insert multiple items at once in a MongoDB collection
- How to remove all items from a MongoDB collection