Monitoring percona Error-log using shell script

vi /backups/alertlog_monotor.sh


#!/bin/bash
SERNAME=srv1.xyz.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/data/mysql
egrep 'Error' $Error_log_loc/$SERNAME.err |sort -u >  $Error_log_loc/$SERNAME_ALERTLOG.txt
cat $Error_log_loc/$SERNAME.err >> $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt

cat /dev/null > $Error_log_loc/$SERNAME.err

if [ -s "$Error_log_loc/$SERNAME_ALERTLOG.txt" ] ; then
cat $Error_log_loc/$SERNAME_ALERTLOG.txt | mail -s "URGENT -ERROR in Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@24x7-itsupport.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
        gzip $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Oracle Alert log monitoring using shell script

Vi /backups/alertlog_monotor.sh

#!/bin/bash
export ORACLE_SID=kmioradb
export ORAENV_ASK=NO
. /home/oracle/.bash_profile
SERNAME=oradb.kminfosystems.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Alert_log_loc=/u01/app/oracle/diag/rdbms/kmioradb/kmioradb/trace/
# Check for the existence of ORA- in the alert log and email/page on error
egrep 'ORA-|error|TNS' $Alert_log_loc/alert_$ORACLE_SID.log |sort -u >  $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt  #### Output file with ORA- errors
cat $Alert_log_loc/alert_$ORACLE_SID.log >> $Alert_log_loc/archived_alert_$ORACLE_SID.log
cat /dev/null >  $Alert_log_loc/alert_$ORACLE_SID.log

if [ -s "$Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt" ] ; then
cat $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " soumya@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Alert_log_loc/archived_alert_${ORACLE_SID}.log $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
        gzip $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)


Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

How to migrate mysql users from one server to another?

Step 1: Get a List of MySQL Users from the source server

[root@db01 tmp]# mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > /tmp/mysql_all_users.txt
Enter password: redhat
[root@db01 tmp]# cat /tmp/mysql_all_users.txt
'CitLivehappy'@'%'
'brightonlands'@'%'
'brochure'@'%'
'carver'@'%'
'CitLivehappy'@'localhost'
'brightonlands'@'localhost'
'brochure'@'localhost'
'carver'@'localhost'


Step 2:- Obtain a List of User Privileges

Fire the next command in the terminal to get the privileges:
[root@db01 tmp]# cd /tmp/
[root@db01 tmp]# while read line; do mysql -B -N -uroot -predhat -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

Basically, the above command reads every individual line in mysql_all_users.txt and tries to get privileges for each user and paste it on mysql_all_users_sql.sql file.
This file will contain the SQL queries to obtain users along with their privileges.

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA67561F7C46D591CC6F15FD7BF1E'
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%'
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%'
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD7FED678FA'
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%'
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD230662388030CA895BEB'
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%'
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1ADBE3'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%'

We can see from mysql_all_users_sql.sql that all the users information along with the privileges and the script is almost ready.
Only thing the above script missing is a ";" at the end of every sql command.

We will add ";" at the end of each sql command
[root@db01 tmp]# sed -i 's/$/;/' mysql_all_users_sql.sql

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA6756C9BE6591CC6F15FD7BF1E';
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%';
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%';
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD6E99678FA';
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%';
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD23CA750C895BEB';
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%';
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1BBE3';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%';

Now add "flush privileges" to reload the grant tables.
[root@server1 tmp]# echo "flush privileges;" >> mysql_all_users_sql.sql

Step 3: Now lets import the sql file into new server.

Transfer this sql file into destination(new) server and fire up the below command.

[root@db02 tmp]# mysql -u root -ppassword < mysql_all_users_sql.sql

How to Install MongoDB on Ubuntu 14.04 ?

How to Install MongoDB on Ubuntu 14.04?

Step #1: Setup a the Package Database
First we’ll import the MongoDB public key used by the package management system:

# sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 7F0CEB10

Then we’ll create a list file for MongoDB:

# echo 'deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen' | sudo tee /etc/apt/sources.list.d/mongodb.list

Now reload the package database:

# sudo apt-get update

Step #2: Install Latest Stable Version MongoDB
At this point, installing MongoDB is as simple as running just one command:

sudo apt-get install -y mongodb-org

