Lỗi access denied for user wime8a60 localhost to database sieuviethn năm 2024
const connection = createConnection({ host: 'localhost', user: 'new_user', password: 'your_password', database: 'hello_world_db' }); Show connection.connect(function(err) { if (err) { console.error('Error connecting to the database: ', err.stack); return; } console.log('Connected to the database as id ', connection.threadId); }); connection.end(); PS C:\Users\wasab\hello-world> node server.js Error connecting to the database: Error: Access denied for user 'new_user'@'localhost' (using password: YES) However, when I run node server.js, I still encounter the access denied error. Your help in resolving this issue would be greatly appreciated. Thank you. Answer
CommentsContent reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party. When working with MySQL on the local machine, you may encounter errors that may prevent you from working with the database and use it with other applications. Access denied for user root@localhost is a MySQL error that can be frustrating, as it prevents you from accessing your MySQL database. This error typically occurs when the root user password is incorrect or the root user doesn’t have the proper privileges to access the MySQL server. The good news is that resolving this issue is relatively straightforward. In this tutorial, we’ll look at common reasons behind the issue. Next, we will explore several solutions you can apply to fix this issue. Whether you’re a beginner or an experienced user, these troubleshooting steps will assist you in resolving the Access denied for user root@localhost MySQL error efficiently and effectively. Table Of Contents Causes of the Access denied for user root@localhost Error in MySQLIn most cases, this error occurs due to one or more of the following reasons: Incorrect PasswordThe password you provided for the root user is incorrect. This could happen if the password was changed, forgotten, or mistyped during login attempts. Incorrect UsernameIn some cases, the provided username can be incorrect. In MySQL, usernames are case-sensitive, so ensure you use the correct case for the username (for instance, root instead of Root). Insufficient PrivilegesThe root user may not have sufficient privileges to access the MySQL server or perform the requested operation. This might happen if privileges are not properly granted or revoked by accident. Host IncompatibilityThe host shown in the MySQL user account is not the one from which the root user attempts to connect. For instance, if the user account is limited to localhost connections but attempts to connect from a different host address, the DBMS will generate an access denied error. Problems in MySQL Server ConfigurationThe user may not be able to access the MySQL server or run specific commands due to server limitations or misconfiguration in the server config files. Conflicts in Password Encryption Method The password encryption method used by the MySQL server might not match the one expected by the client. As a result, the user may experience this error because the client expects a different authentication plugin or method. Network Issues Connectivity issues, firewall rules, or network configurations could prevent the MySQL client from establishing a connection to the server, resulting in the access denied error. How to Resolve the Access denied for user ‘root’@’localhost’ ErrorNow that you know the causes behind this error, let’s see how you can fix it and restore access to the MySQL server. We will start with the prerequisites. The Prerequisites
Fix 1: Check Username and PasswordDouble-check the username and password you’re using to connect to MySQL. Ensure that the username is correct (case-sensitive) and that you use the correct password. Fix 2: Reset Password for the root UserIf you suspect the root password is incorrect or you have forgotten it, we recommend resetting it using the MySQL password reset procedure. This typically involves stopping the MySQL server, starting it again with the –skip-grant-tables option to bypass authentication, resetting the root password, and finally restarting the server normally. Here’s a general outline of the steps: Step 1: Determine the Current MySQL VersionRun the following command in the terminal to get the current version information:
With MySQL, you might see something like mySQL Ver 14.14 Distrib 5.7.16 for Linux (x86_64) Likewise, the output for the MariaDB server version looks like this: mysql Ver 15.1 Distrib 5.5.52-MariaDB. Step 2: Stop the Database ServerNow, you can run the following command to stop the database server:
Alternatively, run the following command to stop the MariaDB service:
3: Restart the Database ServerIf you start MySQL and MariaDB servers without loading user privileges information, you can access the database command line with root permissions and no password protection. You will need this capability to access the database. Start by stopping the database from loading the grant tables, which include user privilege data. You should also stop networking to prevent other clients from connecting to the server, as it poses a security risk. Run the following command to launch the database without enabling networking or loading the grant tables:
Instead, a database shell prompt will appear immediately. Type help or \h on the MySQL prompt to get assistance. Similarly, run \c to remove the current input statement. Step 4: Change the root Password Start by running the following statement to remove all current privileges:
You can now modify the current password of the root user. Use the following statement for MariaDB 10.1.20 and newer and MySQL 5.7.6 and newer:
Alternatively, run the following statement to set the password for ‘root’@’localhost’ in MySQL 5.7.5 and older:
Also, make sure to reload the grant tables. You should notice an indication that the instruction was carried out successfully. OK query, affecting 0 rows in 0.00 seconds Step# 5: Restart the Database Service You can now terminate the manual database server instance and restart it using the original configuration because the password was updated. Run the following command to restart the database: For MySQL`# sudo systemctl start mysql` For MariaDB ` sudo systemctl start mariadb`Next, log into the MySQL shell as the root user with the following command: `# sudo systemctl stop MySQL`0 The command should prompt you for the password. Alternatively, run the following sequence of commands: `# sudo systemctl stop MySQL`1 `# sudo systemctl stop MySQL`2 Fix 3: Grant Appropriate PrivilegesIn rare cases, the root user may lack sufficient privileges. If that’s the case behind the error, you can grant them manually. For this, log into the MySQL shell as a user with administrative privileges and execute the following GRANT statements to grant the necessary privileges to the root user. `# sudo systemctl stop MySQL`3
Fix 4: Check Host AccessVerify that the root user can connect from the localhost host. You can check the user’s host access in the MySQL user table (mysql.user) and adjust it (if necessary) by running the following statement: `# sudo systemctl stop MySQL`5 Fix 5: Check MySQL Server ConfigurationReview the MySQL server config file (typically my.cnf or mysqld.cnf) for settings restricting access or authentication methods. Ensure authentication methods such as mysql_native_password are enabled and properly configured in the configuration file. To connect to MySQL as root with a password, you must change its authentication method from auth_socket to mysql_native_password. Launch the MySQL shell from your terminal by running this command: `# sudo systemctl stop MySQL`6 Next, execute the following statement to see which authentication method is being used for the MySQL users: `# sudo systemctl stop MySQL`7 Next, run the following statement that alters the root password you set in Step 2 and updates it to a strong password of your own.`# sudo systemctl stop MySQL`8 Next, you should verify the authentication methods used by every user to ensure the root is no longer authenticating through the auth_socket plugin: `# sudo systemctl stop MySQL`7 Finally, run the following statement to exit the MySQL shell:`# sudo systemctl stop mariadb `0 Fix 6: Check Network and Firewall SettingsFinally, you should check that there are no network connectivity issues or firewall rules blocking connections to the MySQL server. In addition, you should verify that the MySQL server is listening to the correct ports and network interfaces. Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database Conclusion The Access denied for user ‘root’@’localhost’ error in MySQL can often be attributed to incorrect login credentials. This error, while common, can cause frustration for users attempting to access their relational database management system. Users can resolve this issue swiftly by double-checking the provided username and password. However, if the problem persists, it’s essential to review the MySQL service configuration and ensure that the appropriate permissions and authentication methods are in place. With careful attention to detail and troubleshooting, users can overcome this hurdle and regain access to MySQL databases. At RedSwitches, we’re dedicated to helping you discover the perfect server solutions to drive your business to new heights. So, if you’re looking for a robust server, we offer the best dedicated server pricing and deliver instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner. FAQs
After troubleshooting, what should I do if I still get the “Access denied” error? If you’ve exhausted troubleshooting options and are still facing the error, consider resetting the root password or creating a new MySQL user with appropriate permissions. Review MySQL’s error logs for more specific error messages that might explain the issue.
|