Category: MySQL Database

MySQL Database

Benchmarking for System and High Memory-CPU utilization – Part 1

Benchmarking for System and High Memory-CPU utilization – Part 1

Hello Guy’s,

Every System Admin and technical person who is working or doing any kind of High Availability stuff, He really needs bench marking to check and verify system health under development, here I am very beginner in this kind of system benchmarking to know how server/CPU/memory and network reacts when doing this and given load (purposely) on system to use or run the service or process. I will explain how I have done this in the part -2 😉

Memory Util

 

CPU Util

 

-Ravi

How to configure multi master MySQL replication

How to configure multi master MySQL replication

Introduction ~

I was planning to write article on Multi Master MySQL replication since long time; Finally started now!. Please refer the the article on “How to configure MySQL replication with one Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Let me inform you all that Multi Master replication in MySQL is purely based on following two variables. It has nothing to do with replication technology used in MySQL replication….

mysql> show variables  like  ‘%increment_%’;

+—————————————+——-+
| Variable_name                                   | Value |
+—————————————+——-+
| auto_increment_increment    |  1    |
| auto_increment_offset               |  1    |
+—————————————+—–+
2 rows in set (0.00 sec)

mysql>

** Requirements ~

a) Master Hosts (2 master in my case) ~

master-1 => 10.66.66.194
master-2 => 10.66.90.135
b) Replication Slave (1 slave) ~
slave => 10.66.75.137

c) MySQL server (with replication support)

** Let us understand how it works ?


* Master-1 Server =>

Set following variables…

mysql> set auto_increment_increment=5;
mysql> set auto_increment_offset=1;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      3 | Ravi Bhure       |
|      5 | Karthik Appigita |
|      7 | Ameya Pandit     |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

* Master-2 Server =>

Set following variables…

mysql> set auto_increment_increment=2;
mysql> set auto_increment_offset=2;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      2 | Nilkanth Parab   |
|      4 | Nishit Shah      |
|      6 | Ram Krishna      |
|      8 | Suhail Thakur    |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

** What is the importance of “auto_increment_increment” and “auto_increment_offset” ~

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default | Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+

auto_increment_offset => This is BASE value for column with “auto_increment” attribute (please refer the above example)
auto_increment_increment => This is the increment value for column with “auto_increment” attribute

** If you combine the both tables (master-1 and master-2) the final table will look like this ~

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      3 | Ravi Bhure       |
|      4 | Nishit Shah      |
|      5 | Karthik Appigita |
|      6 | Ram Krishna      |
|      7 | Ameya Pandit     |
|      8 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Where –
N => nth number of master server (on master-1 keep it 1 and on master-2 keep it 2 and so on..)
M => Total number of Master Server (2 in our case but better to keep this value high so that we can add new master server easily)

log-slave-updates => Slave server does not log to its own binary log any updates that are received from a Master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log.

** Make sure that MySQL is running and up on all master servers and slave server-

How to setup Multi Master MySQL replication ? –

Step 1] Create Database/Tables on  Master  & Slave Servers –

You can create DB on all master & slave server or  create on one server and export that DB on  rest of all servers…

Master-1 => Create DB and Table

mysql> create database student;

mysql> use student;

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

mysql> show tables;
+——————-+
| Tables_in_student |
+——————-+
| class             |
+——————-+
1 row in set (0.00 sec)

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default         |      Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.00 sec)

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

* Now take dump of “student” DB and export it on all master and Slave server…

[root@master-1~]# mysqldump -u root -p  -d student > /home/arunsb/student.sql

* SCP the dump file on master-2 and slave server ~
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.90.135:/tmp/student.sql
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.75.137:/tmp/student.sql

Login on master-2 and slave ~

mysql> create database student;

[root@master-2~]# mysql -u root -p student < /tmp/student.sql
Enter password:
[root@master-2~]#

[root@master-2~]# mysql -u root -p
Enter password:

mysql> use student

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

** Please repeat the same steps on Slave server as well…

Step 2] Update “my.cnf” config file on master-1,master-2 and slave server –

[root@master-1~]# cat /etc/my.cnf

###########################
##MySQL replication setting

#Master setting(1)
server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=1
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-1~]#

[root@master-2~]# cat /etc/mysql/my.cnf

###########################
##MySQL replication setting

#Master setting(2)
server-id = 2
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db=student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=2
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.66.194
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-2~]#

* please create directory for binary log and set permission…

[root@master-1~]# mkdir -p /var/log/mysql/binary/
[root@master-1~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

[root@master-2~]# mkdir -p /var/log/mysql/binary/
[root@master-2~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

** MySQL Replication Slave ~

[root@slave~]# cat  /etc/my.cnf

[mysqld]

########################################
##slave setting
server-id=4
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
########################################

[root@slave~]#


Step 3] Give Replication permission on both masters ~


** Master (1 & 2) ~

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’10.66.%.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.00 sec)

mysql>

Step 4] Restart MySQL on both master as well as replication slave server ~

** Please verify setting on master-1 and master-2 server…

* Master-1

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

* Master-2

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

** Please verify ‘master’ & ‘slave’ status on both masters(1 & 2) and slave –

mysql> show master status;
mysql> show slave status;

** Multi Master replication is started…

Step 5] Add few records on Master-1 & Master-2 server at same time ~

Add records on both master server at same time and check master and replication slave status as shown above….

** Add following on master-1

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

** Add following on master-2

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

** Please verify the numbers of records on both masters and slave….

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      6 | Ravi Bhure       |
|     11 | Karthik Appigita |
|     16 | Ameya Pandit     |
|     17 | Nishit Shah      |
|     22 | Ram Krishna      |
|     27 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

* So we all learned to configure multi-master MySQL replication. Enjoy!!

Regards,
Arun Bagul

Partitioning in MySQL

Partitioning in MySQL

Introduction –

