#!/bin/sh

for dbname in `mysqlshow | awk '{print $2}'`
do
mysqlcheck $dbname ticket -Fq
done

# call this shell script and find the errors
time sh check.sh | grep error

When there is an issue, We should gather some preliminary data to analyze first.

tee 'output.txt';
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;
SELECT SLEEP(60);
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS;
SHOW FULL PROCESSLIST;

SHOW GLOBAL VARIABLES;
notee;

Study the 'output.txt' file. Also the my.cnf and slow query log
From the OS;

free -m
ps aux
df -h

iostat -x 1 5
vmstat 1 5
top -b -n 3
mysql> show global status;

It is incredibly easy to migrate from MySQL to PostgreSQL. Just migrate your schema first (leaving foreign keys out until after restore) and then the mysqldump files are almost perfectly compatible with PostgreSQL when using:

mysqldump --compatible=postgresql --no-create-db --no-create-info --add-locks=false --quote-names=false --complete-insert=true

I just had to convert \' to two single quotes and fix MySQL's infamous null dates (0000-00-00 00:00:00) to be true nulls and was able to pipe the backup right into psql.

sed -e "s/\\\'/\'\'/g" -e "s/\'0000-00-00 00:00:00\'/null/g" backup.sql > output.sql

Following my.cnf parameter is highly recommended on high configuration machine. This will make importing myisam only data much faster.

#datadir=/var/lib/mysql
datadir=/mnt/data/mysql/
user=root
skip-innodb
default-storage-engine=myisam
key-buffer=1G
bulk_insert_buffer_size=1G
myisam_max_sort_file_size=20G

tmpdir=/mnt/data/mysql

If you are using Ubuntu and can not change the data directory, you need to edit the file

/etc/apparmor.d/usr.sbin.mysqld

Disable firewall (ufw disable) and check the ownership and directory permissions.

ufw disable

Innodb tables queries based on information_schema may be slower. In order to improve the performance, we need to disable metadata updates.

set global innodb_stats_on_metadata = 0;

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

# sysctl vm.swappiness
vm.swappiness = 60

With that setting the Linux kernel feels encouraged to use the available swap. On a dedicated MySQL server, swappiness should be set to zero to discourage kernel from swapping out application memory.

# sysctl -w vm.swappiness=0
vm.swappiness = 0

This changes the runtime setting. To allow the change to survive system restarts, just add vm.swappiness=0 as a separate line into /etc/sysctl.conf

Set up git:

yum install git-core
cd ~/.ssh
ssh-keygen -t rsa -C "shantanu.oak@gmail.com"

Contents of the file "id_rsa.pub" should be copied to gitHub site.
On the GitHub site Click “Account Settings” > Click “SSH Public Keys” > Click “Add another public key”

This will allow passwordless access to github site:
ssh -T git@github.com
_____

Global setup:

git config --global user.name "Shantanu Oak"
git config --global user.email shantanu.oak@gmail.com
_____

Next steps:

mkdir syllabalize_unicode
cd syllabalize_unicode
git init
touch README
git add README
git commit -m 'first commit'
git remote add origin git@github.com:shantanuo/syllabalize_unicode.git
git push -u origin master
_____

Existing Git Repo?

cd existing_git_repo
git remote add origin git@github.com:shantanuo/syllabalize_unicode.git
git push -u origin master
_____

clone (download) the source
git clone git@github.com:ahiguti/HandlerSocket-Plugin-for-MySQL.git
_____

Install MySQL

mkdir download
cd download
wget http://files.directadmin.com/services/all/mysql/64-bit/5.5.20/MySQL-client-5.5.20-1.linux2.6.x86_64.rpm
wget http://files.directadmin.com/services/all/mysql/64-bit/5.5.20/MySQL-devel-5.5.20-1.linux2.6.x86_64.rpm
wget http://files.directadmin.com/services/all/mysql/64-bit/5.5.20/MySQL-server-5.5.20-1.linux2.6.x86_64.rpm
wget http://files.directadmin.com/services/all/mysql/64-bit/5.5.20/MySQL-shared-5.5.20-1.linux2.6.x86_64.rpm

The commands are as follows:

rpm -iUh *

After installing mysql shared compact library, this was working as expected

wget ftp://ftp.isu.edu.tw/pub/Unix/Database/MySQL/Downloads/MySQL-5.5/MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm
rpm -ihv MySQL-shared-compat-5.5.24-1.el6.x86_64.rpm
yum install php-mysql --skip-broken

_____

Here is how it can be achieved using remi or epel

/usr/bin/wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
/bin/rpm -Uvh *.rpm
/usr/bin/yum install --enablerepo=remi mysql-server.x86_64*

Using epel:

wget http://download.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
rpm -Uvh epel-release-5-4.noarch.rpm
yum install python26

Divides the buffer pool into a user-specified number of separate regions, each with its own LRU list and related data structures, to reduce contention during concurrent memory read and write operations. The size specified by innodb_buffer_pool_size is divided among all the buffer pool instances. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1 gigabyte.

If you need fresh mysql installation you need to run script to create default mysql database and system tables. The script is called "mysql_install_db" and is either in the bin or in scripts directory of your mysql installation. Change the my.cnf to reflect new datadir and run mysql_install_db for it to create mysql database and system tables in the new datatir and then start mysqld_safe.