How caches used in MySQL

Following caches are generally available in MySQL. Key Cache This is a shared cache for all B-tree index blocks which are in different MYI files. Internally, this cache is using hashing and reverse linked lists algorithm for quick caching for most recently used blocks and flushed the blocks which are not recently used or some…

Postgres Plus 8.4 vs. MySQL 5.5

Nice comparison by EnterpriseDB. http://get.enterprisedb.com/whitepapers/Postgres_Plus_8.4_vs_MySQL_5.5.pdf Below Topics are included in this comparison. General Comparison Commentary Core Database Features Database Capacities Transaction Programming and Control Query Capabilities Business Intelligence and Data Warehousing Network/Distributed Services Bulk Data Management/Protection Security Features DBA Tools Developer Functionality Scalability Solutions High Availability Solutions Here, one more link which can help to…

Why server id important in MySQL Replication

Set server id in my.cnf is one of the necessary steps to implement or start replication. The general rule is, server id must be unique across the environment where we are implementing MySQL replication. Generally while implementing multi-master/circular replication, we are setting option –log-slave-updates= 1. so when any statement of master will be executed on…

How to use mysql_upgrade script

There are 2 kind of upgrade is possible in MySQL.     Major version upgrade (i.e MySQL 4.1 to MySQL 5.0)     Minor version upgrade (i.e MySQL 5.0.45 to MySQL 5.0.47) In the Major version upgrade, people are generally using very common technique which is, taking backup by mysqldump from MySQL 4.1 and reload into MySQL…

How heartbeat helpful for MySQL Replication

Heartbeat is a project from Linux-HA. http://www.linux-ha.org/wiki/Heartbeat. Heartbeat will be helpful when we would have pair and/or more servers which are configured to replicate in circular fashion. I.e  In MySQL Replication, if master will be fail we have to do manually failover or we have to make script which will manage this thing. But if…

How to monitor performance of MySQL Server

Performance Monitoring of MySQL Server: Following are the command which we can use for session or server level performance for MySQL server. SHOW GLOBAL STATUS – shows global server status SHOW LOCAL STATUS  – This is used for session level server status Have to check following values to know how server works. Aborted_clients : Usually…

How to reset MySQL root password

1. Stop mysql service shell> /etc/init.d/mysql stop 2. Start to MySQL server without password. shell> mysqld_safe –skip-grant-tables & 3. Connect to mysql server using mysql client. shell> mysql -u root Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 107Server version: 5.1.49-1ubuntu8.1 (Ubuntu)Type ‘help;’ or ‘h’ for help. Type…