MySQL Backup/Restore

I’ve been working with MySQL databases years. Since most of my projects have been fairly small, I’ve managed backups and schema updates manually using phpMyAdmin. Typically I will setup 3 separate server environments and databases: dev, test, and live.

User Scenarios

The scenarios that I would like to implement are:

  1. Create ‘dev’ database, tables, and optionally initial content.
  2. Optionally import existing content from ‘live’ to ‘dev’.
  3. Develop and test code using the ‘dev’ environment and database.
  4. Copy the ‘dev’ database to the ‘test’ database
  5. Optionally import existing content from ‘live’ to ‘test’
  6. Test using the ‘test’ environment
  7. Lock the ‘live’ database
  8. Backup the ‘live’ database
  9. Copy the ‘test’ database to the ‘live’ database
  10. Restore the ‘live’ content
  11. Unlock the ‘live’ database

MySQL supports doing all of these things.

Creating the Database and (optionally) Initial Content

Creating the database is done using CREATE TABLE. Using a PHP function to do this works well because then the structure of the table is documented and can be checked into the SCM.

Importing Existing Content

Development on the ‘dev’ site is easier if the ‘dev’ database contains valid data. If the ‘live’ and ‘dev’ databases reside on the same server then the content from the ‘live’ database can easily be copied into the ‘dev’ database using ‘INSERT INTO dbname.tablename (cols) SELECT cols FROM dbname.tablename’. If not, then the ‘live’ database will need to be unloaded to a file, then the file loaded into the ‘dev’ database as described for backup below.

Copying the Dev Database to Test

Once new code is developed and tested in the ‘dev’ environment it needs to be copied as-is to the ‘test’ environment. This is easily done using ‘CREATE TABLE dbname.tablename AS (SELECT * FROM dbname.tablename)’. Updated content can be copied from ‘live’ to ‘test’ as described above for copying data into ‘dev’.

Lock the Live Database

Although MySQL provides a locking mechanism, this is probably not the best approach in this case. A better approach might be to set a flag in the user login or identification code. I call this setting a ‘service mode’. Other users that try to interact with the site will be told that the site is down for service, and to try again later.

Backup the Live Database Content

MySQL provides a BACKUP and RESTORE commands, but these are marked as ‘deprecated’. Since the structure of the database is recorded in the code which creates the database, only the content needs to be backed up. This can be done using what MySQL calls ‘unloading’ and ‘loading’: “SELECT * FROM table INTO OUTFILE filename FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ ” and “LOAD DATA INFILE ‘filename’ INTO TABLE table FIELDS TERMINATED BY ‘,'”

Recreate the Live Database

Use the same code that was used to create the ‘dev’ tables to recreate the ‘live’ tables.

Restore the Live Database Content

As stated above, MySQL provides a RESTORE command but it is marked as ‘deprecated’. Since the structure of the database was recreated in the previous step, load the content using “LOAD DATA INFILE ‘filename’ INTO TABLE table FIELDS TERMINATED BY ‘,'”

Unlock the Live Database

This is done by turning off ‘service mode’.

Keyboard Mapping

I’ve been doing a lot of web programming using Eclipse on both my work Windows machine and my personal Macbook Pro. I’m finding the difference in the “Start of line” and “End of line” keys to be extremely annoying. So I’m looking to remap the Mac keystrokes on the external keyboard I use with the Macbook Pro to match those of my office Windows machine.
This post appears to provide the info I need to do this: http://www.erasetotheleft.com/post/mac-os-x-key-bindings
I may look a bit more though, and see about remapping the Windows keyboard to match the Macbook Pro instead 🙂

BorderContainer disappears when height=100%

I ran into a snag getting the tab container to work when specifying height and widths of 100%.

Solution: I eventually discovered that all 3 html, body, and the BorderContainer div must be set to 100%. I had not specified anything for html and body, and when I set the div tag to 100% the whole thing just went blank.