If you’d like MongoDB to auto-update with apt-get than you’re done with the installation. But, it’s possible to ‘pin’ the version of MongoDB you just installed to prevent apt-get from auto-updating.

Step #3: Get MongoDB Running

Start-Up MongoDB:-
# sudo service mongod start

Check MongoDB Service Status
#sudo service mongod status

Summary List of Status Statistics (Continuous)

#mongostat

Summary List of Status Statistics (5 Rows, Summarized Every 2 Seconds)

#mongostat --rowcount 5 2

Enter the MongoDB Command Line

#mongo

Step 4:-
Now we can configure and basic setting in Mongodb Database Server
# vi /etc/mongod.conf
logappend=true
logpath=logpath=/var/log/mongodb/mongod.log
port=27017
dbpath=/var/lib/mongo
smallfiles = true
bind_ip = 127.0.0.1,159.203.180.48 [To change and open a certain ip's access for mongodb ]
:wq

Register a Connected Server using 2012 SQL Server Management Studio

Register a Connected Server using 2012 SQL Server Management Studio :-

By registering the server, you can save the connection information for servers that we access frequently. A server can be registered before connecting, or at the time of connection
from Object Explorer.


To register a connected server:-
Open up sql server management studio 2012.
In Object Explorer, right-click a server to which you already are connected, and then click Register.

* Server name
Enter the name you want to use for the registered server. Registering a local or remote server using SQL Server Management Studio lets you store the server connection information
for future connections. This field defaults to the server name entered when you were connecting to the server. You can retain this server name or enter another easy-to-use
name for the server.

* Authentication
There are two type of authentication available .
a.Windows authentication
b.Sql server authentication

Choose anyone of above for the authentication method.

* Server description
Enter an optional description of the server. The maximum number of characters allowed is 250.

* Save
Click to save the information you have entered and create a registered server.

Different SQL Server Roles

Different SQL Server Roles:-

Server Roles:-

The Server Roles page lists all possible roles that can be assigned to the new login. The following options are available:
bulkadmin:-
Members of the bulkadmin fixed server role can run the BULK INSERT statement.

dbcreator:-
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

diskadmin:-
Members of the diskadmin fixed server role can manage disk files.

processadmin:-
Members of the processadmin fixed server role can terminate processes running in an instance of the Database Engine.

public:-
All SQL Server users, groups, and roles belong to the public fixed server role by default.

securityadmin:-
Members of the securityadmin fixed server role manage logins and their properties. They can GRANT, DENY, and REVOKE server-level permissions. They can also GRANT, DENY, and REVOKE
database-level permissions. Additionally, they can reset passwords for SQL Server logins.

serveradmin:-
Members of the serveradmin fixed server role can change server-wide configuration options and shut down the server.

setupadmin :-
Members of the setupadmin fixed server role can add and remove linked servers, and they can execute some system stored procedures.

sysadmin :-
Members of the sysadmin fixed server role can perform any activity in the Database Engine.



Database-Level Roles:-

db_owner :- Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database,
and can also drop the database.

db_securityadmin:- Members of the db_securityadmin fixed database role can modify role membership and manage permissions. Adding principals
to this role could enable unintended privilege escalation.

db_accessadmin :- Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups,
and SQL Server logins.

db_backupoperator :- Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin :- Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter:- Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader :- Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter :- Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user
tables within a database.

db_denydatareader :- Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.

How to setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu

Setup MariaDB (Master-Slave) Replication on CentOS/RHEL 6x & Ubuntu
===================================================================

Our Testing Environment Consists of the Following Machines (Both are CentOS 6) :

Master: 192.168.72.220 ser1.kmi.com
Slave: 192.168.72.221 ser2.kmi.com

Step: 1. Bind Hosts File (Both Server) :

# vi /etc/hosts

192.168.72.220 ser1.kmi.com ser1
192.168.72.221 ser2.kmi.com ser2

-- Save & Quit (:wq)

Step: 2. Stop Iptables & Disabled Selinux (Both Server) :

For CentOS:

# service iptables stop
# chkconfig iptables off

For Ubuntu:

# service ufw stop

# vi /etc/sysconfig/selinux

SELINUX=disabled

-- Save & Quit (:wq)

Step: 3. Restart the Both Server :

# init 6

Step: 4. Install NTP :

# yum -y install ntp
# service ntpd restart
# chkconfig ntpd on
# ntpdate pool.ntp.org

Step: 5. Create MariaDB.repo on Both Server :

For CentOS:

# rpm --import http://yum.mariadb.org/RPM-GPG-KEY-MariaDB

# vi /etc/yum.repos.d/MariaDB.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

-- Save & Quit (:wq)

For Ubuntu :

# apt-get install software-properties-common
# apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
# add-apt-repository 'deb [arch=amd64,i386] http://mirrors.neusoft.edu.cn/mariadb/repo/10.1/ubuntu trusty main'

Step: 6. Install MariaDB on Both Servers :

For CentOS:

# yum -y install MariaDB-server MariaDB-client

For Ubuntu:

# apt-get update
# apt-get install mariadb-server

Step: 7. Start MariaDB Service & Set MariaDB Root Password on Both Master & Slave Server :

# service mysqld start
# chkconfig mysqld on

# mysql_secure_installation

Step: 8. Check MariaDB Version on Both Node :

# mysql -V
mysql  Ver 15.1 Distrib 10.1.10-MariaDB, for Linux (x86_64) using readline 5.1

Step: 9. Configuring MariaDB Server on Master Node :

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
server_id=1
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 10. Restart the MariaDB Service :

# service mysqld restart

Step: 11. Log on to MariaDB Master Node & Create a User Called "Slave" & Give Appropriate Permission :

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> GRANT REPLICATION SLAVE ON *.* TO slave IDENTIFIED BY 'SlavePassword' WITH GRANT OPTION;
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> FLUSH TABLES WITH READ LOCK;
MySQL [(none)]> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      181 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit

Step: 12. Now Take Backup of Full Database :

# mysqldump -u root -proot_password employees > /tmp/employees.sql

Step: 13. After Taken Dump, Log on to MariaDB & Unlock Tables :

# mysql -u root -proot_password

MySQL [(none)]> UNLOCK TABLES;
MySQL [(none)]> exit;

Step: 14. Copy the Dump File to the Slave Node :

# scp -r /tmp/employees.sql root@192.168.72.221:/tmp/

Step: 15. Restore the Dump File on Slave Node :

# mysql -u root -predhat

MySQL [(none)]> create database employees;
MySQL [(none)]> exit;

# mysql -u root -proot_password employees < /tmp/employees.sql

Step: 16. Configuring MariaDB on Slave Node :

Note: It needs to be a Different Integer than 1, as we used 1 in the Master:

# mv /etc/my.cnf /etc/my.bak
# vi /etc/my.cnf

[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

[mysqld]
server_id=2
replicate-do-db=employees
bind-address=0.0.0.0
log-bin=mysql-bin
binlog_format=mixed
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

-- Save & Quit (:wq)

Step: 17. Restart the MariaDB Server :

# service mysqld restart

Step: 18. Setting Up MariaDB Replication on Slave Node :

Note: Special attention to the "MASTER_LOG_FILE" & "MASTER_LOG_POS" Variables, which should match the Values returned by "SHOW MASTER STATUS" in STEP 10 of above.

# mysql -u root -proot_password

MySQL [(none)]> STOP SLAVE;
MySQL [(none)]> CHANGE MASTER TO
  MASTER_HOST='192.168.72.220',
  MASTER_USER='slave',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=181,
  MASTER_CONNECT_RETRY=10;
 
Step: 19. Start the Slave & Check its Status :

MySQL [(none)]> START SLAVE;
MySQL [(none)]> SHOW SLAVE STATUS\G;

Note: If the SHOW SLAVE STATUS\G; command returns any Errors. Use those errors to troubleshoot & then run START SLAVE; to test again.

Step: 20. Test MariaDB Database Replication :

Add some Record to the "employees" Database in the Master Node :

MySQL [(none)]> use employees;
MySQL [(none)]> CREATE TABLE Guests1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
);

Verify that this Change was Replicated in the Slave Node :

MySQL [(none)]> USE employees;
MySQL [(none)]> show tables;

As you can see, Replication is Working Correctly from Master to Slave.

Done...!!!