Just a few weeks until the 2021 JavaScript Full-Stack Bootcamp opens.
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 root
user:
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 %
wildcard:
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';
The 2021 JavaScript Full-Stack Bootcamp will start at the end of March 2021. Don't miss this opportunity, signup to the waiting list!
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