Partitioning is available in Oracle since 8i (few feature or type) version but in MySQL it is available after 5.1 version. Partitioning allows you to distribute portions of table across a file system (physically at different location). User can define the partitioning of table so that some portion of table can stored at different physical location….

Table is structure which has columns and rows. Partitioning can be applied to rows or volumes.

* Horizontal partitioning ~ Storing different rows of table to different physical location is called as horizontal partitioning of table.
* Vertical partitioning ~ Storing different columns of table to different physical location is called as vertical partitioning of table.

MySQL supports only support “Horizontal partitioning” !!

** Advantages of using partitions ~

a) Smaller and more manageable pieces of data
b) Faster access of data
c) Import / export at the partition Level
d) Each partitions is independent of other
e) Easy to use
f) Reduced recovery time
g) Failure impact is less

1] How to check whether MySQL installed on your system supports ‘partitioning’ ?

* Check MySQL version as…

root@laptop:~# mysqladmin version -u root -p

* Check ‘partition’ plugin is installed or not… ?

mysql> SHOW PLUGINS;
+————+———-+—————-+———+———+
| Name | Status | Type | Library | License |
+————+———-+—————-+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL | **

…..

+————+———-+—————-+———+———+
10 rows in set (0.00 sec)

mysql>

* Check above output. If you don’t see ‘partition’ entry in above query. Then MySQL version does not support partitioning. If it entry exist then check “have_partitioning” system variable is set to ‘YES’ or not….

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
1 row in set (0.00 sec)
Partitioning
mysql>

2] Which storage engines (table type) supports partitioning?

MySQL partitioning engine runs in a separate layer and can interact with any of storage engine. So we can use most of the storage engine as backend/table type to store data. Note we must use same storage engine for all partitions of table.

3] Types of Partitions ~

There are 4 types of partitioning in MySQL or Oracle

a) Range Partitioning
b) List Partitioning
c) Hash Partitioning
d) Key Partitioning

NOTE ~ In any type of partitioning – partitions are always numbered automatically and in sequence starting with 0. When a new row is inserted into a partitioned table, these partition numbers that are used in identifying the correct partition. Also partition names are not case-sensitive. Number of partitions for the table, this must be expressed as a positive, non-zero integer literal with no leading zeroes, and may not be an expression.

MAXVALUE ~ It is an integer value that is always greater than the largest possible integer value (least upper limit).

** Let’s create database ‘mydata’ and create table with each partition type –

mysql> create database mydata;
Query OK, 1 row affected (0.33 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydata |
| mysql |
+——————–+
3 rows in set (0.00 sec)

mysql>

A] Range Partitioning ~ table is partitioned by range of values. Each partition contains row which lies within a given range. The ranges should be
contiguous but not overlapping…

mysql> use mydata;
Database changed

mysql> CREATE TABLE cricket (
-> player_name VARCHAR(30),
-> country_name VARCHAR(30),
-> match_count INT,
-> run_scored INT NOT NULL
-> )
-> PARTITION BY RANGE (run_scored)
-> (
-> PARTITION p0 VALUES LESS THAN (26),
-> PARTITION p1 VALUES LESS THAN (51),
-> PARTITION p2 VALUES LESS THAN (76),
-> PARTITION p3 VALUES LESS THAN (101),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
->
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from mydata;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
+——————+
1 row in set (0.00 sec)

mysql> desc cricket;
+————–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| player_name | varchar(30) | YES | | NULL | |
| country_name | varchar(30) | YES | | NULL | |
| match_count | int(11) | YES | | NULL | |
| run_scored | int(11) | NO | | NULL | |
+————–+————-+——+—–+———+——-+
4 rows in set (0.00 sec)

mysql>

*** If cricketer’s score is…
<= 25 then row will go to p0 partition
<= 50 then row will go to p1 partition and so on ..
If score is more than 100 then row will go to p4 partition

* inserting few records to test behavior…

mysql> SELECT * FROM cricket;
+————-+————–+————-+————+
| player_name | country_name | match_count | run_scored |
+————-+————–+————-+————+
| Rahul | India | 2 | 16 |
| Sachin | India | 2 | 56 |
| Saurav | India | 2 | 80 |
| Sachin | India | 1 | 122 |
| Rahul | India | 1 | 153 |
| Saurav | India | 1 | 183 |
+————-+————–+————-+————+
6 rows in set (0.00 sec)

mysql>

** Let’s us check the physical location of database….

root@laptop:~# ls /var/lib/mysql/mydata/
cricket.frm cricket#P#p0.MYD cricket#P#p1.MYD cricket#P#p2.MYD cricket#P#p3.MYD cricket#P#p4.MYD db.opt
cricket.par cricket#P#p0.MYI cricket#P#p1.MYI cricket#P#p2.MYI cricket#P#p3.MYI cricket#P#p4.MYI
root@laptop:~#

B] LIST Partitioning ~ It is similar to range partitioning. But each partition is defined based on the columns value which is one of value in lists,
rather than in one of a set of contiguous ranges of values…

mysql> CREATE TABLE world (
-> country_name VARCHAR(25) NOT NULL,
-> country_id INT NOT NULL
-> )
-> PARTITION BY LIST (country_id)
-> (
-> PARTITION asia VALUES IN (91,95,35,20),
-> PARTITION europe VALUES IN (10,16,82,84),
-> PARTITION america VALUES IN (12,25),
-> PARTITION australia VALUES IN (60,65)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
| world |
+——————+
2 rows in set (0.00 sec)

mysql>

* inserting few records to test behavior…

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

** What if ‘country_id’ value NOT matching with lists values ?

=> use IGNORE keyword while inserting records… let’s try

mysql> INSERT IGNORE INTO world VALUES (‘South Africa’,50);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

C] HASH Partitioning ~ HASH is used primarily to ensure an even distribution of data among a all number of partitions. If you do not include a
PARTITIONS clause while creating table with HASH partitioning, the number of partitions by defaults is one(1). The partition in
which the record is stored is determined by which_partition = MOD(expression, total_no_partition)

