Thursday, 17 October 2019

All those Logs in MySQL

If you are a MySQL guy, this might be a silly topic for you. But, there are lot many people out there who’re still confused about the binlogs, relay logs, transaction logs.. So, this is intended for them!
Below are the logs created by MySQL with brief explanation :
  • Error Log : This is one of the important log. When enabled using the startup option –log-error or with variable log_error in the config file, MySQL writes ‘what’s important happening’ to this log file. If these options are not given, the log entries are written to the console and not to the file.

  • Transaction Logs : I’ve seen people familiar with other RDBMS asking if there is a transaction log in MySQL. Also, there are people confused Binary log as the transaction log. Well, that’s an half truth !  Of course, MySQL do have transaction logs and they are known innodb log files (ib_logfiles) . By default 2 files are created : ib_logfile0 and ib_logfile1 . They are written/overwritten in circular fashion.    These transaction logs are also known as Redo logs and they play a crucial role in MySQL’s operation.     More details on this can be found in here: https://dev.mysql.com/doc/refman/5.7/en/innodb-init-startup-configuration.html#innodb-startup-log-file-configuration

  • Binary Logs : These logs, when enabled with log_bin option, contains all those changes committed to the database. They play crucial role in point-in-time recovery solution and replication.

  • Relay Logs : These logs are specific to the Slave server. When an instance is configured as slave to a master server, the I/O thread starts reading Master’s Binlog and creates the Slave’s version of it in the form of Relay logs. They typically are the replica of Master’s Binlog, however, transactions might be in a different order. Please note that, Relay logs are flushed whenever the I/O position is manually reset or slave re-configured.

  • General Log : This log is by default disabled and when enabled, contains almost everything that MySQL is doing. It logs all the connections, their activities, everything. Though it’s useful, the size of it might be a serious issue. Generally, it’s enabled in a  particular time frame for the audit purpose. Otherwise, it’s better stay disabled.

  • Slow Query Log : As the name indicates, it logs all the slow queries which is running beyond the specified time, when configured. It is helpful for having track of long running queries.

  • Audit Log : This is not a common log. It can be generated only if the Enterprise Audit Plugin is enabled. When enabled, it logs all the connections and queries which can be filtered based on users or databases or source, etc. This is one of the very useful log for auditing .

Slave with GTID – How to use slave_skip_errors ?

Okay, here’s a challenge. Assume that you’ve a Master-Slave setup. Both are GTID enabled and Slave is having auto_position set to 1. Which means, it’s using GTID positions to get in sync with master.

Now, suppose someone executes some insert queries into one of the table with auto_increment key column in the Slave server directly  . And so, in next few seconds, the Slave breaks down with the Duplicate Entry(1062) error. How you fix?
Since you’re smart, you inject some empty transactions to skip those GTID positions. Fair. 
But, what if there are hundreds of thousands of inserts done directly and you can’t keep skip them manually. And, unfortunately there’s no alternative for ‘sql_slave_skip_counter=1000..’ in GTID mode. Also, you can’t use slave-skip-errors=1062 in config file in GTID mode.  So, what’s the solution ?

It’s pretty simple. Add ‘slave-skip-errors=1062’ to the config file and restart MySQL. Wait.. But, in GTID mode will that work?  Oh yes that works, but not yet.
Once MySQL is up, do RESET SLAVE ALL. But before take a note of everything inside master.info file or from the table.

And, now is the key point. Execute CHANGE MASTER command, but without auto position option. Use master_log_file and master_log_pos options. Start Slave. This should work without any errors. Once it’s in sync, you can enable auto_position option.
You can do the same for any other replication errors. But, be cautious. Do only if you’re aware of the reasons for the inconsistency.

Monday, 2 January 2017

Announcement: New Blog !!

Okay, here comes an announcement for the first time!!

I've got a new blog and moving all these stuffs to there. 
So now on, refer the below link:

https://databasically.wordpress.com/

Tuesday, 20 September 2016

MySQL: Architecture..!

Ah..! That's a very basic topic and I'm writing about it now..!! That's because it never lasts in my memory for long time. It always rolls back from my mind , might be because I never felt it's importance - apart from that of interviews !
However, after a brief Googling, here is an overview of MySQL's architecture in my words.

Unlike MS SQL Server or other RDBMS, MySQL doesn't have a standard architecture diagram that we can refer to. Or may be, couldn't find out from the official site of MySQL. 

Anyhow, I was able to get one of the know logical architecture diagram which describes MySQL well and easy to understand is :




That's a 3 layer logical architecture. 

The topmost layer facing the incoming connections/clients includes authentication, security related things.

