XF 1.4 Partial backup restore

[alethiophile]

Have just attempted a reinstall of XF, in order to attempt fixing CPU usage issues (earlier install was the product of several install/overwrite cycles, so felt like trying to reproduce on a clean version). Process followed was: back up database, according to instructions here. Back up forum, simply by moving the install directory away from the httpd's watch path. Install XF according to the instructions provided in the docs, letting it delete the old database. After install, restore database from backup.

At the end of this process, I realized that the database backup had been truncated near the end. The backup contains the entire table 'xf_user' but cuts off near the beginning of the table 'xf_user_alert'. The backup mostly restores correctly, and I can verify the presence of all the appropriate tables and rows (before xf_user_alert) via phpMyAdmin. However, with this backup I cannot log in as an admin, which seems as if it would necessarily be the first step of any recovery.

In retrospect I should have run the install to a new database and let the old one remain. Failing this, however, is there a way of recovering from the partial backup? It seems pretty clear that all the relevant data is present, of posts, threads and users anyway.

 

[Jake Bunce]

Looking at tables after xf_user_alert (alphabetically), you will have lost user passwords and the usergroup definitions.

The recovery process will be as follows:

1) Recreate the missing tables. You can compare against a new XF installation to get those table definitions. Or consult with this file:

library/XenForo/Install/Data/MySql.php

2) Fill in any missing default records in those tables. Again, consult with a new XF installation.

3) Fill in any missing non-default records. Most notably, these user tables all need a record for each user_id in the xf_user table:

xf_user_profile
xf_user_option
xf_user_privacy
xf_user_authenticate

4) Recreate your usergroups which should start by inserting the default records into the xf_user_group table (from step 2). Then login to the Admin CP and manage the groups from there. Your ability to login will involve your user password which is the xf_user_authenticate table. All users will have lost their passwords. You can use this query to manually set a password for yourself:

https://xenforo.com/community/resources/password-reset-query.368/

When recreating your usergroups you need to try to use the same user_group_ids as before since your xf_user records still refer to those ids. Without a backup you will need to work from memory, or you may be able to infer which ids belonged to which groups by looking at specific users in xf_user with which you are familiar.

5) Rebuild your user info:

Admin CP -> Tools -> Rebuild Caches -> Rebuild User Caches

 

[Xon]

@alethiophile make sure you have a working backup before making any changes.

Additionally:

• Check how much free diskspace you have.
• Check you backup SQL file and check what the last data is or if it is truncated. It might be the restore didn't work properly, rather than the backup fail.
• Check that you still have your old pre-import database.

Seriously consider paying someone like @MattW or similar todo these services if you are unfamiliar with this. As otherwise you risk serious data loss.

 

[alethiophile]

All check out positive. The restore file is indeed what's truncated, and all the data are still in the old DB. I'm vaguely hoping I can get users' passwords back by running another import (into a temporary DB) and looking at the auth table there.

 

[alethiophile]

Okay, by re-running the import I've gotten a test database with a correct set of users. I've verified that user IDs are the same for each user in the test DB and the real one. Thus, the password data are available.

Questions:

1. Is there anything wrong with just transplanting this table, and the remaining missing, into the real DB, since user IDs are the same?
2. For the ~20 users added since the switch, what value should their entries in the 'profile', 'option', 'privacy' and 'authenticate' tables get?

 

[Jake Bunce]

Okay, by re-running the import I've gotten a test database with a correct set of users. I've verified that user IDs are the same for each user in the test DB and the real one. Thus, the password data are available.

Questions:

1. Is there anything wrong with just transplanting this table, and the remaining missing, into the real DB, since user IDs are the same?
2. For the ~20 users added since the switch, what value should their entries in the 'profile', 'option', 'privacy' and 'authenticate' tables get?

So you have a complete backup? I recommend using that backup in its entirety. Then post a notice on your forum explaining that there was some recent data loss.

Trying to merge together two backups is possible but requires great care and MySQL skill. Just take the hit.

 

[alethiophile]

So you have a complete backup? I recommend using that backup in its entirety. Then post a notice on your forum explaining that there was some recent data loss.

Trying to merge together two backups is possible but requires great care and MySQL skill. Just take the hit.

Failing back to the backup (some 4-5 days old) was an option, but not a great one. In the end I successfully got the full thing up, with only loss of user data that originated after the switch. This just worked by taking a full mysqldump from the imported (test) board, cutting off everything before xf_user_alert this time, and running the restore process on that. (Then dumping default values in for users created since, and sending password-reset emails to them and rebuilding caches and such.)

As of this moment, things seem to be up and working. Many thanks to everyone for all the help.