Reinstalando o MySQL ou redefinindo sua senha root

Question

Estou executando o MySQL 5.7.19, sob o LAMP, em dois Ubuntu 16.04. Esta é uma instância do AWS EC2.

Já faz algum tempo que eu instalei o MySQL e não o usei, então não seria um problema se eu o reinstalasse. Nada que eu insiro para a senha de root funciona.

Eu tentei

 sudo /etc/init.d/mysql stop
 sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
sudo  mysql -u root

Resultou em

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Eu então tentei o procedimento descrito aqui . Quando entrei

 sudo apt-get install mysql-server

Eu tenho

Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 linux-headers-generic : Depends: linux-headers-4.4.0-119-generic but it is     not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).

Quando eu entro

ps -aux | grep mysql

o resultado é

ubuntu   27152  0.0  0.0  12948   984 pts/1    S+   12:30   0:00 grep --color=auto mysql

Quando eu entro

 mysql -u root -p

Eu obtenho

Enter password:

Então o mysql não foi desinstalado e não consigo reconfigurá-lo.

Atualização 1:

Quando eu corri

sudo dpkg-reconfigure mysql-server-5.7

Eu tenho

update-alternatives: warning: forcing reinstallation of alternative 
/etc/mysql/mysql.cnf because link group my.cnf is broken
update-alternatives: warning: not replacing /etc/mysql/my.cnf with a link
mysql_upgrade: [ERROR] unknown option '--~'
Upgrade process encountered error and will not continue.

 mysql -u root -p

resulta em

Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Editar 2

Eu tentei o seguinte com os resultados associados.

ubuntu@ip-10-0-1-73:~$ sudo apt autoremove --purge mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 linux-headers-generic : Depends: linux-headers-4.4.0-119-generic but it is     not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify     a solution).
ubuntu@ip-10-0-1-73:~$ sudo apt install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 linux-headers-generic : Depends: linux-headers-4.4.0-119-generic but it is     not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify     a solution).

Rodando

apt-get -f install

não mudou nada.

Editar 3:

Se eu usar

sudo mysql -u root

leva-me ao mysql. Se eu usar o switch -p, ele aceita qualquer coisa como uma senha. No entanto, quando eu entro

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('newPassword') WHERE User='root';

Eu obtenho

Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

Warning (Code 1681): 'PASSWORD' is deprecated and will be removed in a future release.

Eu então inseri

FLUSH PRIVILEGES;

e recebi a seguinte mensagem

Query OK, 0 rows affected (0.00 sec)

Eu saí e ainda obtenho o mesmo resultado se eu não usar o sudo

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

Editar 4:

Eu tentei o seguinte com os resultados seguintes.

$ sudo apt install linux-headers-4.4.0-119-generic
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 linux-headers-4.4.0-119-generic : Depends: linux-headers-4.4.0-119 but it     is not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).
ubuntu@ip-10-0-1-73:~$ sudo apt-get -f install linux-headers-4.4.0-119-    generic
Reading package lists... Done
Building dependency tree
Reading state information... Done
You might want to run 'apt-get -f install' to correct these:
The following packages have unmet dependencies:
 linux-headers-4.4.0-119-generic : Depends: linux-headers-4.4.0-119 but it     is not going to be installed
E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a solution).
$ sudo apt-get -f install
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following packages were automatically installed and are no longer     required:
  linux-headers-4.4.0-64 linux-headers-4.4.0-64-generic linux-headers-4.4.0-    66 linux-headers-4.4.0-66-generic linux-headers-4.4.0-70 linux-headers-4.4.0-70-generic
  linux-headers-4.4.0-71 linux-headers-4.4.0-71-generic linux-headers-4.4.0-    72 linux-headers-4.4.0-72-generic linux-headers-4.4.0-75 linux-headers-4.4.0-75-    generic
  linux-headers-4.4.0-78 linux-headers-4.4.0-78-generic linux-headers-4.4.0-    79 linux-headers-4.4.0-79-generic linux-headers-4.4.0-81 linux-headers-4.4.0-81-generic
  linux-headers-4.4.0-83 linux-headers-4.4.0-83-generic linux-headers-4.4.0-87 linux-headers-4.4.0-87-generic linux-headers-4.4.0-89 linux-headers-4.4.0-89-generic
  linux-headers-4.4.0-91 linux-headers-4.4.0-91-generic linux-headers-4.4.0-92 linux-headers-4.4.0-92-generic linux-image-4.4.0-112-generic
  linux-image-4.4.0-64-generic linux-image-4.4.0-66-generic linux-image-    4.4.0-70-generic linux-image-4.4.0-71-generic linux-image-4.4.0-72-generic
  linux-image-4.4.0-75-generic linux-image-4.4.0-78-generic linux-image-4.4.0-79-generic linux-image-4.4.0-81-generic linux-image-4.4.0-83-generic
  linux-image-4.4.0-87-generic linux-image-4.4.0-89-generic linux-image-4.4.0-91-generic linux-image-4.4.0-92-generic linux-image-4.4.0-97-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  linux-headers-4.4.0-119 linux-headers-4.4.0-119-generic
