In WordPress CMS, your details are saved to a table named “
users“, and website admins can manage the user’s account and roles with the help of the WP dashboard. This table has the following fields:
If the site has a membership feature, it is a good practice to include this table in the entire site’s backup. What if you ignore this table or exclude it from the backup? If the site is hacked, the hacker may create and store several thousand fake accounts in the DB. Finding genuine records in this situation would be like finding a needle in a haystack. From this example, it is clear that the
users table is important, and we must export or back it up daily.
How to import or export WordPress account data? There are three ways to export or import WordPress account data. We’ve covered all of the methods below:
Exporting/importing accounts with MySQL command:
The quickest way to export or import account data is to execute the following MySQL command after logging in to PHPMyAdmin or the server via SSH with the help of a command prompt or terminal:
mysqldump -u -p dbName TableName > backup_name.sql
In the above command,
dbName is the name of the WordPress database.
TableName is the name of the table you want the command to export. The
backup_name.sql is the file that you want to backup. The command will create a backup of the table “
tableName“. If you see this file’s contents, you’ll find several insert SQL commands and a command to create the user table if the table doesn’t exist. To restore the backup i.e. import users from the backup file, run the following MySQL command:
mysql -u root -p dbName < backup_name.sql
Using import and export users and customers plugin:
As the name suggests, this plugin is a tool for managing the WordPress user database table data. If you use the above method, the command will replace the entire table. This extension can ignore the passwords column if you change its “never update password” setting. It can import accounts from CSV files and can send emails to each account whose information is being replaced/updated.
If your site was hacked and 1000s of fake records were created by the hacker, you can get rid of these records with this plugin as it can delete the accounts that aren’t present in the backup. The plugin can also change the roles of accounts that aren’t in the backed-up table.
The export option of the extension lets you back up WordPress accounts created within two particular dates of their choice. It can export only the columns of the selected WordPress database tables. The WordPress CMS stores the date on which you registered an account in the
user_registered column. The data type used for this column is the timestamp. You can make the plugin change this format while exporting the data. This extension can import the data at regular intervals daily, every 5 minutes, twice/once daily, or once weekly. After updating a record, it can update the account roles.
Using Export user data plugin:
When you activate this extension, you’ll find a new option in the “users” menu of the WP dashboard – “export user data”. When you click this menu, WP will open EUD‘s settings page, where you’ll see a list of meta fields, a drop-down list to choose the format of the exported file, and a text field to enter the file name. Next to the meta fields, you’ll find a large text area. Any meta field you choose from the list will appear in this text area. Click the “Run Export” option once you fill out the form or select its options.
Using backup plugins:
Some WordPress backup extensions can export/dump only the database tables of your choice. You should use such a plugin to import or export user table data. Here are the two great WP extensions that let you create backups of individual WP tables:
The popular backup extension BackWpUp lists all your site’s database tables in the DB Backup tab on its settings page. You’ll find a checkbox to select/deselect the table on the left side of the table name. By default, the plugin selects all the tables required to keep the WP CMS/site functioning i.e. It will back up all important tables on your site. Select the “
users” table if you want BackWPUp to export only the “
users” table. This extension can schedule backup. It can generate a special link for manually creating a backup of the chosen tables. When you open this link in the browser, it will immediately create a backup of the tables they’ve chosen on its Settings page.
Backup Guard is similar to BackWPUp but has a better interface. To back up the WordPress
users table with this plugin, click the blue-colored backup button and select the “custom backup” checkbox that appears on the settings page of the extension. Now, select the “custom” radio button and choose the table “users” from the list of tables the plugin displays. Once your backup is ready, download it. BG enables you to schedule DB backups. It can also move the backup from the server to your Dropbox account. Next to the option of deleting the backup, you’ll find the option to restore the backup.
Closing words: These are the three techniques you can employ to import or export the WordPress user table. Using an extension is easier than executing MySQL commands. Thus, if you’re not comfortable playing with the MySQL database server tables or SQL queries, use the plugin we’ve shared above.