Here’s some WordPress SQL queries and bash stuff that have helped me out in the past!

If you have any questions or suggestions/improvements let me know

Reset Admin Password

UPDATE wp_users SET user_pass=MD5('newpassword123') WHERE ID = 1;

Check WordPress Versiongrep ^\$wp\_version wp-includes/version.php

Create New Admin account

INSERT INTO `wp_users` (`user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_status`, `display_name`) VALUES ('username', MD5('password'), 'friendly-name', 'someone@example.com', 'http://example.com', '0', 'Your Name');
SELECT LAST_INSERT_ID() INTO @userid;INSERT INTO `wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, @userid, 'wp_capabilities', 'a:1:{s:13:"administrator";s:1:"1";}'), (NULL, @userid, 'wp_user_level', '10');

Change the site URL

SELECT * FROM wp_options WHERE option_name = 'siteurl' OR option_name = 'home' ;
UPDATE wp_options SET option_value = 'http://staging.walacea.com' WHERE option_name = 'siteurl' OR option_name = 'home' ;

Disbale all plugins

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Show users and Privilidges

mysql> SELECT user_login,user_registered,meta_value FROM wp_users INNER JOIN wp_usermeta ON wp_users.id = wp_usermeta.user_id and meta_key = 'wp_capabilities';

+---------------+---------------------+---------------------------------+
| user_login    | user_registered     | meta_value                      |
+---------------+---------------------+---------------------------------+
| administrator | 2013-12-21 10:36:30 | a:1:{s:13:"administrator";b:1;} |
| author        | 2014-11-25 15:50:34 | a:1:{s:6:"author";b:1;}         |
| editor        | 2014-11-25 15:51:18 | a:1:{s:6:"editor";b:1;}         |
| contributor   | 2014-11-25 15:51:48 | a:1:{s:11:"contributor";b:1;}   |
| subscriber    | 2014-11-25 15:52:11 | a:1:{s:10:"subscriber";b:1;}    |
+---------------+---------------------+---------------------------------+
5 rows in set (0.01 sec)

Update theme to Twenty Fourteen

UPDATE wp_options SET option_value = 'twentyfourteen' WHERE option_name = 'template' OR option_name = 'stylesheet';
UPDATE wp_options SET option_value = 'Twenty Fourteen' WHERE option_name = 'current_theme';

Set/Fix WordPress file permissions

One of the most irritating things about WordPress is getting the permissions right! Here is a nice script from Mike at conigliaro.org

Thanks Mike!

#!/bin/bash
#
# This script configures WordPress file permissions based on recommendations
# from http://codex.wordpress.org/Hardening_WordPress#File_permissions
#
# Author: Michael Conigliaro
#
WP_OWNER= # <-- wordpress FTP owner
WP_GROUP= # <-- wordpress FTP group
WP_ROOT=  # <-- wordpress DocumentRoot
WS_GROUP= # <-- Webserver group apache/www-data etc

# reset to safe defaults
find ${WP_ROOT} -exec chown ${WP_OWNER}:${WP_GROUP} {} \;
find ${WP_ROOT} -type d -exec chmod 755 {} \;
find ${WP_ROOT} -type f -exec chmod 644 {} \;

# allow wordpress to manage wp-config.php (but prevent world access)
chgrp ${WS_GROUP} ${WP_ROOT}/wp-config.php
chmod 660 ${WP_ROOT}/wp-config.php

# allow wordpress to manage .htaccess
touch ${WP_ROOT}/.htaccess
chgrp ${WS_GROUP} ${WP_ROOT}/.htaccess
chmod 664 ${WP_ROOT}/.htaccess

# allow wordpress to manage wp-content
find ${WP_ROOT}/wp-content -exec chgrp ${WS_GROUP} {} \;
find ${WP_ROOT}/wp-content -type d -exec chmod 775 {} \;
find ${WP_ROOT}/wp-content -type f -exec chmod 664 {} \;

 

Administration Over SSL

Add the below lines to the wp-config.php file above the ‘/* That’s all, stop editing! Happy blogging. */’ line

define('FORCE_SSL_ADMIN', true);
define('FORCE_SSL_LOGIN', true);

This ensures the login AND the administration is done over SSL

You could also use the below htaccess:

RewriteCond %{THE_REQUEST} ^[A-Z]{3,9}\ /(.*)\ HTTP/ [NC]
RewriteCond %{HTTPS} !=on [NC]
RewriteRule ^/?(wp-admin/|wp-login\.php) https://mysite.com%{REQUEST_URI}%{QUERY_STRING} [R=301,QSA,L]

 

Find out how many SQL queries are executed every time a page is loaded.

Add the below to one of the theme files, I usually add to footer.php

if ( current_user_can( 'manage_options' ) ) {
echo $wpdb->num_queries . " SQL queries performed.";
} else {
  // Uncomment the below line to show SQL queries to everybody
  // echo $wpdb->num_queries . " SQL queries performed.";
}

Here are some configuration parametres you can add to your wp-config.php file for FTP.

define('FS_METHOD', 'direct');
/*
forces the filesystem method. It should only be "direct", "ssh2", "ftpext", or "ftpsockets". Generally, you should only change this if you are experiencing update problems. If you change it and it doesn't help, change it back/remove it. Under most circumstances, setting it to 'ftpsockets' will work if the automatically chosen method does not.
    (Primary Preference) "direct" forces it to use Direct File I/O requests from within PHP, this is fraught with opening up security issues on poorly configured hosts, This is chosen automatically when appropriate.
    (Secondary Preference) "ssh2" is to force the usage of the SSH PHP Extension if installed
    (3rd Preference) "ftpext" is to force the usage of the FTP PHP Extension for FTP Access, and finally
    (4th Preference) "ftpsockets" utilises the PHP Sockets Class for FTP Access.
*/
define('FTP_BASE', '/var/www/vhosts/example.com/httpdocs/'); // is the full path to the "base"(ABSPATH) folder of the WordPress installation.
define('FTP_CONTENT_DIR', '/var/www/vhosts/example.com/httpdocs/wp-content/'); // is the full path to the wp-content folder of the WordPress installation.
define('FTP_PLUGIN_DIR ', '/var/www/vhosts/example.com/httpdocs/plugins/'); // is the full path to the plugins folder of the WordPress installation.
define('FTP_PUBKEY', '/var/www/vhosts/example.com/httpdocs/.ssh/id_rsa.pub'); // is the full path to your SSH public key.
define('FTP_PRIKEY', '/var/www/vhosts/example.com/httpdocs/.ssh/id_rsa'); // is the full path to your SSH private key.
define('FTP_USER', 'FTPusername'); // is the FTP username
define('FTP_PASS', 'FTPpassword'); // is the password for the FTP User
define('FTP_HOST', 'localhost'); // FTP Host - usually localhost.
define('FTP_SSL', false); // This is for "Secure FTP" not for  SFTP.

Pretty Permalinks

If wordpress doesn’t have access to write to your .htaccess file then you’ll need to add the below for Pretty Permalinks to work
# BEGIN WordPress

RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]

# END WordPress

Show users and Privilidges

SSL traffic is passed from the load balancer as http traffic and may cause redirect issues depending on how the code is setup
// Check for SSL from LB
if(isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] == 'https'){
$_SERVER['HTTPS']='on';
}