Tag Archives: mysql

Virtual Web Hosting on OSX Leopard

Recently I brought a new iMac for my home development, music, movies, games and general play things. For work I develop Web Applications and with my ISP I have a static IP address, so I decided to set up a small testing webserver. Since Apple has built in PHP and Apache2, this was pretty much a couple simple steps.

I used my post about Installing PHP5 and mySQL on Mac OSX Leopard to recompile PHP and mySQL, then I started setting up my Virtual Hosts.

Heres what I did from a fresh Install of Leopard;

Step One: Enable Apache Web Sharing

Open your System Preferences, then Select the Sharing option.

Enable the Web Sharing checkbox.

Enable Apache Web Sharing

Step 2: Install PHP and mySQL

Download and install PHP 5 and mySQL 5 and install using my guide @ Installing PHP5 and mySQL on Mac OSX Leopard.

Step 3: Enable Virtual Hosts

Next you have to tell apache that we are going to use ‘Virtual Hosts’ and not just the default document server.

I am going to explain how to edit this in TextMate, but if you don’t use TextMate ( and why not? ) in a later post I can explain how to use Terminal and PICO to do the changes.

Open TextMate, and go to teh file menu, and select Open…

Check the Show Hidden Files checkbox and navigate to

/private/etc/apache2/

and select the httpd.conf file.

Open your Apache httpd.config file

Now if you press the Apple ( Command ) + L kets which will bring up the Go to Line dialog box. Go to line 465.

TextMate 'Go to Line'

You will see the Virtual Hosts Include line.

Remove the # so that the file will now be included when Apache runs.

# Virtual hosts
Include /private/etc/apache2/extra/httpd-vhosts.conf

Save and close the file. You will be asked for your password.

Step 4: Create your Virtual Hosts

Still in TextMate go to the File Menu and Select Open…. This time you want to navigate to the Virtual Hosts file.

/private/etc/apache2/extra/httpd-vhosts.conf

Open your Virtual Hosts Config File

The first line to edit in this file is to remove the :80 on the NameVirtualHost *:80.

NameVirtualHost *:80

becomes

NameVirtualHost *

For each site you you are going to be serving, you need to add a VirtualHost block.



    ServerAdmin your@email.com
    DocumentRoot "/Library/WebServer/Documents/mysite.com"
    ServerName mysite.com
    ServerAlias www.mysite.com
    ErrorLog "/Library/WebServer/logs/mysite.com_error"
    CustomLog "/Library/WebServer/logs/mysite.com_access" common




    ServerAdmin your@email.com
    DocumentRoot "/Library/WebServer/Documents/mysite2.com"
    ServerName mysite2.com
    ServerAlias www.mysite2.com
    ErrorLog "/Library/WebServer/logs/mysite2.com_error"
    CustomLog "/Library/WebServer/logs/mysite2.com_access" common




    ServerAdmin your@email.com
    DocumentRoot "/Library/WebServer/Documents/mysite3.com"
    ServerName mysite3.com
    ServerAlias www.mysite3.com
    ErrorLog "/Library/WebServer/logs/mysite3.com_error"
    CustomLog "/Library/WebServer/logs/mysite3.com_access" common

Save this file, using your Admin Password.

Step 5: Restart Apache

Now we need to restart the Apache Web Server, you can do this the same as Step 1, disable Web Sharing then re-enable it.

Virtual Hosting Summary

Now you should be able to host more than one domain successfully on your Mac. Things to note are you must be pointing the A Record for your domain DNS Records to your IP Address, and you must point port 80 of your Router or internet gateway to your Mac Internal IP address, I would recommend. using a static Internal IP Address for this machine.

Using PHP exec to backup mySQL databases.

Backing up your site and files is a Must, and many servers have back-up options available to them. But one thing that is forgotten far too often when backing up your site, is backing up your database.

With a blog, like this and most wordpress blogs, the only part of the site to regularly change is the database. The site itself rarely changes other than the few uploaded photos.

Every Post, Comment, Trackback, registration all modifies the database. I like to take mywordpress DB from this online Blog, and dump in to my local copy so I have content, and comments/trackbacks where I test plugins/designs/hacks.

