MySQL – CodeSleepShred

Useful MySQL Queries For WordPress Migration

August 2, 2016 CodeSleepShred

See what’s hogging up your MySQL db

	# query db to see the largest tables

	SELECT
	table_schema as `Database`,
	table_name AS `Table`,
	round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
	FROM information_schema.TABLES
	ORDER BY (data_length + index_length) DESC;

view raw

gistfile1.txt

hosted with ❤ by GitHub

Revisions Genocide

	# revisions genocide

	DELETE a,b,c
	FROM wp_posts a
	LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
	LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
	LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
	WHERE a.post_type = 'revision'
	AND d.taxonomy != 'link_category';

view raw

gistfile1.txt

hosted with ❤ by GitHub

Cluttered up DB, create a mass drop query

	SELECT CONCAT( 'DROP TABLE `', TABLE_NAME, '`;' ) AS query
	FROM INFORMATION_SCHEMA.TABLES
	WHERE TABLE_NAME LIKE '%_gmp_%'

view raw

gistfile1.txt

hosted with ❤ by GitHub

WordCamp Seattle 2012 – May 19th @ Seattle Art Museum

March 8, 2012 CodeSleepShred

This year’s WordCamp Seattle will be held at the Seattle Art Museum in downtown Seattle. I am looking forward to this years WordCamp Seattle since last year we didn’t have one. 🙁 In 2011, I went down to San Francisco to get my fix! I plan on attending a few WordCamps on the west coast in 2012!

If your looking to jump in on the WordPress action, get your tickets today! ~~We’re almost sold out!~~ SOLD OUT!

Who: WordPress Nerdballers
What: WordCamp Seattle 2012
Where:
Seattle Art Museum
1300 1st Avenue, Seattle, WA
(206) 654-3100
How: [~~Get your tickets here!~~ SOLD OUT!] – If you missed out on a ticket, WordCamp Seattle still needs volunteers! Help out, sign up here: http://2012.seattle.wordcamp.org/volunteer/
Why: Because, CMS.

The WordCamp Seattle 2012 Widget

Spread the word! I just quickly made this widget since WC Seattle hasn’t made one yet!

[html]<a href="http://2012.seattle.wordcamp.org" target="_blank"><img title="I am attending WordCamp Seattle 2012" src="http://codesleepshred.com/wp-content/blogs.dir/1/2012/03/wordcamp-seattle-2012.gif" alt="I am attending WordCamp Seattle 2012" /></a>[/html]

Photo credit goes to Doug Mahugh

Inspiration: WordPress Multisite Running On Amazon Web Services

March 6, 2012 CodeSleepShred

I honestly don’t know why, but I have always feared the day that I would dive head first into Amazon Web Services. In the last two weeks, I have been researching the in’s and out’s of AWS, how people are using it to deploy applications, security, it’s architecture and reading AWS terminology… It is very overwhelming, but I think it’s time I embarked on this adventure.

A few inspirations, Earmilk.com’s Blake Shoji. Although I have never met IRL, nor interacted with him online; the Earmilk network boggles my mind. They definitely sparked my curiosity! If your a music lover, I’d recommend checking them out!

While I was reading up on AWS, I happen to find David Jensen’s blog post on how to install WordPress on Amazon AWS EC2. I am going to use this documentation as a basis for my study, although I do want to branch off to NGINX. We’ll see where the path takes me!

If you need further inspiration that will help motivate you to jump into the cloud, I highly recommend watching “The Known Universe” with The XX Intro Extended dubbed over it. Seriously, the possibilities are endless!

How To Setup LAMP on uBuntu 11.04 – Also, WordPress

May 14, 2011 CodeSleepShred

In this article, we will work on setting up a LAMP environment for uBuntu 11.04. As an added bonus, I will setup WordPress 3.2 in the environment for you nerds. As you may notice in previous posts, I have a nice little lappy which allows me to dev on the run.

Installing LAMP onto uBuntu 11.04

First thing is first, we need to install tasksel then run it via terminal.

[code lang=”bash”]
sudo apt-get install tasksel
sudo tasksel[/code]

Select LAMP Server and proceed to install it!

While you are installing LAMP, you will be prompted to set a password for MySQL root.

First of all, /var/www/ directory is set for root user only. We want to allow our IDE’s, such as Netbeans or Vi/Vim/Nano have permissions to write to this directory. So let’s sudo su and chown /var/www/

[code lang=”bash”]
sudo su
chown yourusername:yourusername /var/www/
exit
[/code]

At this point, let’s test to see if LAMP is running.

