Linux mysql установить пароль root

How to change the mysql root password

I have installed MySQL server 5 on redhat linux. I can’t login as root so I can’t change the root password.

mysql -u root -p Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 

mysqladmin -u root password 'newpass' 

mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' 

/sbin/service mysqld start --skip-grant-tables 

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass') -> WHERE User='root'; ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user' 

I even uninstalled mysql-server (using yum) and then reinstalled it but that did not help. How do I force reset the root password?

10 Answers 10

One option is to save UPDATE mysql.user SET Password=PASSWORD(‘newpass’) WHERE User=’root’; into a file and then manually start mysqld with —init-file=FILENAME . Once the server starts, it should reset your password, and then you should be able to log in. After this, you should shut down the server and start it normally.

Good lord, why would someone give a -1 to the only solution I found to be working. An init sql file was the only way I could overwrite the mysql root user password and be able to use it. It is a shame you need to fallback to such operations on a freshly installed software, yet it is good to have a reliable workaround.

A little late to the game, but I had the same issue on a raspberry pi install and found out that it needs elevation. Adding a sudo to the front of the password change allowed it to work.

sudo mysqladmin -u root password 'newpass' 

followed by an elevated sql access

If either are not run as sudo, it will fail.

The root user password is an empty string by default.

And (using password: NO) says that there is no password.

Do you try to login from another system? I imagine you can only login as root user locally.

I am not sure, but I don’t think so. Unfortunately I never used redhat. EDIT: But why don’t you just try it?

Note that if you want to try logging in with no password, you should just avoid specifying -p on the command line altogether.

Note: The root password may not by blank by default, now (just found this out). I installed MySQL 5.7.16 from a yum repository and found that the installer had created a temporary password that was stored in a plain text file 😛 (for yum this was /var/log/mysqld.log)

I removed the MySQL installation and deleted the data files, and then reinstalled it.

Then I was able to set the root password. Once you set the root password to something. mysqladmin won’t let you reset it if you don’t know it.

To reset it, you’ve got to have ownership over how mysqld is executed, and feed it an init file to change the root password: https://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

This helped me on Windows with MySQL Server 5.6. Make sure you change the mysqld path to point to where you have installed MySql Server, for me it was «C:\Program Files\mysql\MySQL Server 5.6\bin\mysqld.exe» :

1. Log on to your system as Administrator.
2. Stop the MySQL server if it is running. For a server that is running as a Windows service, go to the Services manager: From the Start menu, select Control Panel, then Administrative Tools, then Services. Find the MySQL service in the list and stop it. If your server is not running as a service, you may need to use the Task Manager to force it to stop.
3. Create a text file containing the following statements. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root'; FLUSH PRIVILEGES; 

C:\> C:\mysql\bin\mysqld --init-file=C:\\mysql-init.txt 

If you installed MySQL to a location other than C:\mysql, adjust the command accordingly. The server executes the contents of the file named by the —init-file option at startup, changing each root account password. You can also add the —console option to the command if you want server output to appear in the console window rather than in a log file. If you installed MySQL using the MySQL Installation Wizard, you may need to specify a —defaults-file option:

C:\> "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld.exe" --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.5\\my.ini" --init-file=C:\\mysql-init.txt 

Источник

Установка, изменение и сброс пароля root в MySQL

Это руководство объясняет, каким образом можно установить, измененить или сбросить (если вы забыли пароль) рутовый пароль в MySQL. Снова и снова я наблюдаю одну и ту же картину:

mysqladmin: connect to server at ‘localhost’ failed error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’.

Поэтому я нашёл время, чтобы напомнить вам как решить связанную с этим проблему в MySQL. Если вы ищете быстрое решение проблемы по сбросу пароля root, можете найте его в конце данного руководства.

mysqladmin -команда, при помощи которой меняется пароль root в MySQL

Метод 1. Установка пароля root в первый раз.

Если вы никогда не устанавливали пароль root в MySQL, сервер не будет требовать пароля root для подключения к вашим базам данных. Чтобы впервые установить пароль MySQL используйте в консоли команду mysqladmin как показано далее:

mysqladmin -u root password newpass

где newpass будет пароль который вы устанавливаете.

Для изменения (обновления) пароля root воспользуйтесь следующей командой:

mysqladmin -u root -p oldpassword newpass

где oldpassword — ваш старый пароль, а newpassword соотвественно новый. Если же вы в ответ получили следующее сообщение:

mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘root’@’localhost’ (using password: YES)’

то это означает, что пароль вы попросту забыли, либо его сменил кто-то другой. Воспользуйтесь следующей инструкцией для восстановления пароля к вашему MySQL.

Изменения пароля MySQL для других пользователей.

Для изменения пароля обычного пользователя введите следующую команду:

mysqladmin -u user-name -p oldpassword newpass

где user-name — имя пользователя для которого вы меняете пароль.

Метод 2 — Обновление или изменение пароля.

