windows

Quite some time ago I published on another site (http://www.teqhead.com) a flash-based tutorial on how to use TrueCrypt, the FREE open-source disk encryption software for Windows Vista/XP, Mac OS X, and Linux.

While the version I based the how-to on is a few releases behind now, the principle and the general functionality all remain the same.

You can choose to view on line by going to the following link: http://www.poscribes.com/files/truecrypt.swf

Or you can choose to download the external application for viewing in your own time, at the bottom of this post or here.

Enjoy!

Dave

In establishing a high-availability web application you need to consider multiple levels of availability and redundancy.

In this first installment discussing MySQL replication I am referring to omni-directional replication. In my follow-up on the subject, next week, we will be covering bi-directional replication or creating what is otherwise known as multi-master replication, and yes I am doing this on Microsoft Windows.

As previously discussed in my post on the hardware upgrades for the Poscribes website, I am introducing multiple levels of redundancy to ensure availability not only with load-balanced web servers but also replicated databases and ultimately fail-over internet connectivity (still working on that one!). Why Windows? Well, doing it with Linux is the norm these days and I just wanted to see how easy it would be.

In this situation I am using MySQL 5.0.51b-community-nt to replicate between my primary or master database running on a single 2.8Ghz Intel Xeon CPU with 1.5Gb RAM (aka DB01) to my secondary or slave database running on a single 800Mhz Intel Pentium III CPU with 896Mb RAM (aka DB02).

So what does replication really mean and what really happens? Well simply put, the queries that are executed against the master database (DB01) from the incoming application requests are recorded in a binary log (bin.log) along with a unique identifier, otherwise known as the log position identification number. Through an established and authentication connection, the slave database server (DB02) reads this log and applies the same queries to its database(s). Obviously there is a little more to it than just that but I don't want to get bogged down with the details when I want to concentrate on the how-to.

I think it's worth noting that the overhead on the servers is minimal. The process is really very simple and I'm going to include, for a default install, all of the code I used to make it happen.

If you are somewhat familiar with MySQL and I kind of hope you are to be performing something like replication in the first place you will be comfortable with using the mysqldump and mysql command syntax.

Now, before we begin with the first step there are a couple of things that I like to do just to make my life easier. In order to use the mysqldump and mysql commands from any directory as apposed to just the c:\program files\mysql\mysql server 5.0\bin directory we can COPY the executables mysql.exe and mysqldump.exe to the c:\windows\system32 directory. Doing this now gives us system wide access to these executables.

Here's a quick list of the steps that will follow:

1) Setup the replication user account and grant privileges
2) Configure the Master server
3) Configure the Slave server
4) Export/Import your current databases
5) Start replication

Okay, so now you have an idea of what we will be covering and you've made life a little easier for yourself, let's start at the beginning with step 1.

Step 1 - Setup the replication user account and grant privileges

On the master database server open a MySQL command line client window or a regular command line window and connect to MySQL using the standard string of:

c:\>mysql -u root -p

Once connected issue the following command to setup a valid user account for replication:

mysql>grant replication slave, replication client on *.* to 'replicator'@'192.168.1.35' identified by 'password';

(Obviously use your own user name, password combination and the IP address or system name that corresponds to your slave or secondary database server.)

[Click To Enlarge]

As you can see above to confirm the establishment of replication rights you can issue the following command:

mysql>show grants for 'replicator'@'192.168.1.35';

You could of course just use the existing 'root' account that has 'all' access, but it's generally not a good idea from a security perspective unless you are just testing.

Now issue the same command on the slave server using the IP address for the master server instead.

You have now established the necessary user account and assigned privileges to make replication possible.

Step 1 complete!

Step 2 - Configure the Master Server

There is very little in fact to do with regards the master server. Having already established a user account for replication in Step 1 we now need to make a couple of small additions to the my.ini file.

Edit the c:\program files\mysql\mysql server 5.0\my.ini (my.cnf is the equivalent on linux) and add the following lines to the bottom of the file.

(On the Master Server)
# On DB01
tmpdir="c:/temp/"
server-id=1
log-bin="c:/Program Files/MySQL/MySQL Server 5.0/data/bin.log"
log-error="c:/Program Files/MySQL/MySQL Server 5.0/data/error.log"
log-bin-index="c:/Program Files/MySQL/MySQL Server 5.0/data/log-bin.index"

The server-id shown above needs to be a unique identifier for this server. Any subsequent slaves that you establish need to have their own unique server-id. Unless you have changed the service account under which MySQL operates you shouldn't have to set any file permissions.

Then save the file.

Step 2 complete!

Step 3 - Configure the Slave Server

Okay, switch systems to the slave database server and make the following additions to its my.ini file just like you did for the master server.

(On the Slave Server)
# On DB02
server-id=2
master-host=db01.yourdomain.net (or IP address)
master-port=3306
master-user=replicator
master-password=password

