If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

• [uname] Your database username
• [pass] The password for your database (note there is no space between -p and the password)
• [dbname] The name of your database
• [backupfile.sql] The filename for your database backup
• [–opt] The mysqldump option

For example, to backup a database named ‘Tutorials’ with the username ‘root’ and with no password to a file tut_backup.sql, you should accomplish this command:

This command will backup the ‘Tutorials’ database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the ‘Tutorials’ database accomplish the command below. Each table name has to be separated by space.

Sometimes it is necessary to back up more that one database at once. In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

The mysqldump command has also some other useful options:

–add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

–no-data: Dumps only the database structure, not the contents.

–add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

If you want to extract the .gz file, use the command below:

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

• Create an appropriately named database on the target machine
• Load the file using the mysql command:

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

To restore compressed backup files you can do the following:

If you need to restore a database that already exists, you’ll need to use mysqlimport command. The syntax for mysqlimport is as follows:

In this article, let us discuss about how to install VMware ESXi.

VMware ESXi is free. However, the software comes with a 60 days evaluation mode. You should register on VMware website to get your free license key to come out of the evaluation mode. Once the ESXi is installed, you can either user vSphere Client on the Direct Console User Interface to administer the host.

VMware ESXi is based on hypervisor architecture that runs directly on top of a hardware as shown below.

1. Download ESXi server

Get the software from the VMware ESXi download page.

Following are the various download options available. Select “ESXi 4.0 Update 1 Installable (CD ISO) Binary (.iso)” and burn a CD.

• ESXi 4.0 Update 1 Installable (CD ISO)
• Upgrade package from ESXi Server 3.5 to ESXi Server 4.0 Update 1
• Upgrade package from ESXi Server 4.0 to ESXi Server 4.0 Update 1
• VMware vSphere Client and Host Update Utility

2. VMware VMvisor Boot Menu

Once you insert the ESXi CD and reboot the server, it will display a boot menu with an option to launch “ESXi Installer” as shown below.

3. VMware ESXi Installer Loading

While the installer is loading all the necessary modules, it will display the server configuration information at the top as shown below. In this example, I was installing VMware ESXi 4.0 on a Dell PowerEdge 2950 server.

4. New ESXi Install

Since this is a new installation of ESXi, select “Install” in the following screen.

5. Accept VMware EULA

Read and accept the EULA by pressing F11.

6. Select a Disk to Install VMware ESXi

VMware ESXi 4.0.0 Installer will display all available disk groups. Choose the Disk where you would like to install the ESXi. It is recommended to choose the Disk0.

7. Confirm ESXi Installation

Confirm that you are ready to start the install process.

8. Installation in Progress

The installation process takes few minutes. While the ESXi is getting installed, it will display a progress bar as shown below.

9. ESXi Installation Complete

You will get the following installation completed message that will prompt you to reboot the server.

10. ESXi Initial Screen

After the ESXi is installed, you’ll get the following screen where you can configure the system by pressing F2.

In the next article, let us review how to perform the initial ESXi configuration.

August 2014: Publishing Content to Multiple Sites, Manage from a Single Location

A growing organization had requirements for ongoing communications to client teams compromised of internal employees as well as customers on the client side. Communications were coming from varied sources on a multitude of channels without uniformity and often without adherence to corporate standards.

One of the channels the company was using to communicate to these teams was the individual, secure portals they were employing for collaboration – uploading content and sharing of information.

This premise of publishing to these portals was ideal but the effort often took days to complete and so the organization approached Abel Solutions for help minimizing the time to author and publish content via an individualized, secure space.

A major requirement was that a content editor would need to manage company announcement content in a single location publishing to many consuming sites. The author should be able to set an expiration date, tag an announcement as active or archived as well as limit the number of announcements displayed at a time. The company envisioned their announcements as an image with a short description. Upon clicking on the announcement title, it would render the full article for reading. This required the company to restrict authoring content to a subset of employees.

To simplify content management, there was an expectation that someone could author once and walk away—not having to reproduce the same information multiple times. SharePoint 2013 proved to be an ideal solution for their portals and for increasing productivity, eliminating administrative overhead, securing content, and publishing compliant content to all client sites with a single click of the mouse.

