Category: Web Development

Truncate a WordPress Database To X-Number of Posts

I regularly work with huge MySQL database files (10G-100G) so making copies and importing them locally for use in VVV is extremely cumbersome (at best). Here are some MySQL statements that allow you to truncate all but the most recent 1000 posts in your WordPress database. These are useful because they allow you to truncate the database to a manageable size before copying to your local environment or development server. Unless you have something unusual, these statements should retain all post-to-postmeta associations, comment-to-post associations, and term-to-post associations.

WARNING: DO NOT run these on your production database. You must make a copy of the whole database first and then run these statements on the copy. You have been warned!

Delete all but the most recent 1000 posts:

DELETE FROM wp_posts WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts WHERE post_type="post" AND post_status="publish" ORDER BY ID DESC LIMIT 1000 ) foo );

Delete all post meta that is not associated with the remaining posts:

DELETE FROM wp_postmeta WHERE post_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete all comments that are not associated with a remaining post:

DELETE FROM wp_comments WHERE comment_post_ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete all comment meta that is not associated with a remaining post:

DELETE FROM wp_commentmeta WHERE comment_ID NOT IN ( SELECT comment_ID FROM ( SELECT comment_ID FROM wp_comments ) foo );

Delete term relationships that don’t associate to a remaining post:

DELETE FROM wp_term_relationships WHERE object_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );

Delete terms that are no longer related to an existing post:

DELETE FROM wp_terms WHERE term_id NOT IN ( SELECT term_taxonomy_id FROM ( SELECT term_taxonomy_id FROM wp_term_relationships ) foo );

As an added bonus, delete transients from the options table:

DELETE FROM wp_options WHERE option_name like '%_transient_%';

And here’s the whole thing in one shot – script it in if you wish:

DELETE FROM wp_posts WHERE ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts WHERE post_type="post" AND post_status="publish" ORDER BY ID DESC LIMIT 1000 ) foo );
DELETE FROM wp_postmeta WHERE post_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_comments WHERE comment_post_ID NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_commentmeta WHERE comment_ID NOT IN ( SELECT comment_ID FROM ( SELECT comment_ID FROM wp_comments ) foo );
DELETE FROM wp_term_relationships WHERE object_id NOT IN ( SELECT ID FROM ( SELECT ID FROM wp_posts ) foo );
DELETE FROM wp_terms WHERE term_id NOT IN ( SELECT term_taxonomy_id FROM ( SELECT term_taxonomy_id FROM wp_term_relationships ) foo );
DELETE FROM wp_options WHERE option_name like '%_transient_%';

November 3, 2015

Easy Automatic Facebook Share Counts (Fully Javascript)

Drop this little gem in your site’s header:

<script>
function numberFormat(x) {
	return x.toString().replace(/B(?=(d{3})+(?!d))/g, ',');
}

jQuery(document).ready(function($){
	$('.trigger_facebook_count').each(function(){
		var url = $(this).attr('data-url');
		var that = $(this);

		$.get('http://graph.facebook.com/' + url, function(data) {
			that.html(numberFormat(data.shares));
		});	
	});
});
</script>

Then, add elements to your page like so:

<div class="trigger_facebook_count" data-url="http://bossip.com"></div>

Remember to change the data-url to whatever URL you are referencing, and viola! You’re done.

October 22, 2015

Web Server iptables Script

Here is an iptables script to set up a solid firewall on a CentOS web server. Remember to change the ip address on line 26 with your IP address, or you will lock yourself out of your own server!

#!/bin/bash

# Backup current iptables rules
iptables-save > /etc/sysconfig/iptables-previous

# Flush tables
iptables -F

# Block null packets
iptables -A INPUT -p tcp --tcp-flags ALL NONE -j DROP

# Reject syn-flood attacks
iptables -A INPUT -p tcp ! --syn -m state --state NEW -j DROP

# Reject XMAS packets
iptables -A INPUT -p tcp --tcp-flags ALL ALL -j DROP

# Allow specific traffic
iptables -A INPUT -i lo -j ACCEPT

# HTTP and HTTPS traffic
iptables -A INPUT -p tcp -m tcp --dport 80 -j ACCEPT
iptables -A INPUT -p tcp -m tcp --dport 443 -j ACCEPT

# SSH from specified clients
iptables -A INPUT -p tcp -s 123.45.67.89 -m tcp --dport 22 -j ACCEPT

# Allow related connections
iptables -I INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT

# Allow outgoing connections
iptables -P OUTPUT ACCEPT

# Block all other connections
iptables -P INPUT DROP

# Save iptables and restart service
iptables-save | sudo tee /etc/sysconfig/iptables

July 11, 2015