So I am stuck with this error when trying to connect my node.js application with MySQL. It won't let me connect to MySQL from localhost, not a single command is working.

The MySql workbench also says the same

I can't use any database commands since it's not letting me access mysql. Gone through almost all possible solutions on the internet none of them worked. Please help me out here even an explanation for this would help if not the solution.

CodePudding user response：

In order to access you must do the following steps :

1. Run the terminate with user permission.

2. Access the path where you have mysql installed.

3. Put the following sentence.

mysql.exe -u root -ppasw

-u : It is the user.

-p : the password but next to the p without space.

If it does not work try this in windows cmd

To restore to a single concrete database.

mysqlbinlog -database='yourFile.00004'

Explanation : The Binary log.

• It has replaced the old update file.

• Its mission is to update the DBs during a recovery operation.

• Replication masters are used as a reminder of the statements to be sent to the slave servers.

• If the name is not specified, the host is chosen.

• Performance drop of 1%.

• Active bins must not be opened during execution.

• If you put extension to the file, it is ignored.

• A new BIN_LOG file is created when :

  • The server is restarted

  • A Flush binary Logs is made

  • The size specified in MAX_BINLOG_SIZE is exceeded.

• The files that are generated have an extension that are sequential numbers and represent the order (index) of their creation controlled by the name host_name.index.

• To activate and decomment the log-bin my.ini directive. If log-bin=file is used, that name will be used to name the sequence of files.

• To delete index files.

  • purge binary before date-time (in this format "yyyy-mm-dd hh:mm:ss" or now() or interval....)

  • purge binary logs to filename; deletes up to this file (this one not included)

  • reset master -> deletes all files

• To disable the binary log, the session variable is used.

  • SQL_LOG_BIN : Up to version 5.6 this one is

  • EXECUTE DB : binlog-do-db=BD

  • DOES NOT RUN DB : binlog-ignore-db=BD

• The commands are the continuation of the binary log.

  • create database

  • alter database

  • drop database

• To see the content of a binary file (must not be open).

mysqlbinlog "file with its path".

• To restore several binary files must be done in one step.

mysqlbinlog file1 file2 file3 file3 | mysql -u root -ppassword

• To restore.

  • Overwrite the file >

  • Adds in the content respecting the content >>

• To restore to a single concrete database.

mysqlbinlog -database='filenamebinlog.00004'

If the above does not work, do this first

Another option

ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

Cause : mysql only has one root user, select MD5 after changing root password, then submit, reboot. Login appears "The host 'localhost' is not allowed to connect to this MySQL server..." Try the user table in another mysql library, overwrite, no, it is estimated that the version is different Resolve : Edit my.ini

Add a sentence to [mysqld]: skip-grant-tables

For example :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
skip-grant-tables

The purpose is :

Bypass MySQL access control, anyone can log in to the MySQL database as an administrator in the console.

It should be noted that after changing the password, the MySQL server must be stopped and restarted to take effect.

Restart the mysql service!