0

I'm in the process of moving everything from a very old production server to a new one. I'm now trying to copy the MySQL database over to the new system. I have very limited knowledge of MySQL, BTW. First some version info:

Old server:

$ mysql -V
mysql  Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

New server:

$ mysql -V
mysql  Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

On the old server, I made a dump of all databases first:

mysqldump -u root -p --all-databases > all_databases.sql

Then on the new server, I imported the databases with:

mysql -u root -p < all_databases.sql

But now, when I try to run mysql, I get an error:

$ mysql
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

So apparently it has overwritten the user database as well (I really had no idea that would happen, but, you know, limited MySQL knowledge...).

So how can I fix this?

2
  • Temporarily disable secure-auth mode, import data, alter the password for 'root'@'localhost' using default securing method, then enable secure-auth mode back.
    – Akina
    Commented Mar 14, 2020 at 18:11
  • Ouch! That's 8 major releases. Good luck. (But it is good to move forward.)
    – Rick James
    Commented Mar 27, 2020 at 21:59

1 Answer 1

1

Writing out @Akina's suggestion:

  1. Edit your MariaDB server options file, which I think is it at /etc/mysql/my.cnf on Debian. In the [mysqld] section, add this line: secure_auth=OFF
  2. Restart MariaDB server: systemctl restart mariadb (assuming you have systemd)
  3. You should now be able to log in using the mysql client and run something like: SET PASSWORD = password('your_new_password'); Alternatively, run mysql_secure_installation from the command-line.
  4. Edit the MariaDB server options file again and remove the line you inserted before, then restart the MariaDB server, and everything should be working, hopefully.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.