Linux Admin Zone

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.

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

I got stuck in a weird problem yesterday, where a newly installed MySQL Server was responsindg slow. I’ve fixed the issue after detecting a small spelling mistake in file which is entirely not related to MySQL at first instance. Here are some tips, if you’re also on the same boat where Server itself doesnt showing any error, but your user’s lodging complaints of being slow.

1. Check MySQL Configurations:

Forgot password of MySQL/PostgreSQL? Here is quick howto to recover/reset the password of root user in both Servers:

MySQL:

Step 1. Stop mysql server or Kill it:

# service mysqld stop

verify whether mysqld process stopped, if not you can go ahead to kill it:

# ps aux | grep mysql

Kill all processes shows by above command.
OR

# killall mysqld

Step 2. Start mysqld process manually without using grant table (to skip requirement of user/password):

# /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --skip-grant-tables

Step 3. Reset root password: