17 Dec

Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants

Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges?

One can easily forget to backup the user privileges but it is much easier to do and you should backup your MySQL server user privileges / grants regularly.

1. Backup MySQL user privileges using mysqldump:

$] mysqldump -uUSER -pPASS --databases mysql > mysql.sql

2. Copy MySQL data directory:

$] cd /var/lib/mysql/ [ assuming default data directory ]
mysql> flush tables with read lock;
$] cp -R mysql /path/to/backup/mysql_grants_dir
mysql> unlock tables

3. Using Percona tool pt-show-grants:

pt-show-grants shows grants (user privileges) from a MySQL server. It can also write REVOKE and DROP user statements as required.

If you donot have Percona Tools already installed you may download individual tool as follows:
$] wget percona.com/get/pt-show-grants
$] chmod +x pt-show-grants
$] pt-show-grants -uUSER -pPASSWORD > user_grants.sql

You may also include following options:
–separate: Write grants with individual privileges
–revoke: Write revoke grants for all grants
–drop: Add drop user command before grant syntax

For more info:
$] perldoc pt-show-grants

Using pt-show-grants:
Getting MySQL user grants for specified user:
root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --drop --only kedar
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.5.27-0ubuntu2-log at 2012-12-17 04:54:34
-- Revoke statements for 'kedar'@'localhost'
REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';
DROP USER 'kedar'@'localhost';
DELETE FROM `mysql`.`user` WHERE `User`='kedar' AND `Host`='localhost';
-- Grants for 'kedar'@'localhost'
GRANT LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO 'kedar'@'localhost';

You can also specify comma separated list of users.

Advantages / use:
– Easily replicate users from one server to another.

// Creating user USER_X on server B like that on server A
pt-show-grants -uUSER -pPASS -hSERVER_A --only USER_X | mysql -uUSER -pPASS -hSERVER_B

– Unified sorted canonicalized grant syntaxes to put in version control
– As pt-show-grants canonicalize the grant syntaxes, it’d be good to do “diff” the grants of two different MySQL instances.
– Easily generate revoke syntaxes for particular user/ users:
root@ubuntu:~# pt-show-grants -uroot -pkedar --revoke --only kedar | grep REVOKE
REVOKE LOCK TABLES, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* FROM 'kedar'@'localhost';

13 Sep

MySQL function returns dates of specified day & between date range

This mysql stored function will return you all the dates between given dates having specified day.
In short if you say: give me all Sundays in this month.

I have following for you:

mysql> select Give_Me_Dates_Days(1,'2012-09-01','2012-10-01') as All_Sundays;
+--------------------------------------------------------+
| All_Sundays |
+--------------------------------------------------------+
| 2012-09-02,2012-09-09,2012-09-16,2012-09-23,2012-09-30 |
+--------------------------------------------------------+

The function parameters are as follows: Give_Me_Dates_Days(DAY_IDENTIFIER,START_DATE,END_DATE)

Sunday = 1 (and rest you can figure-out).

[ Ofcourse I’ve not done much validations eg. start_date < end_date. ]

Check the code for getting dates of specified days between a date-range as follows. You may download the sql here:Give_Me_Dates_Days.sql

DELIMITER $$
DROP function IF EXISTS `Give_Me_Dates_Days` $$
CREATE function `Give_Me_Dates_Days` (in_day int, in_date1 timestamp, in_date2 timestamp) returns varchar(4000) deterministic
BEGIN
DECLARE tot_dates int;
DECLARE proc_date timestamp;
DECLARE dates varchar(4000) default '';
#1= sunday
# drop temporary table if exists selecteddates;
# create temporary table selecteddates ( dates timestamp );
set proc_date=in_date1;
while proc_date < in_date2
do
if (dayofweek(proc_date)=in_day) then
set dates=concat(dates,date(proc_date),',');
# insert into selecteddates values (proc_date);
end if;
set proc_date=date_add(proc_date, interval 1 day);
end while;
return trim(trailing ',' from dates);
END $$
DELIMITER ;

05 Jul

Generate random test data for MySQL using routines

At times you’ll find yourself responsible for generating test data for newly created tables for testing or sampling purpose. There are tools that will generate random data for you but they’re not free. At-times you’ll write scripts to generate data but those will be table specific.

