SSH port forwarding/tunneling for MySQL connection
January 2nd, 2008Create an account on the remote MySQL database server.
useradd -s /bin/false remote_user mkdir /home/remote_user/.ssh touch /home/remote_user/.ssh/authorized_keys chown -R remote_user:remote_user /home/remote_user/.ssh chmod 755 /home/remote_user/.ssh chmod 600 /home/remote_user/.ssh/authorized_keys
Add MySQL permissions in the remote MySQL database to allow user connections from localhost.
USE mysql; GRANT ALL ON db.* TO database_user@127.0.0.1 IDENTIFIED BY 'database_pass'; FLUSH PRIVILEGES;
Now, on the local server (as root) create an RSA key pair to avoide the need for passwords for remote_user. (Simply hit enter for each question encountered.)
ssh-keygen -t rsa
Now transfer the public key file to the remote server from your local server.
scp /var/root/.ssh/id_rsa.pub root@remote_server.com:/tmp/local_server.local_rsa.pub ssh remote_server.com cat /tmp/local_server.local_rsa.pub >> /home/remote_user/.ssh/authorized_keys
On the local server, create an SSH tunnel to the remote MySQL database server using the following command.
ssh -fNg -L 3306:127.0.0.1:3306 remote_user@remote_server.com sleep 9999
To use this from PHP you would simply do this:
<?php ... $remote_server_mysql = mysql_connect( "127.0.0.1", "database_user", "database_pass" ); mysql_select_db( "database", $remote_server_mysql ); ... ?>
Posted in programming, php, OS, linux, networking, Databases, MySQL, Quick Tips
Top Of Page | 1 Comment »Using the UNIX tail -f command to watch log files
December 4th, 2007When troubleshooting a problem in Apache it is a pain to open the log file, find an error, close the log file, and then open it again after you perform some action. This is why ‘tail’ is so handy.
Simply use the command below to view the last 100 lines of a log file, and as new entries are added to the file they are automatically displayed on the screen.
tail -f /the/file/name -n -100
Plesk backup problem
November 27th, 2007I had a problem backing up domains on a server and received the following error message:
Error: Unable to create backup session: Specified file is not accessible
Finally i solved this problem!!!! it was all about wrong permission in some folders!
check that the folders ‘/var/lib/psa/dumps’ and ‘/var/lib/psa/dumps/tmp’ are chmod to 777
Change/update time on Linux
November 9th, 2007Sets whether the hardware clock is stored as UTC or local time.
/etc/sysconfig/clock
Symlink /etc/localtime to /usr/share/zoneinfo/… to set your timezone.
To set the current system date/time.
date MMDDhhmm
To set the hardware clock.
/sbin/hwclock --systohc
Javascript disable right click and image tool bar
September 26th, 2007A recent request from a client was to make life more difficult for his competitors to steal images from his site. He was worried that they could save the images using the Image Toolbar in newer versions of Internet Explorer, and by using the right click menu on the page. For those of you that wish to stop the right clickers from stealing your code, or from stealing images, here is a quick and easy way to make life more difficult for them.
To disable the Internet Explorer Image Toolbar for an entire page add this meta tag:
<meta http-equiv="imagetoolbar" content="no">
To disable the Internet Explorer Image Toolbar for an individual Image do this:
<img src="image.gif" width="250" height="250" galleryimg="no">
Now to handle those who love their “right-click”. For the entire page:
<body oncontextmenu="return false">
And for individual images:
<img src="image.gif" width="250" height="250" oncontextmenu="return false">
CSS style sheet switcher using Prototype
September 25th, 2007A recent project had a requirementfor a style switcher allowing a visitor to control font size on the site as needed. (Many do not know how to change it through a browser, and most sites are not designed to allow for font size changes without completely trashing presentation.)
Because the project in question was a dynamic site I wanted to make a style sheet switcher that didn’t force a page reload and it seemed like most examples on the net required a page to reload with a new stylesheet. (After PHP or some other scripting language decided which css sheet to render the page with.) This meant additional calls to the server to repopulate data grids and other info, after re-running a query and parsing in PHP. To me this was truly unacceptable load on a server to simply change font sizes on a page.
Posted in programming, php, javascript, Quick Tips, css, html, prototype
Top Of Page | Leave a Comment »PHP failing to upload images on a Plesk managed server
September 14th, 2007I recently upgraded a few of my Linux servers from Plesk 7.5 to 8.2. Everything seemed to be fine, however calls started coming in about PHP forms not uploading images correctly.
My team and I were troubleshooting this problem for a few days trying to narrow down the problem. We looked at folder permissions, script permissions, database connectivity, and almost anything else we could think of. Then we stumbled across the error message given by PHP (who would have thought to look at the error message?) that said function move_uploaded_file will not work in safe_mode.
Now looking at the PHP info the main php.ini was set with safe_mode off. However, I found that the httpd.conf for the individual domains on a Plesk managed server carries a declaration for PHP safe_mode, and it was turned on. Upon further investigation I found that in the Plesk interface for the domain there is a checkbox beside the PHP setting that allows safe_mode to be turned on and off.
Mission accomplished!
Posted in programming, php, OS, linux, servers, Quick Tips, Plesk, apache
Top Of Page | Leave a Comment »Adding mssql capability to PHP5 on CentOS
September 6th, 2007I had a need to connect to MSSQL using PHP version 5 from a CentOS 5 server. To do this I needed FreeTDS and the module mssql in PHP. After a diligent search I found that there was no quick and easy way to install the mssql module, like “up2date install php-gd” to get GD to work from PHP.
I found a few places that had ideas and hints to make this work, but finally used the directions I found at http://www.howtoforge.com/installing_php_mssql_centos5.0 (slightly altered to fit my means) and will duplicate here so I can easily find it later.
At first I thought I needed to recompile PHP, but as it turned out I only needed to pre-compile it so that I could fetch the mssql.so, and then manually edit the php.ini to use it.
Posted in programming, php, OS, linux, zend, servers, Databases, MSSQL, Quick Tips, apache
Top Of Page | Leave a Comment »Using JOIN within the Zend Framework
August 16th, 2007I found documentation very sparse on the subject of using JOIN with the Zend Framework. So i set out on a quest of many hours figuring out how to get it to work. Here is what I ended up with.
I do not claim that this is the best way to do it, or that it is correct, but here is how I solved this and got JOIN working within Zend Framework.
Apache parse PHP from within files using ‘.html’ extension
August 7th, 2007Recently I had someone that needed to move their site to a new server. The site was entirely created with the files using the .html extension, but had embeded inside.
I simply cleared this up by adding the following line to the httpd.conf, and after a reboot of Apache i was set to go.
AddType application/x-httpd-php .html
If you were to need this type of action for files using the .htm extension you would do the same thing, but replace the .html to .htm in the line.
Headphone problem with Ubuntu 7.04 (Feisty Fawn)
July 11th, 2007I was having a sporadic problem with my headphone jack in my new Dell E1505 laptop that came pre-loaded with Ubuntu 7.04 (Feisty Fawn), using ALSA. The problem was that when I plugged in my headphones, sometimes they worked, and other times nothing happened. No sound from my headphones at all.
Here is how I fixed the sound in my Dell laptop…
Apache redirecting to home directory using userdir module
July 10th, 2007Development is made easier by running a full development environment on your workstation. I personally run a full LAMP (Linux, Apache, MySQL, PHP) environment that allows me to test what I am developing on my workstation. In this case I am on my new Dell Inspiron E1505n running Ubuntu 7.04 Feisty Fawn.
One of the problems with doing this is the need to play with permissions on ever site in the server root. So instead I am using ‘userdir’ to keep the sites for easy development and testing. In other words I use apache to pull the site from my home directory for display in my browser. Here is how I did it.
Posted in programming, OS, linux, networking, servers, Quick Tips, Ubuntu, apache
Top Of Page | Leave a Comment »Laptop not recovering from suspend mode (Dell with Ubuntu 7.04 Feisty Fawn)
July 9th, 2007I am pretty mobile, and find it a pain to boot up my laptop every time I need it. So instead I simply close it to put it into suspend mode, and simply open it and log in without the needed wait for booting up. (Even though Ubuntu loads very quickly.)
The problem is that sometimes the laptop does not recover, and I am faced with a black screen instead of my login screen. This forces me to force shutdown by hitting the power button, or forcing reboot by hitting Ctrl+Alt+Delete.
After some searching I found a remedy that seems to have fixed the problem. I simply added ‘noapic nolapic’ to my boot. Here is how I did it:
$ sudo vi /boot/grub/menu.lst
Then I found the “kernel” line that corresponds with my boot process, and appended ‘noapic nolapic’ to the end of it. Here is how that line looked when I was finished:
kernel /vmlinuz-2.6.20-16-generic noapic nolapic
Your line may differ slightly, but simply appending to the end will work fine.
Restarting Plesk base services
July 5th, 2007I recently had the need to restart Plesk on a server. Everything on the server was running just fine, but Plesk was not responding.
After some searching I found these handy lines that allowed me to get Plesk back up and running.
# /etc/rc.d/init.d/psa stopall
# /etc/rc.d/init.d/psa start
Note: Must be run as root, or su.
New Dell Inspiron with Preloaded Ubuntu 7.04 (Feisty Fawn)
June 27th, 2007Well, after a couple of weeks waiting my shiny new Dell Inspiron E1505 preloaded with Ubuntu 7.04 (Feisty Fawn) has finally arrived. First I must say that it took a LONG time. It is pretty common for Dell to take some time getting PC’s and/or Servers delivered after it is ordered. (IBM and others tend to get them out faster.) Second, after a few days with the new laptop, it was worth the wait.
Microsoft Bluetooth keyboard and mouse on Ubuntu 7 (Feisty Fawn)
May 26th, 2007I have a Microsoft Optical Desktop Elite Keyboard and Mouse for Bluetooth, and wanted to get them working on my newly installed Ubuntu 7.04 Feisty Fawn desktop. (This was my choice after the adventure of Windows Vista deciding it was not Genuine 1 1/2 months after install, even though it was.) See this article for the details.
I found the basics of this article on the Ubuntu forums, but found that I needed to adapt it a bit. So here is that article, with my changes/additions.
Goodbye Windows Vista
May 23rd, 2007Well, I installed Windows Vista 1 1/2 months ago. During that time I dealt with many driver problems, software issues, and quirks. But the end has come. Today Windows Vista decided it was not Genuine, and that I needed to activate it. Of course it is Genuine, and it had already been activated, so I put in a quick (HAH!) call to Microsoft to activate it after the automated method of doing it over the web failed.
Redhat Linux uptime command
May 16th, 2007The uptime command is very handy to get a snapshot of info about the servers time since last reboot, and load.
Command:
$ uptime
Sample Results:
20:43:04 up 40 days, 13:43, 6 users, load average: 2.03, 1.68, 1.5
Breakdown of results:
- The current time (20:43:04)
- How long the system has been running in days and hours (up 40 days 13:43)
- How many users are currently logged on (6 users)
- The system load averages for the past 1, 5, and 15 minutes (2.03, 1.68, 1.5)
This is the same information contained in the header line displayed by w and top command:
$ w
$ top
Note that w displays who is logged on and what they are doing.
The top command provides a dynamic real-time view of a running Linux/UNIX/BSD system.
Script memory usage, and displaying with PHP
May 15th, 2007I recently had a problem where a script written by someone else was stopping. There was not warning, error, or indication at all what the problem was. The script simply died as if the process ended.
After a bit of troubleshooting I found that the script was ending due to memory loss. So here is how I used PHP’s memory_get_usage() to troubleshoot through it.
Here is a chart I used for conversion, to simply display the numbers.
1 Byte = 8 Bit
1 Kilobyte = 1024 Bytes
1 Megabyte = 1048576 Bytes
1 Gigabyte = 1073741824 Bytes
Here is how I used echo to display the memory used in MB’s at different points in the script to troubleshoot.
echo "Memory Usage: " . (memory_get_usage()/1048576) . " MB n";
MySqlDump export and MySQL import
May 14th, 2007To export data as a backup you can’t beat mysqldump.
# mysqldump -u username database > database_2007-05-14.sql
Then to restore a database you would use:
# mysql -u username -p database < database_2007-05-14.sql
It doesn’t get any easier than that!
Getting differences between dates quickly in PHP or MySQL
February 7th, 2007I recently needed a way to figure out the difference, in days, between two dates. Here is how I did it.
Using PHP:
$expireDate = "2006-02-07";
$year = substr($expireDate, 0, 4);
$month = substr($expireDate, 5, 2);
$day = substr($expireDate, 8, 2);
$splitExpireDate = (mktime(0, 0, 0, $month, $day, $year));
$today = (mktime(0, 0, 0, date("m"), date("d"), date("Y")));
$difference = (($today) - ($splitExpireDate));
$convertToDays = ($difference/86400);
echo $convertToDays;
Using MySQL:
SELECT (TO_DAYS(expire_date) - TO_DAYS(CURDATE())) as days_expired FROM tablename;
Get past date or convert different date format with php
January 25th, 2007Quick way to figure out a past date in PHP.
echo date("Y-m-d", strtotime("20 days ago"));
echo date('Y-m-d',strtotime('12/15/2008'));
MySQL FIND_IN_SET
January 22nd, 2007I was faced with a field in the database that had a comma separated list of INT’s with a space after the comma. The application searched this field to generate the recordset to display on a web page.
First I tried using LIKE, with something along this line: ($state_id is being passed by PHP)
SELECT * WHERE `assoc_states` LIKE '%$state_id%';Well, as you can guess if I was searching for an INT less than two numbers I would get anything that had that digit in it. (Ex. - Searching for ‘%4%’ would also give me entries of 4, 14, 24, 34, 42, 44, etc.)
I may have been able to use RLIKE, but I didn’t fully understand it and there were no good examples of doing what I needed.
That was when I stumbled across FIND_IN_SET. Here is what MySQL.com has to say about it:
Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (”,”) character.
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); -> 2
Here is what I ended up with: ($state_id is being passed by PHP)
SELECT * WHERE FIND_IN_SET('$state_id', `assoc_state`)>0;
MySQL ADDDATE or DATE_ADD
January 15th, 2007This weekend I needed a couple of MySQL query items that were out of the ordinary, so I thought I would write about them.
ADDDATE or DATE_ADD both work the same, but came in very handy. I was struggling with an application where I need to have the date 42 days from now. PHP doesn’t really have anything that is easy to use, and after a few tries I decided to turn to MySQL. Below is what MySQL.com has to say on these:
When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the discussion for DATE_ADD().
mysql> SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
mysql> SELECT ADDDATE('1998-01-02', INTERVAL 31 DAY);
-> '1998-02-02'
Having Microsoft Fonts on Linux (Ubuntu)
January 10th, 2007I have been using Ubuntu in the office lately. However, I did not have the standard fonts that you would find on a Windows system for use in documents. So I simply used the following method to add them:
# sudo aptitude install msttcorefonts
For these fonts to display in 96dpi you may also need to add a line in your /etc/X11/xorg.conf:
# sudo vi /etc/X11/xorg.conf
Add this line in the Section “Device” somewhere before EndSection
Option "DPI" "96x96"
Then you will need to either restart X or reboot to apply the settings. After the restart/reboot you can use the following command to see if you are using 96dpi.
# xdpyinfo | grep resolution
