MySQL via SSH for PHP

I was moving a site from one server to another which has a lot of database transactions, and I did not want to lose any. So at 2am I started the move, at this time the traffic is fairly quiet. Then I realised that neither hosts allowed remote access to MySQL without their super admin’s help. I didn’t like the idea of trying to sync the databases up after the fact so I created an SSH tunnel to the new DB. This meant I could have the old server’s PHP talking to the new server’s MySQL until the DNS propagation was complete.

To do so:

1. I exported a MySQL dump from the old DB server and upload it to the new server

2. In terminal (PuTtY for Windows fans) I SSH into the old server and from there created a port (3377) on the old server which pointed to the new server

[cc lang=”bash”]ssh -L 3377:127.0.0.1:3306 a_user@example.com[/cc]

3. I wanted to see if it worked so I run MySQL in a new terminal window (the existing terminal window needs to remain open, as the switch over of A-Record will not take long I am not worried)

[cc lang=”bash”]mysql -u dbuser -p -h 127.0.0.1 -P 3377[/cc]

4. MySQL on the old server was able to connect to the MySQL instance on the new server

5. Finally I needed PHP on the old server to connect to the MySQL on the new server while the propagation period is taking place

[cc lang=”PHP”]$db = mysqli_connect(‘127.0.0.1’, ‘dbuser’, ‘password’, ‘the_db’, 3377);[/cc]

And that is it!

Advertisements

Using WordPress Functions outside of WordPress

For one of the sites I manage there is something funny happening with the server time. The site uses WordPress and I really like the way WordPress $wpdb class works. In a few sites I manage I occasionally have scripts which preform routine functions which effect the content in WordPress. To use all of the WordPress functions you simply add ‘include_once(“wp-load.php”);’ to the top of you PHP script.

To track the time I created the following script and I have set up a CRON Job to run every minute to track the time on the server.

The PHP is:
[cc lang=”PHP”]include_once(“wp-load.php”);
global $wpdb;
$query=”INSERT INTO time_track (php_time) VALUES (‘”.date(‘Y-m-d H:i:s’).”‘)”;
$wpdb->query($query);[/cc]

By including the wp-load, I have full access to all of the WordPress functions. In this case I am using the $wpdb class which has reduced my code to 3 lines (could be 2 if I typed the query directly into the query function).

The table SQL is:
[cc lang=”SQL”]CREATE TABLE `time_track` (
`id` int(11) NOT NULL auto_increment,
`php_time` timestamp NULL default NULL,
`mysql_time` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1[/cc]

This is a great way to have stand alone scripts which interact with the WordPress install. I am sure that the wp-load is adding extra overhead, though it does not matter as only a CRON Job once a minute is calling the script.

Quick Tip – Google Font Directory

Sick of the 16 Web safe font? Well Google has a solution.

Google Font Directory is a list of additional fonts you can add to your website available for use, for free, and really simply. Check out Toolnames which is using the GFD.

To add a new font you just paste the embed code for the font in between your head tags, before your style-sheet and then you are able to use the additional font in your site while knowing it will work in Safari, FireFox, IE6+ and Chrome, on Windows and Mac (maybe Linux).

For instance I want to use Molengo I paste the below into my head tags

And then am free to use Molengo in my Font-Family calls in CSS.


h1 { font-family: 'Molengo', arial, serif; }

Quick Tip – Simple HTML5 Video

HTML5 is awesome. It extends the DOM to be ready for the future once broadband is wide enough to replace your old broadcast TV in full HD.

Video is obviously the most exciting element. Now you can directly interact with the video in the DOM which can mean you can make a fully, easily to update scripted site which does not rely on plugins.

There is a few things to note about HTML5 video:

  1. There is no standard video format so you will need to encode your videos using both MP4 and OGG Theora. Google has WebM which may become the standard. Use Handbrake to encode the MP4 version and ffmpegtotheora to encode the OGG. Google this, it will take you less then 5 minutes to encode your video in the formats.
  2. You will want to use a fall-back for older browsers. I like Flowplayer which is a Flash video player
  3. Progressively enhance your site. If you want to do some cool stuff with the video ensure that you still have a working site for those using a 9 years old browser.

If we want to have a video with is 843×480 which preloads, plays once it is buffered and contains controls we will use the below

<video width="843" height="480" preload autoplay controls>
 <source src="/video.mp4" type='video/mp4; codecs="avc1.42E01E, mp4a.40.2"'>
 <source src="/video.ogv" type='video/ogg; codecs="theora, vorbis"'>
</video>

That is exactly how simple HTML5 video can be. Of course you may want to add poster or change the settings.

Since we want fallback for IE we will use Flowplayer by embedding it after the other sources. This is a cool thing about the element is that it has fallback.

<video width="843" height="480" preload autoplay controls>
 <source src="/video.mp4" type='video/mp4; codecs="avc1.42E01E, mp4a.40.2"'>
 <source src="/video.ogv" type='video/ogg; codecs="theora, vorbis"'>
 
</video>

If you want a real in depth look at HTML5 video I recommend you read:

365 Days of Free PSDs

365psd is a simple concept, give away your old PSDs. Basically designers create amazing designs which clients do not like. So instead of discarding them to never see life the designers give the designs away for free.

Of course you cannot resell the designs, though who would?

The designs are simple great.

Installing WPMU on Media Temple VPS (Plesk) in 5 Minutes!

This is a quick post to help you install WordPress ? (WPMU) on Media Temple. This is a task that you would think would be very easy (and is with Terminal). MT go all out to advertise that they are super easy to use. This is not so! They use Plesk which is a quite a dog of a service compared to cPanel. I am sure there is going to be something great you can do with Plesk I don’t know about. This said for what I need it for it is very limited.

I am going to assume that since you are setting up WPMU that you can sign-up up to MT without assistance.

So you have gotten setup with MT and the first thing you notice is that the File Manager sucks!

  1. First turn on SSH access for Media Temple
  2. Open Terminal, Putty etc
  3. In Terminal type in
    ssh root@yourdomain.com
  4. Enter your password
  5. In Terminal type in
    cd /var/www/vhosts/yourdomain.com/httpdocs/
  6. In Terminal type in
    wget http://mu.wordpress.org/latest.tar.gz
  7. In Terminal type in
     tar zxvf wordpress-mu-2.9.1.1.tar.gz
    

    or what ever version downloaded (the ls command will show you all of the files)

  8. In Terminal type in
    chmod 777 /var/www/vhosts/yourdomain.com/httpdocs /var/www/vhosts/yourdomain.com/httpdocs/wp-content/
    
  9. In Terminal type in
    rm index.html
  10. Navigate you browser to yourdomain.com
  11. Enter in all your database information and you are away as Media Temple VHOSTS is pre-configured
  12. In Terminal type in
    chmod 755 /var/www/vhosts/yourdomain.com/httpdocs /var/www/vhosts/yourdomain.com/httpdocs/wp-content/

So in 12 easy steps you have WPMU on Media Temple.

WordPress just got better

So I just found another reason to love wordpress with the iPhone app for wordpress allowing me to post posts while out. This means that I can write posts in a lot of new places and give out a lot more random thoughts.

So I have to admit that it is not as easy to post on the iPhone as a normal keyboard. This means That the posts from the iPhone are more likely to be shorter or I just have to get better at it. Continue reading