Category Archives: SysAdmin - Page 2

Restore MySQL Dump File to a Different Database Name

Restore MySQL Data to a Different DatabaseToday I had to take a MySQL database backup from one database and then restore it to a different database name (for development/testing purposes). I tried a number of different tricks that I found online through Google without any success. The issue was that the user I was using to restore the data was NOT an administrator (root) on the database so it was failing with permission errors – always with an error related to the name of the database I restored FROM.

Okay, time to go old-school. So… I opened the .sql file that was created by the MySQL backup and behold – it’s text! How great is that!? Not some cryptic gibberish like a SQL Server database backup file format.

When looking at the file a little, right there near the top I found the issue. The MySQL backup files by default assume you want to restore to the exact same file name. So then have this line:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

In that line the database that I restored from was actually named “test”. As you can see, it checks for the existence of the database and if it doesn’t exist, it creates the database. Well, in this case I didn’t want that database created and my user didn’t have permissions to perform that action anyway.

So I changed the name of the old database (“test”) to match the name I wanted for the new database (“newTest”).

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `newTest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

Great – one error avoided! But thankfully, before existing the .sql file, I noticed the very next line in the file:

USE `test`;

Well, that’s no good. That’s going to use the ORIGINAL database that I performed the restore from. Yikes. If my user had permissions to that database it would have overwritten the original. Not good at all. So, let’s change that…

USE `newTest`;

I did a quick scan through the rest of the file and nothing stood out to me as an issue. I performed the restore (in this case using MySQL Workbench but any tool – even a command line – would have worked fine) and everything went nice and smoothly. Like magic I now have the data from the “test” database restored to my new database named “newTest”.

I hope this is helpful! Cheers, and happy hosting!

Automating Website Creation with PowerShell v3

I will be the first to admit that I am not a scripting pro. Matter of fact, I am pretty much a novice at it. Recently, I stumbled across a PowerShell script that creates a website, populates it with default text page, and launches IE to test the page. This script was written for PowerShell v2. I have thought about the script a few times and decided to sit down and port it over to PowerShell v3. All I can say is I am glad I did. Using the ISE, Integrated Scripting Environment, for PowerShell v3 made me feel like a real scripter.

This was the original script that I put together following the guide above.

Set-ExecutionPolicy RemoteSigned
Import-Module WebAdministration
$webroot=New-Item c:\PSCreatedSite -type Directory
New-Item c:\PSCreatedSite\PSCreatedApp -type Directory
Set-Content c:\PSCreatedSite\default.htm “PSCreated Default Page”
Set-Content c:\PSCreatedSite\PSCreatedApp\default.htm “PSCreated\PSCreatedApp Default Page”
New-Item iis:\apppools\PSCreatedSite
New-Item iis:\Sites\PSCreatedSite -physicalpath $webroot -bindings @{protocol=”http”;bindingInformation=”:81:”}
Set-ItemProperty iis:\Sites\PSCreatedSite -Name applicationpool -Value PSCreatedSite
New-Item iis:\sites\pscreatedsite\pscreatedapp -physicalpath c:\pscreatedsite\pscreatedapp -type Application
Set-ItemProperty iis:\sites\pscreatedsite\pscreatedapp -Name applicationpool -Value PSCreatedSite
#
#now for the automated test as well
$ie=New-Object -com internetexplorer.application
$ie.visible = $true
$ie.Navigate(“http://localhost:81/”)

I updated all of the New-Item commands that referenced the IIS: drive and and changed them to use the New-WebAppPool and New-Website cmdlets. Using the new Commands Add-On, I was able to select the New-WebSite command, for instance, and see the parameters that were required. As you can see, the only required parameter to create a new website is the Name. Since I am going to be creating multiple sites on a test server that only has 1 assigned IP, I want to include HostHeader, and PhysicalPath as well. It is also a best practice to create application pools for each website, so I specified that as well in my command. I then used the Insert capability to insert the complete command into the console where I could copy and paste it into my script. This GUI ensures that the syntax is correct when you insert it into your script.

new-website_command

I decided I also wanted to update the script to include the ability to parse a csv file to provide the input for the Website creation commands. I went with a very generic list. I created the csv file with 2 variables: Website and Path. Here is a sample of the contents of the websites.csv file that I created. By using the localtest.me domain, there is no need to create host file entries to test your local websites. This domain resolves back to 127.0.0.1. If you would like additional information related to localtest.me, check out Scott Forsyth’s blog post.

Website,path
“test1.localtest.me”,”c:\inetpub\test1.localtest.me”
“test2.localtest.me”,”c:\inetpub\test2.localtest.me”
“test3.localtest.me”,”c:\inetpub\test3.localtest.me”

And finally, the thing you have read this whole document for, the script itself. Smile

<#
This can be used to script the creation of Websites. It was written using PowerShell v3 on Windows Server 2012 for IIS8. It uses the WebAdministration module that is one of many modules included in this new release. Use at your own risk. Once you copy it, you own it.

by Terri Donahue
#>

Import-Module WebAdministration
Import-Csv “C:\Scripts\websites.csv” | ForEach-Object {
$WebSiteName = $_.Website
$PathInfo = $_.path
New-Item $PathInfo -ItemType Directory
New-WebAppPool -Name $WebSiteName
New-Website -Name $WebSiteName -ApplicationPool $WebSiteName -HostHeader $WebSiteName -PhysicalPath $PathInfo
Set-Content $PathInfo\default.htm “PSCreated Default Page”
}

This is just a sample script, but as you see, it is quite powerful. Since I didn’t specify the WebSite ID, it was automatically generated starting with the next available number. The application pool was created using the Application Pool Defaults. All of these things could have been included in the script and set according to required specifications. I used the $WebSiteName to populate the name of the site, the host header and also the application pool name. This just provides consistency if you are managing a large number of websites on a server. If your directory structure already exists, you could populate the existing path in your websites.csv file and remove the New-Item $PathInfo -ItemType Directory line of the script. You would also want to remove the Set-Content line to ensure that you do not overwrite any instance of default.htm if your code is already deployed.

Here is a screenshot of IIS Manager after running the script that shows the application pools as well as the websites that were created.

image

I hope you find this information helpful and look forward to my next trip into the scripting wilds.

Terri is a Support Specialist at OrcsWeb, a hosted server company providing managed hosting solutions.

A great walk-through of the new Windows Server 2012 Server Manager console

Gabe over at OrcsWeb wrote up a great post showing a walk-through of the new Microsoft Windows Server 2012 Server Manager interface. If you move from Windows Server 2008 to Server 2012 and see this for the first time, it can be daunting. This walk-through will help get you prepared for a smooth transition to the new interface.

Server Manager in Windows Server 2012

VMware Workstation error: virtual machine’s policies are too old…

I recently upgraded my VMware Workstation to version 9 and today when I went to open a few of my VMs to do some quick testing I got an error related to “virtual machine’s policies are too old…”. Grr. Nothing changed but the upgrade.

After a bit of Googling I found a link with a solution that worked for me:

1. Edit the vmx-file and remove all lines starting with policy*
2. Rename the vmpl file if it exists

I don’t know why I (and apparently other people) had to perform these steps after this v9 upgrade, but I did. I hope this helps someone else out there scratching there head in potential panic mode about possible “lost” VMs.

Orchard Memory Usage and Performance

A guest post by Rick Barber – a senior technical lead over at managed Windows host OrcsWeb:


At OrcsWeb we recently came across an issue where a user was having performance issues with a bare-bones Orchard installation on a shared server. We tried to reproduce the issue and sure enough, any time the site was browsed to it took about 20 seconds to load.

Initial troubleshooting identified the cause for the 20 second load times: the application pool was constantly recycling because it was hitting the virtual memory limit. Great! Now we knew what was going on and could begin our quest to get to the root cause of the recycle.

The next obvious place to look was the memory settings on the application pool. Since this is a shared server there are memory limits in place. Orchard takes a large chunk of memory for the initial compile which was causing the application pool to hit the virtual memory limit. We increased the virtual memory limit to 2 GB and the application pool was still recycling, just not as often. We set the virtual memory limit to unlimited and the application stabilized and we continued to monitor it.  Setting Virtual Memory Limit (KB) to 0 effectively allows it to use unlimited memory as shown above.

A short time later we noticed that the application was using over 5 GB of virtual memory. We tried some other memory settings but they all caused the application pool to recycle frequently.

After more investigation we noticed that the application pool was running in 64-bit mode. Since that setting allows more memory use for an application, we changed it to 32-bit mode. Setting ‘Enable 32-Bit Applications’ to True sets the application pool to 32-bit.  Almost immediately the application started running properly.

We were then able to go back and set a reasonable amount of virtual memory to the application pool. The Orchard site continues to run properly utilizing less than 800 MB of virtual memory.


Happy hosting!