So here is a simple PHP script that will back up a whole database, and save the output as either a .sql file, or a compressed gunzip archive to a specified directory.

function mysql_backup($type,$host, $user, $password, $table, $path) {
	global $day;
	if($type == "gz"){
		// Gunzipp Output (.gz)
		$backup = $path.$table.'_'.$day.'.gz';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table gzip > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}elseif($type == "sql"){
		// SQL Outout (.sql)
		$backup = $path.$table.'_'.$day.'.sql';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}
	return true;
}

Here is an example of how to use it.
In a PHP file.

$day = date("l"); // Lowercase L
mysql_backup('sql','localhost', 'SQL_Username', 'SQL_Password', 'table', '/root/path/to/mysql/data/backups/');

The Function perametors are in order;

'sql' or 'gz'         -> Type of backup.
'localhost'          -> mySQL Server path
'SQL_Username' -> mySQL Username with all read/write privileges
'SQL_Password'  -> mySQL Password
'table'               -> mySQL Table to backup

// $day is set to name the backup,  I use 'l' to name the backup the full day ( monday, tuesday.. sunday)

Here is a full example to back up multiple databases, If used with a CRON, you can get a 7 day rotation of any number of databases.

< ?php

$day = date('l');

function mysql_backup($type,$host, $user, $password, $table, $path) {
	global $day;
	if($type == "gz"){
		// Gunzipp Output (.gz)
		$backup = $path.$table.'_'.$day.'.gz';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table gzip > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}elseif($type == "sql"){
		// SQL Outout (.sql)
		$backup = $path.$table.'_'.$day.'.sql';
		exec(
			sprintf(
				'/usr/local/mysql/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s',
				$host,
				$user,
				$password,
				$table,
				$backup
			)
		);
	}
	return true;
}

