B.3.2.2 Can’t connect to [local] MySQL server

At Bobcares, we often get requests to fix MySQL errors, as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this MySQL for our customers.

I also can’t secure the installation due to the same error

What should I do?

Apr 29

in Other DevOps Questions

by
Kichu

• 19,040 points

reshown

Apr 30

by Hemant

1,055 views

i have recently installed MySQL *Ver 8. 28-0ubuntu0. 3 for Linux on x86_64 ((Ubuntu))*

after installation when i try to access my shell its showing

both with or without sudo, i know this has answers on stack and other forums but none seems to help me, i have tried reinstalling MySQL server but that also didn’t help, i tried skip grant option and then changing root password but that failed as well , need help in this , thank you

Got a new rails demo and i can’t start the server
i tried «mysql -u root -p» and its didn’t work
here is my database. yml:

asked Apr 21 at 3:55

B.3.2.2 Can't connect to [local] MySQL server

Check your root password on your MySQL server. Looks like you are using the wrong password.

answered Apr 21 at 8:49

6211 gold badge4 silver badges15 bronze badges

I get error like:

but it doesnt work.

Edit to include screenshots supplied in comments:

B.3.2.2 Can't connect to [local] MySQL server

B.3.2.2 Can't connect to [local] MySQL server

Question

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

то данная статья поможет вам разобраться в проблеме и устранить её.

Самым простым (и самым безопасным) решением будет создать нового пользователя и предоставить ему требуемые привилегии.

Но начнём мы с другого решения:

Open terminal and type

sudo mysql -u root -p

It will prompt you in mysql, here you can fire any mysql commands.

Use mysql table for change table type, so we can use empty password. Bellow is command for it

now we have to flush the privileges, because we have used UPDATE. If you use INSERT, UPDATE or DELETE on grant tables directly you need use FLUSH PRIVILEGES in order to reload the grant tables.

service mysql restart

Hope this may help

Thank you.

B.3.2.2 Can't connect to [local] MySQL server

Yoodley is reader-supported. When you buy through links on our site, we may earn an affiliate commission.

I have read through other responses to this error and none of them work.

mysql
mysql -u root
mysql -u root -p

All of them fail with the error:

If I try without sudo I get exactly the same error except with usr instead of root in the output.

I have tried stopping and starting mysqld with no success. Mysql itself seems to have installed under /usr/bin — Just to ensure that access was good to the directory I completed a chmod 777 bin and retried with the same result.

Seems all the answers to this question in google have failed.

B.3.2.2 Can't connect to [local] MySQL server

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

B.3.2.2 Can't connect to [local] MySQL server

You can enable access for root using one MySQL command.

I see this when I ssh to my Ubuntu server:

Welcome to Ubuntu 20. 4 LTS (GNU/Linux 5. 0-1018-aws x86_64)

sudo apt-get remove —purge mysql*
sudo apt-get purge mysql*
sudo apt-get autoremove
sudo apt-get autoclean
sudo apt-get remove dbconfig-mysql
sudo apt-get dist-upgrade

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql. service

When I run:

The idea of the command is to set the password.

How can I install mysql in this server to then be able to manage my databases?

I recently tried the answer from this question, but it just lead me to the same error.

asked Jan 15, 2019 at 6:30

Logging in using MySQL Workbench was successful for me after this.

B.3.2.2 Can't connect to [local] MySQL server

4,28011 gold badges19 silver badges31 bronze badges

answered Dec 2, 2019 at 12:16

Setup Temp Environment to access MySQL

sudo systemctl stop mysqld
sudo systemctl set-environment MYSQLD_OPTS=»—skip-grant-tables»
sudo systemctl start mysqld

Login in MySQL

Flush Previous Privileges & Grant All

answered Oct 7, 2019 at 7:28

B.3.2.2 Can't connect to [local] MySQL server

I am using Ubuntu 21. 04 which changed the mySQL from 5. x to 8. I am in favor of using the latest software so don’t want to return to 5. What worked in 5. x no longer works in 8.

Again, I am not sure what is different in 8. 0, but I noticed the identified by is slightly changed. So now I have

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

Дополнительно:  Как включить цифры справа на ноутбуке

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

Authored by:  Rackspace Support

Stop the MySQL service

sudo /etc/init. d/mysqld stop

