Wednesday, April 25, 2012

Installing Wordpress on CentOS 5.x

This is not the famous 5 minute installation, but it comes close. It takes other minor items into account, like php53 and the APC accelerator cache. I'll follow up soon with some backup scripts, but here's a baseline installation guide.

At the time of this blog entry, WordPress was up to version 3.3.2

Assumptions:
Server has a clean install of CentOS 5.x and access to the internet.

1. Login as root or su

2. Install Webserver and Database and other prerequisites
yum install httpd* mysqld* gcc* pcre-devel

3. Install php 5.3
http://noveckg.blogspot.com/2012/01/installing-php-53-on-centos-5x.html

4. Install a php accelerator
http://noveckg.blogspot.com/2012/01/installation-of-apc-on-php-53-centos-5x.html

5. Start services and configure to start on boot

chkconfig httpd on && service httpd start
chkconfig mysqld on && service mysqld start

6. Lock down mysql
/usr/bin/mysqladmin -u root password 'yourpasswordhere'
7. Create an empty wordpress database and account in Mysql
mysql -u root -p yourpasswordhere
At the mysql prompt:
CREATE DATABASE mydbname CHARSET 'utf8';
GRANT select,insert,update,delete,create,drop,index,alter
ON mydbname.*
TO mywpuser@localhost IDENTIFIED BY 'wpuserpassword';
flush privileges;
quit;

8. Download wordpress
cd /temp
wget http://wordpress.org/latest.zip
unzip latest.zip
cd wordpress
cp -r * /var/www/html/


9. Create a Wordpress data directory and assign permissions
cd /var/www/html/wp-content
mkdir uploads
mkdir cache
cd ../
chown –R apache:apache *

10. Copy default config file

cp wp-sample-config.php wp-config.php

11.    Edit config file
nano wp-config.php
Modify the following vars:
DB_NAME
The database name created in Step 7
DB_USER
The username created in Step 7
DB_PASSWORD
The password created in Step 7
DB_HOST
Localhost
(or specify remote host if applicable)

12. Open up a web browser to start WordPress config
http://my.server.ip/wp-admin/install.php

Press on..

-noveck

Thursday, April 12, 2012

Moodle: PHP script to convert Mysql database from MYISAM to InnoDB

I wrote (or rather modified an existing script) that batch converts the moodle database from MyISAM to InnoDB. This saves a lot of time, rather than writing a couple hundred ALTER TABLE statements, or even using the phpMyadmin interface.
Credit for the original script:
http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb
 
Please test on a copy of the database before using. Do not run on a production database! 
I can't emphasize that enough.

It's been tested on both Moodle 1.9 and 2.0/2/2, see script preamble for further details.

It's a potentially hazardous script, so I won't recommend placing it in any web accessible folders (like /var/www/html/..). It's been made relatively simple to implement, and the instructions are below:

0. Login as root/su

1. Ensure that InnoDB is enabled on your server
mysql -u root -p *****
SHOW ENGINES;
Expected output
...
InnoDB | YES | Supports transactions...


2. Install script on your server
Copy/Paste the code at the bottom of the post into a new file named innodb_convert.php on your Moodle server where the database is located.
Please ensure that it is not in a web accessible folder.



3. Give the file execute permissions to the script
chmod +x innodb_convert.php

4. Configuration
Open the file and navigate to the database configuration section (approx line 48). Set relevant variables.
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions 
$dbpass = 'mypass'; //user password
$mydb = 'mydb'; //specify schema or db to be modified
5. Save and exit.

6. From the command line, call the script using php
cd /tmp
php innodb_convert.php



7. Normal output expected:
ALTER TABLE mytable1 engine=InnodDB;
ALTER TABLE mytable2 engine=InnodDB;
ALTER TABLE mytablen engine=InnodDB;
Operation Completed

Confirm Storage Engine conversion using phpyadmin
or from Mysql: show create tablename.
Script Execution time: 999.99 seconds

The script (see preamble for additional information):

Download text file here.

<?php
// WARNING: PLEASE TEST BEFORE RUNNING ON A PRODUCTION DATABASE
// Tested on Moodle 1.9 and Moodle 2.0/2.2
//
// Disclaimer:
//
// This script was written to be executed from the command line in 
// Linux using a MYSQL database 
// and MYISAM storage engine. It converts the storage engine to 
// INNODB using the ALTER TABLE command.
//
// It assumes that the INNODB engine has been enabled. 
// This can be confirmed by logging into the mysql prompt 
// and running the command: 
// show engines;
//
// If it is disabled, check your my.cnf and 
// comment out the line: skip-innodb
//
// It does not check if disabled, so the script would execute 
 //and attempt to co
// It contains secure information, so DO NOT PLACE IN WEB DIRECTORY
//
// Execute from /temp and delete afterwards
//
// This is a modification of a script located here:
// http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-to-innodb 
//
// Version : 1.2
// Date  : 11 April 2012
// Diff  :  check for recordset before executing alter table
//
// Version : 1.1
// Date  : 31 January 2012
// Diff  :  Added Timer to script; Display user and DB being affected
//
//
// Version :  1.0 
// Date  : 25 January 2012
//
// 
//
// Noveck Gowandan http://noveckg.blogspot.com

//Time Calculation - start count

$time = explode(' ',microtime());
$time = $time[1] + $time[0]; //return array
$begintime = $time; //define begin time

//Your database connection items here
 
$host = 'myhost'; //Specify host
$dbuser = 'myuser'; // Specify user with alter permissions 
$dbpass = 'mypass'; //user password
$mydb = 'mydb'; //specify schema or db to be modified


//connect to database using variables above
$link = mysql_connect($host,$dbuser,$dbpass);
$db = mysql_select_db($mydb);
   
if (!$link) {
die('Could not connect: ' . mysql_error());

}
echo "Connected Successfully to: $host." . "\n\n";
echo "Using database: $mydb." . "\n\n"; 
echo "Running script as $dbuser." . "\n\n";


//show tables in database
$sql = 'SHOW TABLES';
$rs = mysql_query($sql);

echo $sql;
echo "\n";

if (!$rs) {
die('SQL Recordset Error: ' . mysql_error());

}
else {
//loop through tables and convert to InnoDB
while($row = mysql_fetch_array($rs))
{
$tbl = $row[0];
$sql = "ALTER TABLE $tbl engine=InnoDB;";
mysql_query($sql);

echo $sql;
echo "\n";
}

echo 'Operation Completed.' . "\n\n";
echo 'Confirm Storage Engine conversion using phpmyadmin ' . "\n" . 'or from mysql: show create table tblname.' . "\n";

}
//close connection
mysql_close($link);

$time = explode(" ", microtime());
$time = $time[1] + $time[0]; 
$endtime = $time; //define end time
$totaltime = ($endtime - $begintime);
echo "Script Execution Time: $totaltime" . " seconds." . "\n\n";


?>