This particular sub-entry focuses on installing MySQL 5.5 on CentOS 5.7.
For reference, CentOS ships with MySQL 5.0.77 by default, at the time of this entry (January 2012). This entry does not involve any migration and assumes a new server is being deployed. Nonstandard repos are required (remi and EPEL)
0. Login as root
1. Remove previously installed MySQL (if applicable)
yum erase mysql
2. Install additional repos
Navigate to temporary directory
cd /tempInstall EPEL repo
wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm
UPDATE 11 MAY 2012: The above URL is sporadic. Please use this alternative if the original does not work. - http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm
rpm -Uvh epel-release-5-4.noarch.rpm
Install remi repo
wget http://rpms.famillecollet.com/enterprise/remi-release-5.rpm
rpm -Uvh remi-release-5.rpm
3. Ensure the repo contains MySQL 5.5
yum --enablerepo=remi list mysql mysql-server
4. Install MySQL 5.5
yum install --enablerepo=remi mysql*
5. Start MySQL and configure to start on boot
service mysqld start
chkconfig mysqld on
6. Run mysql upgrade script
mysql_upgrade -u root -p
7. Change Mysql default Password
/usr/bin/mysqladmin -u root password 'yourpasswordhere'
8. Check to ensure mysql 5.5 is installed.
mysql --version
That's it! MySQL 5.5 should now be installed. Dont forget to tune your server:
http://noveckg.blogspot.com/2011/04/some-mysql-database-tuning-from-non-dba.html
-noveck
Wednesday, January 11, 2012
Tuesday, January 3, 2012
2012 - The end of...
..the world?
Maybe not, but certainly I plan to make it the end of Moodle 1.9 on my production systems. Or at least get a head start on planning a migration strategy from 1.9 to 2.2.
In other news, CentOS recently released version 6 on December 20th, 2011, so I'll take that opportunity to build a fresh new test environment CentOS 6.2, php5.3, mysql5.5 and of course Moodle 2.2
Look out for a fresh Moodle 2 installation guide on CentOS 6, coming soon to theaters near you.
Maybe not, but certainly I plan to make it the end of Moodle 1.9 on my production systems. Or at least get a head start on planning a migration strategy from 1.9 to 2.2.
In other news, CentOS recently released version 6 on December 20th, 2011, so I'll take that opportunity to build a fresh new test environment CentOS 6.2, php5.3, mysql5.5 and of course Moodle 2.2
Look out for a fresh Moodle 2 installation guide on CentOS 6, coming soon to theaters near you.
Thursday, December 1, 2011
Vacation!
I'll be on vacation for the rest of 2011, but I've lined up a couple items to work on.
1. Splitting the statistics processing from the moodlecron
2. Investigating MYSQL replication as part of the disaster recovery plan
3. The much dreaded moodle 2.x upgrade strategy.
Cheers,
-n
1. Splitting the statistics processing from the moodlecron
2. Investigating MYSQL replication as part of the disaster recovery plan
3. The much dreaded moodle 2.x upgrade strategy.
Cheers,
-n
Monday, November 7, 2011
Moodle Apache/DB Connection Errors!
The Moodle's config.php includes a section to email connection errors to an email address
$CFG->emailconnectionerrorsto = 'myemail@org.com'
I've noticed some random errors popping up in my inbox, at the same time my stats script is configured to run.
Something along the lines of:
WARNING: Database connection error: http://mysitemoodle.com
I found this especially curious, especially my Zabbix Server was not reporting any errors. So I dug a little deeper, and ran a couple of the Mysql Reporting tools I use.
<>
Turns out I was maxing out my max_connections config.
My old config was 385 and I had originally increased this to 500 with the deployment of the LB/HA Moodle Environment (earlier post)
I had to do some more tweaking of the mysql configuration file. /etc/my.cnf
The default wait_timeout is very long: 14400 sec.
In my original deployment in May this year, I reduced this to 3600 sec.
With these errors randomly cropping up during the statsrun, I had to tweak it yet again.
I reduced the key_buffer_size to increase the max_connections to 550. This was necessary to ensure that the server does not over-allocate RAM. I also reduced the wait_timeout to 1800 sec, which is fine considering the hardware my server uses. (2 Quad core proc, 32GB RAM)
A quick save and restart of the mysql service was all it took.
96+ hours and I'm still actively monitoring, no more connection errors and we're down to about a max of 300 connections at the peak.
-n
$CFG->emailconnectionerrorsto = 'myemail@org.com'
I've noticed some random errors popping up in my inbox, at the same time my stats script is configured to run.
Something along the lines of:
WARNING: Database connection error: http://mysitemoodle.com
I found this especially curious, especially my Zabbix Server was not reporting any errors. So I dug a little deeper, and ran a couple of the Mysql Reporting tools I use.
<
Turns out I was maxing out my max_connections config.
My old config was 385 and I had originally increased this to 500 with the deployment of the LB/HA Moodle Environment (earlier post)
I had to do some more tweaking of the mysql configuration file. /etc/my.cnf
The default wait_timeout is very long: 14400 sec.
In my original deployment in May this year, I reduced this to 3600 sec.
With these errors randomly cropping up during the statsrun, I had to tweak it yet again.
I reduced the key_buffer_size to increase the max_connections to 550. This was necessary to ensure that the server does not over-allocate RAM. I also reduced the wait_timeout to 1800 sec, which is fine considering the hardware my server uses. (2 Quad core proc, 32GB RAM)
A quick save and restart of the mysql service was all it took.
96+ hours and I'm still actively monitoring, no more connection errors and we're down to about a max of 300 connections at the peak.
-n
Friday, October 28, 2011
Useful Queries for Moodle
I've come to compile a list of queries for Moodle, aggregrated from various sources on the internet. Credits to the original authors, whomever they be. (sorry for not keeping track)
# Top 20 courses in terms of student enrolment for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.fullname, c.shortname, count( usr.id )
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Student' and c.idnumber like '%\_2010%'
GROUP BY c.fullname
ORDER BY count( usr.id ) DESC
LIMIT 0 , 20
# List of Lecturers and how much courses they teach
SELECT user.firstname, user.lastname, user.email, count( role.id ) AS courses
FROM mdl_course course
JOIN mdl_context context ON ( course.id = context.instanceid
AND context.contextlevel =50 )
JOIN mdl_role_assignments assign ON ( context.id = assign.contextid )
JOIN mdl_role role ON ( assign.roleid = role.id )
JOIN mdl_user user ON ( assign.userid = user.id )
WHERE role.shortname = 'editingteacher'
GROUP BY user.firstname, user.lastname, user.email
ORDER BY user.lastname, user.firstname
# Courses with MMS streaming media for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.shortname
FROM mdl_resource r, mdl_course c
WHERE r.reference LIKE '%mms://%'
AND c.shortname LIKE '%\_2010%'
AND r.course = c.id
#List of courses and number of activities for specfic year or term (assuming year code is built in to courses) (sorted by total num_activities )
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount
FROM mdl_course m
LEFT JOIN (
SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount
FROM (
SELECT course, count( * ) AS subcount
FROM mdl_resource
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_quiz
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_assignment
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_survey
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_label
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_glossary
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_wiki
GROUP BY course) AS courseCount
GROUP BY courseCount.course) AS cCount ON cCount.course = m.id
WHERE m.shortname LIKE '%\_2010%'
ORDER BY cCount.totalcount ASC
# Top 20 courses in terms of student enrolment for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.fullname, c.shortname, count( usr.id )
FROM mdl_course c
INNER JOIN mdl_context cx ON c.id = cx.instanceid
AND cx.contextlevel = '50'
INNER JOIN mdl_role_assignments ra ON cx.id = ra.contextid
INNER JOIN mdl_role r ON ra.roleid = r.id
INNER JOIN mdl_user usr ON ra.userid = usr.id
WHERE r.name = 'Student' and c.idnumber like '%\_2010%'
GROUP BY c.fullname
ORDER BY count( usr.id ) DESC
LIMIT 0 , 20
# List of Lecturers and how much courses they teach
SELECT user.firstname, user.lastname, user.email, count( role.id ) AS courses
FROM mdl_course course
JOIN mdl_context context ON ( course.id = context.instanceid
AND context.contextlevel =50 )
JOIN mdl_role_assignments assign ON ( context.id = assign.contextid )
JOIN mdl_role role ON ( assign.roleid = role.id )
JOIN mdl_user user ON ( assign.userid = user.id )
WHERE role.shortname = 'editingteacher'
GROUP BY user.firstname, user.lastname, user.email
ORDER BY user.lastname, user.firstname
# Courses with MMS streaming media for specfic year or term (assuming year code is built in to courses)
SELECT DISTINCT c.shortname
FROM mdl_resource r, mdl_course c
WHERE r.reference LIKE '%mms://%'
AND c.shortname LIKE '%\_2010%'
AND r.course = c.id
#List of courses and number of activities for specfic year or term (assuming year code is built in to courses) (sorted by total num_activities )
SELECT m.id, m.`shortname` , m.fullname, cCount.totalcount
FROM mdl_course m
LEFT JOIN (
SELECT courseCount.course, sum( courseCount.subcount ) AS totalcount
FROM (
SELECT course, count( * ) AS subcount
FROM mdl_resource
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_quiz
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_assignment
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_survey
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_label
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_glossary
GROUP BY course
UNION ALL SELECT course, count( * ) AS subcount
FROM mdl_wiki
GROUP BY course) AS courseCount
GROUP BY courseCount.course) AS cCount ON cCount.course = m.id
WHERE m.shortname LIKE '%\_2010%'
ORDER BY cCount.totalcount ASC
Deploying a loadbalanced Apache front end
This particular configuration is being used to support a large Moodle Installation.
The front end comprises of:
1 Loadbalancer
3 webservers
The load balancer virtual IP
Prerequisites:
Fully built and configured CentOS Apache-based webservers
One clean CentOS server to act as loadbalancer
IP's
Webserver 1- 192.168.100.1
Webserver 2 - 192.168.100.2
Webserver 3- 192.168.100.3
Loadbalancer - 192.168.100.5
Virtual IP- 192.168.100.10
2. Create specific test file on each webserver to allow the Loadbalancer to check.
echo foo > /var/www/html/test.html
3. Create unique identifiers on each webserver to ensure the load balancing algorithm works. These files will be deleted later on.
On each webserver x (replace x with associated number)
echo "This is Webserver X" > /var/www/html/index.html
4. Create a loopback interface with the virtual IP to terminate on each webserver:
nano /etc/sysconfig/network-scripts/ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.100.20
NETMASK=255.255.255.255
ONBOOT=yes
NAME=loopback
5. Configure kernel to announce ARP requests/send responses
nano /etc/sysctl.conf
Modify the following entries
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.eth0.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.eth0.arp_announce = 2
6. Reload modified kernel parameters and bring up loopback:
sysctl -p
ifup lo:0
2. Install necessary packages
yum install -y heartbeat heartbeat-ldirectord ipvsadm
3. Configure ldirectord and heartbeat in autostart list.
chkconfig --add ldirectord
chkconfig --del heartbeat
4. Modify kernel to allow IP forwarding
nano /etc/sysctl.conf
find the following parameter:
net.ipv4.ip_forward = 0
change the parameter:
net.ipv4.ip_forward = 1
5. Reload modified kernel parameter:
sysctl -p
6. Configure secondary ethernet interface for Virtual IP:
nano /etc/sysconfig/network-scripts/ifcfg-eth0:0
DEVICE=eth0:0
BOOTPROTO=none
ONBOOT=yes
HWADDR=3a:5d:23:ad:67:47 <>
NETMASK=255.255.255.0
IPADDR=192.168.100.10
GATEWAY=192.168.100.1
TYPE=Ethernet
7. Create ldirector configuration file:
nano /etc/ha.d/ldirectord.cf
checktimeout=10
checkinterval=2
autoreload=no
logfile="/var/log/ldirectord.log"
quiescent=no
virtual=192.168.100.10:80
real=192.168.100.1:80 gate
real= 192.168.100.2:80 gate
real= 192.168.100.3:80 gate
service=http
request="test.html"
receive="foo"
scheduler=wlc
protocol=tcp
checktype=negotiate
8. Restart the ldirectord service:
service ldirectord restart
9. Test the configuration:
ipvsadm -l
Expected output:
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP yoursite.com:http wlc
-> web03:http Route 1 412 0
-> web02:http Route 1 411 0
-> web01:http Route 1 411 0
That should be it! Your DNS may need to be configured to point your external IP to the Virtual IP.
-n
The front end comprises of:
1 Loadbalancer
3 webservers
The load balancer virtual IP
Prerequisites:
Fully built and configured CentOS Apache-based webservers
One clean CentOS server to act as loadbalancer
IP's
Webserver 1- 192.168.100.1
Webserver 2 - 192.168.100.2
Webserver 3- 192.168.100.3
Loadbalancer - 192.168.100.5
Virtual IP- 192.168.100.10
On Each Webserver
1. Open Terminal Interface2. Create specific test file on each webserver to allow the Loadbalancer to check.
echo foo > /var/www/html/test.html
3. Create unique identifiers on each webserver to ensure the load balancing algorithm works. These files will be deleted later on.
On each webserver x (replace x with associated number)
echo "This is Webserver X" > /var/www/html/index.html
4. Create a loopback interface with the virtual IP to terminate on each webserver:
nano /etc/sysconfig/network-scripts/ifcfg-lo:0
DEVICE=lo:0
IPADDR=192.168.100.20
NETMASK=255.255.255.255
ONBOOT=yes
NAME=loopback
5. Configure kernel to announce ARP requests/send responses
nano /etc/sysctl.conf
Modify the following entries
net.ipv4.conf.all.arp_ignore = 1
net.ipv4.conf.eth0.arp_ignore = 1
net.ipv4.conf.all.arp_announce = 2
net.ipv4.conf.eth0.arp_announce = 2
6. Reload modified kernel parameters and bring up loopback:
sysctl -p
ifup lo:0
On the Loadbalancer
1. Open command line2. Install necessary packages
yum install -y heartbeat heartbeat-ldirectord ipvsadm
3. Configure ldirectord and heartbeat in autostart list.
chkconfig --add ldirectord
chkconfig --del heartbeat
4. Modify kernel to allow IP forwarding
nano /etc/sysctl.conf
find the following parameter:
net.ipv4.ip_forward = 0
change the parameter:
net.ipv4.ip_forward = 1
5. Reload modified kernel parameter:
sysctl -p
6. Configure secondary ethernet interface for Virtual IP:
nano /etc/sysconfig/network-scripts/ifcfg-eth0:0
DEVICE=eth0:0
BOOTPROTO=none
ONBOOT=yes
HWADDR=3a:5d:23:ad:67:47 <
NETMASK=255.255.255.0
IPADDR=192.168.100.10
GATEWAY=192.168.100.1
TYPE=Ethernet
7. Create ldirector configuration file:
nano /etc/ha.d/ldirectord.cf
checktimeout=10
checkinterval=2
autoreload=no
logfile="/var/log/ldirectord.log"
quiescent=no
virtual=192.168.100.10:80
real=192.168.100.1:80 gate
real= 192.168.100.2:80 gate
real= 192.168.100.3:80 gate
service=http
request="test.html"
receive="foo"
scheduler=wlc
protocol=tcp
checktype=negotiate
8. Restart the ldirectord service:
service ldirectord restart
9. Test the configuration:
ipvsadm -l
Expected output:
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP yoursite.com:http wlc
-> web03:http Route 1 412 0
-> web02:http Route 1 411 0
-> web01:http Route 1 411 0
That should be it! Your DNS may need to be configured to point your external IP to the Virtual IP.
-n
Tuesday, August 16, 2011
NTFS support on Centos 5.x
If you have a Removable memory device (USB Memory stick, USB Hard drive) that is formatted in NTFS, it may not be able to mount on Centos by default.
Just a couple pacakges are needed to automagically detect and mount NTFS-formatted drives.
0. Login as root
1. Update then Install packages
yum -y update
yum install fuse fuse-ntfs-3g
2. Plugin your drive and it will auto-mount and display in a little window.
-n
Just a couple pacakges are needed to automagically detect and mount NTFS-formatted drives.
0. Login as root
1. Update then Install packages
yum -y update
yum install fuse fuse-ntfs-3g
2. Plugin your drive and it will auto-mount and display in a little window.
-n
Subscribe to:
Posts (Atom)