Communication protocols in MySQL

Generally, MySQL supports connections between clients and the server using several networking protocols. Below are the main protocols which are used by client to connect with MySQL server. TCP/IP Unix socket file Named pipe Shared memory TCP/IP connections are supported by any MySQL server unless the server is started with the –skip-networking option. These connections…

Event scheduler in MySQL 5.1

I just recently used Event scheduler which was the major feature in MySQL 5.1 version. Its very much similar to the linux crontab functionality. MySQL Event is nothing but a bunch of statements which we can run on specific schedule. When you will create any event, its just a database object like table, view or…

Some guidelines for MySQL security

Don’t share root user password and mysql.user table acess with anyone till you have full trust on it. Because that encrypted password is real password in MySQL so if anyone knows that than he/she can easily login with any user if he has access to his host. Check with “mysql -uroot ” command, If you…

Limitations of partitioning in MySQL 5.1

Some limitations of partitioning in MySQL 5.1 on one page. All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.These rules also apply to existing nonpartitioned tables that you wish to partition. Partitioning applies to all data and indexes of a table;…

Clear command doesn’t work for clear the screen in MySQL

I was very much confused when I have tried to clear the screen of MySQL and I didn’t got any result. root@nilnandan-Satellite-A200:~# mysql -uroot -p Enter password: Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement. mysql> show databases; +——————–+ | Database           | +——————–+ | information_schema | | mysql              |…

How FLUSH TABLES works in MySQL

“FLUSH TABLES” is generally used for closing all tables by force in MySQL. Its really interesting that how mysql do this. Most of time we are doing this because someone adds new tables outside of MySQL. (Like for MyISAM tables, we are copying .FRM, .MYD and .MYI files. We can do this when MySQL is…

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…