Linux Admin Zone

MySQL stopped default inclusion of InnoDB in latest 5.1.x, so if you need InnoDB, you have to compile it from source. I have done following steps in CentOS 5.4 server to compile MySQL and optimize it later for a heavy site:

1. Remove earlier installation of MySQL, if any and download source rpm from MySQL site. I’ve also removed earlier installation of PHP here to upgrade it:

$ rpm -e php-mysql mysql-server php php-devel php-pear

Download the latest source rpm from MySQL download site, you will get source rpm like below and install it. Installation of source rpm will put it as tar file in /usr/src/redhat/SOURCES, make sure to create /usr/src/redhat/SOURCES directory before installation:

Recently I was in need to convert all databases of a MySQL server from MyISAM engine to InnoDB. They are facing severe issues due to table level locking in MyISAM and wanted move to InnoDB. This task can be a mammoth work but if you work little smarter, all you need to execute few statements and look at the progress while having cup of coffee. Also note that you can convert in both side, means from MyISAM to InnoDB and from InnoDB to MyISAM using these commands.

Sometimes in large deployments, there are cases when MySQL server, setup by you long time back which has been in use by multiple teams in your organization, needs some change or update or intrupption in its service and you are in need to know how many clients are there which connects to this server.

One way to know is to check user table in ‘mysql’ database, you might have created individual users/databases for your clients/users. But over the time, unless taking care seriously, we tend to forget the exactly how many users/scripts/machines are there making connections. May be you have used same read-only user in various scripts scattered over many machines which you’ve created just for a single client.

On a large server which run primarily mysql service only, the first step you should take is to copy the proper config file which is already optimized for large systems:

# mv /etc/my.cnf/my.cnf_OLD
# cp /usr/share/doc/mysql-server-5.0.44/my-huge.cnf /etc/my.cnf

I’m using 5.0.44 version here on a CentOS 5.2 box. You need to adjust path as per your mysql version.

Sometimes when running MySQL in batch mode, it requires to ignore some mysql errors which pops in between.

As an example, we can consider a scenerio where thousands of inserts required to be done and the statements are saved in a file, suppose in indata.sql. But it may be the case that some records are already there in database which you’re trying to insert. MySQL will show error something like below and exit:

ERROR 1062 (23000) at line 6: Duplicate entry 'blablabla' for key 2