Monday, November 5, 2012

Batch convert MyISAM tables to InnoDB from Terminal


A couple months ago I posted on a modified php script to convert tables MySQL tables from MyISAM to InnoDB. It worked all well and good for my test environment, but I realized that it may not be the best solution for a dedicated database server where php is not installed.

In this case, this post assumes you have both su access and mysql root access in order to batch generate the queries to convert the storage engine. I strongly recommend isolating the database from the front end at this point, it means shutting your application down until the operation has been complete.

Please TEST before running in your production environment!

Credit to this post on RackerHacker for the kickstart query generation.

This post does not cover the advantages/disadvantages of either storage engine, that is covered at MySQL's site and at the Oracle Blog.

The benefits of Moodle on InnoDB is covered here.

Some prerequisites to get out of the way.

a. Ensure that InnoDB is enabled on the server
mysql -u root -p *****
SHOW ENGINES;

Expected output
...
InnoDB | YES | Supports transactions...
b. Disable the writeback cache (as per MySQL's recommendation)
hdparm -W0 /dev/sdx (where sdx contains the /var/lib/mysql or your specific mysql location )


c. Tune the my.cnf according to MySQL's recommendation
Later on, the Day32's Tuning Primer and the Mysql Tuner Script can be used to fine tune the other server parameters.

Prereq's aside, let's push forward onto the crux of the matter.

0. Login as root on your database server.

1. Take a backup before proceeding any further.

2. Double check that you have a backup. Please.

3. Run a check on the database to avoid a GIGO issue.
mysqlcheck -c -o -r -f -h localhost -u mydbusername -p mydbname
<enter your db password>

4. Login as MySQL root and generate the batch queries, send to output file (note the single quotes plus backticks)
select concat(‘ALTER TABLE `’,table_schema,’`.`’,table_name,’` ENGINE=InnoDB;’)
from information_schema.tables
Where table_schema=’mydbname’
and ENGINE=’MyISAM’
into outfile ‘/tmp/InnoBatchConvert.sql’

5. Logout from MySQL root and back to shell
 At the shell:
mysql -u root -p --verbose < /tmp/InnoBatchConvert.sql
<enter password>

6. After the script has completed, check all the tables to ensure the storage engine is InnoDB
select table_name, engine
from information_schema.tables
where table_schema = 'mydatabasename';

7. Check the database again.
mysqlcheck -c -h localhost -u mydbusername -p mydbname
<enter your db password>

That's it! All you need to do now is ensure you have a decent backup strategy in place and the InnoDB backup script is covered here. I'll follow up soon with a detailed restore procedure as well as perhaps my own version of my innodb backup scripts with rotation built in.

-noveck

Tuesday, October 2, 2012

Send email from command line through an MS Exchange Relay (Alt title: Installing SSMTP on CentOS 5.x)

The situation required me to use an existing MS Exchange Server as a mail relay to send an email from a custom notification shell script I'm working on.

I tried configuring Postfix, Sendmail, Mailx, Nail, all to no avail. This took quite some time in troubleshooting and Google was not particularly helpful.

Eventually I stumbled across this post, which mentioned using the EPEL repo's to install SSMTP. Simple, but effective.

Therefore, this entry focuses on how to setup your server to relay mail, using the SSMTP package (because it works)


0. Login as root or su.

1. Check to ensure that the permissions are correct to relay mail.
telnet my.emailserver.ip.address 25
EHLO
MAIL FROM: myvalidsenderaddress@foobar.org
RCPT TO: myvalidrecipient@foobar.org
DATA
This is a test message.
. <Hit Enter>


quit

You should have received an email if the permissions are correct.

2. Install the EPEL repo

cd /tmp
wget http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm

 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

3. Remove sendmail
yum remove sendmail

4. Install SSMTP
yum install ssmtp --enablerepo=EPEL

5. Configuration
Backup original conf file
cp /etc/ssmtp/ssmtp.conf /etc/ssmtp/ssmtp.conf.bkp

Edit the conf file
nano /etc/ssmtp/ssmtp.conf 

Edit the following lines:
mailhub=my.emailserver.ip.address:25
Hostname=ValidDomainHostName.foobar.org

*Depending on your configuration, you may or may not need TLS, check the SSMTP.conf man for details.

6. Restart service
service ssmtp restart

7. Test the application
echo "This is the body of my email" | ssmtp myemail@foobar.org

That's it!

This worked for me where all the others mysteriously failed. Now I'm off to finish my script. I'll post another update soon!

-noveck

Monday, September 17, 2012

Installation of VNC Server on CentOS 5.x

 I honestly thought I documented this already, but I searched the blog and was unable to locate it.

This covers installation and quick configuration of VNC on a server, in the event that Desktop access is needed on a remote server. In some cases, the CLI is not enough, and I've found this very useful.

On to the entry:

1. Login as root (or su)

2. Install required packages:
yum  -y install vnc-server

3. Secure the VNC server:
vncpasswd
Set mypassword
cd ~/.vnc
ls

the passwd folder should be listed

4. Configure the server:
nano /etc/sysconfig/vncservers
add to end of file:
VNCSERVERS="3:myusername"
VNCSERVERARGS [3] = "-geometry 1024x768"

5. Start the VNC service and configure service to autostart on boot:
service vncserver start
chkconfig vncserver on


6. Further configuration:
cd ~/.vnc
nano xstartup

add under the line "# Add the foll..."
(while true ; do xterm ; done) &
remove comments(#) from
#unset SESSION_MANAGER
#exec /etc/x11/xinit/xinitrc
7. Restart the vnc service
Service vncserver restart

8. Configure firewall to allow VNC server traffic
system-config-securitylevel-tui
add 5903:tcp to exceptions list

9. Open VNC client from remote peer and connect to VNC Server:
server: 192.168.x.x:3
username myusername
password: mypassword (as set in Step 3)


finito
-noveck

Sunday, September 9, 2012

LinuxCon2012!

Just got back from Linux Foundation's LinuxCon2012 (Aug 29-31) in San Diego.

Some great sessions and the slides were made public. It was a powerful reminder of the immense possibilities of open source and who are some of the companies running Linux in their enterprise.

See Linux Foundation's site for some of the brilliant presentations. One of my personal favourites was the fact that SpaceX's shuttle is running on linux.

 Lots of great ideas in hand, stay tuned for some fresh projects!

-noveck

Monday, August 6, 2012

Identifying and Repairing MySQL (MYISAM) Table fragmentation

I had to perform some server maintenance recently, so I decided to re-run the handy mysql tuner tool, as mentioned in my Database Tuning entry.

Lo and behold, a couple tables were identified as fragmented, even though I have a weekly optimization script that checks and repairs any fragmentation.

Just in case there was some sort of anomaly that would bite me in the butt, I decided to run a check to identify the fragmented tables and manually repair those specifically. I found this post by Sean Hull on the DatabaseJournal very helpful!

To quote the author:
"MySQL tables, including MyISAM and InnoDB, two of the most common types, experience fragmentation as data is inserted and deleted randomly. Fragmentation can leave large holes in your table, blocks which must be read when scanning the table. Optimizing your table can therefore make full table scans and range scans more efficient."


The process:

0. Login as root on the Mysql server
mysql -u root -p ************

1. Identify fragmented tablesTake note of the tables, or perhaps select into an outfile if you so desire. This may come in handy if you want to script or automate the identification and repair of the fragmented tables.

select table_schema, table_name, data_free, engine
from information_schema.tables where table_schema
not in ('information_schema', 'mysql') and data_free > 0;


2. Repair the table fragmentation
This could be scripted, but was manually executed as I only had about 4 fragmented tables.
optimize table tablename1;

3. Rerun Step 1 to ensure the fragmentation has been resolved.
 
-noveck

Tuesday, July 31, 2012

Mounting an ISO to an installed VM on VMWare ESXi


I had a little maintenance item to do which involved mounting an ISO to a working VM. Even when the ISO was attached from the VM settings, it somehow was not booting from the device, apparently ESXi sets the first boot device to the HDD by default once a OS is loaded and it will never boot from the CD/ISO. In this case I was using Linux gparted, but for the Windows guys it can be a Windows Server Rescue or something.
This is how I got it to work:
1. Set boot delay to 3000 milliseconds.
                 -Right Click VM -> Edit settings
                 - Options Tab, edit Boot options -> Power on Boot Delay parameter (I used 3000ms or 3 seconds)

2. Mount the ISO from the Datastore / Client DVD
                 - Right Click VM -> Edit settings
                 - Options Tab, CD/DVD Drive x, browse to uploaded ISO or Client Device.

3. Start the VM, at the boot menu hit ESC

4. Select CD/DVD boot option

5. Use your ISO media / Application.

6. When completed reset the Boot Delay parameter to 0.

-noveck

Monday, July 30, 2012

Installing gparted on CentOS 5.x

Okay, so a situation arose where I need to use gparted to do a quick resize on a production server, and this package is not part of the base repository.

It is in the EPEL repo, so that will need to be installed first (if not already done).

Anyhow, it's really simple, so read on...

Assumptions: Base installation of CentOS 5.x

0. Login as root or su

1. Install additional repos

Navigate to temporary directory

cd /temp
download EPEL repo

wget
http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm

Note: 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

Install the repo
rpm -Uvh epel-release-5-4.noarch.rpm

2. Install the gparted package and dependencies.

yum install gparted --enablerepo=epel disablerepo=rpmforge

*note the rpmforge repo must be disabled before installing (where applicable)

3. Run the application from terminal
gparted

More information can be found here: http://gparted.sourceforge.net/


-noveck

Monday, June 25, 2012

MYSQL ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (111)

So I was thrown this error recently, while setting up a testing mysql environment and attempting to access it from a remote host. Usually it's the simple things that are overlooked, so here's how to resolve the error:
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (111)

The first thing we can check is to see if the user from the remote host is allowed.

1. Login as root on mysql server
mysql -u root -p

2. Select database and show users.
select * from mysql.user\G

**A vertical list will be displayed. Look at the first two fields for each entry. If the user is only allowed to connect from localhost, this may be the problem.
Example:

Host: localhost
User: mydbuser

A user will have to be defined with the same parameters as mydbuser for the remote host (or hosts)
Here's where your documentation will come in handy (or you can hope the old query exists in the mysql buffer!)

3. Allow remote hosts to connect
grant select,insert,update,delete,create,drop,index,alter on mydbname.* to mydbuser@'192.168.1.%' identified by 'mydbpassword' ;

flush privileges;

Note: if you only want to allow a certain host, specify the IP instead of the wildcard.


The second issue that may cause this error is a MySQL configuration.

1. Open MySQL config file
nano /etc/my.cnf

2. Ensure that the following are commented out.

#skip-external-locking
#skip-networking
#bind-address = xx.xx.xx.xx

Save and exit

3. Restart mysql service
service mysqld start

The third issue that may contribute to this error may be the security configuration rejecting incoming requests on the server.

1. Login as root on db server

2. Add rule to iptables
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

** this grants access to the entire subnet, use a specific IP where applicable.

service iptables save

3. Restart iptables service
service iptables restart


Test from remote host by using the following:
mysql -h 192.168.my.dbip -u mydbuser -p


-noveck



Monday, June 18, 2012

Moodle Issues: Backup and restore / course import fails

It was one of those strange situations that popped up after a disaster recovery scenario.

The database server suffered hardware failure, but that was quickly resolved and some tests performed on the database -
MYISAMCHK
and
MYSQLCHECK

Both tests indicated no issues.

Soon after I started getting reports of the course imports or (backup/restores) failing, and troubleshooting took a couple hours. There were a lot of leads on the moodle forums but this is the solution to my particular issue. I strongly recommend turning debugging on from the moodle interface, simply to get as much description on the error as possible.
Debugging revealed a cryptic message: Warning: fopen******* [function.fopen]: failed to open stream: No such file or directory in ******/backuplib.php on line ***followed by a bunch of fwrite problems.

PROBLEM MESSAGE: An error occurred while backing up course start

Solution:

0. Login as admin to the moodle interface.


1. Open the mysqladmin tool  (this can also be done from the MYSQL Server)
2. Truncate the following tables:
  • mdl_backup_courses
  • mdl_backup_files
  • mdl_backup_ids
TRUNCATE TABLE tablename;

3.  Run a mysqlcheck with the check and repair flags just in case.

In my case, this solved the problem. The important point to note, is that the debugging may lead you in the correct direction, so DO NOT USE THIS SOLUTION if you are not certain your problem is identical!


Cheers,
-noveck

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";


?>

Thursday, March 15, 2012

Setting Up Master-Slave MySQL Replication on CentOS

I'm currently doing some research and testing into a MySQL Master-Slave setup on CentOS. It's a bit of a finicky setup and I had a couple stumbling blocks which may or may not happen to you. Just in case, these are documented further down the post.

No sense beating about the bush, let's jump right in...

First of all, this original guide was most helpful in my quest:


 Assumptions:
Base install of Centos completed.
IP of Mysql Master: 192.168.1.10
IP of Mysql Slave: 192.168.1.11
Default Mysql Path: /var/lib/mysql
Replication User: slave_user

On both Master and Slave:

0. Login as root or su


1. Install mysql packages
Yum install mysql*

2. Configure to start on boot
Chkconfig mysqld on

3. Start daemon and set relevant passwords
service mysqld start
--set root pw
mysqladmin –u root password “newpasswordgoeshere”

On Master:

1. Modify my.cnf

a)  remove comments or delete lines to enable networking and bind
#skip-networking
#bind-address = 127.0.0.1


b)    Designate server id
server-id = 1