mysql> CREATE TABLE africa (
-> country_name VARCHAR(50),
-> population INT,
-> census_date DATE NOT NULL DEFAULT ‘2000-01-01’
-> )
-> PARTITION BY HASH (YEAR(census_date))
-> PARTITIONS 5;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| africa |
| cricket |
| world |
+——————+
3 rows in set (0.01 sec)

mysql>

mysql> SELECT * FROM africa;
+————–+————+————-+
| country_name | population | census_date |
+————–+————+————-+
| Nigeria | 2278032 | 2008-09-18 |
| Egypt | 21278032 | 2008-05-28 |
| South Africa | 1678032 | 2009-04-20 |
+————–+————+————-+
3 rows in set (0.00 sec)

mysql>

D] KEY Partitioning ~ It is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression.

4] Subpartitioning ~

It is also known as composite partitioning in which partition is divided in to sub partition. Use SUBPARTITION clauses while creating table.

**Few useful command to know more about tables –

mysql> SHOW CREATE TABLE cricket \G
*************************** 1. row ***************************
Table: cricket
Create Table: CREATE TABLE `cricket` (
`player_name` varchar(30) DEFAULT NULL,
`country_name` varchar(30) DEFAULT NULL,
`match_count` int(11) DEFAULT NULL,
`run_scored` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (run_scored)
(PARTITION p0 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (51) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (76) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (101) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql>

** Below statement will give information about all tables in given DB…

mysql> SHOW TABLE STATUS FROM mydata;

Thank you,
Arun Bagul

MySQL Clustering – configuration and testing

MySQL Clustering – configuration and testing

Introduction – MySQL Clustering is simple, easy to setup and reliable solution. That enables clustering of in-memory databases in a shared-nothing system. MySQL supports only NDB storage or table type system in clustering environment. MySQL Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB. A MySQL Cluster consists of a set of computers, each running a one or more processes which may include a MySQL Server (API), a Data (Storage) node, a Management server.

In MySQL Cluster the tables are stored in the Data (Storage) nodes. Due to this tables are directly accessible from all other MySQL servers in the cluster, provided that the Database which contains the table is available (if not need to create database) on all MySQL nodes. The Data stored in the Data nodes for MySQL Cluster can be mirrored. The NDB storage engine can be configured with a range of failover and load balancing options. NDB is an in-memory storage engine offering high-availability and data-persistence features.

mysql-clustering

There are three types of cluster nodes, in MySQL Cluster configuration. There will be at least three nodes, one of each of types is require to configure MySQL clustering successfully.

1) Management (MGM) Node – Manages the other nodes within the MySQL Cluster, performing such functions as providing configuration data, starting and stopping nodes, running backup, and so forth. Because this node type manages the configuration of the other nodes, a node of this type should be started first, before any other node. An MGM node is started with the command ndb_mgmd.

2) Data node – This node stores cluster data. There are as many data nodes as there are replicas, times the number of fragments. For example, with two replicas, each having two fragments, you will need four data nodes. It is not necessary to have more than one replica. A data node is started with the command ndbd.

3) SQL node – use to accesses the cluster data. SQL node is a traditional MySQL server that uses the NDB Cluster storage engine. An SQL node is typically started with the command mysqld –ndbcluster or by using mysqld with the ndbcluster option added to my.cnf.

An SQL node is actually just a specialised type of API node, which designates any application which accesses Cluster data. One example of an API node is the ndb_restore utility that is used to restore a cluster backup. It is possible to write such applications using the NDB API.

** Let’s implement MySQL cluster practically –

In my case there are three(3) Storage Node ie (192.168.0.2 ,192.168.0.3 and 192.168.0.4), four(4) MySQL API server (192.168.0.1, 192.168.0.2, 192.168.0.3 and 192.168.0.4 ) and one Management Node (192.168.0.5)…

* Please follow these steps carefully you can easily set MySQL clustering.

1] Login to Management node here IP of MGM node is 192.168.0.5

* Create directory where data for Mgmd (Management node) is stored –

root@fileserver:~# mkdir /var/lib/mysql-cluster_MGM
root@fileserver:~#

root@fileserver:~# ll -d /var/lib/mysql-cluster_MGM
drwxr-xr-x 2 root root 4096 2008-01-05 11:22 /var/lib/mysql-cluster_MGM
root@fileserver:~#

* Make sure that the owner of data directory is MySQL user ie “mysql” –

root@fileserver:~# chown mysql:mysql -R /var/lib/mysql-cluster_MGM/
root@fileserver:~#

root@fileserver:~# ll -d /var/lib/mysql-cluster_MGM/
drwxr-xr-x 2 mysql mysql 4096 2008-01-05 11:22 /var/lib/mysql-cluster_MGM/
root@fileserver:~#

2] How to start Management Node

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgmd –help
Usage: ndb_mgmd [OPTIONS]
MySQL distrib 5.0.21, for pc-linux-gnu (i486)
-?, –usage Display this help and exit.
-?, –help Display this help and exit.
-V, –version Output version information and exit.
-c, –ndb-connectstring=name
Set connect string for connecting to ndb_mgmd. Syntax:
“[nodeid=<id>;][host=]<hostname>[:<port>]”. Overides
specifying entries in NDB_CONNECTSTRING and Ndb.cfg
–ndb-mgmd-host=name
Set host and port for connecting to ndb_mgmd. Syntax:
<hostname>[:<port>].
–ndb-nodeid=# Set node id for this node.
–ndb-shm Allow optimizing using shared memory connections when
available
–ndb-optimized-node-selection
Select nodes for transactions in a more optimal way
-c, –connect-string=name
same as –ndb-connectstring
–core-file Write core on errors.
–character-sets-dir=name
Directory where character sets are.
-f, –config-file=name
Specify cluster configuration file
-P, –print-full-config
Print full config and exit
-d, –daemon Run ndb_mgmd in daemon mode (default)
–interactive Run interactive. Not supported but provided for testing
purposes
–no-nodeid-checks Do not provide any node id checks
–nodaemon Don’t run as daemon, but don’t read from stdin
–mycnf Read cluster config from my.cnf

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————–
ndb-connectstring (No default value)
ndb-mgmd-host (No default value)
ndb-nodeid 0
ndb-shm FALSE
ndb-optimized-node-selection TRUE
connect-string (No default value)
core-file FALSE
character-sets-dir (No default value)
config-file (No default value)
print-full-config FALSE
daemon TRUE
interactive FALSE
no-nodeid-checks FALSE
nodaemon FALSE
mycnf FALSE
root@fileserver:/var/lib/mysql-cluster_MGM#

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgmd -f /var/lib/mysql-cluster_MGM/config.conf
root@fileserver:/var/lib/mysql-cluster_MGM#

root@fileserver:/var/lib/mysql-cluster_MGM# netstat -nlp | grep ndb
tcp 0 0 0.0.0.0:1186 0.0.0.0:* LISTEN 24291/ndb_mgmd

root@fileserver:/var/lib/mysql-cluster_MGM#
root@fileserver:/var/lib/mysql-cluster_MGM# /etc/init.d/mysql-ndb
mysql-ndb mysql-ndb-mgm
root@fileserver:/var/lib/mysql-cluster_MGM# /etc/init.d/mysql-ndb-mgm

3] Managment Node command line utility –

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
ndb_mgm ndb_mgmd
root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 (not connected, accepting connect from 192.168.0.2)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 (not connected, accepting connect from 192.168.0.4)

ndb_mgm>

4] Login to Storage Node 192.168.0.2 and edit my.cnf file –

* Make changes to my.cnf file as shown below –

root@asterisk:/etc/mysql# tail /etc/mysql/my.cnf
# The following configuration is read by the ndbd storage daemons,
# not from the ndb_mgmd management daemon.
#
#################################
#Use this MySQL server as NDB storage/Data node
#MGM node IP is – 192.168.0.5

[MYSQL_CLUSTER]
ndb-connectstring=192.168.0.5

root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
root@asterisk:/etc/mysql# netstat -nlp | grep 3306
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 19518/mysqld
root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# ndbd –help
Usage: ndbd [OPTIONS]
MySQL distrib 5.0.22, for pc-linux-gnu (i486)
-?, –usage Display this help and exit.
-?, –help Display this help and exit.
-V, –version Output version information and exit.
-c, –ndb-connectstring=name
Set connect string for connecting to ndb_mgmd. Syntax:
“[nodeid=<id>;][host=]<hostname>[:<port>]”. Overides
specifying entries in NDB_CONNECTSTRING and Ndb.cfg
–ndb-mgmd-host=name
Set host and port for connecting to ndb_mgmd. Syntax:
<hostname>[:<port>].
–ndb-nodeid=# Set node id for this node.
–ndb-shm Allow optimizing using shared memory connections when
available
–ndb-optimized-node-selection
Select nodes for transactions in a more optimal way
-c, –connect-string=name
same as –ndb-connectstring
–core-file Write core on errors.
–character-sets-dir=name
Directory where character sets are.
–initial Perform initial start of ndbd, including cleaning the
file system. Consult documentation before using this
-n, –nostart Don’t start ndbd immediately. Ndbd will await command
from ndb_mgmd
-d, –daemon Start ndbd as daemon (default)
–nodaemon Do not start ndbd as daemon, provided for testing
purposes
–foreground Run real ndbd in foreground, provided for debugging
purposes (implies –nodaemon)

Variables (–variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
——————————— —————————–
ndb-connectstring 192.168.0.5
ndb-mgmd-host (No default value)
ndb-nodeid 0
ndb-shm FALSE
ndb-optimized-node-selection TRUE
connect-string 192.168.0.5
core-file FALSE
character-sets-dir (No default value)
initial FALSE
nostart FALSE
daemon TRUE
nodaemon FALSE
foreground FALSE
root@asterisk:/etc/mysql#

* Data on Storage node is stored in ” /var/lib/mysql-cluster” directory. The MySQL user ie “mysql” need to be owner of this directory.

root@asterisk:/etc/mysql# ll -d /var/lib/mysql-cluster/
drwxr-xr-x 2 root root 4096 2006-06-16 17:38 /var/lib/mysql-cluster/
root@asterisk:/etc/mysql#

root@asterisk:/etc/mysql# chown mysql:mysql -R /var/lib/mysql-cluster/
root@asterisk:/etc/mysql# ll -d /var/lib/mysql-cluster/
drwxr-xr-x 2 mysql mysql 4096 2006-06-16 17:38 /var/lib/mysql-cluster/
root@asterisk:/etc/mysql#

** How to start Storage or Data node first time –

root@asterisk:/etc/mysql# ndbd –initial

….
root@asterisk:/etc/mysql#

Note – please use “–initial” while starting storage or data node first time
root@asterisk:/etc/mysql# netstat -nlp | grep ndb
tcp 0 0 192.168.0.2:38321 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:43001 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:41115 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:54651 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:55003 0.0.0.0:* LISTEN 19686/ndbd
tcp 0 0 192.168.0.2:59453 0.0.0.0:* LISTEN 19686/ndbd

root@asterisk:/etc/mysql#

NOTE :: Please follow the same steps as mention here for other storage node ie 192.168.0.3 and 192.168.0.4.

5] Goto Managment node and check whether 192.168.0.2 Storage node is activated or not

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, starting, Nodegroup: 0)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 (not connected, accepting connect from 192.168.0.4)

ndb_mgm>

6] Now login to server 192.168.0.4 and configure mysql server as MySQL Node (API) –

root@linserver:~# vi /etc/mysql/my.cnf

[mysqld]