The following NEW packages will be installed:
  linux-headers-4.4.0-119 linux-headers-4.4.0-119-generic
0 upgraded, 2 newly installed, 0 to remove and 224 not upgraded.
10 not fully installed or removed.
Need to get 0 B/10.8 MB of archives.
After this operation, 78.3 MB of additional disk space will be used.
Do you want to continue? [Y/n]
(Reading database ... 512472 files and directories currently installed.)
Preparing to unpack .../linux-headers-4.4.0-119_4.4.0-119.143_all.deb ...
Unpacking linux-headers-4.4.0-119 (4.4.0-119.143) ...
dpkg: error processing archive /var/cache/apt/archives/linux-headers-4.4.0-    119_4.4.0-119.143_all.deb (--unpack):
 unable to create '/usr/src/linux-headers-4.4.0-    119/include/linux/cpufeature.h.dpkg-new' (while processing './usr/src/linux-        headers-4.4.0-119/include/linux/cpufeature.h'): No space left on device
No apport report written because the error message indicates a disk full     error
                                                                                      dpkg-deb: error: subprocess paste was killed by signal (Broken pipe)
Preparing to unpack .../linux-headers-4.4.0-119-generic_4.4.0-    119.143_amd64.deb ...
Unpacking linux-headers-4.4.0-119-generic (4.4.0-119.143) ...
dpkg: error processing archive /var/cache/apt/archives/linux-headers-4.4.0-    119-generic_4.4.0-119.143_amd64.deb (--unpack):
 unable to create '/usr/src/linux-headers-4.4.0-119-    generic/include/config/rtc/drv/88pm860x.h.dpkg-new' (while processing     './usr/src/linux-headers-4.4.0-119-generic/include/config/rtc/drv/88pm860x.h'):     No space left on device
No apport report written because the error message indicates a disk full     error
                                                                                  dpkg-deb: error: subprocess paste was killed by signal (Broken pipe)
Errors were encountered while processing:
 /var/cache/apt/archives/linux-headers-4.4.0-119_4.4.0-119.143_all.deb
 /var/cache/apt/archives/linux-headers-4.4.0-119-generic_4.4.0-    119.143_amd64.deb
E: Sub-process /usr/bin/dpkg returned an error code (1)

$df
Filesystem     1K-blocks    Used Available Use% Mounted on
udev              498080       0    498080   0% /dev
tmpfs             101440   12068     89372  12% /run
/dev/xvda1       8117828 5515716   2166836  72% /
tmpfs             507184       0    507184   0% /dev/shm
tmpfs               5120       0      5120   0% /run/lock
tmpfs             507184       0    507184   0% /sys/fs/cgroup
tmpfs             101444       0    101444   0% /run/user/1000

    

Answer 1

Finalmente resolvido.

Primeiro, encontre e remova todas as instâncias de my.cnf.bak. Também removi o /etc/alternatives/my.cnf, /var/lib/dpkg/alternatives/my.cnf e /etc/mysql/conf.d (mas fiz o backup deles). Então

sudo apt-get remove --purge mysql-\*
sudo apt-get install mysql-server mysql-client

Se você ainda tiver algo como

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

isso geralmente é resolvido com

sudo service mysql start

    

Answer 2

Para redefinir a senha do MySQL, siga o procedimento de " B.5.3.2.2 Redefinindo a senha Senha Raiz: Sistemas Unix e Unix-like ":

Stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process. Use the actual path name of the .pid file in the following command:

kill 'cat /mysql-data-directory/host_name.pid'

Use backticks (not forward quotation marks) with the cat command. These cause the output of cat to be substituted into the kill command.

Create a text file containing the password-assignment statement on a single line. Replace the password with the password that you want to use.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

Save the file. This example assumes that you name the file /home/me/mysql-init. The file contains the password, so do not save it where it can be read by other users. If you are not logged in as mysql (the user the server runs as), make sure that the file has permissions that permit mysql to read it.

Start the MySQL server with the special --init-file option:

mysqld --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing the 'root'@'localhost' account password.

Other options may be necessary as well, depending on how you normally start your server. For example, --defaults-file may be needed before --init-file.

After the server has started successfully, delete /home/me/mysql-init.

    

Answer 3

Primeiro, vamos corrigir seu problema com o gerenciador de pacotes. Instale as dependências ausentes usando:

sudo apt install -f

Depois disso, se você já tiver desinstalado mysql-server , reinstale-o:

sudo apt install mysql-server

Se você não o desinstalou ainda, ou se reinstalá-lo não solicitou uma nova senha de root do MySQL, execute (adaptando o número da versão, se necessário):

sudo dpkg-reconfigure mysql-server-5.7

Isso deve ter redefinido sua senha agora. Tente fazer login novamente como root com o prompt de senha interativo:

mysql -u root -p

Se você ainda receber o erro

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

isso provavelmente significa que o servidor MySQL não está rodando. Tente iniciá-lo com:

sudo systemctl restart mysql

Depois disso, systemctl status mysql deve reportar como "ativo (em execução)". Tente fazer login novamente.