Solution
The SharePoint 2013 Product Catalog feature was implemented to achieve this goal. In this article, you will learn how to implement a product catalog solution for publishing content from a single location out to multiple site collections.

NOTE: This solution was designed and implemented for SharePoint 2013 Enterprise [On-Premise].

Implementation Requirements:

1. SharePoint Server 2013 Enterprise [On-Premise]
2. At minimum, two site collections – an authoring site and publishing site
3. Managed Metadata Service
4. Administrative permissions

Authoring Site
Let’s begin by designing the Authoring site collection that will be used to manage content for the announcements (i.e., image library and announcement lists).

1. Create a site collection
2. Go to Site Settings
3. Under Site Collection Administration, select Site Collection Features
4. Find Cross-Site Collection Publishing, click Activate

Next, let’s create a Term Set that will be used to determine whether an announcement is Active or Archived.

1. Access the Term Store Management Tool via Site Settings.
2. Under Site Administration, select Term Store Management
3. Create a term set with terms defining announcements Active or Archived.
4. For this term set, under the Intended Use tab, select Available for Tagging.
5. Select Save.

In the meantime, proceed to create a SharePoint list for managing announcement content. Create an announcements list creating and adding appropriate columns as needed. It is important to remember the columns and their associated Managed Properties for search capabilities. Managed Properties are what the Search Engine uses to find information or values, not columns themselves. Map a field to the term set created above.

1. Access the List Settings, the select Catalog Settings.

2. Select Enable this library as a catalog and other appropriate settings (i.e., Navigation Hierarchy) and fields as necessary.

Now, you’re ready to inform SharePoint 2013 Search that new columns are available. Through Central Admin or from List Settings > Reindex List, continue to initiate a Full Crawl.

Publishing Site
As for the Publishing Site, create a site collection with preferred template of choice. Accessing Site Settings > Manage Catalog Connections.

Click Connect for your published Catalog

Designate desired location to publish announcements, insert a Content Search Web Part onto the page. Select Change Query to build your query for search results. In the web part panel, select a Display Template best fit for your needs. In some instances, you may have to create a custom Display Template if out of the box options are not sufficient. Select the appropriate Property Mappings for available fields.NOTE: Please remember to restrict the search results on only the published catalog.

Summary
If there is a need to manage content from a centralized, secure location to other information websites in a time-efficient way, SharePoint 2013 Product Catalog feature is an excellent, easy-to-implement solution. This solution lessens the strain on resources responsible for the process of managing content to multiple websites.

This TOTM was contributed by SharePoint Consultant, Recortis Echols.

https://technet.microsoft.com/en-us/library/jj635883.aspx

http://www.asp.net/mvc/overview/deployment/visual-studio-web-deployment/deploying-to-iis

When it comes to Linux servers, Debian and Ubuntu are probably two of the more popular distros out there. Since Ubuntu also is the most popular standard consumer OS, let’s compared Ubuntu to Windows Server 2012 to figure out which is right for your business.

Whether you are considering switching from Linux to Windows, or even are currently using Windows Server but thinking about Linux over an upgrade to Server 2012 – this will help give you an idea of what both are about.

The overview might miss a few big hitters or features, but it at least helps paint a picture.

Let’s start with Ubuntu:

UBUNTU FOR SERVER

Ubuntu has become a big force in the Linux world, despite the fact that many Linux purists don’t care much for the UnityUI that has brought more of a mainstream look and feel to Linux.

Some of the best features for Ubuntu on Servers include the following:

Ubuntu Software Center

When it comes to finding programs for managing your server, USC makes life easier. The terminal still is the preferred way for doing many things in Linux, but this certainly comes in handy as well.

Raid Configurations

Making raid arrays is actually pretty cheap and easy in Ubuntu, thanks to the mdadm tool. You don’t need to use CLI and there is even a tool that tells you if the raid is degrading and will even help you rebuild the array.

File Sharing & Storing

Although you might be considering Linux (or currently using it) for your server, it is still more than likely that many, if not all, of your workstations will run on Windows PCs. That is why the ability to share files and storage with Windows PCs is important – luckily Linux handles this well enough.

