CODEWITHSUNDEEP
mysqlubuntu
Luuk Wuijster
Luuk Wuijster

Reputation: 6878

Mysql password expired and my mysql.user table is corrupted

Okay, I have a little problem.

My password is expired and my users table is corrupted. I can login via

mysql -u root -p

but on every action I perform I get the folowing error:

Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted.

I have read that you can fix the mysql.user table with the folowing command:

mysql_upgrade -u root -p

But when I do that I get the folowing error:

mysql_upgrade: Got error: 1862: Your password has expired. To log in you must change it using a client that supports expired passwords. while connecting to the MySQL server Upgrade process encountered error and will not continue. So, How do I fix this?

I have backups of all my tables so I won't be a problem if I have to reset all my databases.

(why the weird format? Stackoverflow thinks it's all code and wants me to put it in code blocks, otherwise I can not save it)

EDIT:

I know my password. That's not the problem at all.

My problem is that the password is expired and I am not able to do anything becuase my mysql.user is corrupted!

Upvotes: 4

Views: 3113

Answers (2)

the_nuts
the_nuts

Reputation: 6054

Had the same issue when restoring an old backup from 2018, reinstalling MySQL as you said in a comment didn't solve the issue.

How I did:

  1. Stop MySQL service

  2. Run mysqld_safe --skip-grant-tables --skip-networking &

    (if you get an error you may need to manually create and chown the directory /run/mysqld)

    --skip-grant-tables will allow passwordless logins and will also disable any check on the password expiration

  3. Now run mysql_upgrade --force and mysqlcheck --repair --all-databases

  4. You can now kill the running mysqld_safe (ps aux | grep mysql to find the PID to kill) and then start the server normally with service mysql start.

    In my case it didn't work and I still had the "Expected 45, found 46" error. In that case go ahead:

  5. Stop the server again and restart it in safe mode as point 2 above

  6. Now you should be able to dump the content, but we must exclude the mysql schema from being dumped.

    Since mysqldump doesn't have a --exclude-database option, we need to get the list of databases to dump. To get the list of existing databases, except system schemas, run:

    mysql -Nse "SELECT GROUP_CONCAT(SCHEMA_NAME SEPARATOR ' ') FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql','information_schema','performance_schema','sys');"

  7. Remove from the list any other db you don't need, and run the dump:

    mysqldump --databases db1 db2 ... db50 > mysqldump.sql

  8. Kill mysqld, move the datadir away and create an empty one (mv /var/lib/mysql /var/lib/mysql-old && mkdir /var/lib/mysql && chown mysql:mysql /var/lib/mysql)

  9. service mysql start and a fresh datadir will be populated.

  10. Run mysql_secure_installation to set a new root password

  11. Import the dump file: cat mysqldump.sql | mysql -u root -p

After that, the server is UP and running without issues.

Upvotes: 1

Mohamed Abd El Hakim
Mohamed Abd El Hakim

Reputation: 78

Try to disable the password expiration option: edit the my.cnf and put 

[mysqld]
default_password_lifetime=0

and try to restart mysql server and try again login again. the source is here https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html

For repairing the database you run mysqlcheck --repair --databases db_name or mysqlcheck --repair --all-databases for repairing all databases The source is here https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html You could first try to repair the database then you could try to disable password lifetime.

Upvotes: 2

Related Questions