foreach(array("sheldons_blog","rogers_blog","janes_blog") as $table){
	if(mysql_backup('sql','localhost', 'SQL_Username', 'SQL_Password', $table, '/root/path/to/mysql/data/backups/')){
		echo('Table backed up. '.$table.'_'.$day.'.sql
'."\n");
	}else{
		die('Error backing up table.' .$table);
	}
}
?>

If you are planning on passing the information from a form, I suggest using PHPs escapeshellarg.

On line 8 of the function you will see I have stated the full path my mySQL, in most cases you will just need

mysqldump

or it may vary depending on your server setup, on one of my servers ‘mysqldump’ works, on this server I need

/usr/local/mysql/bin/mysqldump

Building a Completely Dynamic site using text files, PHP and no mySQL.

I spent a fair amount of time trying to help people on PHP forums such as webdeveloper.com : Sheldon and one thing I see on a regular basis is over complex systems for basic site structures with people who at this stage don’t have enough knowledge of PHP to build a secure structure.

Now don’t get me wrong, their not stupid, and I’m for sure not the greatest !

So, tonight, a sunday evening, nothing on the tele, and less of a social life lately it seams. I have spent 2 hours building a completely dynamic flat file web structure.

I built this for a couple of reasons, I have never built a system using a complete class that I have written from line 1. I have worked with classes, and extended classes, but never started from scratch. It was something to do, and.. It could be helpful to others, and that how I roll, I like to help out.

Flat file pages
Small footprint < 12k
No mySQL/ database needed
Simple integration
Customisable template design
Runs on Apache and IIS Severs
Supports Apache Mod ReWrite URLs
Secure againsts including ‘Bad Files’

Installation:

1:Drop it in the directory you wish to run the system.

2:Customise the ‘index.php’ file with your own HTML, making sure you keep the PHP.

3:Open ./includes/simpleSite.inc’, find the function ‘config()’ and check that all of the ‘$config[]‘ array values are correct. They should be 99% dynamic.

This uses a class to construct the functions, so required a late version of PHP 4, but PHP 5 is better If your current web host doesnt offer up to date software contact me via my web design & hosting business Inbox Design

This is offered as is, I am happy to help out with installing and custimizing the design, If you have any bugs or feature requests, just ask

Free Download: [download#2] -> simpleSitev0.2.zip (Update v0.2)

Free Download: [download#1] -> simpleSitev0.1.zip

Installing PHP5 and mySQL on Mac OSX Leopard

There has been a lot of posts on a lot of sites that explain how to get PHP and mySQL working & talking on OSX Leopard, but none that are fully step by step. So with thanks to The Busy Geek ( www.thebusygeek.com ), Here is a comprehensive guide.

You will need a very basic knowledge of using OSX Terminal ( Command Line Commands) and your root password.

Step 1:

Go to Developer.MySQL.com and download the copy that matches your Machine. In my case because I am on a G4 PowerPC iBook, I had to download the 10.4 version (Mac OS X 10.4 (PowerPC, 32-bit)) as there is no 10.5 version for PPC.

Once you have downloaded the package, install all 2 packages, and the preference pane.

Installing the MySQL packages.

Step 2:

Open Terminal ( Applications / Utilities / Terminal.app ) and log in as root;

sudo su

Enter your root password when prompted.
Navigate to your mysql directory;

cd /usr/local/mysql

If you view the directory you should see something like the following;

ls


COPYING			data		scripts
EXCEPTIONS-CLIENT	docs		share
INSTALL-BINARY		include		sql-bench
README			lib		support-files
bin			man		tests
configure		mysql-test

Next we start mySQL.

./scripts/mysql_install_db --user=mysql

It should run a whole lot of commands, starting with…

Installing MySQL system tables...

Once that has finished we add the mySQL User permissions

./bin/mysqld_safe --user=mysql &

Which will ( or should ) give this..

Starting mysqld daemon with databases from /usr/local/mysql/data

Now the mySQL root user’s password. and then confirm password and database location.

./bin/mysqladmin -u root password '**PASSWORD**'

./bin/mysqladmin -u root -h **IP_ADDRESS** password '**PASSWORD**'

Now, All going well, Lets hope you get this error:

./bin/mysqladmin: connect to server at '**IP_ADDRESS**' failed
error: 'Host '**IP_ADDRESS**' is not allowed to connect to this MySQL server'

Thats good news, This means mySQL is running and that is step 2 finished.

Step 3:

Next Step, We will install PHP, You think huh? Apple already has a copy of PHP pre-complied in OSX?
Yeah, true, but ( No Offense Apple ) its shit.

Still as root user:

cd /usr/local/

We want to download the newest build of PHP from Marc Liyanage’s site. Thank you to Marc, who has openly releasd and supported Great PHP builds for years! And totally for free!
* Make sure this path represents the newest version.

curl -O http://www2.entropy.ch/download/php5-5.2.5-6-beta.tar.gz

We have used cURL as OSX does not support wGet. Remember the -0.

You will see if download like this…

 % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
 51 85.2M   51 43.4M    0     0  47790      0  0:31:10  0:15:54  0:15:16 52563

Once the download has completed, uncompress it with this command:

tar -zxvf php5-5.2.5-6-beta.tar.gz

Making sure that the file name represents the downloaded file name.
(z = gunzipp, x = uncompress, v = view, f = force ).

View the dir;

ls

You should get a directory looking like this;

OpenSourceLicenses	include		mysql-5.0.51b-osx10.4-powerpc
OpenSourceVersions	lib		php5
bin			mysql		php5-5.2.5-6-beta.tar.gz

Go into the PHP5 Directory; Then view the dir.

CD php5

ls

bin			etc		info		libphp5.so	php.d
entropy-php.conf	include		lib		man		share

Then Copy the entropy-php.conf file in to the Apache2 Dir installed by Apple.

cp entropy-php.conf /etc/apache2/other/

Now we need to tell Apache to use our new version of PHP and not the original Apple build.

cd /etc/apache2/

Open the httpd.conf and comment out the following line.

LoadModule php5_module        libexec/apache2/libphp5.so

by placing a # in front of it.

#LoadModule php5_module        libexec/apache2/libphp5.so

To Save your changes press “ctrl x”, there will be a prompt to save, press “y”, then press “return” to save with the same filename.

Now restart Apache with this Command

apachectl restart

And we are done.

Installing PHP and MmySQL Summary

Begin writing your PHP/mySQL applications.

You can access your local Apache environment at two locations:

http://127.0.0.1/		/Library/WebServer/Documents/

or for your User account:

http://127.0.0.1/~username/		/Users/username/Sites/

Enjoy

Sheldon Lendrum

Development n More