I hate generating dummy data, yes I do and I assume you do too! I think that’s the major reason I wrote these MySQL functions and procedures for Generating dummy test data.

People can manage these things with a simple perl / shell script with loops but again that always need your time.
Why can’t MySQL generate data for it’s own table when MySQL better knows the table than anyone else !! :)

Below are a set of functions and a stored procedure that will make our life easy and of-course it’s free ;).

Download the sql code: Generate dummy data for MySQL.

How to install and generate dummy data:
mysql -uUSER -pPASSWORD DATABASE < populate_dummy_data.txt

How use installed functions to generate test data:
- To generate test data of 1000 rows for sakila.film table execute following sql command:
call populate('sakila','film',1000,'N');

MySQL set of functions to get random values generated for individual data-types.

## MySQL function to generate random string of specified length
DROP function if exists get_string;
delimiter $$
CREATE FUNCTION get_string(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,IFNULL(ELT(1+FLOOR(RAND() * 53), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),'Kedar'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$
delimiter ;

## MySQL function to generate random Enum-ID from specified enum definition
DELIMITER $$
DROP FUNCTION IF EXISTS get_enum $$
CREATE FUNCTION get_enum(col_type varchar(100)) RETURNS VARCHAR(100) DETERMINISTIC
RETURN if((@var:=ceil(rand()*10)) > (length(col_type)-length(replace(col_type,',',''))+1),(length(col_type)-length(replace(col_type,',',''))+1),@var);
$$
DELIMITER ;

## MySQL function to generate random float value from specified precision and scale.
DELIMITER $$
DROP FUNCTION IF EXISTS get_float $$
CREATE FUNCTION get_float(in_precision int, in_scale int) RETURNS VARCHAR(100) DETERMINISTIC
RETURN round(rand()*pow(10,(in_precision-in_scale)),in_scale)
$$
DELIMITER ;

## MySQL function to generate random date (of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_date $$
CREATE FUNCTION get_date() RETURNS VARCHAR(10) DETERMINISTIC
RETURN DATE(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random time.
DELIMITER $$
DROP FUNCTION IF EXISTS get_time $$
CREATE FUNCTION get_time() RETURNS INTEGER DETERMINISTIC
RETURN TIME(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random int.
DELIMITER $$
DROP FUNCTION IF EXISTS get_int $$
CREATE FUNCTION get_int() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*10000000)
$$
DELIMITER ;

## MySQL function to generate random tinyint.
DELIMITER $$
DROP FUNCTION IF EXISTS get_tinyint $$
CREATE FUNCTION get_tinyint() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*100)
$$
DELIMITER ;

## MySQL function to generate random varchar column of specified length(alpha-numeric string).
DELIMITER $$
DROP FUNCTION IF EXISTS get_varchar $$
CREATE FUNCTION get_varchar(in_length int) RETURNS VARCHAR(500) DETERMINISTIC
RETURN SUBSTRING(MD5(RAND()) FROM 1 FOR in_length)
$$
DELIMITER ;

## MySQL function to generate random datetime value (any datetime of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_datetime $$
CREATE FUNCTION get_datetime() RETURNS VARCHAR(20) DETERMINISTIC
RETURN FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200)
$$
DELIMITER ;

The MySQL Stored procedure that populates MySQL tables with dummy data:

DELIMITER $$
DROP PROCEDURE IF EXISTS populate $$
CREATE PROCEDURE populate(in_db varchar(20), in_table varchar(20), in_rows int, in_debug char(1))
BEGIN
/*
|
| Developer: Kedar Vaijanapurkar
| USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE);
| EXAMPLE: call populate('sakila','film',100,'N');
| Debug-mode will print an SQL that's executed and iterated.
|
*/

DECLARE col_name VARCHAR(100);
DECLARE col_type VARCHAR(100);
DECLARE col_datatype VARCHAR(100);
DECLARE col_maxlen VARCHAR(100);
DECLARE col_extra VARCHAR(100);
DECLARE col_num_precision VARCHAR(100);
DECLARE col_num_scale VARCHAR(100);
DECLARE func_query VARCHAR(1000);
DECLARE i INT;

DECLARE done INT DEFAULT 0;
DECLARE cur_datatype cursor FOR
SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_schema.columns WHERE table_name=in_table AND table_schema=in_db;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET func_query='';

OPEN cur_datatype;
datatype_loop: loop
FETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale;
#SELECT CONCAT(col_name,"-", col_type,"-", col_datatype,"-", IFNULL(col_maxlen,'NULL'),"-", IFNULL(col_extra,'NULL')) AS VALS;

IF (done = 1) THEN
leave datatype_loop;
END IF;

CASE
WHEN col_extra='auto_increment' THEN SET func_query=concat(func_query,'NULL, ');
WHEN col_datatype in ('int','bigint') THEN SET func_query=concat(func_query,'get_int(), ');
WHEN col_datatype in ('varchar','char') THEN SET func_query=concat(func_query,'get_string(',ifnull(col_maxlen,0),'), ');
WHEN col_datatype in ('tinyint', 'smallint','year') or col_datatype='mediumint' THEN SET func_query=concat(func_query,'get_tinyint(), ');
WHEN col_datatype in ('datetime','timestamp') THEN SET func_query=concat(func_query,'get_datetime(), ');
WHEN col_datatype in ('float', 'decimal') THEN SET func_query=concat(func_query,'get_float(',col_num_precision,',',col_num_scale,'), ');
WHEN col_datatype in ('enum','set') THEN SET func_query=concat(func_query,'get_enum("',col_type,'"), ');
ELSE SET func_query=concat(func_query,'get_varchar(',ifnull(col_maxlen,0),'), ');
END CASE;
end loop datatype_loop;
close cur_datatype;

SET func_query=trim(trailing ', ' FROM func_query);
SET @func_query=concat("INSERT INTO ", in_db,".",in_table," VALUES (",func_query,");");
IF in_debug='Y' THEN
select @func_query;
END IF;
SET i=in_rows;
populate :loop
WHILE (i>0) DO
PREPARE t_stmt FROM @func_query;
EXECUTE t_stmt;
SET i=i-1;
END WHILE;
LEAVE populate;
END LOOP populate;
SELECT "Kedar Vaijanapurkar" AS "Developed by";
END
$$
DELIMITER ;

Let me know if you like it and share if you find it useful. Please report any bug.
Below is the code for review:

Edit: I'd like to mention Ronald Speelman's post [http://moinne.com/blog/ronald/mysql/howto-generate-meaningful-test-data-using-a-mysql-function] of generating meaningful test data is great and it's something I'd like to work towards.

26 May

Men in Black-3 (MiB-3) – Xtreme Movie Review

man in black-3

“I just learned that Agent K has been assassinated by Boris from future, an alien criminal in 1969. I must travel through a time and stop him and save young Agent K. And I have only 24 hours to do the stuff and return home safely!! ”

O Yeah!! Will Smith Just told me the base line script of MIB-3 and that’s what the movie is all about. But that’s not the end, for MIB lovers the movie has got much more to offer.

men in black-3

It took 10 years for Men In Black to get along for MIB-3 but finally Will Smith AKA Agent J is back with good touch of comedy, well written sci-fi story and action movie. What the movie doesn’t yet show in demo videos is the creepy and “yuk” moments that girls and kids may not enjoy. I enjoyed it though :)

If you’re looking for a quick notes to “Go for MIB-3 or not”, here are the points:
– You’re a fan of Will and/or MIB, you ought to watch this.
– You’re a fan of sci-fi type movies, this one is right for you.
– You haven’t seen MIB-1 and MIB-2, you can still go for MIB-3. Just read the following 2 points and go for it.
1. Neurolyzer: A device that erases your memory to the preset time on it.
2. Agent J is Will Smith and Agent K is his partner to tackle the aliens and protect earth.

Now, When there is a Will, there is Smith.
:) What I took from the movie is Jokes, a lot of funny and comedy dialogue sequences that keep your lips in a “smiled” position.

