How to import users from another archive and keep their passwords

Problem

You have a number of users that you want to transfer to another archive. However, no password information is contained in the XML file with the exported users. Therefore users are not able to login to the new archive.

Solution

First import the users' data from the XML export file. Then (re)set their passwords. The password hashes are taken directly from the MySQL database.

The Details

XML exports of users data do not include the password hashes. I guess this is for security reasons. Therefore, all passwords of the imported users are set to NULL. (I personally think it would be nice to have the pasword hashes included in the export file. At least when requested by a command line option.)

However, the following workaround went well for me:

First export user data as XML:

bin/export old_archive user XML > somefile.xml

Then retrieve the passwords in a handy format:

mysql -u db_user -p old_archive_db --skip-column-names -e 'select concat("update user set password=\"", password, "\"  where username=\"", username, "\";") from user;' > passwords.sql

Import the users into the new archive:

bin/import new_archive user XML somefile.xml

Finally (re)set the passwords from the SQL file:

mysql -u new_archive_user -p new_archive < passwords.sql