(This post is for sysadmins. If you use WooCommerce but aren’t a sysadmin, it won’t help you. I can’t help you either. Sorry.)
I use WooCommerce on my bookstore. It’s built on top of WordPress and, as such, attracts spam users. About 40,000 of them since I opened it. The last time I investigated performance problems, I installed Bulk WP Delete to get rid of them. From everything I saw, it would only remove the spam users and leave the customers unaffected.
I was wrong. A small percentage of customers lost their accounts.
I took a backup of the database before purging the spam users, because I’m not a complete idiot. And I kept it close at hand.
If you accidentally blow away user accounts you need, and want to restore them without touching the rest of your database, you can. I used the command line. You can probably do the same using whatever pointy-clicky tool you like, but don’t ask me for help–I don’t know how to use them. Talk to your sysadmin.
The first step is to take a backup of your database. Make sure it’s a good backup, and keep it close at hand. If you screw up, you’ll need it.
I created a VM on my desktop, installed MariaDB, and restored the pre-purge database on that VM. Check the name of your Woocommerce database. Mine is named rapacity, because that’s why I run my own bookstore.
When a user tells you that their account is missing, get the email address they used to create their account. My example user is joe@bucketsofmoney.com.
On your VM, extract the user’s account information to a file.
# mysqldump --user=root -p rapacity wp_users --where='user_email="joe@bucketsofmoney.com"' --skip-add-drop-table --no-create-info > bucketsofmoney.wp_users
Go into the file and look for the user description.
INSERT INTO `wp_users` VALUES (3185...
The first number is user ID. Grab that user’s metadata.
# mysqldump --user=root -p rapacity wp_usermeta --where='user_id=3185' --skip-add-drop-table --no-create-info > bucketsofmoney.wp_usermeta
Copy these two files to your web server. Log into the web server,
MariaDB [(none)]> use rapacity;
MariaDB [rapacity]> source bucketsofmoney.wp_users;
MariaDB [rapacity]> source bucketsofmoney.wp_usermeta;
Now log into the WooCommerce interface and go to Users. Search for that account. Switch the user’s role to Customer.
The user now exists, but their existing orders are disassociated from their account. Go to the Woocommerce page, then Reports, and hit the Customers report. Under the “All Customers” tab, search for the restored account. Over on the right side, one of the buttons will give the option to reconnect previous orders to the account. That button doesn’t appear unless there are previous orders. Hit it.
The account is now restored.
If you screw up and fry the existing database, restore from backup:
# mysql -u root -p --one-database rapacity < mysql-backup-2019-03-23
As you might guess, this blog is mostly for my own later reference.
And the next time I have to delete spam users, I plan to just go into the database and drop every user who has never ordered anything. Forget plugins.
How did I solve the performance issues? My new web site has more images per page, especially the fancy new front page. Installing “WP Super Cache” dramatically improved everything.