Jokes apart; The movie starts with Boris (played nicely by Jemaine Clement), an alien escaping from the prison of moon. Boris aims to time travel to 1969 to assassinate Agent K, the time when Agent K had captured him and chopped his hand off.

I got surprised to see Nicole, the PCD girl, taking a small but creepy and funny role being lover of Boris and helping him escape from the Moon-Jail, with no more than 2 dialogues! Anyways the story continues…

During the regular life Agent J notices that everything has changed suddenly and he couldn’t find his partner Agent K. Agent J learns that Agent K has been already dead in a complex story sequence and decides to go to past 1969 to stop the Future Boris to kill Past Agent K and stop changing the history. Yep that’s MIB-3 :)

The way Will (Agent J) travels through time is a great animation and movie sequence when he jumps from Chrysler building. In past times Agent K has to put some security gadget on Apollo 11 to secure earth from the attack of Boris-kind-of-aliens while Boris from Future and Boris from Past try to stop Past Agent K.

Bike lovers ‘d like the bike of Boris from future.

The past involves Griffin, another alien who has a gift of seeing future, who actually provides the security gadget to Agent K. Another important character is a security-officer who helps Agent J & K to pass through security for the Apollo 11 and helps him in installing the gadget. That’s where Will (Agent J) learns about his past.
Anyways, Agent J and K successfully install the security gadget and kill both Boris from Future and Past – the happy sci-fi ending.