#
#setting for SQL-node of MySQL cluser
#
###########################################
#Use this MySQL server as NDB storage/Data node
#MGM node IP is – 192.168.0.5
#set Table/engine type to NDB
ndbcluster
#set MGM IP address
ndb-connectstring=192.168.0.5

root@linserver:~#

7] Goto Managment node and check whether 192.168.0.4 as MySQL node is activated or not –

root@fileserver:/var/lib/mysql-cluster_MGM# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, starting, Nodegroup: 0)
id=3 @192.168.0.3 (Version: 5.0.21, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 (not connected, accepting connect from 192.168.0.4)

ndb_mgm>

** Now check that MySQL API node 192.168.0.4 is activated

ndb_mgm> show
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, Nodegroup: 0, Master)
id=3 @192.168.0.3 (Version: 5.0.21, Nodegroup: 0)
id=4 @192.168.0.4 (Version: 5.0.22, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 @192.168.0.4 (Version: 5.0.22)

ndb_mgm>

NOTE:: Please follow the same steps as mention here for other MySQL API node ie 192.168.0.1, 192.168.0.2 and 192.168.0.3

** How to check/use MySQL Clustering –

8] Login to MySQL server 192.168.0.4 and follow the process

root@linserver:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 708 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> create database arunbagul;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| arunbagul |
+——————–+
2 rows in set (0.00 sec)

mysql>

mysql> use arunbagul;
Database changed
mysql> CREATE TABLE cluster_tab (rollno INT NOT NULL , name varchar(255)) ENGINE=’NDBCLUSTER’;
Query OK, 0 rows affected (0.38 sec)

mysql>

mysql> SHOW TABLES;
+———————+
| Tables_in_arunbagul |
+———————+
| cluster_tab |
+———————+
1 row in set (0.00 sec)

mysql>
mysql> INSERT INTO cluster_tab VALUES ( 1 , ‘Arun Bagul’ ) ;
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO cluster_tab VALUES ( 2 , ‘Yogesh Nikam’ );
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO cluster_tab VALUES ( 3 , ‘Ketan Mayangar’ );
Query OK, 1 row affected (0.01 sec)

mysql> select * from cluster_tab;
+——–+—————-+
| rollno | name |
+——–+—————-+
| 2 | Yogesh Nikam |
| 3 | Ketan Mayangar |
| 1 | Arun Bagul |
+——–+—————-+
3 rows in set (0.16 sec)

mysql>

9] Now login to MySQL server of 192.168.0.3 which is acting as MySQL node (API).

root@proxy:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| auth |
| mysql |
+——————–+
7 rows in set (0.00 sec)

mysql>

NOTE :: See out put of above query. It’s not showning the “arunbagul” DB which is clustered environment. Please Remember that you need to create DB on each MySQL API node, for DB which is in MySQL cluster! Then after the Clustered DB can be accessed from all MySQL API. All the tables etc are also available….

mysql> create database arunbagul;
Query OK, 1 row affected (0.07 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| arunbagul |
| auth |
| mysql |
+——————–+
8 rows in set (0.00 sec)

mysql> use arunbagul ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> show tables;
+———————+
| Tables_in_arunbagul |
+———————+
| cluster_tab |
+———————+
1 row in set (0.01 sec)

mysql> select * from cluster_tab;
+——–+—————-+
| rollno | name |
+——–+—————-+
| 1 | Arun Bagul |
| 3 | Ketan Mayangar |
| 2 | Yogesh Nikam |
+——–+—————-+
3 rows in set (0.04 sec)

mysql> INSERT INTO cluster_tab VALUES ( 4 , ‘Nishit Shah’ );
Query OK, 1 row affected (0.10 sec)

mysql> quit
Bye
root@proxy:~#

10] Now login to MySQL server 192.168.0.4 which is MySQL API node.

root@linserver:~# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 800 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> select * from cluster_tab;
+——–+—————-+
| rollno | name |
+——–+—————-+
| 2 | Yogesh Nikam |
| 3 | Ketan Mayangar |
| 1 | Arun Bagul |
| 4 | Nishit Shah |
+——–+—————-+
4 rows in set (0.12 sec)

mysql>

11] Now login to MySQL Managment node.. and check the MySQL Cluster;

root@fileserver:/home# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 @192.168.0.2 (Version: 5.0.22, Nodegroup: 0, Master)
id=3 @192.168.0.3 (Version: 5.0.21, Nodegroup: 0)
id=4 @192.168.0.4 (Version: 5.0.22, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 @192.168.0.3 (Version: 5.0.21)
id=8 @192.168.0.4 (Version: 5.0.22)

ndb_mgm>

12] How to start/stop, restart MySQL Cluster –

** Login to MGM node and then start MGMD node as…

root@fileserver:~# ndb_mgmd -f /var/lib/mysql-cluster_MGM/config.conf
root@fileserver:~# ndb_mgm
— NDB Cluster — Management Client —
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)] 3 node(s)
id=2 (not connected, accepting connect from 192.168.0.2)
id=3 (not connected, accepting connect from 192.168.0.3)
id=4 (not connected, accepting connect from 192.168.0.4)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.0.5 (Version: 5.0.21)

[mysqld(API)] 4 node(s)
id=5 (not connected, accepting connect from 192.168.0.1)
id=6 (not connected, accepting connect from 192.168.0.2)
id=7 (not connected, accepting connect from 192.168.0.3)
id=8 (not connected, accepting connect from 192.168.0.4)

ndb_mgm>

*** How to start NDBD Node from MGM interface..

ndb_mgm> 2 STATUS
Node 2: not connected

ndb_mgm> 2 START
Start failed.
* 22: Error
* No contact with the process (dead ?).

ndb_mgm>

That’s it!! MySQL clustering is working…

Thank you,
Arun Bagul

MySQL Replication ( One master multiple slave )

MySQL Replication ( One master multiple slave )

Introduction : –

