Wordpress, the engine that is behind the post you are currently reading, is easy to upgrade with most hosting environments. However, upgrades can get tricky in locked-down enterprise environments.
For the most secure environment, the MYSQL database user that is used by Wordpress (hardcoded in your wp-config.php file) to retrieve and set information should not have Drop, Alter, and Create database privileges. If it did, malicious code would have an easier time disrupting the database. This is a problem because upgrading Wordpress usually involves some major additions or changes to the database structure, for which these privileges are needed.
So what is the solution? Basically it is running the upgrade as the root user. But, hardcoding root credentials in your wp-config.php file for a little bit is not good security practice, even if you change it back afterward. What you need to do is run an SQL script with all the database statements that are required for upgrade directly on the MYSQL server, or by using the MYSQL command line client.
Capturing the upgrade sequence is a little more difficult. Most enterprise installations have a development environment with looser security, possibly only accessible from inside the firewall of your organization. If your development environment is setup with a database user that does have alter, drop, and create credentials you’re in good shape.
Open up the /wp-includes/wp-db.php file and find the query() function. This is the function that Wordpress calls before each and every database call. On the first line of the function, add this line:
error_log($query);
This will output each query into your PHP error log file. Empty this file before attempting an upgrade on your development environment. Wordpress upgrades involve replacing all core files, and then logging in to the admin interface and upgrading the database by clicking a prominent upgrade button. After you have done this, the error log file will contain all the queries run by the upgrade.
There are more queries in here than are necessary to upgrade Wordpress. Delete all select queries, leaving a combination of alter, drop, create, or insert statements. Rename the error log file upgrade.sql and you’ve got the entire database upgrade in a ready to run script.
Publish the changed files up to your live environment. Your site will stay up even if a database change is required in the back end – this is a feature of Wordpress allowing for easier upgrades without site downtime. Next, run your upgrade.sql script with root privileges, or send it to your DBA team to run. You’re all upgraded, and you kept your site secure the entire time.
This same process can be used for upgrades to Wordpress Plugins, which often also require Alter, Create, and Drop statements to their tables.