Start MySQL without a password

sudo mysqld_safe —skip-grant-tables &

Connect to MySQL

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

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

Log in to the database

Test the new password by logging in to the database.

Enter your new password when prompted.

  • Install MySQL server on the Ubuntu operating system
  • Configure MySQL server on the Ubuntu operating system

I was having a really difficult time getting into Mysql after I first install it in my ubuntu 18. 04 box. Please see below steps I made in installing mysql-server

The short version of the installation is simple: update your package index, install the mysql-server package, and then run the included security script.

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

NOTE: The installation will no longer prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure. The 3rd command of “sudo mysql_secure_installation” will let you configure your mysql server with passwords and other options such as allow remote connections.

First you have to stop the mysql daemon

systemctl stop msyql

or you can also do this

sudo service mysql stop

Then you uninstall the mysql

$ sudo apt-get remove —purge mysql-server mysql-client mysql-common -y
$ sudo apt-get autoremove -y
$ sudo apt-get autoclean

Remove the MySQL folder:

$ rm -rf /etc/mysql

Delete all MySQL files on your server:

Your system should no longer contain default MySQL related files.

But still i get the same error of

So I came up with this solution from here.

Short steps are:

Happy Database Everyone!

Это может быть, если пароль не был задан при установке.

Порядок действий для установки/смены пароля root в mysql следующий:

Остановить mysql:sudo service mysql stop

После этого подключиться к mysql командой:mysql -u root

Обновить пароль root’a:

И перезапустить сервис:sudo service mysql restart
Если на шаге 2 вы добавляли skip-grant-tables в /etc/mysql/mysql. conf. d/mysqld. cnf — удалить эту строчку.

Подробнее в Русскоязычной документации Ubuntu

Пароль по умолчанию пустой. Возможно, вы неправильно набрали команду. Скопируйте именно эту: mysql -u root -p. На запрос пароля надо просто нажать Enter.

Попробуйте запустить mysql_secure_installation.

Если все равно не пускает — поищите пароль в логе: sudo grep ‘temporary password’ /var/log/mysqld. log.

Если и этот вариант не подошел — возможно, устанавливаете из какого-то левого репозитория. Удалите sudo apt-get purge mysql*, выключите левые репозитории и установите заново sudo apt-get install mysql-server.

Он пишет что пароль не нужен.

Тут два варианта, ИМХО. 1) Вы что-то не так поняли из курса:
2) Составитель курса что-то упустил. В любом случае или стоило бы сюда ссылку кинуть на этот курс или писать составителю.

По проблеме. Сервер mysql пишет вам, что пользователю ‘root’ доступ закрыт. Как мне кажется, нужно вначале создать бд, применить схему и там создастся пользователь, с данными которого вы подключитесь к бд. А слова «Using password: NO» означает лишь, что пароль и не использовался.

But now the above command will give you access denied error.

$ sudo mysql -u root -p

$ mysql -hxxx. xxx. xxx. xxx -uroot -p

Here also you will be prompted to enter root password. On entering the correct password, you will be able to login successfully. In this case, you need to also ensure that your MySQL database allows remote access.

How to Remove NaN from Python ListDelete All Except One File in LinuxHow to Mount Remote Directory or Filesystem in LinuxHow to Check Bad Sectors in LinuxHow to Convert Markdown to HTML

Quick solution for MySQL 8 (and MySQL 5. 7) error:

In this article we will do only one which is the one I’m using most.