MySQL Replication allows databases on one MySQL server to be duplicated on another, were introduced in MySQL 3.23.15.

For a description of the syntax of replication-related SQL statements,I suggest that you visit Web site at http://www.mysql.com often and read updates to this chapter. Replication is constantly being improved, and updates the manual frequently with the most current information.

MySQL support for one-way replication. One server acts as the master, while one or more other servers act as slaves. The master server writes updates to its binary log files, and maintains an index of the files to keep track of log rotation. These logs serve as a record of updates to be sent to slave servers. When a slave server connects to the master server, it informs the master of its last position within the logs since the last successfully propagated update. The slave catches up any updates that have occurred since then, and then blocks and waits for the master to notify it of new updates.

A slave server can also serve as a master if you want to set up chained replication servers.

Note that when you are using replication, all updates to the tables that are replicated should be performed on the master server. Otherwise, you must always be careful to avoid conflicts between updates that users make to tables on the master and updates that they make to tables on the slave.

1] One-way replication has benefits for robustness, speed, and system administration –

* Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.
* Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers.
* SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of sync. This load-balancing strategy is effective if non-updating queries dominate, but that is the normal case.
* Another benefit of using replication is that you can perform backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made.

2] Replication Implementation Overview –

MySQL replication is based on the master server keeping track of all changes to your databases (updates, deletes, and so on) in the binary logs. Therefore, to use replication, you must enable binary logging on the master server. Each slave server receives binary log from the master. Master saves updates which master has recorded in its binary log, so that the slave can execute the same updates on its copy of the data.

It is very important to realize that the binary log is simply a record starting from the fixed point in time at which you enable binary logging. Any slaves that you set up will need copies of the databases on your master as they existed at the moment you enabled binary logging on the master. If you start your slaves with databases that are not the same as what was on the master when the binary log was started, your slaves may fail.

One way to copy the master’s data to the slave is to use the LOAD DATA FROM MASTER statement. Be aware that LOAD DATA FROM MASTER is available only as of MySQL 4.0.0 and currently works only if all the tables on the master are MyISAM type. Also, this statement acquires a global read lock, so no updates on the master are possible while the tables are being transferred to the slave. When we implement lock-free hot table backup (in MySQL 5.0), this global read lock will no longer be necessary.

Due to these limitations, we recommend that at this point you use LOAD DATA FROM MASTER only if the dataset on the master is relatively small, or if a prolonged read lock on the master is acceptable. While the actual speed of LOAD DATA FROM MASTER may vary from system to system, a good rule of thumb for how long it will take is 1 second per 1MB of data. That is only a rough estimate, but you should get close to it if both master and slave are equivalent to 700MHz Pentium performance and are connected through a 100MBit/s network.

After the slave has been set up with a copy of the master’s data, it will simply connect to the master and wait for updates to process. If the master goes away or the slave loses connectivity with your master, it will keep trying to connect periodically until it is able to reconnect and resume listening for updates. The retry interval is controlled by the –master-connect-retry option. The default is 60 seconds.

Each slave keeps track of where it left off. The master server has no knowledge of how many slaves there are or which ones are up to date at any given time.

3] Replication Implementation Details –

MySQL replication capabilities are implemented using three threads (one on the master server and two on the slave). When START SLAVE is issued, the slave creates an I/O thread. The I/O thread connects to the master and asks it to send the statements recorded in its binary logs. The master creates a thread to send the binary log contents to the slave. This thread can be identified as the Binlog Dump thread in the output of SHOW PROCESSLIST on the master. The slave I/O thread reads what the master Binlog Dump thread sends and simply copies it to some local files in the slave’s data directory called relay logs. The third thread is the SQL thread, which the slave creates to read the relay logs and execute the updates they contain.

In the preceding description, there are three threads per slave. For a master that has multiple slaves, it creates one thread for each currently connected slave, and each slave has its own I/O and SQL threads.

For versions of MySQL before 4.0.2, replication involves only two threads (one on the master and one on the slave). The slave I/O and SQL threads are combined as a single thread, and no relay log files are used.

The advantage of using two slave threads is that statement reading and execution are separated into two independent tasks. The task of reading statements is not slowed down if statement execution is slow. For example, if the slave server has not been running for a while, its I/O thread can quickly fetch all the binary log contents from the master when the slave starts, even if the SQL thread lags far behind and may take hours to catch up. If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is locally stored in the slave’s relay logs for execution when next the slave starts. This allows the binary logs to be purged on the master, because it no longer need wait for the slave to fetch their contents.

The SHOW PROCESSLIST statement provides information that tells you what is happening on the master and on the slave regarding replication.

MySQL Master — 192.168.0.8

[root@testserver ~]# cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database
#log = /var/log/mysql/mysql.log

############################################
##MySQL replication setting

server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log

[root@testserver ~]#

[root@testserver ~]# mkdir /var/log/mysql/binary/
[root@testserver ~]# ll -d /var/log/mysql/binary/
drwxr-xr-x 2 root root 4096 Jan 30 15:30 /var/log/mysql/binary/
[root@testserver ~]# ll -d /var/log/mysql/
drwxr-xr-x 3 mysql mysql 4096 Jan 30 15:30 /var/log/mysql/
[root@testserver ~]# chown mysql:mysql -R /var/log/mysql/
binary/ mysql.log
[root@testserver ~]# chown mysql:mysql -R /var/log/mysql/binary/
[root@testserver ~]#

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 950
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
+——————–+
9 rows in set (0.00 sec)

mysql> create database student;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| student |
+——————–+
10 rows in set (0.00 sec)

mysql> use student;
Database changed
mysql>

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );
Query OK, 0 rows affected (0.32 sec)

mysql> DESC class;
+——–+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——–+————-+——+—–+———+—————-+
| rollno | int(5) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.03 sec)

mysql>

