Linux: awk - Group By Count File Data

While its pretty easy to do 'Group By' at database level, 'awk' enables us to do same at file level.

Consider a file as below:

# cat test.csv
anita 111
rama 555
david 555
raj 111
shaik 222
naren 222

Here, I want to know how many names in the 1st column are having same values in the second column. 

That's nothing but a simple Group By in MySQL or any other database. 

So, load to a table and execute the query.

But, if the file having crores of data, MySQL query would be a costly operation.

In this time, 'awk' command will come for your help.

Below is the command:

# awk  '{arr[$2]++;}END{for(i in arr)print i, arr[i] ;}' test.csv

555 2

111 2

222 2

Here, awk is fetching the second column into an array and counting the appearance.

If, the file is comma separated(CSV), use the below command:

# awk  -F, '{arr[$2]++;}END{for(i in arr)print i, arr[i] ;}' test.csv

555 2

111 2

222 2

MySQL: Configuration File

When you install MySQL through RPM, before starting the the MySQL process, make sure /etc/my.cnf do exist. And, if it's not, do create one with the basic configuration values.

Below is a sample my.cnf file for a server with InnoDB as the default engine:


[mysqld]
performance_schema
skip-name-resolve
server-id                       = 1
innodb_file_per_table
max_connections                 = 1000
max_allowed_packet             = 512M

slow-query-log                  = 1
slow_query_log_file             = /var/lib/mysql/myServer_slow.log

sort_buffer_size                = 32M
read_buffer_size                = 32M
read_rnd_buffer_size            = 16M
join_buffer_size                = 32M

# innodb parameters
tmpdir                          = /var/lib/mysql
datadir                         = /var/lib/mysql
innodb_data_home_dir            = /var/lib/mysql
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_log_group_home_dir       = /var/lib/mysql
innodb_log_files_in_group       = 2
innodb_buffer_pool_size         = 40G
innodb_additional_mem_pool_size = 64M
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 2047M
innodb_change_buffering         = inserts
innodb_flush_method             = O_DIRECT


# parameters added for thread cache size, wait time out, interactive timeout and table open cache
wait_timeout                    = 28800
interactive_timeout            = 28800
table_open_cache               = 2000
thread_cache_size              = 1000

[mysql]
max_allowed_packet              = 512M



The explanations of these variables..I think I'll do in next posts..

MySQL: User Management

User Management in MySQL is one of the fundamental admin task and is quite easy, as well.

So, here are the basic commands regarding this:


** To list all the MySQL users: Users will be stored in the system table mysql.user. So, you need query this to fetch the users:


mysql> select user,host,password from mysql.user;

Output:

+-----------------+--------------+-------------------------------------------+
| user            | host         | password                                  |
+-----------------+--------------+-------------------------------------------+
| root            | localhost    | *0EF40B7834E1725C32C7EC640FC474ED72254B69 |
| dump            | 127.0.0.1    | *E1A36C05EDBEB86BF19531672613ECFC88F3DB2F |
| root            | 127.0.0.1    | *0EF40B7834E1725C32C7EC640FC474ED72254B69 |

| readonly        | %            | *9E1258A6F6806487E2BFA236472D4D28236F6215 |
+-----------------+--------------+-------------------------------------------+

4 rows in set (0.009 sec)

Note that, Password is encrypted here. This you've to take care while setting up password for any user by using PASSWORD(), a system function.


** To create new user and grant privilege: There are 3 ways to create an user:

1. Using CREATE command:

CREATE USER username@'host' IDENTIFIED BY 'password';
GRANT SELECT ON db.table TO 'username'@'host';
FLUSH PRIVILEGES;


2. Using GRANT command:

GRANT SELECT ON db.table TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

The above command will create the user, if it is not present.


3. Inserting into mysql.user table:

INSERT INTO mysql.user(user,host,password) values ('usrename','host','encrypted_password');

GRANT SELECT ON db.table TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

However, this is not a RECOMMENDED way. Make sure, this password is encrypted using below query:

SELECT PASSWORD('password');



**Removing User/Access:


To Revoke access:

REVOKE SELECT ON db.table FROM 'username'@'host';
FLUSH PRIVILEGES;


To Remove user:

DROP USER 'username'@'host';
FLUSH PRIVILEGES;

or