Security & Data Protection

Ubuntu has built-in firewalls turned on by default and has automatic security updates with file encryption support. There are also advanced features like password vaults and due to the nature of Linux, it is relatively malware and virus proof (though not completely).

Ubuntu Server Cost

If your organization doesn’t mind “being on its own” when it comes to customer support and aide, Ubuntu Server is totally free. Looking for support? Canonical offers it starting at $320 per server, per year.

WINDOWS SERVER 2012

Windows Server 2012 hasn’t been out for that long yet but it truly brings many great new features to the table. This time around there is the new MetroUI (as seen with Windows 8) alongside the traditional desktop. There is also a much stronger cloud focus in the latest server version, as well as many new features.

Windows Apps

From the Windows App Store to commercial Windows apps, there is a ton of software that works for Windows. While Linux also has quite a bit of software (most open-source), Windows Server has even more.

Raid Configurations

If Raid configurations are important to you, you’ll be happy to know thatMicrosoft put a lot of focus into this with Server 2012. The latest version of Windows Server includes a brand new feature called “Storage Pool and Spaces”.

What is that exactly? It is like a raid0 but without needing to strip the data across all disks. If one drive fails, you simply replace it and keep your data. Unlike raid-5 it doesn’t take half the space for backup drives and also utilizes the very efficient ReFS file system.

File Sharing & Storing

There probably isn’t much to say here, sharing and storing is ultra-simple with Windows Server 2012. Ubuntu does a good job here, but Server 2012 does a better one.

Security & Data Protection

Windows Server 2012 goes along way into making the experience more secure than past versions of Windows Server, merging their security suit into a comprehensive anti-virus/malware system called Windows Defender. There is also Bitlocker Protection to encrypt your data.

All in all, this is one of the most secure Windows experiences to date, but Linux is admittedly stronger in this aspect.

Other Unique Windows Advantages

As seen in one of our other articles, some of the more unique aspects of Server 2012 includes its major push towards Hyper-V, its ability to turn off and on a GUI at will, and a unique ability to ‘stream’ intensive apps to low power Windows devices – including making it possible to run x86 desktop apps on your network even on Windows RT devices like the Surface RT.

Price

Depending on the version your business needs, you are likely talking about close to $1000, if not tons more than that. Obviously if you are a DIY kind of organization that doesn’t need the customer support, Ubuntu is a lot cheaper.

While Ubuntu (and any Linux server distro for that matter) has a lot to offer, Microsoft also has many unique features that make it work great in an existing Windows environment. Ultimately it is up to you to decide which OS works better for your business, though.

You might believe that dumping Windows and switching to Linux is a difficult task, but the change in thought and the perception of that switch are the most difficult. If you’ve attempted an upgrade from Windows XP to Windows 7, you know what pain is.

Business owners find that Linux, for what was once a “niche” operating system, provides the necessary components and services on which many rely. Linux continues its entry into the world’s largest data centers, onto hundreds of thousands of individual desktops, and it represents a near 100 percent domination of the cloud services industry. Take the time to discover Linux and use it in your business. Here are ten reasons to give Linux at least a second look:

1. Commercial Support

In the past, businesses used the lack of commercial support as the main reason for staying with Windows. Red Hat, Novell and Canonical, the “big three” commercial Linux providers, have put this fear to rest. Each of these companies offers 24x7x365 support for your mission-critical applications and business services.

2. .NET Support

Businesses that have standardized on Microsoft technology, specifically their .NET web technology, can rely on Linux for support of those same .NET applications. Novell owns and supports the Mono project that maintains .NET compatibility. One of the Mono project’s goals is to provide businesses the ability to make a choice and to resist vendor lock-in. Additionally, the Mono project offers Visual Studio plugins so that .NET developers can easily transfer Windows-based .NET applications without changing their familiar development tools. Why would Novell and others put forth the effort to create a .NET environment for Linux? For real .NET application stability, Linux is a better choice than Windows.

3. Unix Uptimes

Linux stability offers business owners the peace of mind that their applications won’t suffer lengthy outages due to operating system instability. Linux enjoys the same high uptimes (often measured in years) that its Unix cousins do. This stability means that Linux can support your “99.999 percent available” service requirements. Rebooting after every patch, service pack, or driver change makes Windows an unstable and unreliable choice for those who need nonstop support for their critical applications and services.