mysql> INSERT INTO class VALUES (”, ‘Arun Bagul’);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql>
mysql> SELECT * FROM class;
+——–+————+
| rollno | name |
+——–+————+
| 1 | Arun Bagul |
+——–+————+
1 row in set (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Suhail Thakur’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Bhavesh Vala’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
+——–+—————+
3 rows in set (0.00 sec)

mysql>
[root@testserver ~]#

[root@testserver ~]# /etc/init.d/mysql restart
Shutting down MySQL… [ OK ]
Starting MySQL [ OK ]
[root@testserver ~]# /etc/init.d/mysql restart
[root@testserver ~]# cd /var/log/mysql/binary/mysql-bin.
mysql-bin.000001 mysql-bin.index
[root@testserver ~]# cd /var/log/mysql/binary/
[root@testserver binary]# ls
mysql-bin.000001 mysql-bin.index
[root@testserver binary]#

[root@testserver binary]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql>
mysql> use student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+——————-+
| Tables_in_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
+——–+—————+
3 rows in set (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Nishit Shah’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
+——–+—————+
4 rows in set (0.00 sec)

mysql>

4] Create user on master server which has permission to read binary logs –

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.0.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@testserver binary]#

[root@testserver binary]# mysql -u root -p
Enter password:
\Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 340
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000002 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.02 sec)

mysql>

mysql> SHOW PROCESSLIST;
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
| 31 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
| 41 | horde | localhost | horde | Sleep | 0 | | NULL |
| 42 | horde | localhost | horde | Query | 1 | statistics | SELECT session_data FROM horde_sessionhandler WHERE session_id = ‘f70a7d64bd353917679814813a513c8f’ |
| 43 | horde | localhost | horde | Query | 1 | statistics | SELECT session_data FROM horde_sessionhandler WHERE session_id = ‘f70a7d64bd353917679814813a513c8f’ |
+—-+——-+———–+——-+———+——+————+——————————————————————————————————+
4 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 31
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

=========================================================

MySQL Slave — 192.168.0.191

5] Edit my.cnf file of replication slave and create DB and upload the dump of DB –

[root@arun ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

########################################
##slave setting
server-id=4
master-port=3306
master-host=192.168.0.8
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student

#slave setting end

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@arun ~]#

[root@arun ~]# mysqladmin create student -u root -p
Enter password:
[root@arun ~]# mysql -u root -p student < /root/arun.sql
Enter password:
[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1020
Server version: 5.0.45-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| student |
+——————–+
12 rows in set (0.01 sec)

mysql> USE student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+——————-+
| Tables_in_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.00 sec)

mysql> quit
Bye
[root@arun ~]# /etc/init.d/mysql restart
Shutting down MySQL. [ OK ]
Starting MySQL [ OK ]
[root@arun ~]#

6] Now login to Master (192.168.0.8) and check Master status —

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1599
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.00 sec)

mysql>

mysql> USE student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+——————-+
| Tables_in_student |
+——————-+
| class |
+——————-+
1 row in set (0.01 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.06 sec)

mysql> quit
Bye
[root@testserver ~]#

7] Now login to Slave ie 192.168.0.191 in my case –

[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.45-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> SHOW SLAVE STATUS;
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Waiting for master to send event | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 98 | arun-relay-bin.000002 | 235 | mysql-bin.000001 | Yes | Yes | student,student | | | | | | 0 | | 0 | 98 | 235 | None | | 0 | No | | | | | | 0 |
+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
1 row in set (0.00 sec)

mysql>

8] Now login to Master Server (192.168.0.8)and check processlist –
mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 98 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.01 sec)

mysql> SHOW PROCESSLIST;
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
| 570 | eximstats | localhost | eximstats | Sleep | 401 | | NULL |
| 1578 | replication | 192.168.0.191:38843 | NULL | Binlog Dump | 494 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 1633 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+——+————-+———————+———–+————-+——+—————————————————————-+——————+
3 rows in set (0.00 sec)

mysql>

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 570
User: eximstats
Host: localhost
db: eximstats
Command: Sleep
Time: 508
State:
Info: NULL
*************************** 2. row ***************************
Id: 1578
User: replication
Host: 192.168.0.191:38843
db: NULL
Command: Binlog Dump
Time: 601
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 3. row ***************************
Id: 1633
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

9] Now login to Slave ie (192.168.0.191) and check the processlist –

mysql> SHOW PROCESSLIST;
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| 1 | system user | | NULL | Connect | 685 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 685 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 5 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
3 rows in set (0.00 sec)

mysql> SHOW PROCESSLIST\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 690
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 690
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 5
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: SHOW PROCESSLIST
3 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

10] How to reset Binary log position on the slave –

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.8′,MASTER_USER=’replication’,MASTER_PASSWORD=’mypwd’, MASTER_LOG_FILE=’mysql-bin.000004′,MASTER_LOG_POS=412;
Query OK, 0 rows affected (0.01 sec)

11] Now login to Master (192.168.0.8) and add records in “class” table of “student” DB –

