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.… Continue reading 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";

Leave a comment

Your email address will not be published. Required fields are marked *