MariaDB MariaDB Regenerate Password

eva2000 · Dec 19, 2016

tdubs said: ↑

am I killing the processes before I do the final step of starting the MySQL server or what
Click to expand...

yup otherwise mysql can't start as the process already exists

tdubs · Dec 19, 2016

So I was able to get that portion sorted but now back to my previous post.

Error upon mysql -u root -p login and then entering the new password:
Code (Text):
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format
I've done the following mysql - How to fix java.sql.SQLException: Server is running in --secure-auth mode, but 'user'@'host' has a password in the old format; (...)? - Stack Overflow

eva2000 · Dec 19, 2016

you don't need to run command like that as update via centmin.sh change root pass updates /root/.my.cnf which allows you just to type mysql to run mysql client instead of needing to do mysql -u root -p login

and well that's incorrect too anyway

it's

mysql -u root -ppassword

if you need to without space between -p and password

eva2000 · Dec 19, 2016

retry centmin.sh menu option 11 submenu option 0 to reset mysql root this time and just then afterwards type the command = mysql

to run mysql client mode interactively

tdubs · Dec 19, 2016

Same thing upon running just the command 'mysql' as suggested.

eva2000 · Dec 19, 2016

tdubs said: ↑
Code (Text):
mysqlstop

sudo mysqld_safe --skip-grant-tables &

mysql -uroot

use mysql;

update user set password=PASSWORD("mynewpassword") where User='root';

 flush privileges;

 quit

mysqlstart
bit of a difference i see from MySQL :: MySQL 5.6 Reference Manual :: B.5.3.2 How to Reset the Root Password instructions

B.5.3.2.3 Resetting the Root Password: Generic Instructions
The preceding sections provide password-resetting instructions specifically for Windows and Unix and Unix-like systems. Alternatively, on any platform, you can reset the password using the mysql client (but this approach is less secure):

Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as SET PASSWORD. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:

shell> mysql

In the mysql client, tell the server to reload the grant tables so that account-management statements work:

mysql> FLUSH PRIVILEGES;

Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a root account with a different host name part, modify the instructions to use that host name.

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

You should now be able to connect to the MySQL server as root using the new password. Stop the server and restart it normally (without the --skip-grant-tables and --skip-networking options).
Click to expand...
Click to expand...
looks like you incorrect commands/steps

tdubs · Dec 19, 2016

Back to previous post even using mysql , mysql -uroot -ppassword , old_passwords=1
Code (Text):
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format
---

Fixed the above error. Simply added secure_auth=FALSE to my.cnf and restarted MySQL.

Thank you @eva2000

eva2000 · Dec 19, 2016

tdubs said: ↑

Fixed the above error. Simply added secure_auth=FALSE to my.cnf and restarted MySQL.

Thank you @eva2000
Click to expand...

not the ideal fix MySQL :: MySQL 5.7 Reference Manual :: 6.1.4 Server Command Options

Note
Passwords that use the pre-4.1 hashing method are less secure than passwords that use the native password hashing method and should be avoided. Pre-4.1 passwords are deprecated and support for them is removed in MySQL 5.7.5.
Click to expand...

you should follow the proper steps outlined in this post here rather than allow pre-4.1 password hashes to exist as support for them eventually will be removed in MariaDB and already removed in MySQL 5.7.5+ so I suspect MariaDB 10.2 might remove them too

tdubs · Dec 19, 2016

Okay, these are the exact steps I've taken based on your post and I'm at a stop here:

Code (Text):

[root@nexus ~]# systemctl stop mariadb

[root@nexus ~]# vi /etc/my.cnf

[mysqld]
skip-grant-tables

[root@nexus ~]# systemctl restart mariadb

[root@nexus ~]# mysql

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('XXXXXX');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings
MariaDB [(none)]>

eva2000 · Dec 19, 2016

usually use weird looks like a bug in mariadb 10.1 like MySQL Bugs: #79027: resetting password using skip-grant-tables bug

use this instead to change root pass

Code (Text):

update mysql.user set authentication_string=password('MyNewPass') where user='root';

Code (Text):

mysqlstop

mysqld_safe --skip-grant-tables --skip-networking &

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

