6.2.22 Troubleshooting Problems Connecting to MySQL

Background

After the virtual machine has been provisioning with LAMP and you check the MariaDB status

systemctl status mariadb

It happened before BUT no documentation to fix this.

Server errors are annoying, especially when they are cryptic like “sql error 1045 sqlstate 28000″.

The message contains some error codes.

But, what’s the real problem here?

At Bobcares, we help website owners resolve complex errors like “sql error 1045 sqlstate 28000“ as part of our Outsourced Hosting Support services.

Today, let’s discuss the top 5 reasons for this error and we fix them.

Apr 29

in Other DevOps Questions

by
Kichu

• 19,040 points

•

2,013 views

I am new to Oracle Cloud and not an expert on MySql, I have built my first Arm-based Ampere A1. I was successfully able to install Apache web server. I tried to do a yum install for MySQL.

I can start and stop mysqld

I can confirm that port 3306 starts

However when I try to login into MySQL itself

$mysql -u root

• I have updated /etc/my.cnf with a password for root
• Turned off SELinux
• sudo mysqld —skip-grant-tables —skip-networking

Any help is greatly appreciated!

I already have a mysql database in local (installed, configured and containing things for a service).

• Socket Unix
• MySQL password : mypassword
• MySQL password confirmation : mypassword
• ERROR

Do you have a solution to force make it using the password ? I can’t disable the password of MySQL because the service is accessible by the internet. (And I want to learn a «clean» solution).

• Access to a command line or terminal window
• MySQL or MariaDB installed

mysql -u root -p

You can enable access for root using one MySQL command.

The same as Ulrich Metzger, after upgrading my machine to Ubuntu 16. 04 and Mysql-server-5. 7, I couldn’t log in with root because the plugin column changed.

My problem was not solved with sudo dpkg-reconfigure mysql-server-5. 7 + removing + purging + cleaning.

I had to stop the mysql service:

# sudo service mysql stop

Then restart the mysql daemon with the —no-grant-tables option:

# sudo mysqld_safe —no-grant-tables &

Then in another terminal, enter the mysql console (which now doesn’t need authentication) with the command mysql, and update the password and plugin columns via a SQL UPDATE:

Finally, kill the mysqld_safe command, restart mysql service and connect to mysql normally:

# sudo service mysql start
# mysql

I have installed MariaDB on Ubuntu 16. But when trying for the first time is says:

And when I try mysql_secure_installation to set a root password is says:

Giving Enter doesn’t work. I read something about «UNIX_SOCKET plugin is installed by default in new installs of Ubuntu 15. 10» but I do not understand how I can log in with this. I hope getting a answer soon!

Одной из наиболее популярных проблем является ошибка mysql №1045 (ошибка доступа). Текст ошибки содержит имя пользователя, которому отказано в доступе, компьютер, с которого производилось подключение, а также ключевое слово YES или NO, которые показывают использовался ли при этом пароль или была попытка выполнить подключение с пустым паролем.

This content has been archived, and is no longer maintained by Indiana University. Information here may no longer be accurate, and links may no longer be available or reliable.

This is document awmx in the Knowledge Base. Last modified on 2021-09-08 10:23:03.

Статистика форума SQLinfo показывает, что одной из наиболее популярных проблем является ошибка mysql №1045 (ошибка доступа). Текст ошибки содержит имя пользователя, которому отказано в доступе, компьютер, с которого производилось подключение, а также ключевое слово YES или NO, которые показывают использовался ли при этом пароль или была попытка выполнить подключение с пустым паролем.

Информация об ошибке

Клиентская версия MySQL -8. 16, подключение аутентификации пароля не совместима

Уменьшите версию клиента MySQL

Использование Default-Auth = mysql_native_password login

Интеллектуальная рекомендация

Когда коллега сделал запрос Get, параметр был на китайском языке, а полученный искаженный код на сервере Tomcat, поэтому я намеренно изучил его, главным образом потому, что кодировка, используемая Tom.

Перегрузка параметров Python

Python не поддерживает перегрузку параметров Обратите внимание на нижний пример: Описание в приведенном выше примере, тестирование () не вызова теста () без параметров, только называемых последним.

PHP имен и автоматический класс загрузки

задний план Недавно друг спросил меня, что мое пространство имен PHP было похоже, но из-за долгосрочного развития автор на самом деле забыл почти, поэтому я не могу ответить. Просто не забудьте быть о.

Stop the mysql demon process using this command :

sudo /etc/init. d/mysql stop

Start the mysqld demon process using the —skip-grant-tables option with this command

sudo /usr/sbin/mysqld —skip-grant-tables —skip-networking &

start the mysql client process using this command

from the mysql prompt execute this command to be able to change any password

Then reset/update your password

If you have a mysql root account that can connect from everywhere, you should also do:

once have received a message indicating a successful query (one or more rows affected), flush privileges:

Then stop the mysqld process and relaunch it with the classical way:

sudo /etc/init. d/mysql stop
sudo /etc/init. d/mysql start