The second layer is the most important one, which includes Parser, Query Cache and Optimizer.
Parser creates a Parse tree for every query that's been executed by the client. It uses Lex and Yacc compilers (What are they ? That's another big story..,may be in the next to next post..!?!) 
Then the Optimizer tries to optimize this Parse tree by applying different execution plans or by re-writing the query itself. 

This Optimized Parse Tree will be passed on to the third layer which comprise of the Storage engines. Storage engines just gives back the result and don't do any optimization to the query.

Meanwhile, the result sent back will be stored in the query cache to serve the repeating queries.

That's the briefest MySQL architecture, as I understood! Hope that gives an idea of how MySQL works.





Wednesday, 7 September 2016

MySQL: High-Availability - Replication


MySQL being one of the best open-source light weight database, comes with the in-built data replication feature. In fact, this feature made MySQL more and more popular in the data world.

Replication in MySQL do mean – The data flow from one server to the other remote server through network, due to which, both of them will be identical to each other. It’s basically having an exact copy of data at a remote place, while, the data being updated dynamically with respect to the source.
The MySQL Replication includes below terminologies:
  •         Master – The source server from where the data is being replicated. Applications usually pointed to this server for write operations.

  •        Slave – This is the destination for replication. Slave is the actual remote mirror copy of Master server.

o   I/O Thread – This is one of the basic component of replication. This thread is responsible for reading the Master’s Binary log content and write it into the Slave’s Relay log.
The performance of this thread is the key factor of Replication speed and it solely depends on the Network Bandwidth.
o   SQL Thread – This is the other important replication component, which is responsible for reading Slave’s Relay logs and execute  them into the Slave Databases. This is always slower, compared to I/O thread and this decides the Delay in Replication/Replication Lag.


  •        Binary Logs – These binary files logs every committed changes on Master. Every modification on the Master server is being written to these files, which are used by slaves for replication. And, also they are used for recovery purposes.

  •         Relay Logs – These are the  Slave’s version of Binary Logs. They are generated only for Slave servers.



Below is the pictorial representation of MySQL Replication with Master-Slave Hierarchy :

Please note that there can be Multiple Slaves connected to Single Master. However, that doesn’t affect Master’s performance.



There are 3 types of replication MySQL supports:
·        Asynchronous Replication

This is MySQL Default. Any flavor of MySQL should support this type of replication. Asynchronous Replication is contrast to Synchronous Replication.
Here, Master sends the committed transactions to it’s Binary Logs and DO NOT wait for the Slave to read this data.
So, while using this type, we can’t be 100% sure that all the data from Master is replicated to Slave!


  •         Semi-synchronous Replication

MySQL 5.5 or above supports this type of Replication. In this type, the Master sends the committed transactions to the Binary Logs and waits for the acknowledgement from at least one of the Slaves that the replicated data is received.
If no Slaves returns an acknowledgement, the Master switch back to Asynchronous mode after timeout, until at least one of the Slaves synchronized.
Using this type of replication ensures the replication is up-to-date, but, it slows the performance.


  •         Synchronous Replication


This type of replication is not available with the normal version of MySQL. This is a part of MySQL Cluster.
In this type , the Master waits until all the Slaves receive and commit the data.  This is much more costlier w.r.t. performance.

Please note that, MySQL Replication is not complete High Availability solution. Because, if the Master goes down, the Slave should be made as new Master , Manually. 
This leads opts for downtime until the manual process completes!

Friday, 8 January 2016

MySQL: Innodb Log Buffer and REDO Logs


Actually, the definition of sys variable innodb_log_buffer_size was confusing me very much. 
What actually this buffer is meant for? 
Is it a part of Innodb buffer pool?

And after extensive googling(!) I got a clear picture of it. 
Here's what I got:

 No doubt, REDO logs are the unsung heroes. But, how exactly it's generated? 

When any DML request comes in, the storage engine reads out the corresponding page from the disk into the memory(innodb buffer pool). 
So, there are now 2 copies of this page: one is in  disk and the other in memory. The page in the memory, which is not yet flushed to disk is called Dirty Page.

For this dirty page, an redo log will be generated locally, in the mini transaction buffer. Which is very internal thing and not of our concern to worry! 
This log is then transferred to the global redo log buffer. And, this redo log buffer size is decided by the variable innodb_log_buffer_size ! This is a separate memory taken from the host.

So, the redo logs in this buffer is written or flushed to redo log files on disk(iblog files) as soon as the buffer is full or a commit happens. 

If we're performing long transactions, it's recommended to increase the size of this buffer to avoid the unnecessary I/Os.

The redo logs will be flushed to the disk before the corresponding dirty pages are flushed.

For a clear insight on how InnoDB works, go through the below link:
https://blogs.oracle.com/mysqlinnodb/entry/redo_logging_in_innodb

