Posts Tagged ‘mysql’

Ubuntu Server Setup Checklist

June 20th, 2009

I’ve now set up half a dozen or so Ubuntu Server installations over the past year or two.  For the last few, I created a checklist to make sure I don’t leave any of the smaller, less obvious things out.  I present it here, completely unmodified.  Note, this is more of a preliminary checklist, as it doesn’t include installation of Apache, MySql, PHP or any other programs such as Postfix.  Basically, I’ll perform these steps regardless of whether It’ll be a web or mail server.  These also assume you like the vi text editor, if not, you can substitute emacs, nano, or whatever your preference when neccesary.  Also, some of these are already done depending on your server host.

Time/Date/Language
Fix Locale Warnings:
# apt-get install language-pack-en

Set Timezone:
# dpkg-reconfigure tzdata

Install Time Daemon:
# apt-get install ntp

General
Install Manual Pages:
# apt-get install man

Add Domain name
# vi /etc/hosts
127.0.0.1 localhost
123.456.789.012 computername.domain.com computername (replace external ip)

Security
Add Administrative User (with home directory):
# useradd myusername -m

Give Admin user Sudo Powers
# visudo
myusername ALL=(ALL) ALL

Set Admin Password
# passwd myusername

Set Admin Shell Preference (optional)
# vi /etc/passwd
myusername: […] :bash

Disable Root SSH Login:
# vi /etc/ssh/sshd_config
PermitRootLogin no <–MAKE SURE YOU CREATED ADMIN & PASSWORD

Tighten default permissions for file & directory creation:
# vi /etc/profile
umask 027 (no default access for others)

Install and Set Up Firewall
# apt-get install ufw
# ufw allow ssh <–DO NOT FORGET THIS
# ufw enable

Get a Random Row from Database

November 29th, 2008

I had an app the other day that required a single random row to be displayed from the database. It’s an easy task, but I tripped on it for a second. Here was my impulse:

//Get the total number of rows from the MySQL database
$query = "SELECT COUNT(*) as total FROM table";
$result = mysql_query( $query);
$rows = mysql_fetch_assoc( $result );

//Let PHP choose a random row
//pick a number between 1 & the total
$row = rand(1, $rows['total'])

//Now get that row
$query = "SELECT * FROM table LIMIT($row, 1)";
$result = mysql_query( $query);

After typing that out, I realized that I had take the long way round the problem. Very similar to taking the Misty Mountain Path rather than the Mines of Moria. Here’s a better way:

//Get a random row from the database
$query = "SELECT * FROM table ORDER BY RAND() LIMIT 1";
$result = mysql_query( $query );

Much better. Gimley the dwarf would be proud