log-bin="c:/Program Files/MySQL/MySQL Server 5.0/data/bin.log"
log-bin-index="c:/Program Files/MySQL/MySQL Server 5.0/data/log-bin.index"
log-error="c:/Program Files/MySQL/MySQL Server 5.0/data/error.log"
relay-log="c:/Program Files/MySQL/MySQL Server 5.0/data/relay.log"
relay-log-info-file="c:/Program Files/MySQL/MySQL Server 5.0/data/relay-log.info"
relay-log-index="c:/Program Files/MySQL/MySQL Server 5.0/data/relay-log.index"

Then save the file.

Step 3 complete!

Step 4 - Export/Import Your Current Databases

Assuming you have an existing database or multiples thereof on your primary database server, you will need to export the data from this server and import it into your slave system. If this is a completely new setup then move on to Step 5.

Okay, to export the data we will use the mysqldump command. To ensure success we need to tell the database to complete all existing queries by locking and flushing the tables and then perform a dump of the data. Here's how we do this, on the master database server:

mysql>mysqldump --user=root --password=password --extended-insert --master-data --lock-all-tables --all-databases > c:\full_backup.sql

This command provides the necessary information to completely rebuild your data on the slave database server along with the binary log position from the master database server to identify where to start replication from.

On the slave database server, copy over the .sql file containing the dump to a location such as c:\ to make it easy to find.

Before importing the dump file we need to first stop the slave service. To do so, open the MySQL command line client or regular command window. Log in using your root or similar credentials and issue the following command:

mysql>stop slave;

Now you are ready to import the data to your slave server.

mysql>mysql --user=root --password=password < c:\full_backup.sql

Once the import has finished running and providing there are no errors you are ready to move on to Step 5.

NOTE: During my install I had an inconsistent error that occurred. This is the error I received:

mysqldump: Couldn't execute 'SHOW TRIGGERS LIKE' filter\_formats: Can't create/write to file 'd:\temp\#sql_c58_0.MYI'

After some head scratching I established that this error was related the McAfee anti-virus blocking the writing to this temp directory, so to get around the issue I established an exception for that folder.

Step 4 complete!

Step 5 - Start Replication

On the master database server restart the MySQL service.
On the slave database server restart the MySQL service.
On the slave database server issue the following command from the MySQL command line client or regular command window:

mysql>start slave;

That's it! You should now be replicating.

To verify this is so issue the following command while still on the slave server:

Mysql>show slave status\G;

And you should see something like the following:

[Click To Enlarge]

You are now replicating! Add a record in whatever way you choose to the master database server and watch as it magically (alright no magic involved, but it is pretty cool!) appears in the slave database.

Next time around for MySQL we will be completing the replication setup by establishing replication back to the master in a multi-master circular or bidirectional configuration.

Enjoy!

Controlling what information can be moved around via thumb drives, i-Pods, cameras and other forms of removable storage media has gained a lot of focus over the past 12-months. It's not to say the need hasn't been there for much longer, because it has, just that media attention following exposure and loss of personal information, for instance social security numbers, has resulted in auditor attention.

There are a number of third-party products that claim to control usage of and the functionality thereto in terms of read or read-write capability for USB storage devices, however, in my experience the simplest approach is usually the only approach that stands the test of time.

What I am referring to here specifically is the control through local security settings and in the case of Windows also through Group Policy and Active Directory.

The NSA have produced a rather enlightening document that not only addresses the need as relates to Microsoft Windows but also that of Linux and Solaris (both 9 and 10) as well as Mac OS X. For further reading you may find the document here on this site or directly from the NSA, here.

Unfortunately this document does not cover the currently available Windows 2003-based Domain and Active Directory.

Windows Vista has a number of additional Administrative Templates that provide better 'out of the box' control of such devices. (It is out of the scope of this post to validate whether exporting any of these templates and subsequently importing them into 2003 would provide this additional functionality and as such I would love to here from anyone that has pursued this direction either from a successful or unsuccessful perspective.)

For now, we have another way. The Administrative Template attached to this post here and listed below may be imported into the active directory domain template in your domain and enabled to effectively provide read-only access to USB devices. This policy change will not disable the usage of any USB powered keyboards or mice.

As mentioned earlier in this post, there are a number of third-party products that report to control access in a similar fashion but most if not all that successfully achieve this are commercial products. Windows Group Policy is an integral, necessary component of any Windows-based Active Directory domain and can be enabled to offer a potentially similar albeit basic level of control at a price acceptable to all.

If you wish to take the level of control even further you may utilize the information found here at the Microsoft web site to disable other devices such as CD-ROM, Floppy and LS-120 drives also.

If there are any errors or omissions to the methods described by the NSA document attached or by the Microsoft website that have subsequently surfaced through user testing please let us know so we can all share from your experiences.

I hope you find this information useful.

Thanks.