Linux Admin Zone

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.

MySQL Cluster is used to provide high-availability, high-redundancy for the distributed computing environment. You might know that beginning with MySQL 5.1.24, support for the NDBCLUSTER storage engine was removed from the standard MySQL server binaries built by MySQL. Therefore, here I’m using MySQL Cluster edition instead of MySQL Community edition. I’m using 3 servers (1 Management and 2 data) to setup cluster in CentOS 5.4 Linux box. The steps mentioned here can be used for quickly deploying a cluster and worked out for me but no guarantee that this will work for you, So its always advisable to go through official MySQL guide as well for production environment. In case you are also riding on cloud computing wave, I’ve already blogged way to setup MySQL cluster in Amazon EC2 environment.

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