[root@testserver ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1642
Server version: 5.0.45-community-log MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> USE student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+——————-+
| Tables_in_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
+——–+—————+
6 rows in set (0.02 sec)

mysql>

mysql> INSERT INTO class VALUES (”, ‘Kaushik Kawa’);
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> INSERT INTO class VALUES (”, ‘Dipak Jadhav’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO class VALUES (”, ‘Binish Philip’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql>
mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
| 7 | Kaushik Kawa |
| 8 | Dipak Jadhav |
| 9 | Binish Philip |
+——–+—————+
9 rows in set (0.00 sec)

mysql>

mysql> SHOW MASTER STATUS;
+——————+———-+—————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+—————–+——————+
| mysql-bin.000001 | 520 | student,student | mysql,mysql |
+——————+———-+—————–+——————+
1 row in set (0.00 sec)

mysql>

12] Now Login to Slave (192.168.0.191) and520 check if Data is replicated or not –

[root@arun ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45-community MySQL Community Edition (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> USE student;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+——————-+
| Tables_in_student |
+——————-+
| class |
+——————-+
1 row in set (0.00 sec)

mysql> SELECT * FROM class;
+——–+—————+
| rollno | name |
+——–+—————+
| 1 | Arun Bagul |
| 2 | Suhail Thakur |
| 3 | Bhavesh Vala |
| 4 | Nishit Shah |
| 5 | Yogesh Nikam |
| 6 | Deven Jadhav |
| 7 | Kaushik Kawa |
| 8 | Dipak Jadhav |
| 9 | Binish Philip |
+——–+—————+
9 rows in set (0.00 sec)

mysql>

mysql> SHOW SLAVE STATUS;
+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————–+——————-+—————-+———————–+
| Waiting for master to send event | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 520 | arun-relay-bin.000002 | 657 | mysql-bin.000001 | Yes | Yes | student,student | | | | | | 0 | | 0 | 520 | 657 | None | | 0 | No | | | | | | 0 |
+———————————-+————-+————-+————-
1 row in set (0.00 sec)

mysql>

13 ]How to reset MySQL slave –

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> RESET SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW SLAVE STATUS;
+—————-+————-+————-+————-+—————| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————–+——————-+—————-+———————–+
| | 192.168.0.8 | replication | 3306 | 60 | | 4 | arun-relay-bin.000001 | 4 | | No | No | student,student | | | | | | 0 | | 0 | 0 | 117 | None | | 0 | No | | | | | | NULL |
+—————-+————-+————-+————-+—————
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.8′, MASTER_USER=’replication’, MASTER_PASSWORD=’mypwd’, MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=520;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW SLAVE STATUS;
+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+—————-+————-+————-+————-+—————+——————+———————+———————–+—————+———————–+——————+——————-+—————–+———————+——————–+————————+————————-+—————————–+————+————+————–+———————+—————–+—————–+—————-+—————+——————–+——————–+——————–+—————–+——————-+—————-+———————–+
| | 192.168.0.8 | replication | 3306 | 60 | mysql-bin.000001 | 520 | arun-relay-bin.000001 | 4 | mysql-bin.000001 | No | No | student,student | | | | | | 0 | | 0 | 520 | 98 | None | | 0 | No | | | | | | NULL |
+—————–+——————-+—————-+———————–+
1 row in set (0.00 sec)

mysql>

** you may use this command to start the slave

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

mysql>quit
Bye
[root@arun ~]#

Regards,

Arun Bagul

Indexes in MySQL

Indexes in MySQL

Managing Indexes in MySQL –

Index is a method/process that MySQL uses to speed up searches and reduce the time it takes to execute complex queries. Index provides an organized list of pointers to the actual data. Due to indexing, when MySQL is executing a query, it does not have to scan each table in its entirety to locate the correct data, but it can instead scan the index, which results in quicker and more efficient access…

MySQL supports five type of indexes that can be created on a table….

1) Primary Key – The value or set of values should be unique in the columns on which the primary key is defined. NULL value is not allowed. Table can include only one primary key.

2) Foreign Key – Enforces the relationship between the referencing columns in the child table where the foreign key is defined and the referenced columns in the parent table.

3) Regular – A basic index that permits duplicate values and null values in the columns on which the index is defined…

4) Unique – Requires that each value or set of values be unique in the columns on which the index is defined. Unlike primary key indexes,null values are allowed.

5) Full-text – Supports full text searches of the values in the columns on which the index is defined. A full text permits duplicate values and null values in those columns. A full-text index can be defined only on MyISAM tables and only on CHAR, VARCHAR columns…

How to create indexes –

(a) In below query for creating table. We are defining two indexes one primary key and other is unique.

mysql> CREATE TABLE market (orderID INT(5), modelID INT(5), Name VARCHAR(40), PRIMARY KEY(orderID), UNIQUE (orderID,modelID));
Query OK, 0 rows affected (0.00 sec)

mysql>

(b) In below query for creating table. We are defining Regular indexing

mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40), INDEX(orderID) );
Query OK, 0 rows affected (0.00 sec)

mysql>

(c) How to add indexes to existing table –

mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40));
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE market ADD PRIMARY KEY (orderID);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

(d) How to create index by using “CREATE INDEX” –

mysql> CREATE TABLE market (orderID INT(5), Name VARCHAR(40));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE INDEX myindex ON market (orderID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>
** How to drop index –

mysql> DROP INDEX myindex ON market;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql>

(e) How to get information about your Databases and tables –

** Below SQL commands can be used to view the database definition for specific database.

mysql> SHOW CREATE DATABASE zabbix;
+———-+——————————————————————-+
| Database | Create Database |
+———-+——————————————————————-+
| zabbix | CREATE DATABASE `zabbix` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+———-+——————————————————————-+
1 row in set (0.00 sec)

mysql>

** SQL command to view table definition

mysql> SHOW CREATE TABLE market;
+——–+———————————————+
| Table | Create Table |
+——–+———————————————+
| market | CREATE TABLE `market` (
`orderID` int(5) default NULL,
`Name` varchar(40) default NULL,
KEY `myindex` (`orderID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+——–+———————————————+
1 row in set (0.00 sec)

mysql>

** How to describe the table structure –

mysql> SHOW COLUMNS FROM market;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| orderID | int(5) | YES | | NULL | |
| Name | varchar(40) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.01 sec)

mysql> DESC market;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| orderID | int(5) | YES | | NULL | |
| Name | varchar(40) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.00 sec)

** How to views indexes defined on perticular table –

mysql> SHOW INDEX FROM market;
Empty set (0.00 sec)

mysql> CREATE INDEX myindex ON market (orderID);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW INDEX FROM market;
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
| market | 1 | myindex | 1 | orderID | A | NULL | NULL | NULL | YES | BTREE | |
+——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+
1 row in set (0.00 sec)

mysql>

Thank you,
Arun Bagul