XAMPP: Using MySQLDump to Backup MySQL on Windows Server 2003 and Windows 7

I couldn’t find anything specific for Backing up the MySQL Databases within XAMPP. There certainly isn’t a pre-configured routine available. In addition, MySQL Workbench (unlike its predecessor MySQL Administrator) does not provide any automated backup facility.

So, I decided to explore the use of “MySQLDump”, a command line routine that can be inserted into a batch file and executed as a “Scheduled Task”. “MySQLDump” is included as part of XAMPP and is found within “<path to XAMPP>\MySQL\bin\”.

Here is the basic syntax I used (from a command prompt):

<path to XAMPP>\MySQL\bin\MySQLDump –u root –p myrootpassword –result-file=”<path to Backup Folder>\MySQLDump.sql” –all-databases

I then copied this into a batch file (MySQLDump.bat) and used “System Tools => Scheduled Tasks” (Task Scheduler in Windows 7) to run the batch file each evening.

Of course, you must ensure that the “MySQLDump.sql” file itself is stored in a safe location and can be recovered in case of server failure / building loss.

Advertisements

MySQL: Referential Integrity

I recently wrote an article covering Referential Integrity within Microsoft SQL Server.

This article covers the same subject within MySQL Server. The principle of using “Foreign Keys” is exactly the same.

We will be using phpMyAdmin to add the key(s). This will be familiar to anyone who maintains a MySQL Database. For the purpose of this exercise, I made a “test” database containing a “parent” table and a “child” table (and added a small amount of test data).

MySQL Foreign Keys 006

I would recommend practicing on a test system. If you intend to make changes to a live / production database, ensure that the data and schema are fully backed up!

Using phpMyAdmin, select the Database you wish to work on.

The first stage of the process is to check that BOTH tables in the relationship are using the “InnoDB” storage engine (MyISAM will not work). Within phpMyAdmin, select each table in turn, then set: Operations => Storage Engine = “InnoDB”.

MySQL Foreign Keys 005

If you remember from my previous article, the “Foreign Key” is always added to the “Child” table. So, if you have an “Orders” Table and an “Order Lines” table, the “Foreign Key” should be added to the “Order Lines” Table.

Select the “Child” table and then go to: Structure => Relation View.

MySQL Foreign Keys 007

Our objective is to create a relationship between the “Parent_No” Field in the “Child” and “Parent” tables.

MySQL Foreign Keys 009

Remember, we are working on the “Relation View” of the “Child” table. From here it is a simple process of completing the “Foreign Key” (INNODB) fields for the “Parent_No” field (see screen capture above). I would recommend setting the “On Delete” and “On Update” actions to “Restrict” – this will prevent any parent records from being removed if there are any associated child records.

If you are feeling brave (and have backed up your system), you can test your changes.

I have two “Parent” records in my test system, both of which relate to a number of records in the “Child” table.

MySQL Foreign Keys 003

If I attempt to remove any of the Parent Records, I will now receive an error from the Database Engine (RDMS).

MySQL Foreign Keys 004

That’s basically it! The database will no longer permit the removal of records from the “Parent” table if associated records still exists within the “Child” table.

XAMPP 1.7.3 for Windows: Folder Structure

The following is a summary of the contents of each folder within a Windows XAMPP installation.

Folder Description Version Notes
Anonymous      
Apache Web Server Service 2.2.14 Configuration file is: “apache\conf\httpd.conf”
cgi-bin      
FileZillaFTP FTP Service 0.9.33 Configure using the XAMPP Console.
htdocs Web Site Pages    
install      
licenses      
MercuryMail SMTP Mail Server Service   A simpler alternative is to configure an ISP Mail Server within “php\php.ini”
MySQL MySQL Database Files 5.1.41 Configure using PHPMyAdmin
Perl Perl Scripting Language Folder 5.10.1  
PHP PHP Scripting Language Folder 5.3.1 PHP is enabled by Default. Configure within “php\php.ini”.
PHP\ext “DLL” Files   There are several “dll” files to be found with “php\ext” to support additional functionality (such as SQL Server). These are enabled / disabled within “php.ini”).
PHPMyAdmin MySQL Administration Tool 3.2.4  
Security      
Sendmail “Fake” Sendmail Application   Configure within “sendmail\sendmail.ini”. A simpler alternative is to configure an ISP Mail Server within “php\php.ini”.
Tmp      
WebAlizer Web Server Usage / Analysis Tool    
WebDAV      

 

Note: XAMPP 1.7.7 is the latest version that can be installed on Windows XP / 2003. All newer versions require Windows Vista / 2008 or newer.

XAMPP Control Panel & Event ID 1073

Just lately, I have been unable to “Log Off” from a Remote Session on my Windows 2003 Server (running XAMPP). It would leave a “1073” Event in the System Log (Failed to Restart / Shutdown Computer). A bit odd, since I am not trying to Shutdown the machine.

I’m not sure why this has started all of a sudden, but the XAMPP Control Panel seems to be causing the trouble. Just make sure it isn’t running when trying to Log Off.

XAMPP Control Panel 001

Migrate MySQL 4.1 to MySQL 5.x (XAMPP for Windows)

I recently moved a standalone MySQL 4.1.7 Server across to a brand new XAMPP Server Running MySQL 5.1.41. This post sets out the procedure I used.

Backup the Existing Database (on the Source Server)

  1. Load phpMyAdmin
  2. Select the database you wish to Migrate (make a not of the Collation Type)
  3. Select the “Export” Tab
  4. Check “Save as File” Click "Go”

Create an Empty Database (on the new XAMPP Server)

  1. Load phpMyAdmin
  2. Create an Empty Database (with the same name and collation as above)

Import the Database (on the new XAMPP Server)

  1. Within phpMyAdmin, Select the Empty Database (created above)
  2. Select the Import Tab
  3. Browse / Select the “Export File” created previously and Click "Go”
  4. Await confirmation of Successful Import **.

** Failure to create an Empty Database on the New XAMPP Server causes Error 1046 “No Database Selected” during the Import Operation.