Tag Archives: MySQL

10 Jan

MySQL variable innodb_flush_method – summarized

innodb_flush_method variable specifies how InnoDB opens and flushes log and data files. In Innodb optimization, setting the variable innodb_flush_method tweaks the performance most of the times but there are cases of otherwise though. If innodb_flush_method is set to O_DIRECT it’d avoid double buffering and reduce swap pressure and helps improving performance.
Continue Reading…

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
Continue Reading…

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).
Continue Reading…

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.



GitHub: mysql random data generator.

Continue Reading…

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 --