c)    set an expiration date for the binlog
expire-logs-days=7

d)    Restart Mysql
service mysqld restart


2. Create Slave user and check Master Status.
Login to mysql as root and add a replication user account with associated privileges

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_password';
--for production environment, please specify IP’s or hostnames.
-- …TO 'slave_user'@'192.168.1._' …

FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Expected output
File        |Position|binlog_do_db|binlog_ignore_db
mysql-bin.001      | 500
!!Take note of the binfile and position, it is needed to start replication on the slave!

3.Backup database using preferred method and transfer to slave and restore.
In this instance, I used the innobackupex tool from percona xtrabackup (see blog post)

a) On the Master, Take the backup
innobackupex --defaults-file=/etc/my.cnf --user=***** --password=***** --databases=mydbname /path/to/backup/

b) SCP the backup files over the network to the slave
scp –r /path/to/backup/ user@server:/path/to/slave/destination**
**not mysql directory!!!



4. Unlock the tables.
Login to Mysql
UNLOCK TABLES;


On Slave:

1. Restore the backup

a) On the Slave, Prep the backup
innobackupex --apply-log --user=****** --password=****** /path /to/slave/destination

b) Restore the backup on slave
service mysqld stop
cp –r /path/to/slave/destination /var/lib/mysql
chown –R mysql:mysql *
chmod –R 771 *
service mysqld start