4. Security

No operating system is 100 percent secure and Linux is no exception. But, Linux offers excellent security for its users. From regular kernel updates to an almost daily list of security patches, Linux code maintainers keep Linux systems very secure. Business owners who rely on commercially supported Linux systems will have access to every available security fix. With Linux, you have a worldwide community providing security fixes, not a single company with closed source code. You are completely dependent on the response of one company to provide you with timely security fixes when you use Windows.

5. Transferable skills

One barrier to Linux adoption was the idea that Linux isn’t enough like Unix, and therefore Unix administrators couldn’t successfully use their knowledge when making the switch to Linux. The Linux filesystem layout looks like any commercial version of Unix. Linux also uses a standard set of Unix commands. There are some Linux commands that do not transfer, but this is also true of any version of Unix.

Windows administrators might find that using a keyboard instead of a mouse is a difficult part of the transition, but once they discover the power of the command line, they might never click again. Don’t worry, though, for you GUI-bound Windows types, Linux has several desktop managers from which to choose–not just one.

6. Commodity hardware

Business owners will like the fact that their “out-of-date” systems will still run Linux and run it well. Fortunately for Linux adopters, there’s no hardware upgrade madness that follows every new version of the software that’s released. Linux runs on x86 32-bit and 64-bit architectures. If your system runs Windows, it will run Linux.

7. Linux is free

You may have heard that Linux is free. It is. Linux is free of charge and it is free in the sense that it is also free of patents and other restrictions that make it unwieldy for creative business owners who wish to edit and enhance the source code. This ability to innovate with Linux has helped create companies like Google, who have taken that ability and converted it into big business. Linux is free, as in freedom.

8. Worldwide community

Linux has the support of a worldwide community of developers who contribute to the source code, security fixes and system enhancements. This active community alsoprovides businesses with free support through forums and community sites. This distributed community gives peace of mind to Linux users, because there’s no single point of failure and no single source for Linux support or development.

9. Linux Foundation

The Linux Foundation is a corporate collective of platinum supporters (Fujitsu, Hitachi, HP, IBM, Intel, NEC, Novell and Oracle) and members who, through donations and membership dues, sponsor Linus Torvalds and others who work on Linux full time. Their purpose is to “promote, protect and standardize Linux to fuel its growth around the world.” It is the primary source for all things Linux. The Linux Foundation is a big positive for Linux users and adopters because its existence assures continued development of Linux.

10. Regular Updates

Are you tired of waiting for a Windows service pack every 18 months? Are you also tired of the difficulty in upgrading your Windows systems every few years because there’s no clear upgrade path? (Ubuntu Linux offers new, improved versions every six months) and long-term support (LTS) versions every two years. Every Linux distribution offers regular updates of its packages and sources several times per year and security fixes as needed. You can leave any upgrade angst in your officially licensed copy of Windows because it’s easy to upgrade and update Linux. And, the best part? No reboot required.

If you’d like to give Linux a try, there are several distributions that are free to download and use without the need for any commercial support contract:

• CentOS – Red Hat Enterprise Linux-based free distribution

• Ubuntu – Free, enterprise Linux distribution (Commercial support available).

• Fedora – The Fedora Project is the free, community-supported version of Red Hat Linux.

• OpenSUSE – The free, community-supported version of Novell’s SUSE Linux.

• Debian – The parent distribution for many Linux distributions including Ubuntu andLinux Mint.

You can find information regarding switching from Windows to Linux through the Linux Foundation or any of its platinum members. When it comes to increasing your efficiency, saving money, and providing non-stop services to your business and its customers, how many reasons do you need?

It’s not uncommon for us to receive requests to build, maintain and/or host Drupal websites on Windows servers.

Here are the main reasons why:

Familiarity – The client is aware of the Windows brand and trusts it.

Experience – The client has existing IT staff, well versed in administrating and maintaining Windows servers.

Costs – The client wants to make use of their existing Windows environment to save costs.

Integration – The project needs integration between a Drupal solution and Windows specific software such as a back-end office, ERP or CRM.

