Restore MySql Database From Just frm and idb File

Hello,

Few days back, I encountered a situation where we needed to restore MySql Database but we only had frm and idb file. Didn't have .sql file or log file from MySql server which is very much needed when you want to restore. So here in this blog I am going to explain how you can still restore MySql Database from only frm and idb file.

First you will need a tool called dbsake which you can install with following command.

curl -s http://get.dbsake.net > dbsake

Once installed, run following command to check if it's installed properly or not. 

chmod u+x dbsake

./dbsake --version

If it shows version and all, it's installed. Now first by using this tool we will get schema of table from frm file. Run following command. 

./dbsake frmdump /PATH_TO_FRM/file.frm

It will give you create table schema like this.

CREATE TABLE `table_name` (

  `column1` int(11) NOT NULL AUTO_INCREMENT,

  `column2` varchar(255) DEFAULT NULL,

  `column3` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`column1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Copy it and run it in your sql editor. If you are migrating it from MySql 5 to 6 you may want to add ROW_FORMAT

CREATE TABLE `table_name` (

  `column1` int(11) NOT NULL AUTO_INCREMENT,

  `column2` varchar(255) DEFAULT NULL,

  `column3` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`column1`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=compact;

Now it will create table. It's time to load data into it. 

First we will delete the table space which is idb file created. Run following command.

ALTER TABLE table_name DISCARD TABLESPACE

Now copy the old idb file and replace the existing one in your MySql server.

Now attach table space again.

ALTER TABLE table_name IMPORT TABLESPACE

That's it and now if you browse the data in table, you can see all your old rows. 

PHP / MySql - Generate Big Reports from Big Data

Big data is a term that describes the large volume of data – both structured and unstructured – that inundates a business on a day-to-day basis. But it’s not the amount of data that’s important. It’s what organizations do with the data that matters. Big data as a service market to grow in near future. “Big Data” analysis is a hot and highly valuable skill now a days. For the big data analysis, an organization needs reports generated from it and to generate reports from big data we need some spacial tricks. In this blog I am going to explain some best practices to generate big reports from big data in PHP / MySql.

Challenges we face in managing Big Data with MySql

In MySql when we have big data we face certain performance issues. Most crucial issue is slow performance of MySql. When you have millions of rows in table and you want to search certain raws from it, it will take some time to get data and you need to improve in that. So in MySql there is a way to make faster data retrieval.

INDEXING

Yes, that 's right indexing solved my issue. There was a table in my database which has 100K records and executing query from it was taking lots of time as there was no indexing. So I created an index on columns which are used mostly for querying that table and after creating index query was working blazing fast.

If you are facing the same issue, better check your database queries and optimize it with indexing to make it faster.

Challenges we face in generating Big Reports with PHP

In PHP when we generate big reports it will surely take time and while working with PHP we have certain restrictions. For example, a PHP script can run for only certain time limits on any server and that limits can not be changed on most of server providers. So in case of generating big reports we surely need time as we have lots of data at backend and from it we are creating reports. So how to deal with it. 

Here is the one solution I always use. You can make use of scheduled cron job. Cron jobs run in background and there is no time restrictions with it so it can run for any amount of time and it can resources as much as possible. So get the reports parameters from the user and save it in database and schedule it for certain time.  For example user want to see day wise sales of products for a month. Save parameters like month in database have a scheduled cron job to read this data and start creating reports in background. Once the report is generated you can notify user about it so they can download it. Which this trick you can generated Big Reports from Big Data.

Amazon RDS Connection Limit Exhausted

Recently in one of our project we built laravel application and deployed it on Amazon AWS and database was on Amazon RDS. In start there was no issue but after some months we had problems with database connection. After sometime laravel app could not connect to Amazon RDS.

So I checked RDS and dashboard and found out that number of database connection was beyond allowed limits. It means connection open to RDS was not closed after sometime and there were lots of half opened connections. That eventually crashing RDS sever and then connection was not established till we restart the RDS server.

So next step was to check database queries and find the queries which are taking time to execute and get data. After looking into all the queries I got the query which is taking long time to execute and that query was responsible half closed or non closed database connections and which is ultimately crashing Amazon RDS server.

So how I fixed that. Well we have a only one solution to speed up database queries and that is

INDEXING

Yes, that 's right indexing solved my issue. There was a table in my database which has 100K records and executing query from it was taking lots of time as there was no indexing. So I created an index on columns which are used mostly for querying that table and after creating index query was working blazing fast.

If you are facing the same issue, better check your database queries and optimize it with indexing to make it faster.

Use existing Sqlite database in Android

Hello,

Since last few days, I have been working with android and I am very excited about it. This is my first blog on Android and many more is to come.

Normally Sqlite database is used with Android application. So this blog is about how you can use existing Sqlite database in Android application. First you need to add following table in your database. I recommend Sqlite  Expert Personal tool to make changes or create new Sqlite database. Open the database and run following query

"CREATE TABLE "android_metadata" ("locale" TEXT DEFAULT 'en_US')"

After that insert record in it.

"INSERT INTO "android_metadata" VALUES ('en_US')"

Once done add your database file in assets folder of your android application. You can do simple copy paste here.

Now we will create class which will have all database related functions and methods. We will use SQLiteOpenHelper class provided by Android framework to connect with Sqlite database. Following is the class definition.

public class DatabaseAdapter extends SQLiteOpenHelper {

         private static String dbPath= "data/data/com.YourPackageName/applicationDb/";
         private static String dbName = "YourDBName";
         private SQLiteDatabase applicationDatabase; 
         private final Context applicationContext;

       
         public VocabTesterDatabaseHelper(Context context) {
                 super(context,  dbName , null, 3);
                 this. applicationContext  = context;
         }


         private boolean checkDataBase(){
                 File dbFile = new File( dbPath +  dbName);
return dbFile.exists();
  }


          private void copyDataBase() throws IOException{
try {

                  InputStream input =  applicationContext .getAssets().open(dbName);
                           String outPutFileName=  dbPath  +  dbName ;
                     OutputStream output = new FileOutputStream( outPutFileName);
                      byte[] buffer = new byte[1024];
                  int length;
                  while ((length = input.read(buffer))>0){
                 output.write(buffer, 0, length);
                  }
                  output.flush();
                  output.close();
                  input.close();
       }
                       catch (IOException e) {
                    Log.v("error",e.toString());
                    }
   }


             public void openDataBase() throws SQLException{
                String fullDbPath= dbPath + dbName;
             applicationDatabase = SQLiteDatabase.openDatabase( fullDbPath,     null,SQLiteDatabase.OPEN_READONLY);
   }

                @Override
public synchronized void close() {
       if( applicationDatabase != null)
        applicationDatabase .close();
             super.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

}

That's it and your database helper class is ready. Initially we placed database in Assets folder and later on we moved it to particular folder in application. Still I am working on this class so I will add more functions to class and at the same time, I will upgrade this post. Stay tuned.

Thanks.