There is another way using «Purge» given in link

use this command to check the possible output

then check the GRANT LIST the out put will be like his

Cart

No products in the cart.

Let’s learn how to get rid of it. Step #1. Open Your MySQL Console

• Left click your WAMP icon located at the bottom right of your desktop.
• Click on MySQL Console.

Step #2. Enter the Provided Commands

If you have a password, you can ignore this part.

• Type in:
  use MySQL;
• Press Enter.
• Flush the privileges by typing:
  FLUSH PRIVILEGES;
• Exit by typing:
  Exit

Using a text editor, such as Notepad++, open your config. inc. php file.

Report

You have already reported this

I’m trying to install queXS cati app on my Ubuntu desktop and I installed MySQL server and PHP 5 and I cannot login into MySQL server as root without password:

But it’s okay when I enter mysql -u root -p

I can’t figure out what the problem is.

7,86238 silver badges49 bronze badges

asked Jan 7, 2014 at 10:42

Add switch -p for password based login:

That is the normal behaviour. You set a root password for your database so from now on you can’t access it without password. That is why it reports:

Obviously when you give the password with the -p switch you succeed.

answered Jan 7, 2014 at 10:47

5k3 gold badges44 silver badges66 bronze badges

If you want to make it easier to access your mysql, create a file. cnf in /root/ with these lines:

where of course mysqlrootpassword is your password for mysql’s root password. When you execute mysql it uses this password.

Attend to the safety of this file — give it secure rights, so that nobody on your server can read it!

1k54 gold badges203 silver badges316 bronze badges

answered Jan 16, 2014 at 22:25

The web gui is easy but to be secure use the terminal.

answered Sep 24, 2014 at 12:36

Примечание: При выполнении данных действий может случится так, что вход для пользователя root будет заблокирован. Во избежание данной проблемы настоятельно рекомендую создать нового пользователя с правами root.

Using this guide, you will discover three methods of resolving this error. ”The best way to understand this error is to break it down into single components. This error occurs when you attempt to login into your MySQL instance on your local machine, hence ‘localhost. ’An example is as shown:

Solution 1: Sudo Into MySQLThe first and most common method to resolve this error is to sudo into your MySQL instance and set the auth_plugin to mysql_native_password. Open your terminal and run:Enter the password for the account and proceed. Next, change the auth_plugin and set a new password for the root account as shown:Changing the authentication plugin will allow you to log in to your server. Next, flush the privileges to update the grant tables:Finally, exit the terminal and attempt your login:If the error persists, you may need to edit the MySQL configuration file. In most cases, it is under the name my. cnf in the /etc or /etc/mysql directories. If the file does not exist, create it. Close and save the file. Next, restart the MySQL server and log in. $ sudo service mysql restart$ mysql -u root -pOnce logged in, flush the privileges and set a new password:Ensure to replace the command above with a secure password. Once completed, edit my. cnf file and remove the entry we added earlier. Restart the MySQL server and log in with your new password. Solution 3 – Start MySQL in Safe ModeMySQL provides you with the mysqld_safe utility for starting the MySQL server. This utility allows us to specify safe features when starting and restarting the server for diagnosing errors. Start by stopping the MySQL Server. $ sudo service mysql stopNext, start MySQL with no authentication as;$ mysqld_safe —skip-grant-tables &The command will start the server in the background as a job.

Once the daemon is running (with authentication disabled), open a new terminal and run:The command above should allow you to log in without a password. Once logged in, flush the privileges and set a new password as:Close the session and restart MySQL. Now log in to your server with your new password. ClosingIn this article, we covered three main methods of resolving theWe hope this tutorial helped you resolve this error.

Before we move on to the reasons for this error, let’s first get an idea of this error.

Website owners face this error when querying data from the SQL server.

For instance, the complete error message looks like this:

In our experience managing servers, we’ll see the major causes of this error and how our Dedicated Support Engineers fix it.

1) Typo in username and password

This is the most common reason for the error “sql error 1045 sqlstate 28000″.

Therefore, SQL server can’t identify the authenticity of the account.

Solution

Mysql databases option in cPanel

In some cases, website owners get errors like this:

And, it can be probably a mis-typed password during the initial setup.

For example, we use the below command to reset the root password in safe mode.

3) User doesn’t exist

Finally, the result is “sql error 1045 sqlstate 28000“.

5) Insufficient privileges

Причина возникновения ошибки 1045

Таким образом, причина возникновения MySQL error 1045 — неправильная комбинация трех параметров: имени пользователя, хоста и пароля.

В качестве имени хоста могут выступать ip адреса, доменные имена, ключевые слова (например, localhost для обозначения локальной машины) и групповые символы (например, % для обозначения любого компьютера кроме локального). Подробный синтаксис смотрите в документации

Примеры. 1) Если вы не указали в явном виде имя хоста

GRANT ALL ON publications. * TO ‘ODBC’ IDENTIFIED BY ‘newpass’;

