After months of testing out Hestia, I am ready to bring over our production Vesta servers to Hestia. Unfortunately, I ran a test today, and we were not successful with the migration
I made backups of the user accounts in VestaCP and rsycned them over to the Hestia server. I placed them in /backup and ran the v-restore-user [username] [backup file].
The restore showed everything was successful, and so does the control panel. But when I load up the websites, they get the “Error establishing a database connection” error (these are WordPress sites specifically but there are other frameworks such as Drupal and Joomla as well).
I did some testing, and it does appear that the databases are there, but the passwords must not have come over, or maybe they are reset to something new?
If I reset the database passwords manually in MySQL to match the original database user password from the Vesta server the sites do work.
So I guess all of that being said, how do I get the databases to come over with the proper passwords when restoring the backups? I am moving about 40ish user accounts, and many have multiple websites so it would be a very long drawn-out task to go into each one and find the password to change it manually.
Any help would be greatly appreciated.
For anyone willing to help me troubleshoot this, I have more information to add:
I found that one of the issues seems to be that the DB users on the Vesta server are using “mysq_native_password” and Hestia is running the newer “caching_sha2_password”
For some reason, the import creates the DB users, but they are not able to log in. That being said I have tried using MySQL alter statements to update the user accounts to the new “caching_sha2_password” plugin but it fails like so:
ALTER USER '[username]'@'localhost' IDENTIFIED WITH caching_sha2_password BY '[password]';
ERROR 1396 (HY000): Operation ALTER USER failed for '[username]'@'localhost'
I have tried switching the MySQL server over to use the “mysql_native_password” plugin and also tried a similar SQL query to alter the user to use that plugin. So far, none have been successful.
The only way I have been able to be successful is to drop the database user entirely and then re-add them.
I can write a script to do this, but the question becomes what permissions do I need to assign to these users so that they do not have access to databases they shouldn’t have? I don’t want to re-create all the database users just to find out that clients now have access to each other’s databases.
You can get the users current permissions via
SHOW GRANTS and recreate then with the same permissions.
Thank you! I should be able to write a script to pull this information out of Vesta and recreate it in Hestia. Any idea why it isn’t working to begin with?
I have no idea. I’ve not done any backup and restore or migration testing in HestiaCP and I have never touched Vesta.
I wonder if VestaCP has mysql8 support I am sure they use MariaDB by default…
That should work fine.
If you really require Mysql8 drop the user and recreate it …