Considerations

Setting up Drupal on Windows server might seem like the logical solution, but here are a few things to consider when considering deploying Drupal on a platform that is not naturally optimized for this use:

• If set-up, deployment, development and testing tasks take longer, then development costs will be higher.
• If core updates, security patches and contributed module work takes longer to implement, then ongoing maintenance costs will be higher. If these tasks are skipped because they’re difficult to implement efficiently, then the chances of a security breach are increased.
• Drupal is a big beast and performance optimization is essential to a successful project. Slow loading pages increase friction, create stress, cause abandonment, and may affect your visibility in Google search.

Challenges

1. Development Skills Crossover

The Drupal community now numbers over a million individuals (based on active drupal.org member accounts).  Let’s be conservative and say that a quarter of that number is developers.

That’s a pretty healthy talent pool to dip into but is dwarfed by the number of skilled Windows technicians there are out there.  The problem is that there is not as much overlap as you might hope between the two.

No developers are well versed in every available technology, and most will focus on a number of associated technologies or disciplines, so a Drupal developer is likely to be highly up to speed with PHP, MySQL, Apache, Linux, and quite possibly other CMS’s, programming languages, etc that go together with other parts of that knowledge base, (e.g. WordPress, node.js, etc).

Likewise someone who has the skills to administrate a Windows server is quite likely to have experience with .NET, SQL Server, IIS, etc.

Unfortunately, the reverse is also true… An individual that’s dedicated a large amount of their career to open source development (which is probably the case if they’ve ended up being a Drupal Developer) is less likely to have the same skills as a Microsoft certified technician and vice versa.

Now I’m being careful to use words such as ‘likely’ here, since there will be many exceptions; highly talented and technologically agnostic individuals who can fit into both sets of shoes, but it’s safe to say these are very much a minority.

Which means that the large talent pool we were looking at before has now shrunk significantly.  There’s no reliable source of numbers here but I’m guessing that you’d be looking at most a few thousand individuals worldwide… many of whom will not be available to contract in.

You can imagine that it will be difficult to find the right people and probably more costly when you do.

If you do already have any tame Drupal Developers who are also Windows server administrators on the side then you’re in a fortunate position, but bear in mind that you would be in the same position if they become unavailable or the relationship breaks down.

2. Robustness

Drupal can and does run on Windows… There are almost certainly examples of Drupal sites on Windows out there.

However installations on Windows will be a significant minority.  Again it’s difficult to get hard numbers on this but I wouldn’t be surprised if less that 1% of Drupal sites run on Windows.

If the proportion is 1% then that means only 1% of site building and maintenance time is occurring on Windows based builds, and if we assume that Windows sites get the same average traffic then only 1% of end user interaction occurs on Windows hosted sites.

In addition, the vast majority of development of the code within Drupal itself and contributed modules and themes is likely to have been done on a LINUX/UNIX based operating system running Apache.

The upshot of this is that if there are issues with Drupal (or it’s modules/themes) that only appear on one operating system or server software then they are far more likely to have been found and solved already on say a LAMP stack then on Windows/IIS.

3. Optimization

During the process of putting together any software, including content systems such as Drupal, hard decisions have to be made based on performance.

An optimization that might improve performance in one set of circumstances could reduce it in another, and sometimes in these situations the developers end up join deciding that the improvement for the many trumps the degradation for the few.  In the case of Windows / Linux / Anything else, it’s pretty easy to imagine which system will end up getting the most benefit from optimization.

The individual decisions may not make much difference on their own, but the combined effect of many such performance decreases across a system as large as Drupal will likely be significant.

Additionally, there’s far more chance that performance issues that exist in Drupal on Windows may not have been identified / isolated / resolved purely because of the much smaller amount of time that can go into testing and developing on Windows.

4. Support

I’m talking about support from Drupal core developers, module maintainers and the community in general here.

If you encounter an issue that occurs in Drupal on Windows and is down to some low level difference in the way that Windows or IIS works, and it cannot be solved without Drupal core or a module being modified then you may find it difficult to get the help you need.

This could be because the relevant people (e.g. the maintainer of the module in question) don’t…