Wednesday, 6 January 2016

MySQL: REDO Logs and UNDO Logs


Most of the DBAs like me, haven't cared about the them..! But, they are the Unsung heroes of MySQL..!!

Yes, UNDO and REDO logs are the 2 different kind of logs; each with it's own purpose and you shouldn't get confused between them ..!!

As the name indicates, REDO logs are for Re-doing things..! 
They hold the transactions which were already executed, but not committed to the disks..! So, the REDO log records every transaction, holds until it gets committed and if needed, it will be used for crash recovery.

By default, there will be 2 REDO logs, named, ib_logfile0 and iblogfile1. 
As of version 5.6 or later, we can have up to 100 REDO logs using the System Variable  innodb_log_files_in_group . 
Since, these logs are overwritten as soon as all the files are full, it is advised to have more files or files with larger size. By default, each log file will be 5 MB and the size can be set using the system variable  innodb_log_file_size. 

The maximum allowed combined size of all the files is 512 GB(innodb_log_file_size * innodb_log_files_in_group <= 512 GB).

The REDO log files location can be set using the variable innodb_log_group_home_dir , if not specified, default directory will be used.


Let's talk about UNDO logs..:

This log stores copy of data that is being modified by any current transaction. So that, at the same time if any other transaction queries for the original data, this log will serve the purpose..!! 
These logs are also called Rollback Segments.

In the earlier versions, these logs were invisible as, UNDO logs were the part of System table space, i.e., ibdata1 and ibdata2. But, as of 5.6 and later, you can create multiple and separate UNDO log files(table space files).

Use variable innodb_undo_tablespaces  to set the number of table space files. Max value is 126 and minimum 0. 
The Undo logs set by the variable innodb_undo_logs will be divided between the set table spaces. Maximum you can create 128 undo logs and it is the default value.

The path of the UNDO log table space can be set by the variable innodb_undo_directory.






Tuesday, 5 January 2016

Linux: Solution for 'Not Able to Access Remote Server'


If you're playing with the Linux servers, one of the bothering issue is that 
you're not able to access other server from the current server

Or, MySQL replication or other service will not work even if you're able to ping from one server to the other or vice versa


Or, you may not be able to access the Tomcat service through your browser


Or, any other such connectivity issue, here is the solution that I used:


Just reset all the Firewall(Iptables) rules and you're done!



First get into SUDO and check the applied rules. It should be looking like below:


[root@myhost ]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     icmp --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:ssh
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:opsession-prxy

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination
REJECT     all  --  anywhere             anywhere            reject-with icmp-host-prohibited

Chain OUTPUT (policy ACCEPT)

target     prot opt source               destination



In my case, the highlighted line is the culprit! It just says to REJECT all incoming connections !

Either you can modify the rules by removing this line, or just like me, remove all the rules !!


Here is how to remove/flush  the firewall rules:




[root@myhost ]# iptables --flush


Yes, that's it!


Wednesday, 4 March 2015

MySQL: Enabling GTIDs

With MySQL 5.6, Oracle has introduced  a new concept of GTIDs. 

So, what's GTID? And, how's it useful for me??

GTID stands for Global Transaction ID. That means, each committed transaction will be logged in with an Unique Identification ID, called, GTID.

GTID = source_id : transaction_id

Where in,
source_id --> Originating server's UUID
transaction_id --> Sequence number in which the transactions are being committed.


The main advantage of GTID is, you don't have to track Master's binlog file and position to resume/setup the replication process. The sync will happen automatically, if both master and slaves are GTID enabled.

And, there's no disadvantage in enabling the GTID, except, you can't follow the traditional way to skip certain transactions and restore ad-hoc data from master. You need to follow different methodology.

Anyhow, here's how to enable GTID:

* Make both Master and Slave as Read-only:

SET @@global.read_only=ON

* Restart/Start both Master and Slave with the below options:
--gtid-mode=ON
--log-bin
--log-slave-updates
--enforce-gtid-consistency 
--skip-slave-start   [Of course, Only for Slave]

Now, to start the replication, execute the below on slave:

CHANGE MASTER TO
MASTER_HOST=*****,
MASTER_PASSWORD=****,
MASTER_PORT=****,
MASTER_AUTO_POSITION=1;

Note that, Binlog info is not required.
Slave reads all the transactions, based on the GTID and skips if it's already been processed.


Tuesday, 25 November 2014

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


Monday, 24 November 2014

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

Friday, 7 November 2014

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

Thursday, 16 October 2014

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 !

Sunday, 5 October 2014

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. 

Friday, 21 February 2014

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.

Thursday, 20 February 2014

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

Wednesday, 19 February 2014

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

Sunday, 16 February 2014

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.