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