Posts in Category: database

MySQL

MyISAM vs InnoDB

MyISAM versus InnoDB

Transactions, referential integrity, concurrency, and Storage limits are the main difference between MyISAM & InnoDB.

Read More

Find out uptime of server and MySql database

To find out the uptime of your Linux server, any one of the following commands will do:

w
uptime
top

To find out the uptime of your mysql demon, log into the server using mysql command line, then issue the following command:
\s

Read More

Poor man’s MySQL replication monitoring

Using MySQL replication slave(s) for reporting (with potentially different storage engines) is a very popular way of scaling database read activities. As usual, you want to be on top of things when replication breaks so end users can be notified and issues addressed. When Nagios, Zabbix, or whatever monitoring tools

Read More

Enable slow-query-log

You can enable slow-log by un-commenting following lines in /etc/mysql/my.cnf

slow-query-log = 1
slow-query-log-file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Last line will tell slow-log to log queries not using indexes. You can keep it commented if you want to ignore queries which are not using indexes.

If your server has less RAM and you are

Read More

MySQL

Enable remote MySQL connection

Remote MySQL

As root, apply:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';

where IP is the IP you want to allow access and USERNAME is the user you use to connect.

Read More

MySQL replication : Replicating only certain databases

Replication replicates EVERYTHING from the master to slave(s), whereas you may just want one or two databases replicated.

At first add this to /etc/my.cnf on the slave:

[mysqld]
replicate-do-db=MyDb

That didn’t work very well for statements like this, assuming you are in a database other than MyDb:

insert into MyDb.TableInMyDb values (SomeValue)

Fine, I thought,

Read More

Oracle Enterprise Manager for MySQL

Oracle Enterprise Manager for MySQL provides real-time monitoring and delivers comprehensive performance, availability and configuration information for your MySQL databases.

MySQL Performance Monitoring

Comprehensive database monitoring enables you to identify the problem areas in your database environment that are degrading performance. View the real-time performance metrics and important KPIs for your MySQL

Read More

Where Are MySQL Files Stored in UNIX / Linux

Where Are MySQL Files Stored in UNIX / Linux

Usually /var/lib/mysql or /var/db/mysql directory used to store database and tales under UNIX like operating systems.

You can use the following command to locate MySQL datadir:

grep datadir /etc/my.cnf

Read More