Once you lost access to the MySQL database or simply you never have it in order to access to this data base you need to do:

  • You will be asked for your OS root password
  • Now you can reset your password with new one or remove the root password( not recommended:

Another possible solution depending on the context might be:

  • open terminal
  • sudo -i and root password
  • mysql
  • and next steps of previous

use this for Debian, Ubuntu, Linux Mint when you lock your account or lost access to it:

sudo mysql —defaults-file=/etc/mysql/debian. cnf

Дополнительно:  Синий экран при запуске браузера windows 10

Some tips related to MySQL error:

  • Have backup of your database and working restore procedure
  • Use strong password and program which remembers the password like HeidiSQL
  • Be sure for the database address — I have friend of mine who mistaken dev and prod and reset password for the wrong database
  • for production databases use high level of security — sudo mysql_secure_installation or something similar
  • SHOW VARIABLES LIKE ‘validate%’;
  • SHOW VARIABLES LIKE ‘validate_password_policy’;
  • SHOW STATUS LIKE ‘validate%’;

grep ‘temporary password’ /var/log/mysqld. log
Sort date (newest date)

You may see something like this;

Securing the MySQL server deployment.

If you see it says

Watch the last 10 minutes of this video, it teaches you how you do it.

Now the real frustration begins in either of the below 2 scenarios:

When I enter the mysql -u root -p command and am prompted for the password.

Please I really need help on this — I have lost count of the number of times I have had to uninstall and reinstall either MySQL or MariaDb because this pain in neck erf an error.

asked Apr 16, 2014 at 6:39

Check the file: /etc/mysql/mariadb. conf. d/mysqld. cnf

verify if this line exists:

plugin-load-add = auth_socket

then go to MariaDB:

and make these modifications:

I didn’t have access to mysqlworkbench, but after I applied this solution, everything ruan well.

B.3.2.2 Can't connect to [local] MySQL server

8k38 gold badges93 silver badges191 bronze badges

answered Jun 15, 2015 at 5:34

I think I may have figured out the issue — It has to do with the long password. I think that this should reported as a bug to MySQl and MariaDB developers because on installation you can create a password with over 100 characters but when you try to login to MySQl or MariaDB when it is in production you get the above error.

So glad I figured this out after so much pain — surprising really that this has not been identified as a bug considering how many years MySQL has been in production.

  • Start with a blank password on a non-production server and is if it works
  • If you are successful with step 1 then proceed with securing your mysql installation by typing the follwing command: sudo /usr/bin/mysql_secure_installation and try entering a more complex password — comination of lowercase letters, numbers, symbols and uppercase letters of at least 8 characters should be fine.
  • I recommend starting with 8 and move upwards till you reach a ceiling with the number of characters you can enter.

The above should be a step in the right direction to getting your MySQL server secured.

answered Apr 16, 2014 at 20:51

1111 gold badge1 silver badge5 bronze badges

Try this command in terminal:

mysql -u root -pnewpassword

26k16 gold badges98 silver badges116 bronze badges

answered Apr 16, 2014 at 7:04

B.3.2.2 Can't connect to [local] MySQL server

The best answer is here

Note that this solution by david-leonardo doesn’t survive a reboot.

answered Oct 15, 2017 at 7:18

3744 silver badges21 bronze badges

sudo apt-get remove mariadb-server
sudo apt-get purge mariadb-server

When I reinstalled, I still didn’t get asked for the root password.

Any ideas what else I could try?

asked Aug 15, 2015 at 16:12

You need to reset the password. so for that

answered May 15, 2016 at 16:16

B.3.2.2 Can't connect to [local] MySQL server

The idea with the new set-up is that you shouldn’t be using passwords at all. See UNIX_SOCKET Authentication Plugin for details.

So if you disable that plug-in for root and set a password, the daily cron job will break as it’s assuming it will log in as root without a password, but with the plug-in.

Later it says:

So it looks like passwords should no longer be used by applications.

answered Jul 21, 2016 at 20:22

B.3.2.2 Can't connect to [local] MySQL server

7757 silver badges15 bronze badges

Can’t reset MySQL (MariaDB) root password

answered Aug 28, 2015 at 6:42

2891 gold badge3 silver badges6 bronze badges

I did it by running this command, right after installation:

$ sudo mysql_secure_installation

At first step, password is blank, so just press Enter.

answered May 30, 2017 at 1:57

Just use sudo mysql -u root — that’s it

answered Oct 12, 2018 at 23:09

B.3.2.2 Can't connect to [local] MySQL server

8,55712 gold badges43 silver badges47 bronze badges

I hope this is going to help someone.

answered Nov 5, 2017 at 13:44

Разрешить пользователю root подключаться к MySQL и MariaDB без sudo

Для получения доступа к базе данных MySQL/MariaDB обычному пользователю без использования sudo привилегий, зайдите в приглашение командной строки MySQL

и запустите следующие команды:

Переключиться на аутентификацию по паролю можно следующим SQL запросом:

Обратите внимание, что вам нужно ввести ПАРОЛЬ.

Это устаревший метод, он не работает в новых версиях MariaDB, но может по-прежнему работать в MySQL.

sudo systemctl restart mysql. service
mysql -u root -p

==================Конец устаревшего метода==================

Создание нового пользователя для работы с MySQL через phpMyAdmin

Подключитесь к mysql

Запустите следующие команды (замените some_pass на желаемый пароль):

B.3.2.2 Can't connect to [local] MySQL server

B.3.2.2 Can't connect to [local] MySQL server

B.3.2.2 Can't connect to [local] MySQL server

Опционально: разрешение удалённых соединений

Помните: разрешение удалённому пользователю иметь все привилегии – это вопрос безопасности, имейте это ввиду, выполняя последующие шаги. Если вы хотите, чтобы этот пользователь имел те же привилегии во время удалённых подключений, дополнительно выполните (замените some_pass на пароль, используемый на Шаге #2):

Связанные статьи

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

B.3.2.2 Can't connect to [local] MySQL server

B.3.2.2 Can't connect to [local] MySQL server

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.

Now, to further the discussion, you could be facing the issue due to multiple reasons, some of which include:

Solution

mysql> FLUSH PRIVILEGES;

The main solution to this is to connect MySQL as root by switching authentication from auth_socket to mysql_native_password in the terminal:

  • Select Use Legacy Password Encryption from the two options available, the other one being Use Strong Password Encryption.
  • Using the search tool, openMySQL.prefPane and select the configuration tab.
  • Click ‘Select’ option of the ‘Configuration File’ and select/private/etc/my.cnf

With skipping the grant table, you can log in from anywhere and do almost anything on the database server.

/usr/local/mysql—macos-x86_64/bin/mysql -uroot -p

  • Since you have already reset the password, it is time to remove ‘skip-grant-tablesetc/mysql/my.cnf
  • Restart MySQL again and log in using the new password. The service will no longer show the error.

FAQs

Error logs are one of the most important logs in terms of IT operations because it helps in detecting and diagnosing functional problems that simply improves performance.

The MYSQL error log basically contains error messages, warnings and different notes which are created during the startup and shutdown phases.

A file or the console is a general location or, say, destination of error logs. When no location is specified, then in windows, the error logs are written to host_name. err ( host_name is the host system name) in the data directory, whereas in UNIX/Linux, the console is the default destination of errors.

How to change the root password for MySQL?

To change the root password in MySQL:

  • ~/mysql-pwd
  • Stop MYSQL withsudo systemctl stop mysqlcommand and then issue the command:sudo mysqld -init-file=~/mysql-pwd. As the command prompt returns, restart the MYSQL using thesudo systemctl start mysql

How to recover the root password for MySQL?

To recover the root password in MySQL:

  • sudo service mysql stopcommand to stop the MYSQL server.
  • mysql -u root

mysql> use mysql;

​mysql> flush privileges;

Note: In the above commands, NEWPASSWORD is the new password to be used.

This error may occur due to different reasons in different scenarios. Some of them are:

Let’s see, how we fix this access denied error for our customers in detail.

Not able to login

Recently, one of our customers approached us saying that he is getting an error like the one shown below while he is trying to log in to MySQL.

B.3.2.2 Can't connect to [local] MySQL server

Initially, we opened /etc/mysql/my. cnf

Here we skipped grant tables as there is no other way to log in.

By skipping the grant table option it allows anyone to log in from anywhere and can do anything on the database.

Note: skip-grant-tables is dangerous, so we will remove this at the end.

3 Then, we restarted the MySQL service using the command below:

Now MySQL is configured to skip the grant-tables. So, we can log in now.

We logged into MySQL using:

Then, we flushed the privileges using:

B.3.2.2 Can't connect to [local] MySQL server

Then, we removed skip-grant-tables from /etc/mysql/my. cnf

Finally, we restarted the MySQL service and logged in to MySQL using the new password.

This fixes the error.

No global privilege

If there is no global privilege then this error can happen. In such cases, our Engineers add the privilege by:

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

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

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

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

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

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

Что делать?

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

Установка новой версии

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

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

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

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

Already have an account? Sign in.

Solution 1: Sudo then Change Password If you get the “access denied” error, one way to solve it is by using sudo to log in to mysql and change the root password. Step 1: Open the command line on your system. Step 3: Enter the password for this account

Оцените статью
Master Hi-technology
Добавить комментарий