Linux Admin Zone

One of my friend requested to setup two independent MySQL DB servers in his CentOS 5.4 server box. One MySQL (5.0.77) service was already running on the machine, So I had to install another one. Though, I dont recommend running multiple instances on a single server, instead due to cheap hardware you may better setup a MySQL cluster for best performance. In this case, after initial investigation, I’ve found that there are few ways to implement this. The idea of using MySQL Sandbox interested me most. At the end of day, there was two server running simultaneously on the using different ports for connections. Here are the steps, I took to install and configure multiple MySQL servers:

So you got the notice that one of table from large DB is crashed/corrupted. You can start a repair table command inside MySQL prompt or from phpmyadmin but if you have enough free RAM in your server, you’d better tweak some options of ‘myisamchk’ and execute it from command prompt. That can save a lot of time

Here is a reapir command that I just executed to repair a table having size of 3.2 GB:

# myisamchk –silent –force –update-state –key_buffer_size=1024M –sort_buffer_size=1024M –read_buffer_size=16M –write_buffer_size=16M tbl_mailing

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.

Next, find out which database engines you are NOT using, you should disable them in config file. For example,let’s say all of our tables are in MyISAM format only, so we can safely disable InnoDB and BDB engines, add following statements in the [mysqld] section of /etc/my.cnf file:

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 scenario 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 or you are importing large data for development/test purpose and you won’t bother much if some rows can be skipped. In normal case, MySQL will show error something like below and exit:

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

This is a quick Howto for setting up MySQL cluster using Amazon EC2 instances. I have used 3 small instances (1 mgmt + 2 data nodes) from RightScale’s CentOS 5 public image to test things. I’ve a working cluster using these steps but there’s no guarantee that these steps works for you as well.

Management server for cluster:
Although we can set up management server for cluster in one of the data node but we should have a third node as if one of the two MySQL cluster nodes fails, and the management server is not running, then the data on the two cluster nodes will become inconsistent and if the same node fails which have management server, then we will not be able to create another node or configure cluster.