Have the relevant experience in the differences that are causing the issue to identify or resolve it

Have the facilities readily available and set up to replicate it

Regard it as a priority This may seem churlish but in reality if they have an issue queue with 20 open issues which could all effect 99% of users then how much priority do you think they will give to issues that only effect 1%?

Summary

Whilst Drupal can run on Windows, it may not be possible to run your particular project as efficiently on Windows over a Drupal tuned hosting stack or cloud instance such as Acquia.

If you can’t, then less efficiency means more development and maintenance cost, less reliability in certain instances, and a potential increase in friction between everyone involved in making the project a success.

Just to be clear, we don’t have any kind of anti-Windows agenda here. There’s no suggestion that Windows is in any way inferior as a hosting environment, you just need a very unique combination of expertise to match what’s possible with a Drupal specific environment that’s been designed to make workflow, performance and security as good as it can be.

The mysqldiff and mysqldbcompare utilities were designed to produce a difference report for objects and in the case of mysqldbcompare the data. Thus, you can compare two databases and produce a report of the differences in both object definitions and data rows.

While that may be very useful, would it not be much more useful to have the ability to produce SQL commands to transform databases? Wait no longer! The latest release of MySQL Utilities has added the ability to generate SQL transformation statements by both the mysqldiff and mysqldbcompare utilities.

To generate SQL transformations in either utility, simply use the –sql option to tell the utility to produce the statements.

Object Transformations with mysqldiff

If you would like to compare the schema of two databases (the objects and their definitions), mysqldiff can do that for you and produce a difference report in a number of formats including CSV, TAB, GRID, and Vertical (like the mysql client’s \G option).

However, its greatest feature is the ability to generate transformation statements to alter the objects so that they conform. Best of all, mysqldiff works on all object types including the ability to recognize renames so you can get a true transformation path for all objects. For even greater flexibility, you can generate the difference in both directions. This means you can generate transformations for db1-to-db2 as well as db2-to-db1 in the same pass. Cool.

The following shows an example of running mysqldiff on two servers where some of the objects have diverged. It also shows how you can generate the reverse transformation statements.

$ mysqldiff –server1=root@localhost –server2=root@otherhost \
–changes-for=server1 –show-reverse util_test:util_test \
–force –difftype=SQL
# server1 on localhost: … connected.
# server2 on localhost: … connected.
# WARNING: Objects in server1.util_test but not in server2.util_test:
# EVENT: e1
# Comparing util_test to util_test [PASS]
# Comparing util_test.f1 to util_test.f1 [PASS]
# Comparing util_test.p1 to util_test.p1 [PASS]
# Comparing util_test.t1 to util_test.t1 [PASS]
# Comparing util_test.t2 to util_test.t2 [PASS]
# Comparing util_test.t3 to util_test.t3 [FAIL]
# Transformation for –changes-for=server1:
#
ALTER TABLE util_test.t3
DROP COLUMN b,
ADD COLUMN d char(30) NULL AFTER a
ENGINE=MyISAM;
#
# Transformation for reverse changes (–changes-for=server2):
#
# ALTER TABLE util_test.t3
# DROP COLUMN d,
# ADD COLUMN b char(30) NULL AFTER a,
# ENGINE=InnoDB;
#
# Comparing util_test.trg to util_test.trg [FAIL]
# Transformation for –changes-for=server1:
#
DROP TRIGGER IF EXISTS `util_test`.`trg`;
CREATE DEFINER=root@localhost TRIGGER util_test.trg BEFORE UPDATE ON util_test.t1
FOR EACH ROW INSERT INTO util_test.t1 VALUES(‘Wax on, wax off’);
#
# Transformation for reverse changes (–changes-for=server2):
#
# DROP TRIGGER IF EXISTS `util_test`.`trg`;
# CREATE DEFINER=root@localhost TRIGGER util_test.trg AFTER INSERT ON util_test.t1
# FOR EACH ROW INSERT INTO util_test.t2 VALUES(‘Test objects count’);
#
# Comparing util_test.v1 to util_test.v1 [FAIL]
# Transformation for –changes-for=server1:
#
ALTER VIEW util_test.v1 AS
select `util_test`.`t2`.`a` AS `a` from `util_test`.`t2`;
#
# Transformation for reverse changes (–changes-for=server2):
#
# ALTER VIEW util_test.v1 AS
# select `util_test`.`t1`.`a` AS `a` from `util_test`.`t1`;
#
Compare failed. One or more differences found.