Alice Eve plays the young Agent O and looks beautiful, a good reason for Agent K to fall in love :)
I saw it in Hindi and here are a few really catchy dialogues from MIB-3:

Agent K to Agent J: Tumhari salary itni nahi hai ki tum ye kaam kar sako.

Agent K to Agent J: Ye ek raaz hai jo kum salary walon ko nahi bataya jata.

Agent K to Agent J: Uski biwi ne muze jala hua khana khilaya tha fir bhi wo mera dost hai.

A few I found from script which are better fun in English:
Agent J: You gotta send me back to 1969…
Alien: First, we gotta get high.
Agent J: My man, for real!
Alien: No, I mean really high.

The movie runs for approx 1.30 hour and never bores you even for a minute. The script is well written with constant witty dialogues and comedy sequences and well directed with good animation.
I saw it in 2D and watching it in 3D’d have made it creepier and nicer :)
It’s full of fun and time-pass entertainment. Go watch it, you won’t be disappointed.
Let me know what do you think about the movie too :)

Here is an official trailer for you:

03 May

Load columnar text file into MySQL table

Ever come accoss a situation where you have to load a flat text file into MySQL with data provided in single column!
Say, your data to be loaded in table looks something like this:

Company1
Street Name
City, MyState 8582
(999)999-999
another_Company
Another Street 2
New City, NWSTATE 8582
(111)111-1111

So how’d you go about loading a text file into MySQL where field values are given in a single column!!
In the above sample, data has fields like company-name, address-1, address-2 and phone given in a text file line by line.
Ofcourse you can write a piece of code in your choice of language: perl, shell, python…
But can you do this by sheer power of MySQL by using some commands or functions only?

Well, normally you will have to have a script for such data to be inserted into database. We always prefer the raw data / text file in the csv / tab separated (columns) file(s), which you can directly load to MySQL table as explained in my previous article: http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server

But that’s not the case here. And hence I have a workaround to share. With little more efforts you can also automate / scriptize this. [ I’m feeling bit lazy now. ;) ]

Follow these steps:
– Save above data in a file [say c:/main.txt considering windows]
– Connect to MySQL through commad prompt

– Create a main table to load all data to process later.

drop table if exists main; create table main ( id integer not null auto_increment primary key,alldata varchar(100), type int );

– Load data to MySQL table:

load data local infile 'C:/mainfile.txt' into table main lines terminated by '\r\n' (alldata) set id=null;
set @var=0;update main set type=if(id mod 4 = 1,@var:=@var+1,@var);

– Create table as per your requirement:

create table company_details (
company varchar(100),
street varchar(100),
citystatezip varchar(100),
phone varchar(100) );

– Generate insert statement:

select concat("insert into company_details (company,street,citystatezip,phone) values (", my_values, ");") from (select group_concat('"',alldata,'"') my_values from main group by type) X;

you may alternatively redirect this data to a txt file and load it back to mysql!

put above query in sql :
mysql -uroot -ppassword database < generate_insert_query.sql > inserts.sql

Finally load thus generated inserts to MySQL database table.
mysql -uroot -ppassword database < inserts.sql

I hope this helps many.

-- Kedar Vaijanapurkar --