MySQL: SELECT / LOAD in Batch

Here's are the MySQL commands to extract data from  table and to load data into tables.
Unlike other DBMS, MySQL offers SELECT..INTO.. and LOAD DATA.. commands are quite faster and convenient. Data would be fetched and loaded in bacthes.


Sample query to extract data from a table:

SELECT *
FROM tmp.my_table
INTO OUTFILE '/file_path/filename.csv'
FIELDS TERMINATED BY '|$|'
LINES TERMINATED BY '\n';

You can select particular columns, instead of  '*' .

OUTFILE should be given with file path and name. By default, file will be created inside data directory.
Important thing to be noted here is, the file will be generated inside the local host only.
Means, if you're executing above query on server S2, fetching data from S1, the file will be generated inside S1 itself.

Also, to execute above command, it requires FILE Privilege for MySQL user and the destination directory should also be having server level MySQL user privilege.

Below is the query that an be used to load data in batches:

LOAD DATA LOCAL 
INFILE '/file_path/filename.csv' 
INTO TABLE tmp.my_table 
CHARACTER SET utf8 
FIELDS TERMINATED '|$|' 
LINES TERMINATED BY '\n';

You can use, 'IGNORE INTO' instead of 'INTO', to ignore the duplicate records.
CHARACTER SET is mentioned explicitly as the data being loaded is having local languages(like kannada, hindi, telugu,..). Without this, data won't be visible - scrambled.

Linux: Concatenate In Linux

Here is the commands to concatenate in Linux.

Consider a file as below:

[root@myserver 6048]# cat test.txt
12
0
123445456
234565443
3883
33

My requirement is to generate update/delete statement for each of this value. Think of a file with millions of records.

My update statement would be:

update mytable set col1='Yes' where col2=' value in the file';

Here's the solution:

[root@myserver 6048]# cat test.txt | sed "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g"
update mytable set col1='Yes' where col2='12';
update mytable set col1='Yes' where col2='0';
update mytable set col1='Yes' where col2='123445456';
update mytable set col1='Yes' where col2='234565443';
update mytable set col1='Yes' where col2='3883';
update mytable set col1='Yes' where col2='33';
update mytable set col1='Yes' where col2='';
update mytable set col1='Yes' where col2='987676';


You may redirect the output to some other file, as below:

[root@myserver 6048]# cat test.txt | sed "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g" > updates_test.sql


You may also use the below query for the purpose:


[root@myserver 6048]# sed  "s/^/update mytable set col1='Yes' where col2='/g;s/$/';/g"  test.txt > updates_test.sql


Ping me for any doubts.. +Kiran Yadagere  | facebook.com/kiranyadagere

Linux: Adding Numbers in a File

Here's a Linux command to add numbers inside a file:


[root@myserver misc]# cat num.test
1
2
3
4
5


This is a sample file with only 5 lines. Think of a file with millions of lines. Either you can do with MS - Excel, which may hang(!!) or use Linux command - 'awk'- which is powerful and easier:

[root@myserver misc]# awk '{s+=$1} END {print s}' num.test
15

As simple as that..!!

MongoDB: mongoexport Command - Sample

Here's is a simple MongoDB 'mongoexport' command syntax to backup the document/database. This command can be used to backup based on particular conditions ('where' clause) -

Go to MongoDB bin  directory and execute as below:

./mongoexport --host localhost --port 10000 -d doc_name -c coll_name -q '{"_id":"12345687899asdc"}' -o /path/file_name.csv

This will export a single record which is matching the given value for field '_id'.

Android: Read Kannada/Other Local Languages in your mobile

Here's how to Read Kannada, other Indian languages or any Unicode characters in your Andriod mobile:

(Mainly for Facebook ..!!)

This one is mainly for old android sets which doesn't have support for Unicode.  All today's Andriod mobiles display it correctly, by default.

Here's the steps what I followed to read Kannada on my Android phone:

1. Download and install 'Opera Mini' from Android Market/Google Play Store

2. Open Opera Mini and type below words in address bar, hit enter:

about:config

3. This will open a settings page. Scroll down to until you see below line:

Use bitmap fonts for complex scripts

4. There should be a drop down list, select 'Yes' and Save it.

5. Now open new tab and test it. You can type below address, which is a kannada website:

sampada.net

You should be able to see Kannada letters, clearly.

Geek: Write Kannada/Other Local Languages

How to write Kannada/Hindi or any other other language in any Website:

  