DELETE FROM mysql.user WHERE USER='username' and host='host';
FLUSH PRIVILEGES;


FLUSH PRIVILEGES -- This command is to Reload the privileges of all the user(reloads from mysql.user table)

MongoDB: Start a Shard Process

Here's sample command to start a MongoDB process, which is having replication set up.

* Go to 'bin' directory, where MongoDB is installed
* Execute the below command to start MongoDB process:

./mongod --shardsvr --replSet r1 --port 27010 --quiet --journal --fork --dbpath /path_of_mongo/rs --logpath /path_of_mongo/logs/rs.log


Desciption:

./mongod --> Calling the MongoDB process

shardsvr --> Option - to make MongoDB process as a Shard

replSet r1 --> Option - to start Shard process as part of Replica set (r1 is the value)

port 27010 --> Option - to start MongoDB process on port 27010

quiet --> Option - to Log less

journal --> Option - to enable journaling - which helps in crash recover - acts as re-do log

fork --> Option - to Start background processes, implicitly 

dbpath --> Option - to specify the installation path of MongoDB

logpath --> Option - to specify the log file path

MongoDB: Change opLog Size

If you've MongoDB Replication setup, you must be aware of opLog..! 

Just as Transaction Log in MS SQL Server or Binary log in MySQL, all those transactions on MongoDB shard will be written into a separate file, called opLog. This opLog will have a fixed size, which means, as soon as it reaches the max size, it will be overwritten. 

So, if there are huge number of transactions, you may need to increase the size of it.
To check your current opLog size:

rs.printReplicationInfo()


To change it's size, below are the plan of execution:

* If the shard in Primary, make it secondary
* Shutdown this secondary shard 
* Start it with different port -- basically, we're isolating it from replica set
* Take dump of opLog -- for safety purpose
* Create a temporary collection 
* Insert the last entry of opLog to this temporary collection
* Drop the existing opLog
* Create a new opLog with custom size
* Insert the record from temporary collection into this new opLog
* You're all set to go..!
* Re-start shard with original port

Here are the steps with commands:

Check size:
> rs.printReplicationInfo()


Make primary as secondary:
> use admin
> rs.stepDown()


Shutdown the server:
> use admin

> db.shutdownServer()


Start with dummy port:
# ./mongod --port 37010 --dbpath /paath/data/rs --logpath /path/logs/rs.log


Create Dump of opLog:
# ./mongodump --db local --collection 'oplog.rs' --host localhost --port 37010 


Create a temp collection:
> use local

> db = db.getSiblingDB('local')
local

> db.temp.drop()

false

> db.temp.save( db.oplog.rs.find( { }, { ts: 1, h: 1 } ).sort( {$natural : -1} ).limit(1).next() )

> db.temp.find()
{ "_id" : ObjectId("5436532b1cd0a62fea32625b"), "ts" : Timestamp(1412841535, 2), "h" : NumberLong("6772852693461355875") }



Drop and Create a new opLog(I'm creating here for 20 GB):
> db = db.getSiblingDB('local')

local

> db.runCommand({create: "oplog.rs", capped: true, size:(20 * 1024 * 1024 * 1024)})
{ "ok" : 1 }

> db.oplog.rs.save(db.temp.findOne())

> db.oplog.rs.find()

{ "_id" : ObjectId("5436532b1cd0a62fea32625b"), "ts" : Timestamp(1412841535, 2), "h" : NumberLong("6772852693461355875") }


Re-start as earlier:
> use admin;

> db.shutdownServer()


That's all !

MySQL : Replication Error 1062

It's been a pretty long time without posts. So, there's a lot to blog it!

One of the common error in MySQL Replication is 'Duplicate Entry' - Error 1062. This pop-ups only if there are any manual intervention.
The SQL Thread stops with this error. Just check the record in the error on both master and slave. If you understand the exact cause, you may delete the record in slave and start replication. Or else, just skip ignore the insert statement using the below command:


mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
mysql> START SLAVE; 

This will skip 1 statement and continues replication. If you're getting lot of such errors, again and again, just add the below entry in Slave's my.cnf and restart MySQL:

[mysqld]

slave_skip_errors    = 1062


This will keep on skipping duplicate entries whenever it occurs.

Please note that, you can use this for any other error, as well. 

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.