Generating Data Transformation with mysqldbcompare

The mysqldbcompare utility provides all of the object difference functionality included in mysqldiff along with the ability to generate transformation SQL statements for data. This means you can make sure your test or development databases are similar to your production databases or perhaps even your offline, read only databases match your online databases. Like mysqldiff, you can also get the reverse transformations at the same time. Very cool, eh?

The following shows an example of running mysqldbcompare to generate differences in data.

This is where the new ‘mysqldbcompare’ utility comes in handy. The mysqldbcompare utility uses the mysqldiff functionality (mysqldiff allows you to find the differences in object definitions for two objects or a list of objects in two databases) and permits you to compare the object definitions and the data among two databases. Not only will it find the differences among database objects and their definitions, it will also find differences in the data!

The databases can reside on the same server or different servers. The utility performs a consistency check to ensure two databases are the same defined as having the same list of objects, identical object definitions (including object names), and for tables the same row counts and the same data.

Some scenarios where mysqldbcompare can be employed include:

• checking master and slave for consistency
• checking production and development databases for consistency
• generating a difference report for expected differences among new and old data
• comparing backups for differences

Running the Utility

Let us take a look at the utility in action. Below are two examples of the utility comparing what should be the same database on two servers. I am using a simple detail shop inventory database used to manage supplies. It consists of two tables (supplier, supplies) and three views (cleaning, finishing_up, and tools).

In the first example, we see an example where the databases are consistent. When you examine the output, you will see each object is inspected in three passes. First, the object definitions are compared. Any discrepancies would be displayed as a difference in their CREATE statements. If the object is a table, a row count test is performed followed by a comparison of the data. Surely, if the row count test fails we know the data check will fail.

Note: This is the same output (and indeed the same code) that is used for mysqldiff. The mysqldbcompare utility has all of the same features with respect to difference type presented (unified, differ, and context) which you can select with the same option named ‘–difftype’.

mysqldbcompare –server1=root@localhost –server2=root@backup_host:3310 inventory:inventory
# server1 on localhost: … connected.
# server2 on localhost: … connected.
# Checking databases inventory on server1 and inventory on server2


Defn Row Data
Type Object Name Diff Count Check
—————————————————————————
TABLE supplier pass pass pass
TABLE supplies pass pass pass
VIEW cleaning pass – –
VIEW finishing_up pass – –
VIEW tools pass – –


Databases are consistent.


# …done

Normally, the mysqldbcompare utility will stop on the first failed test. This default means you can run the utility as a safeguard on data that you expect to be consistent. However, if you suspect or know there will be differences in the database objects or data and want to run all of the checks, you can use the ‘–run-all-tests’ option. This option will run the tests on all objects even if some tests fail. Note that this does not include system or access errors such as a down server or incorrect login – those errors will cause the utility to fail with an appropriate error message.

In the second example, we expect the databases to be different and we want to know which data is different. As you can see, the utility found differences in the object definitions as well as differences in the data. Both are reported.

mysqldbcompare –server1=root@localhost –server2=root@backup_host:3310 inventory:inventory –run-all-tests
# server1 on localhost: … connected.
# server2 on localhost: … connected.
# Checking databases inventory on server1 and inventory on server2


WARNING: Objects in server1:inventory but not in server2:inventory:
VIEW: finishing_up
VIEW: cleaning


Defn Row Data
Type Object Name Diff Count Check
—————————————————————————
TABLE supplier pass FAIL FAIL


Row counts are not the same among inventory.supplier and inventory.supplier.


Data differences found among rows:
— inventory.supplier
+++ inventory.supplier
@@ -1,2 +1,2 @@
code,name
-2,Never Enough Inc.
+2,Wesayso Corporation


Rows in inventory.supplier not in inventory.supplier
code,name
3,Never Enough Inc.


TABLE supplies pass FAIL FAIL


Row counts are not the same among inventory.supplies and inventory.supplies.


