Master & Cmd-R

Using Cron to automate MySQL maintenance

We recently needed to go through a list of around 50 sites that we host through HostGator and optimize all of their MySQL databases. Going through each one and optimizing the database tables manually was a very unappealing process, so my trusty compatriot Keith worked out a process to use Cron jobs to automate the procedure. It took a bit of tweaking to get the right arguments in place, and a lot of looking around on the internet to fix a problem with conflicting arguments in the script, but in the end we triumphed… and man, is it awesome!

 

This is taken in the context of our site hosting in HostGator, but the process will be similar (or the same) for any site that uses CPanel.

 

First, log into your CPanel and look for the Cron jobs icon:

clip_image001

 

Update the email address that you want your Cron job to go to: in this case, we’re specifying the output, but it never hurts to have this set for any future jobs you want to run that don’t need to have a specific email output configuration. (more on this in a bit).

clip_image002

 

The next section is where you create your Cron job – in order to test and make sure everything is working, create a new job to run every 15 minutes. What, you don’t believe in testing scripts before releasing them into the wild? I… I don’t even know you any more!

image

 

Paste the following code into the command section:

mysqlcheck -u root -ppassword --auto-repair --optimize --all-databases 2>&1 | mail -s "MySQL Database Optimization" myemail

 

And click on the Add New Cron Job button

image

 

*Replace root with your username and password with your password – note that while there is a space between the –u and your username, there is no space between –p and your password, and (most importantly), there are two dashes on the auto-repair, optimize, and all-databases switches. Also replace myemail with the email address that you’d like to have the results sent to (myemail@domain.com).

 

Here’s what the individual sections mean:

mysqlcheck –u root –ppassword --auto-repair --optimize –all-databases

 

This section is pretty straightforward – runs a check on all your databases and repairs and optimizes them. If you’d like to run this against a single database, change —all databases to db_name or —databases [database1 database2] and so on.

 

2>&1 | mail -s "MySQL Database Optimization" myemail

 

This is the bit that outputs to an email address that you specify, and allows you to specify a subject for your email. If you leave this option off entirely, it will send an email upon completion to the email that you specified earlier, but it will dump the entire script into the subject line of your email, which includes your username and password – yikes!

 

If you’d like to not have an email sent at all, add this section right after all-databases:

>/dev/null 2>&1

 

Your new job will show up immediately below, and should look like this:

image

 

Once you’ve received a confirmation email, and everything is working – simply go back and create a new job that runs on whatever schedule you’d like – in our case, we chose the 1st and the 15th of every month, but you can use any timeframe you’d like.

image

image

 

Delete your test job, and you’re all set to go!

image

How to install & configure PDF iFilter for SharePoint Server 2010

This is something we do as a part of every SharePoint Server 2010 install – we have been unable to find any reliable instructions on how to do this, and this works for us every time.

Follow the steps below to install and configure PDF iFilter on SharePoint Server 2010:

  • Install PDF iFilter 9.0 (64 bit) from here.
  • Download the following PDF icon file:
Right click and “Save As”

And save it to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\IMAGES\

  • Next you need to edit your docIcon.xml file so that PDF files will use that icon. Navigate to

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\XML\docIcon.xml
and add the following line:

<Mapping Key=”pdf” Value=”pdf16.gif” OpenControl= />

**Adding the OpenControl=”” option at the end of this line fixes a problem with SharePoint prompting you to open a PDF as Read-Only or Edit – thanks to David Kvas for his post on this!

A couple of things to note:

  • Be careful you don’t introduce rogue ascii characters (don’t copy and paste from the web page into the file without making sure the formatting has been removed). A good safe bet is to simply copy a similar line from where you plan to put the new line and make your changes that way.
  • Also, because this is an XML file, don’t just plunk this line at the top or the bottom of the file, it needs to go in the <ByExtension></ByExtension> section. Again, a good idea would be simply to find that section, and work your way down the list (it’s alphabetical) and insert the line in its proper order. That way you can be sure you haven’t put it outside of its XML tags – which can cause all kinds of issues (Application broken because of an extra character in your code that you can’t see no matter how hard you look? *raises hand*)

* Note: If the file is locked and you’re unable to save your changes, perform a quick IISRESET to take care of that – be careful not to interrupt your users.

  • Next, open the registry and navigate to the following location:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office Server\14.0\Search\Setup\ContentIndexCommon\Filters\Extension

  • Right-click Extension, then click NewKey:


  • Name the Key .pdf


  • Now add the following GUID in the default value as shown in the figure below.
     

    {E8978DA6-047F-4E3D-9C78-CDBE46041603}


  • Open SharePoint Central Admin, and click on Manage Service Applications


  • Click on your Search Service Application:


  • Select File Types from the Crawling Menu on the left:


  • Select New File Type:


  • And type pdf into the File extension box, and click OK:


  • Once you click OK, it will take you back to the previous screen where you will now see the PDF file type showing up with the icon that you specified earlier in the first phase of this process:


    * Note: if the icon doesn’t show up immediately, you’ll need to do an IISRESET to reflect the changes you made to the docIcon.xml

  • Then restart the SharePoint Server Search 14 service as shown in the figure below:

    (Start-Run-services.msc, in case you forgot)