2) Другой первопричиной ошибки mysql 1045 может быть неправильное использование кавычек.

Что делать?

Если изначально была ошибка:

Phpmyadmin

Устанавливаете новую версию MySQL, но в конце при завершении конфигурации выпадает ошибка:

Это происходит потому, что ранее у вас стоял MySQL, который вы удалили без сноса самих баз. Если вы не помните старый пароль и вам нужны эти данные, то выполните установку новой версии без смены пароля

Answer

It seems that you have forgotten root password which was set during installation of MariaDB

Solution:- reset root password

2) Run mysqld safe version by below command
sudo mysqld_safe —skip-grant-tables &

3) Run mysql client with below commnad on new terminal
mysql -u root

5) Login with new password, set in above command
mysql -u root -p
Enter password:

If not specifying the host to connect (with -h flag), MySQL client will try to connect to the localhost instance while you may be trying to connect to another host/port instance.

Fix: Double check if you are trying to connect to localhost, or be sure to specify host and port if it’s not localhost:

2) User does not exist

or for public IP:

4) Password is wrong, or the user forgot his password

Fix: Check and/or reset password:

5) Special characters in the password being converted by Bash

Fix: Prevent bash from interpreting special characters by wrapping password in single quotes:

6) SSL is required but the client is not using it

Fix: Adding –ssl-mode flag (–ssl flag is deprecated but can be used too)

7) PAM backend not working

To reset password:

Finally, if you are genuinely locked out and need to circumvent the authentication mechanisms in order to regain access to the database, here are a few simple steps to do so:

• Stop the instance
• Start the instance
• Start MySQL again

Таким образом, причина возникновения MySQL error 1045 — неправильная комбинация трех параметров: имени пользователя, хоста и пароля.

GRANT ALL ON publications. * TO ‘ODBC’ IDENTIFIED BY ‘newpass’;

Это происходит потому, что ранее у вас стоял MySQL, который вы удалили без сноса самих баз. Если вы не помните старый пароль и вам нужны эти данные, то выполните установку новой версии без смены пароля, а потом смените пароль вручную через режим —skip-grant-tables.

Статья написана по материалам форума SQLinfo, т. в ней описаны не все потенциально возможные случаи возникновения ошибки mysql №1045, а только те, что обсуждались на форуме. Если ваш случай не рассмотрен в статье, то задавайте вопрос на форуме SQLinfo
Вам ответят, а статья будет расширена.

Дата публикации: 25. 2013

Форум

• Первое новое сообщение
• Поиск

• Ошибки Open Server
• Часовой пояс:

Контакты:
, предоставляем бесплатную удалённую техническую поддержку через TeamViewer

Related Questions In Other DevOps Questions

Already have an account? Sign in.

Enter your password at the prompt. A MySQL shell loads.

After you run the commands listed above, exit the MySQL shell by pressing CTRL + D on your keyboard or type exit; and hit enter. There is no need to restart the mysqld service to log in.

Now try again to access MySQL with root. In the terminal, type in:

There are different ways to approach this issue, but we selected the easiest and fastest method. Make sure to enter the commands as listed in the article to avoid errors in SQL syntax.

From the other cloud services that we have, we knew that this is something to do with the ‘UNIX_SOCKET’ authentication.

We also know that cause as this new machine has been rebuilt a few times, changing the root password OR changing the MariaDB root password has got nothing to do with this.

After logging in to MariaDB, we found nothing on this query:

We found something interesting configuration on the /etc/mysql/debian. cnf. The password was blank on both client and mysql_upgrade. We put the password for testing purposes.

Note: Ensure to double quote within the password. For instance: password = “password”

Restart the MariaDB:

systemctl restart mariadb

Check the MariaDB status:

The issue has gone.

Note: This may fix the issue, however, we are investigating this approach and we believe it’s not safe to put the password into this file. On the flip side though, the MariaDB root password has to be different anyway from the operating system root.

Change the MariaDB root password:

Test the root account.

Open the file – /etc/mysql/debian. cnf

Change the password to a new password.

Stop MariaDB service:

sudo systemctl stop mariadb

Start the MariaDB service:

sudo systemctl start mariadb

Check the status again:

sudo systemctl status mariadb

sudo apt-get update sudo apt-get install mysql-server-5. 5 sudo apt-get install mysql-client-5. 5 sudo apt-get install mysql-common sudo apt-get install glade sudo apt-get install ntp

Then I do

cd ~/Desktop/iPDC-v1. 1/DBServer-1. 1 mysql -uroot -proot <«Db. sql»

How may I fix it and continue?

Mike Brant

answer at
2014-02-21

210

Note: For MySQL 5. 7+, please see the answer from Lahiru to this question. That contains more current information.

The default root password is blank (i. , an empty string), not root. So you can just log in as:

You should obviously change your root password after installation:

› See also:
Mysql
Sql

› Estimated Reading Time: 1 min› See also:
Database

› See also:
Mysql
Sql

› See also:
Function

› See also:
Laravel