MySQL
MySQL Installation: Manage Root and User Accounts
2 min read
#MySQLTable Of Content
- Prerequisites
- Important Note
- What is `mysql_secure_installation`?
- What is `caching_sha2_password`?
- Install MySQL (If Not Installed)
- Login to MySQL
- Set Password for Root
- Exit from MySQL
- Run the `mysql_secure_installation` Program
- Login to MySQL as Root User
- Create New User
- Grant New User All Privileges
- Exit from MySQL
- Restart Web Server
Enhance MySQL Security: Manage Root and User Accounts
Secure your MySQL database by setting a strong password for the root user and creating a new user with appropriate privileges. This guide covers using the
mysql_secure_installation
tool and secure password hashing to improve your MySQL installation.
Prerequisites
- MySQL server installed on your Linux server/VPS.
- SSH access to your server with root privileges.
Important Note
- Avoid Root for Everyday Use: It’s highly recommended to avoid using the root user for everyday database operations. Instead, create a new user for regular tasks.
- Grant Least Privileges: Only grant the necessary privileges to new users based on their specific needs.
What is mysql_secure_installation
?
This tool enhances the security of your MySQL installation by allowing you to:
- Set a password for root accounts.
- Remove root accounts accessible from outside the local host.
- Remove anonymous-user accounts.
- Delete the test database and privileges allowing access to databases starting with
test_
.
What is caching_sha2_password
?
caching_sha2_password
is an authentication plugin using SHA-256 password hashing, offering stronger encryption than the native authentication method. Other plugins, such as mysql_native_password
, are also available.
Install MySQL (If Not Installed)
apt update
apt upgrade
apt install mysql-server
Login to MySQL
mysql
Set Password for Root
- Syntax:
ALTER USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password';
- Example:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Hello123456#';
Exit from MySQL
exit
Run the mysql_secure_installation
Program
mysql_secure_installation
Login to MySQL as Root User
mysql -u root -p
Create New User
- Syntax:
CREATE USER 'user_name'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'user_password';
- Example:
CREATE USER 'kumar'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Hello123456#';
Grant New User All Privileges
- Syntax:
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;
- Example:
GRANT ALL PRIVILEGES ON *.* TO 'kumar'@'localhost' WITH GRANT OPTION;
Exit from MySQL
exit
Restart Web Server
-
If Apache:
service apache2 restart
-
If Nginx:
service nginx restart