MySQL: Query to calculate database size.

So Guys, how do you calculate the size of your databases on db server using a simple & single query..
.
.
.
Well this thing is easily achievable by a simple query. For this you need to query information_schema, which will result you the database size and other information.

   SELECT table_schema "DB Name", ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 1) "DB Size in GB" 
   FROM   information_schema.tables GROUP  BY table_schema;

:)

MySQL: Database Backup and Restore Using Command Line

MySQL, one of the most commonly & widely used database in world for web applications. Its popularity for use with web applications is closely tied to the popularity of PHP, which is often combined with MySQL. Several high-traffic web sites use MySQL for its data storage and logging of user data, including Flickr, Facebook, Wikipedia, Google, Nokia and YouTube.

MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump command line utility is available with MySQL installation (bin directory) that can be used to take backup of databases and perform various task using command line. 

MySQL Master Slave Replication

MySQL replication is a process that allows us to easily maintain multiple copies of a MySQL data. MySQL data get automatically copied from a master to a slave database. This concept is helpful for many reasons including 
a. Automatically backing up for the data.
b. Providing alternate database server if master server get crashed somehow.
To check the replication status in Master Slave environment (MySQL). Just need to follow these steps:

Managing MySQL Server Logs

MySQL Server provides several logs that help us find out what activity is taking place. MySQL provides different types of log files. 
Error Log: contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. 
General Query Log: is a general record of what  mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. 
Binary Log: contains “events” that describe database changes such as table creation operations or changes to table data. 

MySQL's REPLACE()

We are in habit of using str_replace() of PHP, but MySQL also provide replace function for same purpose. 
The REPLACE() function takes three parameters: 

• The string or column name to do the replacement on 
• What to look for 
• What to replace it with

    SELECT REPLACE('My name is garav', 'garav', 'Gaurav.') as result;
    result: My name is Gaurav.

Working days b/w two days & Weekends b/w two dates

MySQL is very rich with built in function as it have thousands of functions. But apart from this rich library sometimes we need to create some custom function to get the task done. Today I encountered same kind of situation. I need a function which can return no. of working days between two dates and it should also return the weekend days between two days. Here is the solution for this problem. I created a user defined function to calculate working dates between two dates. 

MySQL Cursors

Cursors are basically named SQL statement which we can be defined in our procedures. We can then easily look at the contents of the cursor by fetching the records from it. It’s a way for us to get information from our tables in one big chunk and then work on it. As always there is no better way to find out about them than actually doing one but because cursors require a little more code than things we have looked at previously we will need to look at the various stages before writing a procedure which uses cursors.

MySQL Stored Procedures

MySQL 5.0 finally introduces functionality for Stored Procedures. But there is always a question in our mind that what are stored procedures and why we use them. When we can use queries then what is the advantages of stored procedures?

The answer is very simple.

A stored procedure is simply a block of code that is stored on the database server.

Advantages of stored procedures?

• When we are working with stored procedures, we kept our logic on database server in compiled form. We need not to compile our queries every time. When we user queries in our code, then every time the query get compiled and after that the query return the results to the application. but when we use stored procedure, we compile the query only once and can call our stored procedure any time.
• Stored procedures can reduce network traffic. When we have to do some complex and repetitive task on the results and again we have to again apply some other logic to get more result then it will be good to use stored procedures.