INNODB Backup and Recovery using mysqldump

InnoDB is a storage engine for MySQL. MySQL 5.5 and later use it by default. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity). It is included as standard in most binaries distributed by “MySQL AB”, the exception being some “OEM” versions.

The key to safe database management is making regular backups. Depending on your data volume, number of MySQL servers, and database workload, you can use these techniques, alone or in combination: hot backup with MySQL Enterprise Backup; cold backup by copying files while the MySQL server is shut down; physical backup for fast operation (especially for restore); logical backup with mysqldump for smaller data volumes or to record the structure of schema objects

Issue : —

C:\Calpont\bin>mysqldump.exe  -u root -p  –database  TestDB > C:\Pushpraj\infinidbPushpraj.sql 

Enter password: *******

mysqldump: Got error: 138: IDB-2029: Lock table command is currently not supported in InfiniDB. when using LOCK TABLES

Solution  :-  use –skip-lock-tables

LOGICAL BACKUP

Windows :::

To take the dump from your mysql for infinidb —-

C:\Calpont\bin>mysqldump.exe –skip-lock-tables -u root -p  –database  TestDB > C:\Pushpraj\infinidbPushpraj.sql 

Enter password: *******

It will create your dump file .

To restore the dump in your mysql —-

First create an empty DB in your Mysql desitination DB.

C:\Calpont\bin>mysql.exe -u root -p  TestDB < C:\Pushpraj\infinidbPushpraj.sql 

Enter password: *******

 

🙂   Have fun   🙂

 

PUSHPRAJ KUMAR

MySQL C – API programming

MySQL C – API programming

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web.

Basic Structure of C Programs that uses MySQL C mysqlwithcAPI 1. All programs must include <mysql/mysql.h> as the last include.2. Define MYSQL type variable. NOTE: THERE CAN BE ONLY ONE MYSQL VARIABLE.3. Initialize MYSQL type variable with mysql_init()4. Load any options, if required, by using mysql_options(). If you don’t need don’t call. You can call this fuction multiple times if you require. If you call this, call this before mysql_real_connect() and after mysql_init().5. Connect by calling mysql_real_connect()6. Call the business logic and MySQL API’s7. Close the MYSQL type variable.

 

Template of program

#include <mysql/mysql.h><return_type> <function_name>(parameters)

{

MYSQL mysql; mysql_init(&mysql);

mysql_options(&mysql,MYSQL_OPT_COMPRESS,0);      

/*call only if required otherwise omit*/

mysql_options(&mysql,MYSQL_READ_DEFAULT_GROUP,”Pushpraj”);        

 /*call only if required otherwise omit*/

mysql_real_connect(….);      

/* now call other API’s*/

mysql_close(&mysql);

}

 Compiling and Running in UNIX :- 

To Compile :-  $gcc mysql_app.c -o mysql_app -I/usr/local/include -L/usr/local/lib/mysql –lmysqlclient

To Run :-  $./mysql_app

Examples:-

1. Test the connection.

#include </usr/include/mysql/my_global.h>

#include </usr/include/mysql/mysql.h>

int main(int argc, char **argv)

{

printf(“MySQL client version: %s\n”, mysql_get_client_info());

  exit(0);

}

Description:-  “mysql_get_client_info()”, this mysql function collects the version information of mysql.