[code lang=”bash”]sudo vi /var/www/index.php[/code]

Within index.php just enter phpinfo(); to check if the PHP core fires an output!

[code lang=”php”]<?php phpinfo(); ?>[/code]

Save index.php and exit.

When you visit http://localhost/index.php you should get an output. If you do not, you can try restarting apache2 with the following command:

[code lang=”bash”]sudo /etc/init.d/apache2 restart[/code]

Installing phpMyAdmin To Manage Your MySQL Databases on uBuntu 11.04

[code lang=”bash”]sudo apt-get install phpmyadmin[/code]

WordPress: World Famous 5 Minute Installation

Head over to WordPress.org and grab the latest version of WordPress. Extract the zip file to /var/www/wordpress

Visit http://localhost/phpmyadmin and create a database.

Under the Privileges tab, goto Add New User. Make sure under Global Privileges click Check All and hit Go.

Visit http://localhost/wordpress and setup your WordPress install! Bam! Your done!

Linuxfest Northwest 2011

May 1, 2011 CodeSleepShred

Arriving at Bellingham Technical College a bit late, tired from late night geeking out on uBuntu. I arrive in a moist parking lot with a grungy overcast. I walk into “Build a Free As In Freedom Cloud” in Haskell 203 late. Old Spice should sponsor Linuxfest, the man next to me smelt of musk; as if he wrestled a bear into submission.

At 11am, Mark Hinkle took the stage discussing “Crash Course on Open Source Cloud Computing” – this man is a genius. Below is his slideshare.

During lunch, of course I opted for the ribs. I met an instructor at WWU and one of the computer science network admins (their names escape me.) It was interesting talking to them about distributed storage.

After lunch I sat in to listen to Adrian Pike’s Agile development concepts and practices for lean startups talk. It was interesting to hear his experience with project management and collaborating with people online. I think the most insightful bit of information was just how their team handles communication. He mentioned Kanban, Scrum, Basecamp, Test suites, etc.

Coincidentally the Amazon AWS talk was canceled. I am not sure why but I ran into two Amazon network technicals hanging out in the hall ways. I had to sit in on a Drupal talk which was semi informative.

The next thing that happened to me was sitting down next to an asian that had a Google Laptop CR-48. I later found out he actually works for Google and was sent to LinuxfestNW to sit in on the Cloud Networking talks. I’m assuming he’s an engineer. He was sitting with one of the Amazon network technicians. David Nalley talk about open source clouds was very informative and I’d love to experiment with the software he mentioned.

All in all, Linuxfest Northwest was just another nerdcore fest. It’s nice to meet / talk with others that understand the techno jargon.

Learning A New Open Source Platform

March 29, 2011 CodeSleepShred

In the last 2 weeks, I’ve picked up a new open source platform called MediaWiki. In the past year I’ve been heavily involved with WordPress theme dev and deployment. Learning MediaWiki has quite a learning curve. It seems that webmasters need a greater emphasis on critical thinking, Apache, PHP, MySQL and the basic understanding on how to run a wiki. Luckily the wiki-geek community is vast, people help one another.

The project, Magickapedia.net – is a wiki based on the popular indie game Magicka. The Magicka wiki we founded in the past month has exploded, with over 1,000,000 views in a month. It’s grown so fast, we’ve had to take time to sit down and battle spammers, learn the open source community and implement various features the out-of-the-box code doesn’t offer by default. We are now in the process of preparing for Magicka Vietnam, standardizing templates and refining the content.

2011 is going to be an exciting year.

MySQL Find And Replace In WordPress Database

March 29, 2011 CodeSleepShred

If you’re migrating domains, changing your alias, or simply learned the hard lessons between your, you’re, you are, here’s a simple mysql query to run on your database.

[box type=”note”]Be sure to backup your database![/box]

You will need phpMyAdmin or SQL Executioner. A basic knowledge in SQL is a must, so run down to Barns & Noble and get your nerd on!

[code lang=”sql”]
update wp_posts set post_content = replace(post_content,’myoldsite.com’,’mynewsite.com’)
[/code]

In this example, we are targeting wp_posts -> post_content. We are performing replace() on anything in post_content that matches “myoldsite.com”.

The basic logic can be applied to anywhere in the database, but this is very useful if for example, all your posts pointed to a site that no longer exists. I’ve also used this to remove author’s contact information in the content. For some reason they thought it was a good idea to include their phone number and e-mail on every post, which later became outdated! I eventually crated a useful author signature and a way they could update this information in /wp-admin/profile.php.