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


?>