MariaDB [(none)]> update mysql.user set authentication_string=password('MyNewPass') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [(none)]> EXIT
Bye

Code (Text):

ps aufx | grep mysql
root      2783  0.0  0.0 113264  1680 pts/0    S    06:13   0:00          \_ /bin/sh /bin/mysqld_safe --skip-grant-tables --skip-networking
mysql     3141  0.1  5.3 515644 100772 pts/0   Sl   06:13   0:00          |   \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=centos7.localdomain.pid --socket=/var/lib/mysql/mysql.sock
root      3204  0.0  0.0 112704   964 pts/0    S+   06:20   0:00          \_ grep --color=auto mysql

kill -9 2783 3141

mysqlstart
Starting mysql (via systemctl):                            [  OK  ]
[1]+  Killed                  mysqld_safe --skip-grant-tables --skip-networking

seems that didn't work as need to specify host = localhost too

Code (Text):

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> update mysql.user set authentication_string=password('MyNewPass') where user='root' AND Host = 'localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye

but on restart doesn't work with access denied

tdubs · Dec 19, 2016

Back to..
Code (Text):
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format
This happened when I killed the mysqld_safe and mysqld process then ran mysqlstart.

Think I may just reinstall Centmin Mod to avoid this headache.

eva2000 · Dec 19, 2016

probably not needed.. i'll get to bottom of this but about to head off to bed heh

it's strange using centmin.sh menu option 11 submenu option 0 works for me though it relies on working existing /root/.my.cnf set root password first

Code (Text):

--------------------------------------------------------
     MariaDB MySQL Upgrade & Management Sub-Menu       
--------------------------------------------------------
0). Regenerate New MySQL Root User Password (local server)
1). MariaDB 5.2.x Branch Upgrade Only
2). MariaDB 5.2.x to MariaDB 5.5 YUM upgrade
3). MariaDB 5.5 to MariaDB 10.x YUM upgrade
4). MariaDB 5.5 to MariaDB 10.1.x YUM upgrade
5). MariaDB 10.x to MariaDB 10.1.x YUM upgrade
6). MariaDB 10.1.x to MariaDB 10.2.x YUM upgrade
7). Back to Main menu
--------------------------------------------------------
Enter option [ 0 - 7 ] 0
--------------------------------------------------------

This routine will reset the MySQL root user password
and properly update the /root/.my.cnf file as well.

This routine is only for local MariaDB MySQL servers not remote ones

Do you want to continue? [y/n]: y

Process begins in 10 seconds...

Previous MySQL root password:

[client]
user=root
password=7y4EMTXcbq7RuGIb

mysqladmin -u root -p7y4EMTXcbq7RuGIb password In3iBuQWt2RaK18c

New MySQL root user password: In3iBuQWt2RaK18c

/root/.my.cnf updated

[client]
user=root
password=In3iBuQWt2RaK18c

Code (Text):

mysqladmin ver
mysqladmin  Ver 9.1 Distrib 10.1.19-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Server version          10.1.19-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 2 min 12 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.037

eva2000 · Dec 19, 2016

could be related but will read when i wake up [MDEV-9835] Valid password is not working after server restart. - JIRA

After mysqld upgrade (by cpanel, mysqlcheck was done many times), user that had `old_password` password, when password is set to [new] password via `SET PASSWORD FOR ... = '*....'` and when server is restarted, user can not anymore login to database. `SHOW GRANTS ...` and `SELECT .. FROM mysql.user` shows that password hash is correct. When password is changed again with `SET PASSWORD` to the correct and same as before hash value, user regains access to the database. But when database is restarted again, user lost access with 'Access denied' again.

Workaround is, when user password is changed with `GRANT USAGE ON ... IDENTIFIED BY PASSWORD '*....'`, after that, when mysqld is restarted user not losing access.
Click to expand...

eva2000 · Dec 20, 2016

How To Reset MySQL Root Password For MariaDB 10.1.19+

Centmin Mod 123.09beta01's centmin.sh menu option 11 submenu option 0 allows resetting MySQL root user password when the known existing MySQL root password is contained in /root/.my.cnf. But when you do not have a valid working MySQL root password in /root/.my.cnf and/or you don't know the existing MySQL root password, you will need to reset it via official documented methods outlined here. However, for MariaDB 10.1.19+ seems the official method isn't working !

