Connecting to MySQL database in Google Compute Engine - Remote Access

This page help you to access MYSQL database in Google Compute Engine from your system or network.

1. Get your public IP address.
2. Open Google Compute Engine in browser window
3. Navigate to /etc/mysql/
4. Open my.cnf file and add line:  bind-address = <internal IP>
5. Restart mysql server: $ service mysql restart
6. Sign in as mysql root user: $mysql -u root -p
7. Create new user: mysql>CREATE USER 'user1'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>'
8. Provide access to the user: mysql> GRANT ALL PRIVILEGES ON *.* TO 'user1'@'<internal-ip-my-client>' IDENTIFIED BY '<some-password>'; 
9. Restart mysql server

MY SQL Stored Procedure to Load Random Data to Table

First execute the below sp to create sample table:

DROP TABLE IF EXISTS `telecount`.`sms_detail`;
CREATE TABLE  `telecount`.`sms_detail` (
  `SMSKEY` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sumID` int(10) NOT NULL,
  `StatusFlag` varchar(45) DEFAULT NULL,
  `DC` varchar(45) DEFAULT NULL,
  `Product` varchar(45) DEFAULT NULL,
  `TNAME` varchar(45) DEFAULT NULL,
  `MSGTYPE` varchar(45) DEFAULT NULL,
  `COUNT` decimal(10,0) DEFAULT NULL,
  `SENDDATE` datetime DEFAULT NULL,
  `Operator` varchar(45) DEFAULT NULL,
  `DELDATE` datetime DEFAULT NULL,
  `Duration` time DEFAULT NULL,
  PRIMARY KEY (`SMSKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

Then create and execute the below stored procs:

DELIMITER $$

DROP PROCEDURE IF EXISTS `SampleProc` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SampleProc`()
BEGIN
  DECLARE x  INT;
    SET x = 1;
    WHILE x  <= 100 DO
      INSERT INTO sms_Detail
                (sumID
                ,StatusFlag
                ,DC
                ,Product
                ,TNAME
                ,MSGTYPE
                ,COUNT
                ,SENDDATE
                ,OPERATOR
                ,DELDATE
                ,DURATION)
    VALUES (x
            , CASE WHEN x MOD 10 < 3 THEN 'FAILURE'
                   WHEN x MOD 10 = 4 THEN 'REJECTED'
              ELSE 'SUCCESS' END
            , CASE WHEN x MOD 11 + x MOD 9 =1 THEN 'VSNL'
                   WHEN x MOD 11 + x MOD 9  =2 THEN 'SIFY'
                   WHEN x MOD 11 + x MOD 9 =3 THEN 'TELENET'
                   WHEN x MOD 11 + x MOD 9 =4 THEN 'GREAMSRD'
                   WHEN x MOD 11 + x MOD 9 =5 THEN 'AIRTEL'
                   WHEN x MOD 11 + x MOD 9 =6 THEN 'AIRONE'
                   WHEN x MOD 11 + x MOD 9 =7 THEN 'BSNL'
                   WHEN x MOD 11 + x MOD 9 =8 THEN 'VSNL'
                   WHEN x MOD 11 + x MOD 9 =9 THEN 'SIFY'
                   WHEN x MOD 11 + x MOD 9 =10 THEN 'SIFY'
                   WHEN x MOD 11 + x MOD 9 =11 THEN 'TELENET'
                   WHEN x MOD 11 + x MOD 9 =12 THEN 'VSNL'
                   WHEN x MOD 11 + x MOD 9 =13 THEN 'VSNL'
                   WHEN x MOD 11 + x MOD 9 =14 THEN 'VSNL'
              ELSE 'BSNL' END
            , CASE WHEN x MOD 10 + x MOD 8 =1 THEN 'SMPPBOX'
                   WHEN x MOD 10 + x MOD 8 =2 THEN 'GEMPAC'
                   WHEN x MOD 10 + x MOD 8 =3 THEN 'BB-II'
                   WHEN x MOD 10 + x MOD 8 =4 THEN 'BULK'
                   WHEN x MOD 10 + x MOD 8 =5 THEN 'ALPHA'
                   WHEN x MOD 10 + x MOD 8 =6 THEN 'SERVON'
                   WHEN x MOD 10 + x MOD 8 =7 THEN 'JB-22'
                   WHEN x MOD 10 + x MOD 8 =8 THEN 'NG'
                   WHEN x MOD 10 + x MOD 8 =9 THEN 'JAS'
                   WHEN x MOD 10 + x MOD 8 =10 THEN 'AIRTXT'
                   WHEN x MOD 10 + x MOD 8 =11 THEN 'NG'
                   WHEN x MOD 10 + x MOD 8 =12 THEN 'AIRTXT'
                   WHEN x MOD 10 + x MOD 8 =13 THEN 'AIRTXT'
                   WHEN x MOD 10 + x MOD 8 =14 THEN 'NG'
                   WHEN x MOD 10 + x MOD 8 =15 THEN 'BULK'
                   WHEN x MOD 10 + x MOD 8 =16 THEN 'NG'
              ELSE 'SMPPBOX' END
            , CASE WHEN x MOD 11 + x MOD 15 =1 THEN 'MBL_DISH'
                   WHEN x MOD 11 + x MOD 15 =2 THEN 'SMPP_OTHERS'
                   WHEN x MOD 11 + x MOD 15 =3 THEN 'MBL_CENTNI'
                   WHEN x MOD 11 + x MOD 15 =4 THEN 'MBL_CITI'
                   WHEN x MOD 11 + x MOD 15 =5 THEN 'EBL_HDFC'
                   WHEN x MOD 11 + x MOD 15 =6 THEN 'MBL_CITI'
                   WHEN x MOD 11 + x MOD 15 =7 THEN 'EBL_HDFC'
                   WHEN x MOD 11 + x MOD 15 =8 THEN 'MBL_ICICI'
                   WHEN x MOD 11 + x MOD 15 =9 THEN 'MBL_CENTNI'
                   WHEN x MOD 11 + x MOD 15 =10 THEN 'MBL_ICICI'
                   WHEN x MOD 11 + x MOD 15 =11 THEN 'MBL_AXIS'
                   WHEN x MOD 11 + x MOD 15 =12 THEN 'SMPP_DEFAULT'
                   WHEN x MOD 11 + x MOD 15 =13 THEN 'SMPP_CENTN'
                   WHEN x MOD 11 + x MOD 15 =14 THEN 'MBL_HDFC'
                   WHEN x MOD 11 + x MOD 15 =15 THEN 'MBL_OTHERS'
                   WHEN x MOD 11 + x MOD 15 =16 THEN 'MBL_OTHERS'
                   WHEN x MOD 11 + x MOD 15 =17 THEN 'MBL_CENTN'
                   WHEN x MOD 11 + x MOD 15 =18 THEN 'EBL_OTHERS'
                   WHEN x MOD 11 + x MOD 15 =19 THEN 'MBL_CUB'
              ELSE 'MBL_HDFC' END
            , CASE WHEN x MOD 8 + x MOD 7 =1 THEN 'AM'
                   WHEN x MOD 8 + x MOD 7 =2 THEN 'EV'
                   WHEN x MOD 8 + x MOD 7 =3 THEN 'DY'
                   WHEN x MOD 8 + x MOD 7 =4 THEN 'NT'
                   WHEN x MOD 8 + x MOD 7 =5 THEN 'NN'
                   WHEN x MOD 8 + x MOD 7 =6 THEN 'MD'
                   WHEN x MOD 8 + x MOD 7 =7 THEN 'EV'
                   WHEN x MOD 8 + x MOD 7 =8 THEN 'PM'
                   WHEN x MOD 8 + x MOD 7 =9 THEN 'AM'
                   WHEN x MOD 8 + x MOD 7 =10 THEN 'AM'
                   WHEN x MOD 8 + x MOD 7 =11 THEN 'DY'
              ELSE 'PM' END
            , 1
            , Now()
            , CASE WHEN x MOD 5 + x MOD 6 =1 THEN 'AIRWEB'
                   WHEN x MOD 5 + x MOD 6 =2 THEN 'WEBAIR'
                   WHEN x MOD 5 + x MOD 6 =3 THEN 'TELESTAR'
                   WHEN x MOD 5 + x MOD 6 =4 THEN 'CITWEB'
                   WHEN x MOD 5 + x MOD 6 =5 THEN 'WEBWING'
                   WHEN x MOD 5 + x MOD 6 =6 THEN 'AIRWEB'
                   WHEN x MOD 5 + x MOD 6 =7 THEN 'AIRWEB'
                   WHEN x MOD 5 + x MOD 6 =8 THEN 'CITWEB'
                   WHEN x MOD 5 + x MOD 6 =9 THEN 'TELESTAR'
              ELSE 'AIRWEB' END
            , CASE WHEN x MOD 9 + x MOD 18 =1 THEN NOW() + INTERVAL 1 MINUTE
                   WHEN x MOD 9 + x MOD 18 =2 THEN NOW() + INTERVAL 2 MINUTE
                   WHEN x MOD 9 + x MOD 18 =3 THEN NOW() + INTERVAL 3 MINUTE
                   WHEN x MOD 9 + x MOD 18 =4 THEN NOW() + INTERVAL 4 MINUTE
                   WHEN x MOD 9 + x MOD 18 =5 THEN NOW() + INTERVAL 5 MINUTE
                   WHEN x MOD 9 + x MOD 18 =6 THEN NOW() + INTERVAL 6 MINUTE
                   WHEN x MOD 9 + x MOD 18 =7 THEN NOW() + INTERVAL 7 MINUTE
                   WHEN x MOD 9 + x MOD 18 =8 THEN NOW() + INTERVAL 8 MINUTE
                   WHEN x MOD 9 + x MOD 18 =9 THEN NOW() + INTERVAL 10 MINUTE
                   WHEN x MOD 9 + x MOD 18 =10 THEN NOW() + INTERVAL 11 MINUTE
                   WHEN x MOD 9 + x MOD 18 =11 THEN NOW() + INTERVAL 13 MINUTE
                   WHEN x MOD 9 + x MOD 18 =12 THEN NOW() + INTERVAL 16 MINUTE
                   WHEN x MOD 9 + x MOD 18 =13 THEN NOW() + INTERVAL 18 MINUTE
                   WHEN x MOD 9 + x MOD 18 =14 THEN NOW() + INTERVAL 19 MINUTE
                   WHEN x MOD 9 + x MOD 18 =15 THEN NOW() + INTERVAL 7 MINUTE
                   WHEN x MOD 9 + x MOD 18 =16 THEN NOW() + INTERVAL 6 MINUTE
                   WHEN x MOD 9 + x MOD 18 =17 THEN NOW() + INTERVAL 9 MINUTE
              ELSE NOW() + INTERVAL 3 MINUTE END
            , '00:02:00');
                       SET  x = x + 1;
                   END WHILE;
           END $$

DELIMITER ;

Regards

Mahadevan

MYSQL Stored Procedure to insert data into table

MYSQL Stored Procedure to insert data into table:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_InsertTelecount` $$
CREATE PROCEDURE `sp_InsertTelecount`()
BEGIN
          DECLARE x  INT;
            SET x = 1;
            WHILE x  <= 10 DO
             insert into sms_detail(sumID
                    ,StatusFlag
                    ,DC
                    ,Product
                    ,TNAME
                    ,MSGTYPE
                    ,SENDDATE
                    ,OPERATOR
                    ,DELDATE
                    ,DURATION)
             VALUES(1, 'Success', 'VSNL', 'SMPPBOX', 'MBS_143', 'PM',CurDate(), 'AIRNET', Curdate(), '00:01:00');
                       SET  x = x + 1;
                   END WHILE;
           END $$

DELIMITER ;