Fixing WordPress Functionality Issues Caused by Database Permission Errors

Problem Background

After a server security incident, I logged into the affected VPS via SSH and immediately created compressed backups of the MySQL data files (not via phpMyAdmin export) and the website files. I then reconfigured the LNMP environment, uploaded and extracted the website files, and directly uploaded the backed-up MySQL data files. After completing these steps, I restarted the server.

Problem Symptoms

Upon accessing the website, the following WordPress abnormalities were observed:

• Although the original administrator account could log into the dashboard, administrator privileges appeared to be lost.
• When writing a new post, the 'Publish' button on the right changed to 'Submit for Review'.
• The comment functionality was not working correctly.
• Plugin management features were malfunctioning.

Root Cause

The root cause was identified as incorrect ownership and group permissions on the MySQL data files. After uploading the original site's MySQL data files (typically located in a directory like /usr/local/mysql/var/) to the new server via SFTP, these files defaulted to belonging to the root user and root group. The MySQL service process (mysqld) usually runs as the mysql user, which then could not read or write these database files properly, leading to a cascade of permission errors.

Fix Steps

To resolve this, the ownership of the database directory must be corrected to the user under which the MySQL service runs (typically mysql). Follow these steps:

Step 1: Correct Database File Permissions

Log into your server via SSH and execute the following command. Replace your_database_name with your WordPress database's actual name (usually found in the DB_NAME definition within the wp-config.php file).

chown -R mysql:mysql /usr/local/mysql/var/your_database_name

Command Explanation:

• chown -R: Recursively change file ownership.
• mysql:mysql: Change the owner to the mysql user and the group to the mysql group.
• /usr/local/mysql/var/your_database_name: The actual path to your WordPress database files. Adjust the path if your MySQL data directory is different (e.g., /var/lib/mysql/).

Step 2: Restart Web Services

After changing permissions, restart the LNMP service stack (or at least the MySQL service) for the changes to take effect. If you use the LNMP one-click installation package, you can run:

/root/lnmp restart

Alternatively, a more generic method is to restart services individually:

systemctl restart mysql  # or service mysql restart
systemctl restart nginx   # or service nginx restart
systemctl restart php-fpm # or service php-fpm restart (if used)

Verify the Result

After completing the steps above, log back into the WordPress admin dashboard. Administrator privileges should be restored, the 'Publish' button should function normally, and comment and plugin management features should work as expected.

Summary and Recommendations

• Backup Best Practice: When migrating or restoring a database, it is strongly recommended to use the mysqldump command to export an SQL file instead of directly copying raw data files. This avoids issues with file permissions and storage engine compatibility. Example command: mysqldump -u username -p database_name > backup.sql.
• Security Hardening: After a server intrusion, thoroughly check all files for malicious code and change all related passwords (database, WordPress admin, SSH, etc.).
• Permission Principle: Web directory files should be owned by the web server user (e.g., www or nginx), while database files should be owned by the database service user (e.g., mysql), adhering to the principle of least privilege.