2. Configure Slave
a) open /etc/my.cnf and add under section [mysqld]
nano /etc/my.cnf
server-id=2
master-host=’192.168.1.10’
master-user=’slave_user’
master-password=’slave_password’
master-connect-retry=60
#Specify database to replicate
replicate-wild-do-table=mydb.%

relay log = /var/lib/mysql/mysql-relay-bin
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
master-info-file = /var/lib/mysql/mysql-master.info


#more than likely this entry exists, however, it does not hurt to check
log-error = /var/log/mysqld.log
Save and exit.

b) Restart Mysql
service mysqld restart

3. Start Replication on Slave
a) Login to Mysqld and execute the following:

STOP SLAVE;
#For this next step, please confirm the binlog name and position from the SHOW MASTER STATUS command on the master, before the tables were unlocked!!
CHANGE MASTER TO MASTER_HOST='192.168.1.10', MASTER_USER='slave_user', MASTER_PASSWORD='slave-password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.001', MASTER_LOG_POS=500;

START SLAVE;

b) To ensure that the slave is replicating, run the following command and take note of the following output:

SHOW SLAVE STATUS \G;
Expected output (snippet)
xxxxxxxxxx
Slave_IO_State: Waiting for master to send event


Slave_IO_Running: Yes
Slave_SQL_Running: Yes


