Tikejhya: Ashish Nepal

Knowledgebase

Category: Linux (Page 1 of 29)

Bash format option [disable autocommenting]

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

#Bash format option [disable autocommenting]
:set formatoptions-=cro

percona sync simplified to run over huge database

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

#!/bin/bash
if [ $# -lt 3 ] ; then
echo "USAGE $0 slavename db_name type"
echo "slavename [e.g db2, db3, db4 ]"
echo "db_name [e.g db1 db2 etc... calm down one at a time]"
echo "type [xxl_table_array xl_table_array unwanted_table all_small]"
exit
fi

LOCKFILE="/tmp/pt_sync.lock"
if [ -e "${LOCKFILE}" ]; then
echo "Already running."
exit 99

else

echo $! > "${LOCKFILE}"
chmod 644 "${LOCKFILE}"
slavehost=$1
backup_dir="./$1-table-sync-`date +%Y-%m-%d-%H-%M`"
mkdir -p $backup_dir
table_size=$3
db=$2

get_list_of_tables()

{
case "$table_size" in
xxl_tables)
tbls="xxl_table_name"
;;
xl_tables)
tbls="xl_table_1 xl_table_2"
;;
large_tables)
tbls="large_table_1 large_table_2"
;;
unwanted_table)
tbls="temp_table1 temp_table2"
;;
all_small)
allsmalltable_sync
;;
*)
echo "chow chow; did you miss anything?"
;;
esac
}

execute_query()
{
for tb in $tbls; do
pt-table-sync h=db1,D=$db,t=$tb h=$slavehost --user=username --password=password --no-check-slave --print >> $backup_dir/$db-$tb.log

done
}
allsmalltable_sync()
{
tbls="$(mysql -Bse "use $db; show tables;" | grep -v 'all_but_shit_tables\|all_but_shit_table_2')"
for tb in $tbls; do
pt-table-sync h=db1,D=$db,t=$tb h=$slavehost --user=username --password=password --no-check-slave --print >> $backup_dir/$db-$tb.log

done
}

get_list_of_tables $2
execute_query
rm -f "${LOCKFILE}"

fi

AWS Automated Snapshot

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

#!/bin/bash

# Volumelist => volume_id:someidentifiername:retaintion_period
volume_list='

vol-01010101:ashishnepal.com:3
vol-01010102:tikejhya.com:3
vol-01010103:tikejhyaroot.com:3
vol-01010104:somerandombox.com:3

'

snapshot_volumes() {
for volume in $volume_list; do

volume_id=$(echo "$volume" | cut -d':' -f1)
host_name=$(echo "$volume" | cut -d':' -f2)
retention_days=$(echo "$volume" | cut -d':' -f3)
retention_date_in_seconds=$(date +%s --date "$retention_days days ago")
snapshot_description=$(date +%Y-%m-%d)

snapshot_id=$(aws ec2 create-snapshot --output=text --description $host_name-$snapshot_description --volume-id $volume_id --query SnapshotId)
aws ec2 create-tags --resource $snapshot_id --tags Key=CreatedBy,Value=AutomatedBackup

done
}

cleanup_snapshots() {
for volume in $volume_list; do

volume_id=$(echo "$volume" | cut -d':' -f1)
host_name=$(echo "$volume" | cut -d':' -f2)
retention_days=$(echo "$volume" | cut -d':' -f3)
retention_date_in_seconds=$(date +%s --date "$retention_days days ago")

snapshot_list=$(aws ec2 describe-snapshots --output=text --filters "Name=volume-id,Values=$volume_id" "Name=tag:CreatedBy,Values=AutomatedBackup" --query Snapshots[].SnapshotId)
for snapshot in $snapshot_list; do

snapshot_date=$(aws ec2 describe-snapshots --output=text --snapshot-ids $snapshot --query Snapshots[].StartTime | awk -F "T" '{printf "%s\n", $1}')
snapshot_date_in_seconds=$(date "--date=$snapshot_date" +%s)
snapshot_description=$(aws ec2 describe-snapshots --snapshot-id $snapshot --query Snapshots[].Description)

if (( $snapshot_date_in_seconds <= $retention_date_in_seconds )); then aws ec2 delete-snapshot --snapshot-id $snapshot echo "aws ec2 delete-snapshot --snapshot-id $snapshot" else echo "Nothing to delete $snapshot" fi done done } cleanup_snapshots snapshot_volumes

diff between two files

diff -u file1 file2 | grep -E “^\+”

Why is innodb inserts slower than MyISAM

Why is innodb inserts slower than MyISAM
Innodb VS MyISAM inserts and bulk loads

mysql> use testdb;

Database changed

mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000012 |
| checking permissions | 0.000002 |
| Opening tables | 0.000004 |
| System lock | 0.000002 |
| Table lock | 0.000001 |
| init | 0.000004 |
| update | 0.000028 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000011 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.03 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000019 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000008 |
| update | 0.000031 |
| end | 0.000001 |
| query end | 0.000002 |
| freeing items | 0.026714 |
| logging slow query | 0.000003 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

# Lets disable ACID compatibility feature on Innodb OFF
mysql> set global innodb_flush_log_at_trx_commit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> set profiling=1; INSERT INTO `myisam` (`myisam`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000003 |
| Opening tables | 0.000023 |
| System lock | 0.000003 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000027 |
| end | 0.000001 |
| query end | 0.000001 |
| freeing items | 0.000013 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

mysql> set profiling=1; INSERT INTO `innodb` (`innodb`) VALUES (2); show profile;
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+———————-+———-+
| Status | Duration |
+———————-+———-+
| starting | 0.000019 |
| checking permissions | 0.000004 |
| Opening tables | 0.000008 |
| System lock | 0.000002 |
| Table lock | 0.000002 |
| init | 0.000009 |
| update | 0.000032 |
| end | 0.000002 |
| query end | 0.000001 |
| freeing items | 0.000017 |
| logging slow query | 0.000001 |
| cleaning up | 0.000001 |
+———————-+———-+
12 rows in set (0.00 sec)

# Lets turn that back again to leave system as it is:
mysql> set global innodb_flush_log_at_trx_commit = 1;
Query OK, 0 rows affected (0.00 sec)

So, We could make certain insert’s, updates or inload, bulk load faster in innodb upto x4 times doing this little tweak.

In certain condition it would be even better doing this as permanent solution while compared to MyISAM since this innodb would give other good features
such as row based lock, RDBMS features such as fk’s.

sftp download using username password

curl -o test.csv -u ‘username’:’password’ sftp://sftpdownloadlocation.com/test.csv

And load file into mysql
mysql -utest -ppass -hlocalhost –database dbname -ss -e ‘LOAD DATA LOCAL INFILE “test.csv” INTO TABLE some_table FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY “””” LINES TERMINATED BY “\n”;’

Bash: occurence count

This is simple bash onliner to count number of same digit match:

I needed to count 1,10,100,1000 occurrence in a file

for num in {1..10}; do echo “$num Digit”; cat /tmp/tt-28 | grep -E -w [0-9]{$num}

cat /tmp/tt-28
1
2
223
4
5
5

so in the given example you find, output as 5 1 Digit and 1 3 digit rest will be shown as 0.

[root@76 ~]# for num in {1..10}; do echo “$num Digit”; cat /tmp/tt-28 | grep -E -w [0-9]{$num} | wc -l ; done
1 Digit
5
2 Digit
0
3 Digit
1

mysql ssl replication certificate

How to generate certificates for mysql replication with ssl.

Lets assume, database server are: db1 [master], db2 [slave] / master of db3, db3[slave]

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db1-key.pem -out db1-req.pem
openssl rsa -in db1-key.pem -out db1-key.pem
openssl x509 -req -in db1-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db1-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db2-key.pem -out db2-req.pem
openssl rsa -in db2-key.pem -out db2-key.pem
openssl x509 -req -in db2-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db2-cert.pem

Test
openssl verify -CAfile ca.pem db1-cert.pem db2-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout db3-key.pem -out db3-req.pem
openssl rsa -in db3-key.pem -out db3-key.pem
openssl x509 -req -in db3-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out db3-cert.pem

Test
openssl verify -CAfile ca.pem db1-cert.pem db2-cert.pem db3-cert.pem

Full setup process can be found:

MySql Replication with SSL

Check SSL details at the command line

# Check ssl details

#Remote cert
openssl s_client -connect ashishnepal.com:pop3s

#Local Cert
c="/path/to/cert"
openssl x509 -noout -dates -subject -issuer -in $c
openssl x509 -noout -dates -subject -issuer -in filename.crt

openssl s_client -host google.com -port 443 | openssl x509 -noout -dates -subject -issuer

Kill zombie process

What’s a Zombie Process?
To understand what a zombie process is and what causes zombie processes to appear, you’ll need to understand a bit about how processes work on Linux.

When a process dies on Linux, it isn’t all removed from memory immediately — its process descriptor stays in memory (the process descriptor only takes a tiny amount of memory). The process’s status becomes EXIT_ZOMBIE and the process’s parent is notified that its child process has died with the SIGCHLD signal. The parent process is then supposed to execute the wait() system call to read the dead process’s exit status and other information. This allows the parent process to get information from the dead process. After wait() is called, the zombie process is completely removed from memory.

Find pid for master process
ps -A -ostat,ppid | awk '/[zZ]/{print $2}'

Find and kill pid of master process
kill -HUP $(ps -A -ostat,ppid | awk '/[zZ]/{print $2}')

Page 1 of 29

Powered by WordPress & Theme by Anders Norén