2. Create table and insert data into table.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con){

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

 

int main(int argc, char **argv)

{

  MYSQL *con = mysql_init(NULL);

  if (con == NULL) {

      fprintf(stderr, “%s\n”, mysql_error(con));

      exit(1);

  } 

if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {

      finish_with_error(con);

  }   

 

  if (mysql_query(con, “DROP TABLE IF EXISTS Dell”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “CREATE TABLE Dell(Id INT, Name TEXT, Price INT)”)) {     

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(1,’vostro14′,26042)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(2,’inspiron15′,37000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(3,’inspiron16′,40000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(4,’ vostro15′,29000)”)) {

      finish_with_error(con);

  }

  if (mysql_query(con, “INSERT INTO Dell VALUES(5,’express’,69000)”)) {

      finish_with_error(con);

  }

  mysql_close(con);

  exit(0);

}

Description:- mysql_init, allocates/intialises a Mysql object suitable for mysql_real_connect()establishes a connection to the database.We provide connection handler, host name, username ,password ,database name, port number , unix socket and client flag.

3. Create connection with Database & fetch the records.

#include <my_global.h>

#include <mysql.h>

void finish_with_error(MYSQL *con)

{

  fprintf(stderr, “%s\n”, mysql_error(con));

  mysql_close(con);

  exit(1);       

}

int main(int argc, char **argv)

{     

  MYSQL *con = mysql_init(NULL);

  if (con == NULL)

  {

      fprintf(stderr, “mysql_init() failed\n”);

      exit(1);

  } 

 

  if (mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0) == NULL)

  {      finish_with_error(con);  }  

 

  if (mysql_query(con, “SELECT * FROM Dell”))

  {      finish_with_error(con);  }  

  MYSQL_RES *result = mysql_store_result(con);

  if (result == NULL)

  {      finish_with_error(con);  }  

  int num_fields = mysql_num_fields(result);

  MYSQL_ROW row;

  while ((row = mysql_fetch_row(result)))

  {

      for(int i = 0; i < num_fields; i++)

      {

          printf(“%s “, row[i] ? row[i] : “NULL”);

      }

      printf(“\n”);

  }

  mysql_free_result(result);

  mysql_close(con);

  exit(0);

}

Description:-

  • Create a connection:: mysql_real_connect(con, “192.168.2.50”, “username”, “password”,”db-Name”, port-No, NULL, 0)
  • Execute query:: mysql_query(con, “SELECT * FROM Dell”)
  • Get the result set:: MYSQL_RES *result = mysql_store_result(con)
  • Fetch all available rows one by one :: row = mysql_fetch_row(result)
  • Free the result set:: mysql_free_result(result)
  • Close the connection:: mysql_close(con);

Thanks

Pushpraj Kumar

My-Sql Advanced Commands

My-Sql Command Prompt

My-Sql Command Prompt

Advanced My-Sql Commands( Asks in Interview )

  1. To clear console              —     \! clear
  2. To delete procedure         —         drop prcedure <procedureName>
  3. Show all stored procedure   —         show procedure status
  4. To get 2nd highest salary       —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1
  5. To get 3rd highest salary       —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
  6. To convert string into datetime type     —SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’) FROM <yourtable>
  7. To check event scheduler is ON/OFF       —select @@event_scheduler
  8. To start event-scheduler           —     set GLOBAL event_scheduler=ON
  9. To delete duplicate records from table

—   delete from table1 USING table1, table1 as vtable

WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name;

So, These are the some Advanced My-SQL features , which may help you…

Have   a   Good   Day………!!!

How To Create “EVENT” In My-Sql

My-Sql  Event with Scheduling Logic

My-Sql Event with Scheduling Logic

 

To Create  Event in My-Sql .

To create event in My-SQL , we have to follow a Pattern which is written below..

1. Declare Delimiter

2. Define Name of the event

3. Define when to schedule

4. Start with “DO”

5. Then “Begin”

6. Define Business logic

(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)

7. Then Declare end of your event like “END <delimiter>”

8. Change Delimiter to normal Delimiter.

Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event…     (Tested and executed Event)

……………………………………………………………………………………………………………….

delimiter $$

CREATE EVENT ue_schedule_test

ON SCHEDULE

EVERY 4 HOUR

DO

BEGIN

DECLARE to_temp TEXT(25);

DECLARE from_temp TEXT(25);

DECLARE pv_temp TEXT(20);

DECLARE done INT DEFAULT FALSE;

DECLARE curs1 CURSOR FOR SELECT name FROM data ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs1;

read_loop: LOOP

FETCH curs1 INTO pv_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL -4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO from_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL 4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO to_temp;

DELETE FROM availibility WHERE Date=from_temp;

CALL usp_availability_test(from_temp,to_temp,pv_temp,’Total’,’+00:00′,’custom’);

IF done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE curs1;

END $$

delimiter ;

………………………………………………………………………………………………..