MySQL
How to Ignore Replication errors in MySQL
Some times you will be getting replication errors on the slaves , this might be coming because of the following any one of the reason Might have killed a query in between in the master server Duplicate entry Version difference , due to this some of the queries may not work in slaves Any other reasons You can put [...]
How to Solve “Cannot delete or update a parent row: a foreign key constraint fails”
I was Converting my Database Engine from Innodb to MyISAM and i got the following error “Cannot delete or update a parent row: a foreign key constraint fails” Solution: I took the entire backup of the DB and edited the file and changed the  ”ENGINE=MyISAM” and restored it to a new DB.
How to solve #2002 Cannot log in to the MySQL server error in phpMyAdmin
If you get  #2002 Cannot log in to the MySQL server in phpMyAdmin change $cfg['Servers'][$i]['host'] = ‘localhost’; to $cfg['Servers'][$i]['host'] = ’127.0.0.1′;
“Wrong permissions on configuration file, should not be world writable!”.
If you get this error message like “Wrong permissions on configuration file, should not be world writable!” in phpMyAdmin and page can not load.It could be because config.inc.php is given write permission. Simply run chmod a-w config.inc.php command it will solve your problem.
How recover corrupt table in MYSQL
Steps to Recover a corrupt table in mysql 1)     Go to  /var/lib/mysql/DB_Name  (Assuming DB is stored in given path) 2)     Run below command to scan whole DB to check for errors. myisamchk /var/lib/mysql/DB_NAME/*.MYI >> /tmp/myisamchk_log.txt (Report will be exported in .txt format) Use can see babel_user is corrupted 3)     Now run myisamchk –r babel_user.MYI command [...]
Example InnoDB my.cnf file for high end GNU/Linux machines
Example InnoDB my.cnf file for high end GNU/Linux machines #—————————————————————- # # my.cnf file # # # See: # # http://dev.mysql.com/doc/refman/5.1/en/server-options.html # http://dev.mysql.com/doc/refman/5.1/en/option-files.html # # You can also dump all the variables set for mysqld with: # # mysqld –verbose –help # #—————————————————————- [client] # These options apply to all client applications # Port and [...]
A my.cnf file designed for InnoDB heavy 8GB+ 64bit environments
This is an example config file for a 64bit 8GB GNU/Linux machine running mainly InnoDB. This config file will not work on a 32bit instance as it is configured to address more memory than 32bit procceses allow. This configuration assumes that this will be a replication master. If this is a slave and it doesn’t [...]
How to check the binlog position from Mysqldump
Bin log position is used to set the replication in MySQL. Â One of the easiest way to set the replication in Mysql in the following ways. First Take the entire Dump of MySQL with mysql command mysqldump -u root -p –all-databases –master-data=2 > dump_file21102010 The second head the 100 lines in the dump file to [...]
MySQL Performance on Amazon EC2
Amazon EC2 makes it easy to get a large number of servers on which to run MySQL, but there are many choices to be made about the server and storage to use. There are 5 types of servers: small, large, extra-large, high-cpu medium and high-cpu extra-large. There are at least two types of storage: local disk and EBS. [...]
How to Optimize MySQL Performance with ZFS
This session focuses on ZFS performance with MySQL, and explores its suitability for deploying workloads based on MyISAM and InnoDB. ZFS performance relative to other file systems and the impact of ZFS compression on MySQL performance will also be examined. The ZFS Intent Log (ZIL) and the second level Adaptive Replacement Cache (L2ARC) benefit from low latencies, and benchmark results will be presented [...]
Fighting MySQL Replication Lag
Slave lag is the typical problem that limits scalability of MySQL Replication. Learn best practices … for scalable, lag-free MySQL Replication
memcached Functions For MySQL Seemless caching for MySQL
The memcached Functions for MySQL are User Defined Functions (UDFs) that give you the power … to interact with both MySQL and memcached in one place
How to Install and Configure Multi-Master Replication Manager for MySQL
How to Install and Configure MMM? This article has been refereed from mysql-mmm.org Installation Guide –  Multi-Master Replication Manager for MySQL This installation guide describes a installation of MMM 2 (without the MMM tools) based on Debian Lenny (5.0) A basic installation contains at least 2 database servers and 1 monitoring server. In this guide, I used [...]
Multi-Master Replication Manager for MySQL – MMM
MMM (Multi-Master Replication Manager for MySQL) is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time). The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP [...]
How to kill long run mysql queries
As a Database Administrator you should be aware about the slow queries .  When you are running a high traffic website with multiple mysql servers , you have to be very careful about long running queries, ideally all queries should get executed below 2 seconds for high traffic site as there are thousands of database queries are coming to [...]