Seconds_behind_master: 0
xxxxxxxxxx

4. Troubleshooting?
If the status is anything other than what is listed above, exit the mysql CLI and check the mysql log for troubleshooting.
cat /var/log/mysqld.log

5. Optional Issues and Fixes (may be specific to my test environment, so may not apply to all scenarios )
I encountered an issue in my test where the replication simply was not starting.
The output of the show slave status was as follows:
xxxxxxxxxx
Slave_IO_State:


Slave_IO_Running: No
Slave_SQL_Running: Yes


Seconds_behind_master: NULL
xxxxxxxxxx
Analysis of the mysqld log indicated:
Error 1236: Could not find first log file name in binary log index.
I followed the advice from this website and got it to work:
http://forums.mysql.com/read.php?26,9390,9390#msg-9390

STOP SLAVE;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001';
CHANGE MASTER TO MASTER_LOG_POS=500;
START SLAVE;



--End.

That was long. You deserve a cup of coffee.

Coming soon - Monitoring the replication and checking for replication errors. I'm still in the research phase here, so I'll update as soon as I can.

-noveck

Thursday, March 1, 2012

Find specific files then move them using a list [Linux]

In a previous post I mentioned finding and deleting certain files from Linux.
http://noveckg.blogspot.com/2011/01/find-and-delete-certain-files-in-centos.html