Finally, perform a fresh crawl of your site(s) to update the index and start showing PDFs – a full crawl is ideal, but if server resources are a concern, perform an incremental crawl at the very least.

  • To get a crawl running, go back into SharePoint Admin, and click on Content Sources under Crawling:


  • Selecting Start all crawls from this menu will perform a full crawl on all content sources:


    If you want to run an incremental crawl (or a full on specific sites) select that site’s drop down menu and choose Start Full Crawl or Start Incremental Crawl. Note that if you have chosen to run an incremental crawl because of time constraints or server load, you’ll need to select each site in your list and start an incremental crawl from the drop-down menu.


Friends don’t let friends use Claims Based Authentication

How to recognize if your site admin is using (claims based authentication)

Claims Based Authentication

* Thanks to Joris Poelmans’ slideshow: Claim Based Authentication in SharePoint 2010 for Community Day 2011,

This one’s pretty easy – as soon as you enable claims based authentication in SharePoint 2010, it attaches a coded ID to all the usernames that looks like this: i:0#.w|contosousername. This coded ID looks like a weird error to the uninitiated, but not to you – oh no! You know what it all means and are able to decrypt it using the following handy dandy decoder ring:

Take it easy, man… claims are fun!

From what I’ve been able to learn about it, claims based authentication has some interesting possibilities that make it worth exploring, but has a few major gotchas. For instance, once you’ve transitioned to claims based authentication, it’s a one way street – there’s no going back.

* http://msdn.microsoft.com/en-us/library/ff953202.aspx

Basically, as with any technology changes, if it’s set up properly it has a lot of new and shiny things that you can do – if set up incorrectly, it will cause a lot of headaches and will have you either blowing your application away and re-creating it, or restoring from your latest backup.

BGInfo errors in LogMeIn?

We’ve started seeing this error message on quite a few of the servers that we log into remotely – given that the error message talks about permissions and group policy, I started looking in that direction trying to find what had changed. The problem turned out to have nothing to do with group policy or user rights, but a setting in LogMeIn!
When you log into a server through LogMeIn, you get this error message:



I have verified that the problem occurs on a server through LogMeIn, but connecting to that same server with RDP will not throw up an error, and BGInfo fires just fine.
Thankfully, the fix is simple: Once you have the server selected, click on Preferences, then General:


Under the Remote Control option, uncheck the box to Disable wallpaper and user interface effects on host computer.

Hit Apply, and then go back to Remote Control and remind LogMeIn and BGInfo that yes, I am Yo’ Daddy!

 


Removing uninstalled (missing) devices in Device Manager

Thanks to Rob for this great tip – I had no idea this was possible, so hopefully there’s others out there who will find this useful!

When you physically uninstall a device in Windows, the device drivers are often not uninstalled and will remain in your Device Manager, even though it’s no longer visible. Removing the drivers for these ghosted devices can clear up a lot of weird issues caused by driver conflicts, or more importantly, prevent issues from occurring in the first place.

Up to this point, my standard procedure to remove these old drivers was to reboot the machine in Safe Mode, which allows you to see all devices and drivers installed on the machine, even if they’re no longer present. This of course has the downside of taking extra time, as you now need to reboot to get into Safe Mode, and then reboot one more time to get back out again. And with servers being what they are, this can sometimes add as much as half an hour to what is probably a fairly simple hardware upgrade.

Here it is…

Your first step is to open up a command prompt, and type in the following:

2-17-2012-9-34-19-AM-1.png

Starting the Device Manager from this command prompt is essential, as the command to show the missing devices only does it for that instance – if you leave this command prompt to open Device Manager it won’t show them!

Once Device Manager is open, click on View-Show hidden devices. In this case, we’re going to be removing an uninstalled Network adapter, so the only one that’s showing in the screenshot below is the one that is currently installed.

image.png

Voila! Along with some other hidden devices, we find the network adapters that were uninstalled.

image.png

(This is what Show hidden devices looks like if you haven’t run the command, or if you’ve mistakenly opened your Device Manager a different way – not from the command prompt. Note that while we can see the standard hidden adapters, we don’t see the ghosted ones like we do above.)

image.png

Now that we can see our ghosted devices, it’s a simple matter of right clicking on the device and uninstalling it:

image.png

Click OK  to confirm:

image.png

 

And you’re done! No need to worry about running another command to reverse this, because as soon as you close Device Manager it resets to its defaults of not showing ghosted devices.

About Me

My name is Jeremy, and I am a cloud addict.

I absolutely love technology that is available to me anywhere on (almost) any device – between Office 365 and OneDrive (and OneDrive for Business), Dropbox and Outlook.com, I am completely cloud enabled. My computer can die tonight and I will pick up on a new computer tomorrow without losing anything… come on! How awesome is that? My goal is to find and use platforms that are device agnostic – I should be able to pick up an iPad, Surface or Android tablet and be able to get at my data, my email, my photos, contacts and calendars without skipping a beat.

I’m a technology consultant and Office 365 Certified Administrator. I do what I love, and love what I do – provide and support technology solutions for a wide variety of clients and scenarios.

I focus primarily on on-premise server solutions utilizing Hyper-V, Windows Server 2012 (R2), SQL 2012, Exchange 2013, Lync 2013 & SharePoint 2013, or a cloud solution harnessing the power of Office 365 to get Exchange, Lync and SharePoint for a fraction of the cost of an on-premise deployment. Windows Azure, and Intune round out my toolkit and allow me to provide tailor-made solutions to my client’s needs.

I love utilizing the power of the command line for making technology dance and sing, hence the title of my blog. Join me as I explore this technology that I love so much and I promise to share new things that I’ve come across so we can all learn together!