MySQL хранит имена пользователей и пароли в таблице пользователей внутри базы данных. Вы можете обновить пароль используя следующий метод:

1. Залогиньтесь в MySQL и введите следующую команду:

2. Начните работу с базой данных. В качестве приглашения для ввода команд вначале строки у вас должно быть mysql>

3. Смените пароль пользователя

mysql> update user set password=PASSWORD(«newpass») where User=’ENTER-USER-NAME-HERE’;

4. Перегрузите привелегии и отлогиньтесь

Этот метод применим в случае использования на вашем сервере PHP и скриптов Perl.

Восстановление пароля root в MySQL.

Вы можете восстановить пароль от баз данных MySQL если повторите следующие 5 шагов:

• Остановите демон MySQL.
• Запустите демон MySQL (mysqld) с опцией —skip-grant-tables, т.к. в этом случае пароль не запрашивается.
• Подключитесь к серверу MySQL c root-привелегиями
• Введите новый пароль.
• Выйдите и перегрузите демон MySQL.

Далее приводятся команды, которые необходимо использовать для каждого шага, при условии, что вы вошли в систему с root-привелегиями.

1. Останавливаем службу MySQL:

/etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2. Запускаем службу с опцией —skip-grant-tables

Должен быть следующий вывод:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

3. Подключаемся с серверу MySQL при помощи клиента mysql:

mysql -u root

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

4. Вводим новый пароль для root:

mysql> use mysql;
mysql> update user set password=PASSWORD(«NEW-ROOT-PASSWORD») where User=’root’;
mysql> flush privileges;
mysql> quit

5. Останавливаем сервер MySQL:

/etc/init.d/mysql stop

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+ Done mysqld_safe —skip-grant-table

Запускаем MySQL-сервер и логинимся с новым паролем:

Источник

How to Change MySQL Root Password in Ubuntu 20.04

Passwords are hard to remember, so if you have forgotten the MySQL root password, luckily, there is a way to change it. This post has been written for you, and by the end of this post, you will have successfully changed the password of MySQL.

Before getting straight to the solution, it is assumed that you are using the latest version of the MySQL database on Ubuntu 20.04 LTS system. This post will provide a step-by-step guide on how to change the MySQL root password in Ubuntu 20.04. So, without wasting any time, let’s start.

Step 1: Check the version of MySQL on Ubuntu 20.04

First of all, check the version of your MySQL because this post contains the solution of changing the root password on version 8 or higher. If the version of your MySQL is lower than 8, then the solution will be different. The command for checking the version of MySQL is given below:

Step 2: Stop the MySQL server

To change the MySQL root password, you first need to shut down the MySQL server, and you can do so using the command:

Check the status of the MySQL server to verify using the command:

Step 3: Skip Grant Tables & Networking

To start the MySQL server without granting the tables and networking check, set the environment variable “MYSQLD_OPTS” which MySQL uses on startup:

Alright, the environment variable is set, and we can log in to the MySQL shell without providing any password.

Step 4: Start the MySQL service

After setting the environment variable “MYSQLD_OPTS”, start the MySQL service now using the command:

Step 5: Confirm the status of the MySQL Server

Confirm the status of the MySQL service, whether it is running or not:

Step 6: Sign In to the MySQL shell

Now, you need to log in as a root user to the MySQL server and for signing in to the MySQL shell, type the command:

Without providing any password, you will log in to the MySQL shell.

Step 7: Alter the root password

Now, flush the privileges first:

Select the MySQL database:

And set the new password for the root user using the ALTER command by typing the following statement:

At the place of “the-new-password” provide your new password. After successfully changing the MySQL root password, log out from the MySQL shell:

Step 8: Revert Database Server to Normal settings

For restarting the database server in “normal” mode, it is required to “revert” the changes we have made so that the networking is enabled and grant tables are loaded.

To do so, firstly unset the “MYSQLD_OPTS” environment variable which you have set previously:

After doing so, remove the modified system configuration by reverting “MySQL”:

Step 9: Kill all MySQL processes & Restart the MySQL service

Now, kill all the processes of MySQL before restarting the MySQL server:

After having the output as shown in the picture, hit “Enter” and restart the MySQL server using the command given below:

Step 10: Log in with the newly set Password

Log in to the MySQL shell with the newly set password:

Provide the recently set password in the ALTER command:

This is it. MySQL’s root password is successfully changed, and you are logged back into the MySQL shell.

Wrap Up

In this step-by-step guide, you have learned to change the MySQL root password on the Ubuntu 20.04 LTS system. This post contains a profound and easy-to-understand solution on resetting the root password of MySQL on Ubuntu 20.04.

About the author

Shehroz Azam

A Javascript Developer & Linux enthusiast with 4 years of industrial experience and proven know-how to combine creative and usability viewpoints resulting in world-class web applications. I have experience working with Vue, React & Node.js & currently working on article writing and video creation.

Источник