This time around, in order to maintain an audit trail and facilitate recovery options, a prudent approach would be to remove the files from the directory and place them somewhere else where disk util is not critical.

In this example, I used these scripts to clean up my MoodleData directory of old backup zipfiles. It saved tons of time so I didnt have to peruse over 8000 courses to clean this crap up. This may or may not apply to all moodle environments. Obviously, if you rely on this form of course backups, these scripts are not the solution for you. I'll reiterate what was said in that earlier post.


Basically what this command does is search a specified directory for a name/filetype and then move the files. 
If you run it from the top level directory (/), chances are something important may get moved and you can screw things up.

Command 1: Generate a list of backup zip files complete with filepath (datestamped for convenience). This can also be used as an index in case any files need to be replaced

find /path/to/moodledata/ -name \*backup\*.zip -exec ls -lh {} \; | awk '{ print $9}' > /path/to/course-backup-filelist-$(date +%Y-%m-%d).txt


 Command 2: read list line by line, and move file to alternative location

cat /path/to/course-backup-filelist-01-03-2012.txt | while read line; do mv "$line" /path/to/other/location; done


I managed to recover close to 20GB of storage from mostly unneeded files.

\m/ (rock on, moodlers)

-noveck



Monday, February 6, 2012

Testing Percona Xtrabackup (innobackupex)

My last post dealt with the installation of the Percona Xtrabackup tool for use on a Mysql innodb database.
After some tweaking and help from the Percona Docs, the following is a test backup, prep and restore of the full database (not incremental).

This assumes a test environment, so please don't try this on a running production environment. Before the database can be restored, the existing one will have to be renamed or moved. The options for this are straightforward. Stop the mysql daemon and make a binary copy of the files, or rename the source folder.

0. Login as root/su

1. Prerequisite configuration items

Configure datadir and in my.cnf and reboot mysqld service
nano /etc/my.cnf
 --find section
[mysqld]
--add line
datadir=/var/lib/mysql

service mysqld restart

2. Run full backup of specific database
innobackupex --defaults-file=/etc/my.cnf --user=***** --password=***** --databases=mydbname /path/to/backup/

3. Prepare backup files
innobackupex --apply-log --user=****** --password=****** /path /to/backup

4. Restore backup
service mysqld stop
cp –r /path/to/backup /var/lib/mysql
chown –R mysql:mysql /var/lib/mysql
chmod –R 771 /var/lib/mysql
service mysqld start

-n

Wednesday, February 1, 2012

Installing Percona xtrabackup on CentOS 5.x

This covers the basic installation of the xtrabackup package on CentOS. This is necessary in order to take hot backups of a database using the InnoDB storage engine. By default, Mysql has no options to take a full hot backup of a live InnoDB database.

For more information,  please see Percona's website located here: http://www.percona.com/doc/percona-xtrabackup/

0. Login as root

1. Get rpm from Percona
(check your server version before installing, there are specific RPM’s for 32-bit and 64-bit)
see here for rpm list: http://www.percona.com/downloads/percona-release/
cd /tmp
wget  http://www.percona.com/downloads/percona-release/percona-release-0.0-1.i386.rpm

2. Install rpm (32-bit on this test machine)
Rpm –Uvh percona-release-0.0-1.i386.rpm

3. Check repo for package
yum list | grep percona


4. Install xtrabackup
yum install xtrabackup --nogpgcheck
I still have testing to do on actual usage, so I'll create a new post on tests of backups and restoring databases.

-n

Tuesday, January 24, 2012

Installing Moodle 2.x on CentOS 5.7

This particular entry deals with installing (not migrating) a base Moodle 2.x installation on CentOS 5.7. A few non-standard packages were recommended at the time of writing this, so some sub-entries were made to document those steps separately.

This assumes the base installation of CentOS 5.7 has been completed and the server has connectivity to the web.

Login as root/su

Note: This installation does not default to an innodb database, As I'm a bit pressed for time at the moment, I'll either update this post, or create a new one on converting the database to innodb.



0. Update OS
yum update -y

1. Install Mysql5.5
See this post: http://noveckg.blogspot.com/2012/01/installation-of-mysql-55-on-centos-5x.html


2. Install Apache
yum install httpd*

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

