Issues with ip6tables

Whilst troubleshooting a VM hanging issue with a EXSi 5.0 guest running CentOS 5, i noticed a strange error after my last kernel update.

It had me a bit confused, as I distinctly recall disabling all IPv6 support while building the machine.

Anyhow, since I don't need IPv6 support at the moment and to avoid any unnecessary red flags while booting, I'll just go ahead and disable it completely.

0. Login as root / su

1. Check to see if it loads on boot.
chkconfig --list | grep ip6tables

//in my instance, it was enabled for levels 2,3,4 and 5;

2. Disable ip6tables
chkconfig ip6tables off

3. Stop ip6tables service (if running)
service ip6tables status (if process is runnig, stop; if not, ignore next line)
service ip6tables stop

That's it!


It may or may not help my VM hanging issue, but at least it'd clear that pesky red flag.

Cheers,
noveck

InnoDB Restore Backup Procedure

My previous post elaborated on some scripts on backing up a MySQL Database with the InnoDB storage engine.

This post documents the restore procedure using the actual backup generated. Steps 7 and 8 are optional, but recommended. Better safe than sorry!

Assumptions:
· Target restore candidate server has Percona Xtrabackup installed.
· Target database server (restore candidate), has no active/production databases running.

Test Environment:
CentOS 5.x
MySQL 5.0 (distro version)
Percona Xtrabackup 2.0.3 installed as per this howto

Backup Directory Structure
/bkp
/bkp/Hourly
/bkp/Daily
/bkp/Monthly


0. Got root? 
 
1. Locate appropriate backup file
Assumption that it is compressed like the Daily or Monthly in my previous post.

cp –r /bkp/Daily/mydbnamexxxxxx.tgz /root/Desktop

2. Uncompress backup file

cd /root/Desktop
tar xzf mydbnamexxxxxx.tgz

3. Prepare the backup file 
cd /path/to/extracted/backup/ 
ls
(expected output should be ibdata1 file, folder containing database base and other xtrabackup named files)

