facebook twitter youtube
Replication Query Optimization DB Tuning Server Load and Query Locking Linux Download Interview Questions Basic DB Commands Mysql
in Replication - 17 Mar, 2016
by Zafar Malik - no comments

Here we will setup Mysql Fabric with auto failover at DB level with 5 servers (1 Fabric Manager or Arbiter + 3 SQL Nodes + 1 Application server): So Below are required server details: 192.168.18.1 : fabric manager : mysql 5.6.29 + fabric + connector required. 192.168.18.2 : Mysql Master : mysql 5.6.29 192.168.18.3 : Mysql slave1 […]

in Visitor's Query - 22 Dec, 2014
by Zafar Malik - no comments

There is no excerpt because this is a protected post.

in Visitor's Query - 22 Dec, 2014
by Zafar Malik - no comments

There is no excerpt because this is a protected post.

in Mysql - 19 Feb, 2014
by Zafar Malik - no comments

Below are some facts related with B-TREE index usage by mysql and one example to understand this logic. a) If any table has approx. 75% same data then index will not be used instead mysql will do table scan. b) Normally mysql use only single index per table. c) Index ordering methodology: Mysql will use […]

in Mysql - 19 Feb, 2014
by Zafar Malik - no comments

Option1: If you want to keep ‘table’ in ‘Database’: Create below ‘stored procedure’: ———————————— DELIMITER $$ USE `test`$$ DROP PROCEDURE IF EXISTS `sp_variable_table`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_variable_table`() BEGIN SELECT CONCAT(‘zafar_’,REPLACE(TIME(NOW()),’:’,’_’)) INTO @tbl; SET @str=CONCAT(“create table “,@tbl,” (pbirfnum BIGINT(20) NOT NULL DEFAULT ’0′, paymentModes TEXT ,paymentmodeDetails TEXT ,shippingCharges TEXT ,shippingDetails TEXT ,hypenedSkuCodes TEXT ,skuCodes TEXT ,itemDetails […]

in Mysql - 13 Feb, 2014
by Zafar Malik - no comments

Suppose there is a title varchar(250) column and there is an index on it as per below Alter table mytable add index (title(16)); Case 1: select id, title from mytable where title=”This is my Title”; Here query will use index as searching string contains 16 character and our index also take care 16 characters.   […]

in Mysql - 13 Feb, 2014
by Zafar Malik - no comments

In simple wording, latin supports only latin characters (like english) but utf8 supports some special characters used in other international languages like french, chineese, arabic etc. As per standard, latin1 stores 1 character in 1 byte while utf8 1 character in 1-3 bytes. But if we store all characters in latin even in a utf8 […]

in Linux - 17 Jan, 2014
by Zafar Malik - no comments

Problem: I was restarting my server’s mysql service when I got this issue as mysql stopped successfully but could not start and throwing below error message. Error Message: server quit without updating PID file (/var/lib/mysql/mysql.pid).[FAILED] Steps for Resolution: Step1: I checked my server’s error logs, where I got below error message. Error Messages: InnoDB: Initializing […]

by Zafar Malik - no comments

Problem: Suddenly one day, high connection/thread alert started to come from a server. Step1: After tracking connections/executing queries by below commands, it came to know that so many connections are going to sleep mode for a specific user (user1) and for a specific DB (mydb). show full processlist; Step2: As some time we were getting […]

in Basic DB Commands - 05 Dec, 2013
by Zafar Malik - no comments

DB Schema Related Commands # Database Creation: MySQL> CREATE DATABASE TEST_DB; — Where TEST_DB is my database name. # Table Creation (under TEST_DB): MySQL> USE TEST_DB; MySQL> CREATE TABLE TEST_TABLE(cust_id int, cust_name varchar(50), cust_address varchar(100)); # Table creation (Including all possible options related with table creation like primary key, index, unique index, relation ship etc.) […]