4. Install php accelerator (for performance)
See this post: http://noveckg.blogspot.com/2012/01/installation-of-apc-on-php-53-centos-5x.html



5. Download latest tarball and install into webroot
cd /temp
download moodle latest: http://download.moodle.org/download.php/stable22/moodle-latest-22.tgz
tar xzvf moodle-latest-22.tgz
cd moodle-latest-22.tgz
cp –r moodle/* /var/www/html/

6. Create empty database 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 mymoodleuser@localhost IDENTIFIED BY 'moodleuserpassword';
flush privileges;
quit;



7. Check web root permissions
cd /var/www/html
chown –R root:root *
chmod –R 755 *


8. Create MoodleData Folder (outside of web root)
mkdir /usr/moodledata
cd /usr/moodledata
chown -R apache:apache *
chmod –R 700 *


Note: Check here for security recommendations: http://docs.moodle.org/20/en/Security_recommendations

9. Setup Config.php
use instructions from here for a new installation: http://docs.moodle.org/21/en/RedHat_Linux_installation

10. Configure Apache to read from Moodle Data
nano /etc/httpd/conf/httpd.confAdd to end of file

[Directory "/usr/moodle/mymoodle"]*
DirectoryIndex index.php
AcceptPathInfo on
AllowOverride None
Options None
Order allow,deny
Allow from all

[/Directory]*
*substitute the [] with <> !!
11. Setup the Moodle Cron
nano /etc/crontab
add line
*/5 * * * * php /var/www/html/admin/cli/cron.php

12. Moodle!
Open up a web browser on the server and hit: http://localhost/admin
Install and configure as desired.


Happy Moodle-2-ing !

-noveck

Friday, January 13, 2012

Installation of APC on php 5.3 (CentOS 5.x)

My previous blog entry used Eaccelerator as a php cache, but it does not appear to be actively maintaned.
http://bart.eaccelerator.net/

In my research for a suitable and robust alternative, I recognized that a lot of large sites were using APC, and it was compatible with my desired application (Moodle)

APC (Alternative PHP Cache) is a free, open, and robust framework for caching and optimizing PHP intermediate code. (taken from: http://pecl.php.net/package/APC)
  
In order to avoid installation conflicts from the base repository, it was much simpler to compile the APC package from source for the php 5.3 on a CentOS 5.x installation.

0. Login as root

1. Go to temp folder and download the latest tarball cd /temp
wget http://pecl.php.net/get/APC-3.1.9.tgz


2. Extract to temp
tar xzvf APC-3.1.9.tgz
cd APC-3.1.9


3. Configure and Install
**amended on 05-07-2012, added pcre-devel install as suggested by yaroslav.

yum install pcre-devel 

phpize
./configure
make
make test
make install



4. Create APC config filenano /etc/php.d/apc.ini
Add to file

extension=apc.so
apc.enabled = 1
apc.shm_segments = 1
apc.shm_size = 64M
apc.max_file_size = 10M
apc.stat=1

Further information on advanced tweaking of configuration items here: http://www.php.net/manual/en/apc.configuration.php
5. Restart the webserver
service httpd restart

6. Check to see if APC was installed properly
nano /var/www/html/phpinfo.php

Add to file

< php
phpinfo();
?>
 
Save and Exit

Launch from web browser: http://yourserver.com/phpinfo.php
The APC configuration item should be found on the page.

7. Install the APC monitor

Copy APC Monitoring file into web root
cp /temp/APC-3.1.9/apc.php /var/www/html/

(There are options to secure this file, see the website for further details: http://pecl.php.net/package/APC)

Check the APC monitor

Launch from web browser: http://yourserver.com/apc.php
*A page should load displaying cache information and host status.

That's it. A quick and painless method of installing the APC Cache for php 5.3 on CentOS 5.x.

-Noveck



Thursday, January 12, 2012

Installation of php 5.3 on CentOS 5.x


CentOS ships with php 5.1 by default, at the time of this entry (January 2012). The php 5.3 packages are available in the base repos, but the older version needs to be removed first to avoid any installation conflicts.

This entry does not involve any migration and assumes a new server is being deployed.

0. Login as root



1. Verify current version of php
php -v

2. Remove previously installed php and modules (if applicable)
yum erase php*

3. Install php 5.3 and modules
yum install php53*


4. Verify correct version of php is installed

php -v

Wednesday, January 11, 2012

Installation of Mysql 5.5 on CentOS 5.x

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

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.