There are many ways, but here is one of the easiest way. (Its for PC/Laptop, doesn't work with mobile)

Follow the steps below:

1. Download and install Mozilla Firefox browser using below link if you don't have already:

http://www.mozilla.org/en-US/firefox/new/

2. Open Firefox and press Alt to see the menu bar. Go to Tools -> Add-ons.

Or

Press Ctrl + Shift + A

3. It opens a new tab 'Add-ons Manager'. In upper right corner there will be a Search bar. Type 'Lipikaar' and press Enter.

4. It will search for an add on - 'Lipikaar' (see the attached image). Now click on Install button. It prompts a dialogue box - click on Accept Install.

It downloads the add-on of 176 KB.

  

5. After installation, click on Restart Now.

6. After restarting, in the same 'Add-on Manager' tab, click on 'Extentions' (see the image). It shows all the installed add-ons. In Lipikaar, click on Options button.

Select your preferred language and click OK (see the image).

7. Last but important step is, whenever you want to type in the language you seleced in Lipikaar, press Ctrl + Alt + L .

Use the same to revert back to english.

Any doubts?!

Geek: Download Video from Web (YouTube)

So, here comes an interesting topic.

How to download videos from any websites(not only YouTube)?

There are many ways. Below is one of the easiest way:

For this, Mozilla Firefox comes to our help. Using an add-on called 'Video download helper'

1. Download and install Mozilla Firefox browser using below link if you don't have already:

http://www.mozilla.org/en-US/firefox/new/

2. Open Firefox and press Alt to see the menu bar. Go to Tools -> Add-ons.

Or

Press Ctrl + Shift + A

3. It opens a new tab 'Add-ons Manager'. In upper right corner there will be a Search bar. Type 'Video Download Helper' and press Enter.

4. It will search for an add on - 'Video DownloadHelper' (see the attached image). Now click on Install button. It prompts a dialogue box - click on Accept Install.

It downloads the add-on of 756 KB.

5. After installation, click on Restart Now.

6. After restarting, in the same 'Add-on Manager' tab, click on 'Extentions'. It shows all the installed add-ons. In DownloadHelper, click on Options button.

Click on 'Services -> Download -> Storage Directory' to set the download location and click OK (see the image).

7. Now open any website and play a video. As the video starts playing, on the left top corner, just before the address bar, the Video Download Helper icon starts rotating. Click on the drop down arrow of it which gives you options to download i different formats.

I would suggest to click on .mp4 format. Select Quick Download from it.(See the image).

8. Your fav video starts downloading now. No need to keep playing the video.

But don't close Mozilla Firefox.

Linux: Sorting a File

Here's simple 'sort' command that will remove the duplicate entry from any file and sort in ascending order:

Consider a file with few numbers:

[root@myhost tmp]# cat testSort.txt
23
4
56
001
34
3
 

To sort it:

[root@myhost tmp]# sort -u testSort.txt > sortd_testSort.txt
 
wherein, -u --> Unique


Its sorted:

[root@myhost tmp]# cat sortd_testSort.txt
001
23
3
34
4
56
Oops! Though it worked, it didn't work correctly. Because the file contains numeric data.

So use the below:

[root@myhost tmp]# sort -u -n testSort.txt > numeric_sortd_testSort.txt wherein, -n --> for numeric data

 
And, now its correct:

[root@myhost tmp]# cat numeric_sortd_testSort.txt
001
3
4
23
34
56
However, this works only for the small files. Smaller than space available with '/tmp' partition.

If you want to sort huge files, use the below:

[root@myhost lib]# sort -u -n -T. testSort.txt > numeric_sortd_testSort_new.txt wherein, -T. --> T indicates the location used as temporary space. By default its '/tmp'.

'.' indicates current directory

MySQL: RPM Installation

Before going through this post, I would recommend you to have glance on my previous post related to this - 'MySQL - Before Installation..

So, here is the steps to install MySQL through RPMs:

* First of all download Server and Client RPMs. 
You can download the latest GA release from MySQL site or version you want from Oracle e-delivery.

Below are the RPMs, I downloaded:

[root@localhost Desktop]# ls -lhrt
total 70M
-rwxrw-rw-. 1 theray theray 52M Feb 12 05:14 MySQL-server-5.6.16-1.el6.x86_64.rpm
-rwxrw-rw-. 1 theray theray 18M Feb 12 05:14 MySQL-client-5.6.16-1.el6.x86_64.rpm


* Make sure that there is not previously installed MySQL, using the below command :

[root@localhost Desktop]# rpm -qa | grep -i mysql
mysql-libs-5.1.47-4.el6.x86_64

If already there, us below command to remove it:

[root@localhost Desktop]# rpm -e rpm_package_name


* Install RPMs:

[root@localhost Desktop]# ls -lrht
total 70M
-rwxrw-rw-. 1 theray theray 52M Feb 12 05:14 MySQL-server-5.6.16-1.el6.x86_64.rpm
-rwxrw-rw-. 1 theray theray 18M Feb 12 05:14 MySQL-client-5.6.16-1.el6.x86_64.rpm

--Installing Client:

[root@localhost Desktop]# rpm -ivh MySQL-client-5.6.16-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]

--Installing Server:

[root@localhost Desktop]# rpm -ivh --force MySQL-server-5.6.16-1.el6.x86_64.rpm
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
2014-02-12 05:39:06 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-02-12 05:39:06 32621 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-02-12 05:39:06 32621 [Note] InnoDB: The InnoDB memory heap is disabled
2014-02-12 05:39:06 32621 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
.
.
.


* Now, create /etc/my.cnf file(its the basic configuration file) with the necessary configurations in it:

[root@localhost Desktop]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

* Start MySQL process :

[root@localhost Desktop]# /etc/init.d/mysql start
Starting MySQL......................................................................................................
.......... SUCCESS!

* Once its started, use the below command to remove anonymous users and make the installations secure:

[root@localhost Desktop]# /usr/bin/mysql_secure_installation

Now the MySQL is ready for use!

MySQL: Slave Status - SECONDS_BEHIND_MASTER

We often execute the below command to check the replication status:

mysql> SHOW SLAVE STATUS \G

This will show all the details regarding replication. Out of which, the field SECONDS_BEHIND_MASTER is the one is the curious one.

It is the time in Seconds, which indicates actual amount of lag between Master and Slave.

In actual words, it's the calculation in seconds between the relay log's latest query event time stamp(which is already executed on master and queued on slave) and the current time stamp of the slave.

So, if the time set on Master's host and Slave's host are different, then, you'll see a scary value for this field.

techGeek: Create Password less PDF !

Now-a-days, almost all PDF docs are password protected! Uff..sometimes I get irritated of it and try to make it password-proof!

And here's how you can make it:

My PDF with password:

No worries! Just close it and do as below:

* Open the PDF with Google Chrome:

*It will prompt for the password and give it:

* Once the PDF is open, click on Print button at right side bottom (or press Ctrl + P) and select Save as PDF. Click on Save :

Its done! 

Now the PDF will be saved without password!!

Linux: Crontab - Brief

I always get confuse whenever I want to set a new cron job. The confuse is with regard to the options too be set!
For those who new to 'cron', its nothing but, an event scheduler in Linux. That means, you can schedule any script to run at any time you wanted to. Its just the system/server should be up and running!

cron job is specific to every user in Linux/Unix. So, one can't see other's cron unless the necessary privileges or sudo root access given.

Whatever, here is the options in cron:


To check cron jobs:

[root@localhost kiran]# crontab -l
no crontab for root

To set cron jobs:

[root@localhost kiran]# crontab -e

After adding, here is how it looks:

[root@localhost kiran]# crontab -l

##Script to test

00 */2 1-31 * 0,2,3   sh /home/kiran/test.sh >> /dev/null

Every Cron job should be given with 5 options:

• minute -> 0-59
• hour -> 0-23
• day of month -> 1-31
• month -> 1-12 
• day of week -> 0-7 (0 is Sunday )

In the above example:

00 -- 0th Minute

*/2 -- Every 2 hours

1-31 -- Every day (1 to 31)

* -- Every Month

0,2,3 -- Sunday,Tuesday,Wednesday

MySQL: Clustered Index

Clustered Index is the InnoDB term for a primary key index. 

InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. 

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. 

The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted.
Thus, the rows ordered by the row ID are physically in insertion order. 

All indexes other than the clustered index are known as secondary indexes.
In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. 

InnoDB uses this primary key value to search for the row in the clustered index.

Physical Strcuture:
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree. 
The default size of an index page is 16KB. 
When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.

The page size can be specified for all InnoDB tablespaces in a MySQL instance by setting the innodb_page_size configuration option before creating the instance.
 
Once the page size for a MySQL instance is set, you cannot change it.
Supported sizes are 16KB, 8KB, and 4KB, corresponding to the option values 16k, 8k, and 4k.

A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size.

MySQL: B-Tree Index

 When any CREATE TABLE is executed, InnoDB internally creates a clustered B-tree index  representing the table. 
If no primary key is explicitly given, internally a primary key is added. 

Each node of B-tree is represented by one page (which is by default is of size 16KB). 


Below is an useful set of info, copied from other blog
(guptavikas.wordpress.com/2012/12/17/b-tree-index-in-mysql/):


B-Tree is the default index for most storage engines in MySql. 
The general idea of a B-Tree is that all the values are stored in order, and each leaf page is the same distance from the root.

A B-Tree index speeds up data access because the storage engine doesn’t have to scan the whole table to find the desired data. 
Instead, it starts at the root node. The slots in the root node hold pointers to child nodes, and the storage engine follows these pointers.

It finds the right pointer by looking at the values in the node pages, which define the upper and lower bounds of the values in the child nodes. 

Eventually, the storage engine either determines that the desired value doesn’t exist or successfully reaches a leaf page. Leaf pages are special, because they have pointers to the indexed data instead of pointers to other pages.

MySQL: InnoDB doublewrite buffer

That's an useful piece of information, so did a copy-paste from MySQL site:

InnoDB uses a novel file flush technique called doublewrite. 

Before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. 

If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the buffer itself as a large sequential chunk, with a single fsync() call to the operating system.

To turn off the doublewrite buffer, specify the option innodb_doublewrite=0.