Home Of RDort

On the fly Compressing Mysqldump Output

The result of mysqldump is a flat text file containing the sql commands used to restore the mysql databases/tables that were dumped. Normally the result of mysqldump is compressed (in regular backups) because the size of the resulted file is normally big and good compression rates are achieved on such text files. To do this manually using gzip we just run:

gzip -v outputfile.sql

This quick tip shows how you can run the same thing on the fly with only one command directly compressing the resulted file. This might be useful in situations where space is a problem and the full dump can’t be saved on the available storage directly because of its size. Also this might be useful to not run it in 2 commands and have one compact command (maybe used also in some backup scripts, etc.)

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

When restoring from a compressed file we can manually uncompress it first:

gunzip -v outputfile.sql.gz

or again we can run in the same command line mysqldump and gunzip:

gunzip < outputfile.sql.gz | mysql < mysql options>

The same can be done using bzip2 compression:

mysqldump < mysqldump options> | bzip2 > outputfile.sql.bz2

and to restore

bunzip2 < outputfile.sql.bz2 | mysql < mysql options>

From my own experience I would not recommend using bzip2 for this because even if the compression is better the time to run it on large files will be much longer than gzip (6-10 times longer). But if you are in need for every bit of space and don’t care that this will take much longer then bzip2 is the solution for you.

Windows Server 2012 R2 based Virtual Router using PowerShell

Every now and then you need to give a set of lab VMs access to Internet but still keep them on a separate, isolated network.

In this scenario, instead of relying on the physical host platform (Hyper-V / VMware), you use a virtual machine with multiple network adapters to do the routing. One benefit of doing that is that this works the same no matter what virtual platform you are using, and obviously that you don’t need to change the host network configuration (something that can be challenging/scary when remoting into a lab server in another city, like I do :) ).

PowerShell cmdlets for Routing and Remote Access (RRAS) in Windows Server 2012 and Windows Server 2012 R2

Installing a router manually may be fun, but doing it using PowerShell is much more fun (and smarter too). Anyway, I though it was about time to write a post on how to setup a virtual router using PowerShell. The prerequisites for this guide is that you have a Windows Server 2012 R2 VM installed with at least two network cars. One connected to the External network (Internet), and one to the internal network where you have your VMs. On both networks there are DHCP servers, but you will obviously set a static IP address on the internal NIC. You don’t want that address to change :)

Note: In this guide I’m using Hyper-V is the virtual platform, but this works equally great on VMware as well. Again, I’m not using any gateway features on the host, only in the VM acting as a router.

The VM used for virtual router is named GW01, Windows Server 2012 R2 is installed and is configured in a workgroup, even though it’s perfectly fine to join it to a domain. The reason for using a workgroup machine is that I just wanted a generic router, without any dependencies.

The GW01 virtual machine, running Windows Server 2012 R2, and having two network cards configured.

The configuration

Setting up Routing and Remote Access is done in three steps:

  1. Configuring the internal NIC
  2. Install the Routing and Remote Access role
  3. Configure the Routing and Remote Access role

Step1 - Configure the internal network adapter

On my GW01 server I have named the network interfaces External and Internal, listed by running:

Get-NetAdapter | Select Name,MacAddress

Listing the network adapters.

To set a static IP address on the internal network adapter you run the following commands:

Get-NetAdapter -Name Internal | New-NetIPAddress -IPAddress -AddressFamily IPv4 -PrefixLength 24


Step 2 - Install the Routing and Remote Access role

Once the network adapter is configured it’s time to add the Install the Routing and Remote Access role, as well as its PowerShell cmdlets, by running the following command:

Install-WindowsFeature Routing -IncludeManagementTools


Step 3 - Install the Routing and Remote Access role

Once the Routing and Remote Access role and its PowerShell cmdlets are added, you can now configure it. In this scenario you set up a simple NAT gateway.

To configure the NAT gateway, run the following commands:

Install-RemoteAccess -VpnType Vpn
cmd.exe /c "netsh routing ip nat install"
cmd.exe /c "netsh routing ip nat add interface $ExternalInterface"
cmd.exe /c "netsh routing ip nat set interface $ExternalInterface mode=full"
cmd.exe /c "netsh routing ip nat add interface $InternalInterface"

If you want to verify the setup you can open the Routing and Remote Access management tool.


SQL SERVER – Restore Master Database – An Easy Solution

http://rdort.com/post/sql-server-start-sql-server-instance-in-single-user-modeToday we will go over two step easy method to restore ‘master’ database. It is really unusal to have need of restoring the master database. In very rare situation this need should arises. It is important to have full backup of master database, without full backup file of master database it can not be restored.

It is necessary to start SQL Server in single user mode before master database can be restored. It is very easy to start SQL Server server in single user mode. Follow the tutorial SQL SERVER – Start SQL Server Instance in Single User Mode.

Once SQL Server instance is running in single user mode, immediately connect it using sqlcmd and run following command to restore the master database.

RESTORE DATABASE master FROM DISK ='C:\BackupLocation\master.back' WITH REPLACE;

I have tested it couple of times and it has worked fine for me. If you encounter any error please leave a comment and I will do my best to solve it.

SQL SERVER – Start SQL Server Instance in Single User Mode

There are certain situation when user wants to start SQL Server Engine in “single user”mode from the start up.

To start SQL Server in single user mode is very simple procedure as displayed below.

Go to SQL Server Configuration Manager and click on  SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd.

Make sure to remove newly added params after required work is completed to restart it in multi user mode.