Useful SQL statements for everyday WordPress administration

Sometimes you just don’t have access to a fancy GUI like phpMyAdmin but you still need to make changes to the WordPress database. This article is about those times.

One page at the time

First of, a wordpress database can grow quite large and the command line mysql utility does not handle this by default. You can tell mysql which command you would like to use as a pager, for me the choice is more or less:

MariaDB [database]> pager less -SFX

The manual page for less has this to say:

-S or –chop-long-lines

Causes lines longer than the screen width to be chopped (truncated) rather than wrapped. That is, the portion of a long line that does not fit in the screen width is not shown. The default is to wrap long lines; that is, display the remainder on the next line.

-F or –quit-if-one-screen

Causes less to automatically exit if the entire file can be displayed on the first screen.

-X or –no-init

Disables sending the termcap initialization and deinitialization strings to the terminal. This is sometimes desirable if the deinitialization string does something unnecessary, like clearing the screen.

The URL of a site

MariaDB [database]> SELECT * FROM wp_options WHERE option_name = 'home' OR option_name = 'siteurl';
MariaDB [database]> UPDATE wp_options  SET option_value = replace(option_value, 'http://oldurl.se', 'https://newurl.se') WHERE option_name = 'home' OR option_name = 'siteurl';

Admin email

MariaDB [database]> UPDATE wp_options SET option_value = replace(option_value, 'old@email.se', 'j@asbra.nu') WHERE option_name = 'admin_email';

User password

MariaDB [database]> update wp_users SET user_pass = MD5(‘secretpassword’) WHERE user_login='username’ LIMIT 1;

And the posts

MariaDB [database]> UPDATE wp_posts    SET guid = replace(guid, 'http://oldurl.se','https://www.newurl.se');
MariaDB [database]> UPDATE wp_posts    SET post_content = replace(post_content, 'http://oldurl.se', 'https://www.newurl.se');
MariaDB [database]> UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://oldurl.se','https://www.newurl.se');

User meta, links & comments

MariaDB [database]> UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://oldurl.se','https://www.newurl.se');
MariaDB [database]> UPDATE wp_links SET link_url = replace(link_url, 'http://oldurl.se','https://www.newurl.se');
MariaDB [database]> UPDATE wp_comments SET comment_content = replace(comment_content , 'http://oldurl.se','https://www.newurl.se');

Linked images

MariaDB [database]> UPDATE wp_posts SET post_content = replace(post_content, 'http://oldurl.se', 'https://www.newurl.se');
MariaDB [database]> UPDATE wp_links SET link_image = replace(link_image, 'http://oldurl.se','https://www.newurl.se');
MariaDB [database]> UPDATE wp_posts SET guid = replace(guid, 'http://oldurl.se','https://www.newurl.se');

Add new admin user

MariaDB [database]> INSERT INTO wp_users (user_login, user_pass, user_nicename, user_email, user_status) VALUES ('username', MD5('password'), 'my-nice-name', 'info@asbra.nu', '0');
MariaDB [database]> INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, (Select max(id) FROM wp_users), 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
MariaDB [database]> INSERT INTO wp_usermeta (umeta_id, user_id, meta_key, meta_value) VALUES (NULL, (Select max(id) FROM wp_users), 'wp_user_level', '10');

Create database & user for wordpress

Change “localhost” to whatever IP your web server has.

MariaDB [(none)]> CREATE DATABASE site.se;

MariaDB [(none)]> GRANT ALL PRIVILEGES ON site.se.* TO "site.se"@"localhost" IDENTIFIED BY "password";

MariaDB [(none)]> GRANT ALL PRIVILEGES ON site.se.* TO "site.se"@"localhost" IDENTIFIED BY "password";