Managing MySQL on a Linux server involves creating users who can access and manage databases. Each user can have specific permissions to ensure data security. In this guide, we’ll walk you through the easy steps to create a MySQL user on a Linux server.
Step 1: Log in to MySQL
First, you need to access the MySQL command-line interface. Open your terminal and log in as the root user (or any user with admin privileges):
mysql -u root -p
- -u root: This tells MySQL to log in as the root user.
- -p: It prompts you to enter the root password for MySQL.
Enter your password when prompted. You should now be inside the MySQL command-line interface.
Step 2: Create a New MySQL User
To create a new user, use the following syntax:
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
- ‘username’: Replace this with the desired username for your new MySQL user.
- ‘localhost’: This restricts the user to connect only from the local server. You can replace
localhostwith a specific IP address or use%to allow access from any host. - ‘password’: Replace this with a strong password for your new user.
Example:
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'StrongPassword123!';
This creates a new user named newuser who can only log in from the local server using the password StrongPassword123!.
Step 3: Grant Permissions to the New User
Once the user is created, you need to give them the necessary permissions to work with databases. Here’s a simple command to grant all privileges on a specific database:
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
- database_name: Replace this with the name of the database the user needs access to. Use
*.*to grant access to all databases. - username: Replace this with the name of the user you just created.
Example:
GRANT ALL PRIVILEGES ON mydatabase.* TO 'newuser'@'localhost';
This gives newuser full access to the mydatabase database.
Step 4: Apply the Changes
MySQL doesn’t immediately apply permission changes. To apply the new privileges, run the following command:
FLUSH PRIVILEGES;
This ensures that all changes take effect.
Step 5: Verify the New User
To check if the new user was successfully created, exit MySQL by typing:
exit
Then, try logging in as the new user:
mysql -u newuser -p
You will be prompted to enter the new user’s password. If everything was set up correctly, you should now be logged in as the new user.