So I figured out how to reset mysql root user password for MariaDB 10.1.19+ server. It seems not all the official mysql documentations have the correct method or it could be a MariaDB specific bug similar to these 2 issues MDEV-7656 and MDEV-9835 and/or specific to CentOS 7 and systemd method/protections.

The method used to reset mysql root user password in MariaDB 10.1.19+ that works is as follows on CentOS 7:

Stop MariaDB MySQL server

Code (Text):

systemctl stop mariadb

Start manually with skip grants tables and skip networking in background

Code (Text):

mysqld_safe --skip-grant-tables --skip-networking &

log into mysql client as root user and you will see the official documentated methods resulting in ERROR 1290 and ERROR 1131 and checking with select to see existing user = root passwords hashed

Code (Text):

mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | centos7.localdomain | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | 127.0.0.1           | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
| root | ::1                 | *A59082D43302731DFF45380446E4650F5B165CDE |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

Only working method is these additional steps still within same mysql client interactive session and you see select where user = root shows new hashed password listed

Code (Text):

MariaDB [(none)]> update mysql.user set password=password('MyNewPass') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | centos7.localdomain | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | 127.0.0.1           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | ::1                 | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

but you still get ERROR 1131. Why ? because it seems you need to quit the existing mysql client session and start a new mysql client session but using the new mysql root password and then use the official mysql documented commands via SET PASSWORD FOR and then flush privileges

Code (Text):

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');                                
ERROR 1131 (42000): You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings
MariaDB [(none)]> quit
Bye

mysql -u root -pMyNewPass
Your MariaDB connection id is 3
Server version: 10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select user, host, password, plugin, authentication_string from mysql.user where user='root';
+------+---------------------+-------------------------------------------+--------+-----------------------+
| user | host                | password                                  | plugin | authentication_string |
+------+---------------------+-------------------------------------------+--------+-----------------------+
| root | localhost           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | centos7.localdomain | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | 127.0.0.1           | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
| root | ::1                 | *A294441C38B03BE12E32771ADDF7976B0DDB8164 |        |                       |
+------+---------------------+-------------------------------------------+--------+-----------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> quit
Bye

Then after you quit, manually kill the manually started mysql server process ids for mysqld_safe and mysqld which in this case from ps aufx command are process ids = 2747 and 3105

Code (Text):

ps aufx | grep mysql | grep -v grep
root      2747  0.0  0.0 113264  1680 pts/0    S    17:48   0:00          \_ /bin/sh /bin/mysqld_safe --skip-grant-tables --skip-networking
mysql     3105  0.3  5.0 515644 95948 pts/0    Sl   17:48   0:00          |   \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --skip-networking --log-error=/var/log/mysqld.log --open-files-limit=8192 --pid-file=centos7.localdomain.pid --socket=/var/lib/mysql/mysql.sock

kill -9 2747 3105

then start MariaDB MySQL server

Code (Text):

systemctl start mariadb

Then edit /root/.my.cnf with new mysql root password

Code (Text):

cat /root/.my.cnf
[client]
user=root
password=MyNewPass

Then check it all works... done !

Code (Text):

mysqladmin ver
mysqladmin  Ver 9.1 Distrib 10.1.19-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Server version          10.1.19-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 32 sec

Threads: 1  Questions: 1  Slow queries: 0  Opens: 18  Flush tables: 1  Open tables: 11  Queries per second avg: 0.031

pamamolf · Dec 20, 2016

I hope that they will fix that and make it easy as before

Log in / Register

Forums

Want to subscribe to topics you're interested in?

MariaDB MariaDB Regenerate Password

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

How To Reset MySQL Root Password For MariaDB 10.1.19+

pamamolf Well-Known Member

.

Log in / Register

Useful Searches

Want to subscribe to topics you're interested in?

MariaDB MariaDB Regenerate Password

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

tdubs Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

How To Reset MySQL Root Password For MariaDB 10.1.19+

pamamolf Well-Known Member

.