All posts by Pradeep Kumar

How to store mysql data in different drive

Some time we require to store mysql data in different drive or folder instead of default on.

To change the data storage location of mysql we need to following changes.

Step1:- Stop Mysql service. you can use /etc/init.d/mysql stop.

Step2:-Create a new folder where you want to store the data. eg new_data_folder.

Step3:- Change the user and group of this folder. chown -R mysql:mysql new_data_folder

Step4:- Copy existing data in new folder from default location(/var/lib/mysql/)

Update the data location in my.cnf file

/etc/apparmor.d/local/usr.sbin.mysqld:

/data/ r, /data/** rwk, …and then reload the AppArmor profiles:

# service apparmor reload

Advanced Analytics Synchronization with Zoho Reports

Zoho Reports is an excellent feature for individuals & businesses for generating reports on the views defined by them within Zoho CRM. Have you ever tried to synchronize any default module other than  Accounts, Activity, Contacts, Potentials, Leads, Users? Actually Zoho does not provide synchronization support for other default modules as of now but Zoho is planning to release the synchronization support for other modules like Quotes, Demos, etc very soon.

Question is how to get the data of remaining default modules to Zoho Reports?

We have only left with the option of manual operation of exporting the data from Zoho CRM and importing it into Zoho Reports. Additionally, you may create custom modules within Zoho CRM and synchronize the data with Zoho Reports.

Useful MySql Commands and Tricks

GROUP_CONCAT

This function returns a string result with the concatenated non-null values from a group. It returns null if there are no non-null values.

Some time we need comma separate values of a group. For example we need to show the details of a department and name of employees. In this case can simply use GROUP_CONCAT(“employee_name”) with “left join” and “group by department_id” query.

Sample Query:

select d.*, GROUP_CONCAT(“e.employee_name”) as employee_list from department as d left join employee as e on d.department_id = e.department_id group by d.department_id


CASE

The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value



EXPLAIN

The EXPLAIN statement can be used to obtain information about how MySQL executes a statement. It is very user at the time of query optimization. EXPLAIN provides additional information and estimates the number of table rows that are filtered by the condition.


EXPLAIN select d.*, GROUP_CONCAT(“e.employee_name”) as employee_list from department as d left join employee as e on d.department_id = e.department_id group by d.department_id

Host A PHP website at Amazon web service using EC2

There are few basic steps to host your PHP(Dynamic) website using EC2.

Login on AWS Management Console. Go to EC2 Tab and Launch a Instance and choose size according to you requirement. After that you need to allocate a new Address under Elastic IP. Associate this IP address with your instance. Now you can access your server(instance) usng this IP from browser. You need to add a security rule under “Security Groups tab” For this choose you security group and add a HTTP rule under Inbound Tab. Now you can login at you server(Instance). For login you need to create a key Pair and it will give you a .pem file. Using this file you can login at your server.Use following command to login “ssh -i filename.pem ec2-user@Public DNS address” Now you can setup your server as a normal server. Install webserver and language any else according to your requirement.

Selecting the Right Mysql Engine

When You designing MySQL based applications, You should decide which engine to use for storing your data. If you don’t think about it during the design phase, you are likely to face complications later in the process.

MyISAM, InnoDB, BDB and HEAP are the most common and popular storage engines. All have some advantages and disadvantages. HEAP is an in-memory storage engine,so we will primarily focus on MyISAM, InnoDB and BDB storage engines.

  • Transactions and Concurrency:- If your application requires transactions and high read/write concurrency, InnoDB is probably your best bet. If your application only need moderate read/write concurrency then BDB might also work for you.
  • If your application depends upon accurate and fast row counts then you can use MyISAM. InnoDB have to count all the rows but MyISAM always knows exact count without re-counting.
  • Only MyISAM tables provide full text functionality.
  • If your tables primarily used for read operations then MyISAM is the most suitable choice. It works most efficiently in that scenario.
  • Locking in MyISAM tables is performed at the table level but InnoDB uses row-level locking in conjunction with a multi-versioning scheme. BDB tables use page level locaking on 8 KB pages. InnoDB uses row-level locking so it gives better performance for high number of insert/update queries. However if your table is generally used for read(select queries) then MyISAM works great as InnoDB’s row-level locking adds some extra overhead.
  • Non-MyISAM tables will generally use more CPU and disk space.
  • MyISAM tables may take a long time to check and repair while InnoDB tables generally recover quickly.
  • Only InnoDB provides foreign key constraints other storage engines do not provide this functionlity.
  • BLOB and TEXT columns in MyISAM table can be indexed. MyISAM tables have a limit of 500 bytes on each key, so the index uses only the first few hundred byte of BLOB or TEXT field. InnoDB also provides lightning fast results for queries that use primary key(s) for look-up.

What is the indexing?

What is mysql indexing? To understand this let’s take a very simple example from real life. A phone book has thousands of phone numbers, and we want to search John’s number. Then we simply go to the page where “J” is first character. After that we check the second character and third and so on. By this process the number can be found in short period. Now that if the number would not have been sorted, then every record/name was needed to be checked and then it will become an lengthy and time consuming process. Indexing also works on same principle.

Now the second point that comes in mind is that why we don’t index all the columns of a mysql table? There are two main reasons for this. First is, It will require extra space to store data of indexes. Second and very important reason is that, it will slow down the insert/update queries as every insert and update query will also need to update the indexes thus adds up extra overhead.

Indexes require a lot of space for performance. A phone book table having 2 billion rows in it, adding an index on name will require a lot of space. If the average name is 8 byte long, mysql need 16 GB space for indexes. Luckily mysql provides full control on indexes. We can index only first 4 byte instead of full index. This is called as partial indexing.

“Alter table phone_book add index(name(4))”

Mysql also provides multiple column indexes. It is very helpful when we can use two or more columns into same query. For example firstname and lastname for phonebook. Multiple column indexing are also referred as composite indexing or compound indexing. Why don’t use separate indexing on firstname and lastname? There is a very simple reason to this. Mysql will ever use only one index per table per query. So in case of separate indexing mysql either uses indexing of firstname or of lastname.

Mysql indexes aren’t always used to locate matching rows for query. We can also use them as constraints. For example unique indexes. Unique indexes ensure that each value of a column appears only once.

With MYISAM tables, the indexes are kept in a completely separate file that contains a list of primary key and a value that represents the byte offset for the record with clustered indexes the primary key and the records are all stored in primary key order. When your data is almost frequently searched via its primary key, clustered indexes make lookups incredibly fast.

Drag Drop Sample with HTML5

Drag Drop is a very common feature. HTML5 introduced it as a stranded feature. Any element can be dragged into HTML5. Following is a very simple example of drag-drop feature. There are four images that could be dragged from div1 to div2. <!DOCTYPE HTML> <html> <head> <style type="text/css"> #div1 {width:550px;height:70px;padding:10px;border:1px solid #aaaaaa;} #div2 {width:550px;height:70px;padding:10px;border:1px solid #aaaaaa;} </style&gt <script type="text/javascript"> function allowDrop(evt) { evt.preventDefault();   function drag(evt) { evt.dataTransfer.setData(“Text”,evt.target.id); } function drop(evt) { var data=evt.dataTransfer.getData(“Text”); evt.target.appendChild(document.getElementById(data)); evt.preventDefault(); } </script> </head> <body> <p>Simple drag drop sample:</p> <div id="div1" ondrop="drop(event)" ondragover="allowDrop(event)"> </div> <br /> <div id="div2" ondrop="drop(event)" ondragover="allowDrop(event)"> <img id="drag1" src="images/image.png" draggable="true" ondragstart="drag(event)" /> <img id="drag2" src="images/image.jpg" draggable="true" ondragstart="drag(event)" /> <img id="drag3" src="images/image.png" draggable="true" ondragstart="drag(event)" /> <img id="drag4" src="images/image.png" draggable="true" ondragstart="drag(event)" /> </div> </body> </html> To make an element draggable, set the draggable attribute to true. "<img draggable=true >" Then specify what should happened when the element is dragged. In above example we are calling drag function “ondragstart” event. The datatransfer.setData methods sets the data type and the value of dragged data. The ondragover event specifies where the dragged data can be dropped. By default data can’t be dropped on other element. To allow a drop, We must prevent the default handling of the element. We used event.preventDafault method for it. Now final step is what should happen when an element dropped. In above example we are appending element into target element. We can also set the value of an input field if require. I hope this example helps you to understand the drag-drop feature of HTML5.