MySQL database repair - myisamchk

Hi,

To check/repair MySQL database which using the database engine MyISAM, we can use the command "myisamchk" . Syntacx is as given below :

 To check the status :

myisamchk /var/lib/mysql/DATABASE_NAME/*.MYI

This will displays the tables that should be repaired.

To repair the whole database,

myisamchk --silent --force --fast --update-state /var/lib/mysql/DATABASE_NAME/*.MYI

To repair a single table:

myisamchk -r /var/lib/mysql/DATABASE_NAME/TABLE_NAME.MYI

MySQL 5.5 Installation from source

Installation of MySQL :

1) Download and extract the package :

wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.21.tar.gz

2) shell> cmake .

3) shell> make

4) make install

End of source-build specific instructions

Post-installation setup

----------------------------------

1) cd /usr/local/mysql

2) chown -R mysql .

3) chgrp -R mysql .

4) scripts/mysql_install_db --user=mysql

5) chown -R root .

6) chown -R mysql data

#Next command is optional

shell> cp support-files/my-medium.cnf /etc/my.cnf

To start MySQL

-------------------------

bin/mysqld_safe --user=mysql &

#Next command is optional

shell> cp support-files/mysql.server /etc/init.d/mysql.server

Once we done this, we can start MySQL using the command “/etc/init.d/mysql.server start” .  

Change collation of MySQL database

Hi,

Please use the following PHP script to change the collation of MySQL databases.

=============================
<?php
$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>

=============================


Replace the necessary fields.

Convert InnoDB TO MyISAM Script

Hi,

To convert InnoDB to MyISAM, you can use the following script. This is applicable for a single database. Stop Apache before doing this.

=======================

 #/bin/bash

#script to convert the whole database to myisam format.

cp -iRfp /var/lib/mysql /var/lib/mysql-$(date +%s)

for DATABASE_NAME in $(mysql --batch --column-names=false -e "show databases");

do

for t in $(mysql --batch --column-names=false -e "show tables" $DATABASE_NAME);

do

mysql -e "alter table $t type=MyIsam" $DATABASE_NAME;

echo "converted "$t" to MyIsam"

done

done 

========================

Thanks.

MySQL installation Error - InnoDB: Error: pthread_create returned 11

Hi,

In the new version of MySQL, after compiling it from source code,i it may not start. While analyzing the error logs if you see the following error :

InnoDB: Error: pthread_create returned 11

Apply this fix :

 ulimit -s unlimited