In addition, the BorderContainer will updating the size of its child divs (those marked with “region=”. It won’t however update any additional divs that are children of those. This tripped me up because I was trying to put an AccordionContainer within the left region div of a BorderContainer. The AccordionContainer height wasn’t being updated and was not working when setting the AccordionContainer to 100%. The solution was to eliminate the left region div, and set “region=’left'” in the AccordionContainer itself.

Now we know.

Starting Dojo Drag and Drop

Last night I began creating a lineup edit page. I’m hoping to use Dojo’s drag-and-drop functionality to make creating each week’s song lineup very easy:

On the left side I will display a list of song positions and names (Intro, 1st congregational, offertory, etc). Next to each item will be the song name.

On the right side I will display all of the songs in the database, and a link to add a new song.

The user will then drag songs from the list on the left onto the lineup position. Any positions not needed can be dragged out of the list, and will appear at the bottom in an ‘unused’ section. They can thus be dragged back if needed. Individual items can be dragged to rearrange the order.

The list of songs will include an MP3 link to allow previewing the song.

Last night I added the code for the lists, and a few lines of Dojo to enable dnd and the result was immediately impressive. With just a few additional statements added to the lists, I can now drag between the lists and within the lists to reorder.

I found a very helpful link which I’m working through: http://www.sitepen.com/blog/2008/06/10/dojo-drag-and-drop-1/

I’ll need to clean up the way it works though. I don’t want the songs to be removed from the song list when dragged onto the lineup. Sometimes the same song may be played twice. There is also some formatting problems due to different columns being displayed in the lineup and songs lists (tables). And there is no functionality yet. DND operations modify the appearance of the form, but don’t get written to the DB yet.

Subversion + Eclipse is wonderful

Ok, I initially had one small problem where I couldn’t commit a couple files. Other files worked just file. So I simply deleted and recreated the 2 files. All is working great now.

I also had created a small problem by adding Subclipse. I’m using Zend Studio for Eclipse 6.1, and it comes bundles with Subversive. So adding Subclipse caused ‘SVN’ to appear twice everywhere. I uninstalled Subclipse and the confusion is gone.

Having worked extensively with both ClearCase and Accurev at work, I wasn’t sure what to expect from Subversion, or how well it would integrate into the Eclipse environment. I must say that I am very impressed. This setup is working way better than what I’m used to at work, which is just standalone AccuRev. The Subversion integration with Eclipse (via the bundled Subversive) is very efficient. The ‘compare’ functions are very helpful, allowing quick selection of previous versions to help me remember what I’ve changed and when on a file-by-file basis.

I know that things can get difficult when managing merging of multiple programmers changes. Since I’m the only one updating this code, I haven’t had to try out Subversions functions for doing this.

Further Development on TheRockBand site

Having made great progress this week setting up subversion, I was anxious to get started this morning, only to have all kinds of weird failures.

It turned out that I had created a new directory when I checked out the code, but hadn’t switched the vhosts paths to point to it, nor the ZSE path info. So it took me awhile to figure that out.

Next I implemented the Song Add/Edit/Delete actions. Delete had a bug caused by the dependent table info not being correct. In this case I used the table name instead of the table class. I determined this by tracing down into the ZF code. When a Zend_Db_Table_Row is deleted, it automatically performs a delete of any dependent tables. This is cool, but means that if I specify dependentTables and referenceMaps then they better be correct.

I’ve been reading up on the Dojo drag-and-drop support, and will be using this for editing lineups. I’ll display a list of the possible lineup items (eg. Special, 1st congregational, etc), and song, and allow them to be dragged into a blank lineup matirx.

But first I will get based pages working so we can get the site back online this weekend.

Updating the website

Ok, so now that I am able to work with the code and interact with Subversion, it’s time to update the website.
1. SSH userid@therockband.org
2. CD therockband.org/live or CD therockband.org/test
3. svn checkout http://svn.therockband.org .

Note the ‘.’ at the end of the svn checkout command line. Without specifying a path svn will use the path in the repository, which in this case is svn.therockband.org which isn’t what we want. Also, if not careful, the files might go right over top of the repository itself (doing nothing).

To avoid confusion, I’m going to create separate directories for the ‘live’ and ‘test’. Now when I run ‘checkout’ I see all of the files getting copied. When I run ‘update’ I see a list of the files that needed to be updated and the new revision number.

It doesn’t get much easier than that.

Continuing with Subversion in Zend Studio for Eclipse

I’ve been making updates to the code. Now I need to check them into SVN. Since I have the SCPlugin installed, I simply use Finder, right-click on the top folder(s), and select ‘Subversion Commit…’. This recursively lists all files that have changed, and checks them in.

So now I’m wondering how to work with Subversion from within Zend Studio for Eclipse (ZSE).

It appears at this point that I’ve made a mistake by installing Subclipse. It appears that Subversive comes already installed with Zend Server CE, so now I see two ‘SVN’s listed in various places. It isn’t readily apparent that either is better than the other, so I’m going to uninstall Subclipse to avoid confusion. So now my Project Configuration shows:
Subversion Client Adapter (Required) 1.6.0.2
Subversion JavaHL Native Library Adapter (Required) 1.6.2.1
Subversive SVN Team Provider (Incubation) 0.7.7.I20090224-1900
SVNKit Library 1.3.0.5847

This appears to have fixed things. So now in the PHP Explorer I see a symbol to the left of each file if the file is out-of-sync with the repository, and a version number to the right of it. Right clicking on any file gives me all the subversion options under the ‘Team’ menu item, and Compare With and Replace With give me options for working with the repository. This is very cool.

Using Subversion in Eclipse

Having setup a Subversion repository on Dreamhost and imported all the existing code, I’m ready to start using it in my day-to-day programming. I’ve already installed the Subclipse 1.4.8 plugin, so let’s figure out how to use it.

To check out a working copy:
* Eclipse File->Import->SVN/Checkout Projects from SVN, next
* Create a new repository (since this is the first time), next.
* Enter the URL to the repository (http://svn.therockband.org
At this point I got an error “Unable to load default SVN Client”.

On OS X Subclipse requires JavaHL. This appeared to be installed, but maybe this is causing the problem. So I downloaded the Universal Mac package from CollabNet (http://www.open.collab.net/downloads/community/). This appears to be a newer version 1.6.2.
Before continuing with the install, I disabled the existing Subversion plugins in Eclipse (everything except Subversive, which indicated that it was required by the Zend Studio CE.
Installation put a copy of Subversion in /opt/subversion.
Now I installed Subclipse in Eclipse from the 1.6 Subclipse path.

Now I can open the SVN Repositories Perspective. However, the ‘Synchronize’ function isn’t working. I’ll leave that for tomorrow.

Setting up Subversion

Having downloaded and installed subversion, I’m ready to set it up for use with all of my website code. Since I’m working on a MacBook Pro, I’ll be using Terminal’s command line interface.

The svn-book available from http://subversion.tigris.org/ is a very useful document.

DreamHost supports subversion, so I’ve setup a repository there and a subdomain. I can access it at svn+ssh://userid@therockband.org/home/userid/svn/TRBSource. On DreamHost user’s must be specifically created or granted permission to access Subversion, so I added permissions to my main ID and created a couple additional IDs with subversion permission.

DreamHost FAQs list instructions for setting up credentials to allow accessing the site without specifying password every time (SSH#Passwordless_login). I set this up and provided the password one time. Now my accesses can be done quickly without being prompted for a password each time.

The initial set of files are added to the repository using the svn import command:
cd /Users/Ron/Sites (the documentroot folder on my MBP)
svn import newrockband2 svn+ssh://userid@therockband.org/home/userid/svn/TRBSource -m “Initial import”
During the process a message is displayed indicating each file being added.

I can now ‘list’ the files using:
svn list svn+ssh://userid@therockband.org/home/userid/svn/TRBSource
to verify that everything is there. The list shows that ‘application/’, ‘public/’, and ‘tests/’ folders were added. These are the contents of my ‘newrockband2’ folder. The folder itself was not added.

So now I rename the original newrockband2 folder, create a new one, and creating a working copy in the new folder using ‘svn checkout …’ on all 3 directories (from within the new directory).

Ok, so now I realize that this isn’t the layout that I really wanted. I didn’t create the recommended ‘trunk’, ‘branches’, and ‘tags’ directories first. So I’ll need to create these directories, and then move my new directories to trunk/. So a quick check with ‘svn help’ shows ‘move’ and ‘mkdir’ commands (among others). ‘svn help mkdir’ and ‘svn help move’ give info on using these commands. However, I’m get an error when trying to use mkdir telling me that I haven’t setup environment variables to display/edit log messages. So for now I’m going to leave it alone. I’ll add/move those later.

First of course I make sure that my backups are up-to-date. I’m using Time Machine, so I verify that it has completed a recent backup. Now I’m going to cleanup my Sites folder, deleting a lot of old stuff (it’s backed up, right?), then check-out the code I just put there.

Having previously installed SCPlugin, now when I look at my newrockband2 folder in Finder I see green checkboxes next to the directories 🙂

However, browsing to the new copy displays an error message complaining that it cannot open the log.txt file. So I locate the log file and change permissions ‘chmod 777 log.txt’ and all appears to be well now.

Now I need to check out the code to the live site (new.therockband.org). This is described in http://wiki.dreamhost.com/Subversion#Gotchas. So I SSH into the site (ssh userid@therockband.org):
cd therockband.org
svn checkout http://svn.therockband.org

Browsing to the site now, I see that the DB path is incorrect (127.0.0.1). The APPLICATION_ENV is specified in the .htaccess file, so I edit it using pico. There appears to still be an error, which I’ll investigate tomorrow. But for now it appears that Subversion is working, and can directly update live site. Tomorrow I’ll investigate using it to deliver to a test site also.