Data differences found among rows:
— inventory.supplies
+++ inventory.supplies
@@ -1,4 +1,4 @@
stock_number,description,qty,cost,type,notes,supplier
-11040,Leather care,1,9.99,other,,1
-11186,Plastic polish,1,9.99,polishing,,1
-11146,Speed shine,1,9.99,repair,,1
+11040,Leather care,1,10.00,other,,1
+11186,Plastic polish,1,10.00,polishing,,1
+11146,Speed shine,1,10.00,repair,,1


Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11104,Interior cleaner,1,9.99,cleaning,,1
11056,Microfiber and foam pad cleaner,1,9.99,cleaning,,1
11136,Rubber cleaner,1,9.99,cleaning,,1
11173,Vinyl and rubber dressing,1,9.99,cleaning,,1
11106,Wheel cleaner,1,9.99,cleaning,,1
11270,Carpet cleaner,1,9.99,cleaning,,1


Rows in inventory.supplies not in inventory.supplies
stock_number,description,qty,cost,type,notes,supplier
11269,Microfiber spray on car wash towel,3,16.99,cleaning,,1
11116,Microfiber wax removal towel,3,16.99,waxing,,1
10665,Glass polish pad,3,10.00,polishing,,1


VIEW tools FAIL – –


— inventory.tools
+++ inventory.tools
@@ -1,1 +1,1 @@
-CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` = ‘tool’)
+CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `inventory`.`tools` AS select `inventory`.`supplies`.`stock_number` AS `stock_number`,`inventory`.`supplies`.`description` AS `description`,`inventory`.`supplies`.`qty` AS `qty`,`inventory`.`supplies`.`cost` AS `cost`,`inventory`.`supplies`.`type` AS `type`,`inventory`.`supplies`.`notes` AS `notes`,`inventory`.`supplies`.`supplier` AS `supplier` from `inventory`.`supplies` where (`inventory`.`supplies`.`type` in (‘tool’,’other’))


Database consistency check failed.

Take a moment to read through the report above. At the top of the report (the first object tested), we see a critical error in the suppliers table. Here we can see that there are two different names for the same supplier_id. All relational database theory aside, that could spell trouble when it comes time to reorder supplies.

Notice the report for the supplies table. In this example, the utility identified three rows that were different among the two databases. It also identified rows that were missing from either table. Clearly, that could help you diagnose what went wrong where in your application (or your data entry).

Lastly, we see a possible issue with the tools view. Here the view definition differs slightly. Depending the use of the view this may be acceptable but it is nice to know nonetheless.

What Does This All Mean?

If data consistency is important to you or if you need a way to quickly determine the differences among data in two databases, the mysqldbcompare utility is a great addition to your toolset. But don’t take my word for it – try it out yourself.

Download and Try It Out!

The MySQL Utilities project is written in Python and is a component of the MySQL Workbench tool. You can download the latest release of the MySQL Workbench here:

http://dev.mysql.com/downloads/workbench/

There are some limitations in this first release. Currently, if the storage engines differ among the tables in the compare, the object definitions will show this difference – which is exactly what you would expect. However, the utility will stop and report the object definition test as a failure. You can still run the data consistency check by using the –force option which instructs the mysqldbcompare utility to run all tests unless they fail from an internal exception (for example, the table files are corrupt).

Got Some Ideas or Want to See New Features?

One really cool feature would be if the utility generate SQL statements for synchronizing the data and objects. Would this be something you would want to see incorporated?

If you’re intrigued by this new utility and in your course of use find new features or new uses that you would like to see incorporated in future revisions, please email me and let me know!

Related Material

Th latest MySQL Utilities development tree (including mysqldbcompare) can be found here:

https://launchpad.net/mysql-utilities

http://sharepointpromag.com/sharepoint-2010/top-10-sharepoint-2010-configuration-mistakes-and-how-fix-them

https://www.leansentry.com/Guide/IIS-AspNet-Hangs

http://forums.iis.net/t/1147420.aspx?Intermittent+Slow+Response+Times

http://blog.fpweb.net/troubleshooting-sharepoint-sluggishness-server-side-issues/#.VhgNrROqpBc