Trying to restore users/passwords for a MySQL server

Question

I had a system failure, but I managed to save the /var/lib/mysql library on my linux system.

I have since rebuilt a new Linux (Ubuntu) system (LAMP). I copied the old /var/lib/mysql/mysql folder to the new system as /var/lib/mysql/old_mysql, and I can see the contents using phpmyadmin.

I also copied all the folders from the old /var/lib/mysql to the new /var/lib/mysql.

If I do the following:

Export the contents of the old_mysql/user table, and import it into the new mysql/user table

Export the contents of the old_mysql/db table, and import it into the new mysql/db table

Will that restore the users and their passwords for all the databases?

Answer 1

You don't need to export or import anything. As long as the versions of MySQL on both machines are identical you can simply

1. Shutdown MySQL process service mysql stop

2. Overwrite the installed /var/lib/mysql with the old /var/lib/mysql.

   A. cp -Rf /var/lib/old_mysql /var/lib/mysql

   B. As a precaution you could move the /var/lib/mysql on the new server mv /var/lib/mysql /var/lib/mysql_default

3. chown mysql:mysql -R /var/lib/mysql

4. Overwrite the installed my.cnf with the old /etc/mysql/my.cnf file

   A. As a precaution you can mv the my.cnf on the new installation mv /etc/mysql/my.cnf /etc/mysql/my.cnf_default

   B. Or even better, you could put a custom .cnf file in /etc/mysql/conf.d

5. Start MySQL process service mysql start

Your users and permissions will be restored automatically

You can verify version info with mysql -V

Good luck