innobackupex –apply-log /root/Desktop/*_dbxxxx/201xxxxx/

4. Stop the MYSQL Daemon and copy the files from the desktop to the mysql directory
service mysqld stop
rsync –rvt --exclude ‘xtrabackup_checkpoints’ --exclude ‘xtrabackup_logfile’ * /var/lib/mysql

5. Change ownership of the restored files and restart the MYSQL Daemon
chown –R mysql:mysql /var/lib/mysql
chmod –R 771 /var/lib/mysql
service mysqld start

6. Login to mysql and ensure the database has been restored via a table count
(It helps to have this documented in your production environment)
mysql –u root –p
<enter password when prompted>
show databases;
use mydbname; (use appropriate database name from list)
show tables;
Quit MYSQL:
\q

7: Execute a mysqlcheck with check flag to determine any data inconsistencies
mysqlcheck –c mydbname –u root –p
<enter password when prompted>

8: Execute a mysqlcheck with optimize flag to optimize restored table data
mysqlcheck –o mydbname –u root –p
<enter password when prompted>

--eof
-noveck

Monitoring and reporting on a remote MySQL database.

Depending on your environment, you won't grant every single server on your network the ability to send email. This sort of security precaution had me in a bit of a pickle, as I wanted to step up the mysqlchecks I currently have scripted via cron on my database servers. If your database server has the ability to send email, this solution may not be the best option for you.


I already had a Zabbix Server sitting around, cheerfully gathering data and pumping reports on my little server farm and I thought about giving it some more work to do, without any extra pay.  The zabbix server already had specific port access to these servers, so the security experts at my organization would not give me hell for this particular solution.


The logic was simple. Execute scripted checks from a remote host, run some data processing, if everything is good, just take a timestamp of execution. If everything is not good, send an email for the sysadmin or DBA to action immediately. The nice thing about this script is that it lists the tables that are not flagged as OK by MySQL.

Simple stuff, really.


Assumptions:
Database Server is 192.168.1.10
Monitoring Server is 192.168.1.11
Database to be monitored = mydb
Location of user scripts = /scripts
SSMTP is installed on the monitoring server. Installation Instructions here.

Both servers should already see each other or be able to ssh to/from one another. If not, seek assistance from your company's network gurus (walk with chocolates).

0. Login as root on the Database server.
You'll need to create an account that the remote server will access over the network.

Login as mysql root.

mysql -u root -p
<enter password>

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER on mydb.* to mydbuser@'192.168.1.11' identified by 'mydbuserpassword';

flush privileges;

1. Test that the remote connection works
From terminal on the monitoring server
mysql -u mydbuser -h 192.168.1.10 -p
<enter password>
You should be presented with a mysql prompt.

2. Install script on the Monitoring server
Copy and paste the following into a remotedbcheck.sh file in your scripts directory. Ensure the executable flag is set

chmod +x remotedbcheck.sh

Actual Script
#!/bin/sh
# A Script to execute mysqlcheck -c from a remote server
# and send an email if any table are flagged as NOT OK.
# The tables with issues are sent in the body of the
# email for admin action.
# Written by: Noveck Gowandan
# 09-01-2013
# Version 1.1
# Added script timer which will be appended to new logfile in mylocation (see variables)

##Variables
    # Location of Results and Status Files (default: /tmp)
    mylocation=/tmp 
    # Database to check
    mydbname=mydb
    # Database user with appropriate mysql access
    mydbuser=mydbuser
    # DB pass
    mydbpass=mydbpassword
    # Specify remote database host (IP preferred, hostname if entries are in /etc/hosts)
    mydbhost=192.168.1.10

    # Email variables (Valid Recipent and Sender Email required)
    notifyemail=myemailaddress@myorg.com
    serveremail=monitoring.server@myorg.com

    # These variables can be left as is
    resultsfilename=mysqlcheck-c-results.txt
    statusfilename=mysqlcheck-c-status.txt
    msgtext=`grep -v OK $mylocation/$mydbhost-$resultsfilename`
    status=0
   
# Start timer
time_start=`date +%s`

#Remove any resultsfile that exists
rm -rf $mylocation/$mydbhost-$resultsfilename

#Remove any statusfile that exists
rm -rf $mylocation/$mydbhost-$statusfilename

#Execute mysqlcheck with -c flag only
mysqlcheck -c $mydbname -u $mydbuser -p$mydbpass -h $mydbhost > $mylocation/$mydbhost-$resultsfilename

#Check results file and generate status file
grep -v OK $mylocation/$mydbhost-$resultsfilename | wc -l > $mylocation/$mydbhost-$statusfilename

#Check Status file, send email
status=`cat $mylocation/$mydbhost-$statusfilename`;

if test $status -ge 1
then

ssmtp $notifyemail << EOF
To: $notifyemail
From: $serveremail
Subject: `echo "PROBLEM: MYSQLCHECK "$mydbhost`;

$msgtext
EOF

else

echo "Last Check OK!" >> $mylocation/$mydbhost-$mydbname-check.log
fi

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute

date >> $mylocation/$mydbhost-$mydbname-check.log
echo "Execution Time was $total_time seconds." >> $mylocation/$mydbhost-$mydbname-check.log
echo "____________" >> $mylocation/$mydbhost-$mydbname-check.log

3. Add script to cron
Depending on your organization and criticality of the service, this may need to be done daily. Given the size of the database in my case and the length of time it takes to run, this example is weekly on Sundays at 2:01 AM.

nano /etc/crontab


Append to file
01 2 * * 0 root /bin/sh /scripts/remotedbcheck.sh


That's it!
The script will execute quietly and will not send any email if everything reports as OK. Be prepared if you do get any email from this script, you should act immediately!

Cheers,
-noveck

Issues with CentOS 6 and EPEL

I was running into some issues with the EPEL repo on CentOS 6.x. I had just installed the OS and the EPEL/remi repos and whilst trying to search for a package the same error kept repeating.

Cannot retrieve metalink for repository: epel

and it kept failing each time.

The fix involved editing the EPEL repo and making a simple change from https to http. Why or how this broke is beyond me, but this fix may help someone else.

Assuming you have root or su:

1. Edit the epel repo
nano /etc/yum.repos.d/epel.repo

Find line:
mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch

Change to
mirrorlist=http://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch

Save and exit.

2. Clean yum
yum clean all


That did it for me!

Cheers,
noveck 

InnoDB Backup Scripts

Following up on my earlier post about converting the storage engine on a MySQL Database from MyISAM to InnoDB, I'd like to share the following scripts with backup rotation built in.
The next blog entry should be a step-by-step restore procedure using the actual backups below.

Test Environment:
CentOS 5.x
MySQL 5.0 (distro version)
Percona Xtrabackup 2.0.3 installed as per this howto

Backup Directory Structure
/bkp
/bkp/Hourly
/bkp/Daily
/bkp/Monthly

Script Output Log Directory
/tmp

Script Directory
/scripts

Disclaimer: I do not guarantee this is the BEST way of doing this, but it works for me. Copy and paste the following into a *.sh file in your scripts directory. Ensure the executable flag is set

chmod +x scriptname.sh

Actual Scripts
innodb_backup_monthly.sh

#!/bin/sh
# An InnoDB Backup Script to backup database Monthly
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Uses tar-gzip to further compress final archive
# Added script timer and modified output to log time
# Start timer

time_start=`date +%s`

# Go to backup location and create Monthly folder with datestamp
cd /bkp/
mkdir M_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/M_db_$(date +%Y%m%d)

# Compress backup into a tarball
tar czf mydbname_$(date +%Y%m%d).tgz M_db*

# Backup rotation section
rm -rf M_db*
rm -rf /bkp/Monthly/$(date +"%B")
mkdir /bkp/Monthly/$(date +"%B")
mv mydbname* /bkp/Monthly/$(date +"%B")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_monthly.log
date  >> /tmp/db_backup_monthly.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_monthly.log

 innodb_backup_monthly.sh

 #!/bin/sh
# An InnoDB Backup Script to backup database DAILY
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Uses tar-gzip to further compress final archive
# Added script timer and modified output to log time

# Start timer
time_start=`date +%s`

# Go to backup location and create Daily folder with datestamp
cd /bkp/
mkdir D_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/D_db_$(date +%Y%m%d)

# Compress backup into a tarball
tar czf mydbname_$(date +%Y%m%d).tgz D_db*

# Backup rotation section
rm -rf D_db*
rm -rf /bkp/Daily/$(date +"%A")
mkdir /bkp/Daily/$(date +"%A")
mv mydbname* /bkp/Daily/$(date +"%A")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_daily.log
date  >> /tmp/db_backup_daily.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_daily.log

innodb_backup_hourly.sh

#!/bin/sh
# An InnoDB Backup Script to backup database HOURLY
#
# Written by: Noveck Gowandan
# 02-10-2012
# Version 1.1
# Modified filename convention
# Added script timer and modified output to log time

# Start timer
time_start=`date +%s`

# Go to backup location and create Hourly folder with datestamp
cd /bkp/
mkdir H_db_$(date +%Y%m%d)

# Execute backup using innobackupex and send to folder created previously
innobackupex --defaults-file=/etc/my.cnf --user=****** --password=****** --databases=mydbname /bkp/H_db_$(date +%Y%m%d)

# Backup rotation section
rm -rf /bkp/Hourly/$(date +"%H")
mkdir /bkp/Hourly/$(date +"%H")
mv H_db* /bkp/Hourly/$(date +"%H")

# Stop timer and calculate total time
time_end=`date +%s`
total_time=`expr $(( $time_end - $time_start ))`

# Log output: datestamp and time takes to execute
echo "____________" >> /tmp/db_backup_hourly.log
date  >> /tmp/db_backup_hourly.log
echo "Execution Time was $total_time seconds." >> /tmp/db_backup_hourly.log

Presentation: Cloud Computing - An Introduction to the Cloud

This was a presentation I did today, a basic introduction to Cloud Computing.

Enjoy!

http://prezi.com/svz06iipozjm/cloud-computing/?auth_key=16c214823b495245cc593e16948f09b1e80818a3


-noveck

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