Friday, 28 December 2018

MySQL databases compare

1) MySQL Workbench method: 
Open workbench > File Menu > New Model. Then Database Menu > Compare Schemas. Select the source and destination database connection details (stored already; enter details if not stored). And keep on going to Next step till database comparison result/report is generated at the last step. 

2) Command line method: 
mysqldump --skip-comments --skip-extended-insert -u root -p dbName1>file1.sql 
mysqldump --skip-comments --skip-extended-insert -u root -p dbName2>file2.sql 
diff file1.sql file2.sql

Installing Apache 2.4 service, Tomcat7 server, and associating Apache with Tomcat7

Installing Apache 2.4 service on a linux machine, Installing Tomcat7 server, Associating Apache with Tomcat7 and Deploying a webapp in tomcat and running the webapp on Apache port
NOTE: Check your OS details ($ cat /proc/version) before installing Apache.

1) The following command will install Apache on your server. 
$ sudo yum -y install httpd

In case, a few dependencies are not resolved (like in my case it was libpcre.so.0), so you may get the following logs if some dependencies are not found:
NOTE: I appended 'sudo' to run/install Apache with root user.

$ sudo yum -y install httpd
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile 
* base: repos-va.psychz.net 
* epel: mirror.es.its.nyu.edu 
* extras: repos-va.psychz.net 
* updates: repos-va.psychz.net
Resolving Dependencies
--> Running transaction check
---> Package httpd.x86_64 0:2.4.9-1 will be installed
--> Processing Dependency: /etc/mime.types for package: httpd-2.4.9-1.x86_64
--> Processing Dependency: libpcre.so.0()(64bit) for package: httpd-2.4.9-1.x86_64
--> Processing Dependency: libdb-4.7.so()(64bit) for package: httpd-2.4.9-1.x86_64
--> Processing Dependency: libaprutil-1.so.0()(64bit) for package: httpd-2.4.9-1.x86_64
--> Processing Dependency: libapr-1.so.0()(64bit) for package: httpd-2.4.9-1.x86_64
--> Running transaction check
---> Package apr.x86_64 0:1.5.1-1 will be installed
---> Package apr-util.x86_64 0:1.5.4-1 will be installed
---> Package compat-db47.x86_64 0:4.7.25-28.el7 will be installed
--> Processing Dependency: compat-db-headers = 4.7.25-28.el7 for package: compat-db47-4.7.25-28.el7.x86_64
---> Package httpd.x86_64 0:2.4.9-1 will be installed
--> Processing Dependency: libpcre.so.0()(64bit) for package: httpd-2.4.9-1.x86_64
---> Package mailcap.noarch 0:2.1.41-2.el7 will be installed
--> Running transaction check
---> Package compat-db-headers.noarch 0:4.7.25-28.el7 will be installed
---> Package httpd.x86_64 0:2.4.9-1 will be installed
--> Processing Dependency: libpcre.so.0()(64bit) for package: httpd-2.4.9-1.x86_64
--> Finished Dependency Resolution
Error: Package: httpd-2.4.9-1.x86_64 (local-linux-addons)
Requires: libpcre.so.0()(64bit)
You could try using --skip-broken to work around the problem 
You could try running: rpm -Va --nofiles --nodigest

2) Tried the following but no luck: 
$ sudo yum -y install httpd --skip-broken
$ rpm -Va --nofiles --nodigest 

Appending '--skip-broken' was skipping the main package itself - httpd-2.4.9-1.x86_64, so it didn't help.

3) Now on conducting some research/googling on this dependency (libpcre.so.0), I found 
that I need to install libpcre , so I downloaded this rpm (lib64pcre0-8.21-2-mdv2012.0.x86_64.rpm) and tried to install:
$ sudo yum install lib64pcre0-8.21-2-mdv2012.0.x86_64.rpm
I got this result/logs:
Loaded plugins: fastestmirror
Examining lib64pcre0-8.21-2-mdv2012.0.x86_64.rpm: lib64pcre0-8.21-2.x86_64
Marking lib64pcre0-8.21-2-mdv2012.0.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package lib64pcre0.x86_64 0:8.21-2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
============================================
Package Arch Version Repository Size
============================================
Installing: 
lib64pcre0 x86_64 8.21-2 
/lib64pcre0-8.21-2-mdv2012.0.x86_64 358 k

Transaction Summary
======================================

Install 1 Package

Total size: 358 k
Installed size: 358 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test

Transaction check error:
file /usr/lib64/libpcre.so.0.0.1 conflicts between attempted installs of lib64pcre0-8.21-2.x86_64 and lib64pcre0-8.21-2.x86_64

4) From above logs, I search this location on my linux server which contained the following files: libpcre16.so.0.2.0 
libpcre32.so.0.0.0 
libpcre.so.1.2.0 

The one 'libpcre.so.0' which was expected was missing as you can see in above 3 files. So, this default version of Apache (httpd-2.4.9-1.x86_64), which is tried being installed, seems to be searching for this dependency ('libpcre.so.0') which is not found. Instead a higher version of it is available in system.

5) So I downloaded an older version of httpd rpm () and installed it: 
$ sudo yum install httpd-2.4.6-40.0.1.el7.x86_64.rpm 
But this version demanded two more dependencies: 
httpd-tools and httpd-devel. 
So I downloaded these two dependencies: 
httpd-tools-2.4.6-40.0.1.el7.x86_64.rpm & httpd-devel-2.4.6-80.el7.centos.1.x86_64.rpm 
And installed them: 
$ sudo yum install httpd-tools-2.4.6-40.0.1.el7.x86_64.rpm 
Same way downloaded httpd-devel and installed.
$ sudo wget "http://mirror.centos...
$ sudo yum install httpd-devel-2.4.6-80.el7.centos.1.x86_64.rpm

6) Apache tomcat should be installed now on its default port 80.
If you want to change the default port of Apache, go to this file : /etc/httpd/conf/httpd.conf
And change the port on following line to 81.
Listen 80

7) Now, you need to install tomcat7 on the same linux machine.
So download tomcat7 (apache-tomcat-7.0.82.tar.gz), unzip it and change the port numbers from server.xml and start the tomcat server.
$ sudo wget "https://archive.apache..." 
$ sudo mkdir tomcat7 (NOTE: Please copy and unzip tomcat at your selected directory) 
$ sudo tar xzf apache-tomcat-7.0.82.tar.gz
Go to apache-tomcat-7.0.82/conf/server.xml
$ sudo vim server.xml

Change the ports based on your requirement. If there is no other tomcat already running on this machine, then you can keep the default ports, else change the existing port number to avoid conflict with running tomcat.
I changed the ports (changed tomcat port 8080 to 9080, AJP port 8009 to 9009, and so on).
After above steps, test if tomcat is running fine on the configured port. Go to bin directory and run:
$ sudo ./startup.sh
If you get message like JAVA_HOME or JRE_HOME not set, then set it using:
$ export JAVA_HOME=/opt/java/jdk1.8.0_45 (change this path based on your java installation directory) 
$ sudo ./startup.sh
You can test the default webapp (already provided by tomcat) by following command: 
$sudo curl "http://localhost:9080/examples/index.html" 
Correct output will be the html content (of index.html file) printed/shown in the console/output. So tomcat is installed and running now.

8) Now, you need to associate Apache and tomcat, which can be done by mod_jk tomcat-connector.
Courtesy/References: http://www.serveridol.com/2015/02/03/how-do-i-ins...

Unfortunately there is no binary available for Apache 2.4. So we have to compile mod_jk from the source code. 
Download mod_jk source code from tomcat-connectors-1.2.40-src.tar.gz. 
$ sudo wget "http://mirrors.ocf.berkeley..."
$ sudo tar xzf tomcat-connectors-1.2.46-src.tar.gz
 
Go to native directory 
$ sudo yum install gcc*
$ sudo ./configure --with-apxs=/usr/bin/apxs 

If there is no 'apxs' under your /usr/bin directory, then you need to install it first, then run above command. 
$ sudo make install

This will install mod_jk.so on "/usr/lib64/httpd/modules/mod_jk.so" path. 
NOTE: If this does not work, you may need to download mod_ssl.so from git repository 
($ sudo wget "https://github...")
and place it under /usr/lib64/httpd/modules/. 
You can rename it to mod_jk.so after placing.

You may need to add above entry on apache config file. So I create a file "jk.conf" on /etc/httpd/conf.d/ folder having the following lines:
JkWorkersFile /etc/httpd/conf.d/workers.properties
JkShmFile /var/log/httpd/mod_jk.shm
JkLogFile /var/log/httpd/mod_jk.log
JkLogLevel info
JkLogStampFormat "[%a %b %d %H:%M:%S %Y] "

$ sudo touch /var/log/httpd/mod_jk.shm /var/log/httpd/mod_jk.log 
NOTE: The above location might be inaccessible (you can get access denied), so you can change the path of the Shm and log file like: /etc/httpd/apachelogs/mod_jk.log and /etc/httpd/apachelogs/mod_jk.shm

9) Create a worker file on the same path "/etc/httpd/conf.d" with following entries.
worker.list=worker1
# Set properties for worker1 (ajp13) 
worker.worker1.type=ajp13
worker.worker1.host=localhost
worker.worker1.port=9009

10) Create a javahost.conf file on the same path with following entries:
<VirtualHost *:81>
ServerName localhost
ServerAlias www.localhost.com DocumentRoot webapps
DirectoryIndex index.html
Alias / webapps
JkMount /*.jsp worker1
JkMount /* worker1
</VirtualHost>

11) Configure httpd.conf file (/etc/httpd/conf/httpd.conf).
Change the path of your webapps directory.
Default entry would be:
#
# DocumentRoot: The directory out of which you will serve your
# documents. By default, all requests are taken from this directory, but
# symbolic links and aliases may be used to point to other locations.
#
DocumentRoot "/var/www/html"

Change it to:
DocumentRoot "/opt/tomcat7/apache-tomcat-7.0.82/webapps" (as per your tomcat webapps path)

Second entry would be:
# Further relax access to the default document root:
<Directory "/var/www/html">

# Possible values for the Options directive are "None", "All", 
# or any combination of: 
# Indexes Includes FollowSymLinks SymLinksifOwnerMatch ExecCGI MultiViews 
#

Change it to:
<Directory "/opt/tomcat7/apache-tomcat-7.0.82/webapps">

Also, enter the server name (domain or IP) with Apache port for access:
Find the following entry:
#
# ServerName gives the name and port that the server uses to identify itself.
# This can often be determined automatically, but we recommend you specify
# it explicitly to prevent problems during startup.
#
# If your host doesn't have a registered DNS name, enter its IP address here.
ServerName localhost:80

Change it to:
ServerName MY-DOMAIN-NAME.com:81

12) Start Apache server:
$ sudo service httpd start
If the service does not start, check the logs as stated in the message by running:
$ systemctl status httpd.service
$ sudo journalctl -xe

You may see error like (error at line 353 syntax error).Comment the line 353 having the following line:
includeOptional conf.d/*.conf

Try starting the apache service now. It should start correctly.You can verify using:
$ sudo service httpd status
And you can stop using:
$ sudo service httpd stop

13) Test:
$ sudo curl "http://localhost:81" (only Apache)
$ sudo curl "http://localhost:9080/examples/index.html" (only Tomcat)
$ sudo curl "http://localhost:81/examples/index.html" (Apache with Tomcat)

The output (of 2nd and 3rd command above) should show the html content of index.html file. If it displays correctly, your Apache and tomcat association is done successfully, else there is some issue/mistake in the configuration done.

Assuming the above configuration done on AWS environment, in order to access the URL from your browser, you need to first open the port(s) - 81 (if not already opened) i.e add the port under the cloud configuration panel (may be AWS Management Console/CloudControl/CLI/SDK any medium). 
Once the port is opened for outside access, then only you can check the URL (with correct domain) from the browser.

MySQL Queries

------------------------------------------------------------------------------------------
1. To find the entries/ids not existing in another table:
------------------------------------------------------------------------------------------

SELECT * FROM tableB WHERE ID NOT IN (SELECT ID from tableA);

------------------------------------------------------------------------------------------
2. To find those entries/ids whose count is greater than 5:
------------------------------------------------------------------------------------------

select ID from tableA GROUP BY ID HAVING count(*)>5;

------------------------------------------------------------------------------------------
3. To connect to a mysql db from another machine:
------------------------------------------------------------------------------------------


Pre-requisites: mysql client must be installed on the instance from where mysql command is to be fired:

mysql -u alfresco -h DB_HOST_NAME -p

Above command will prompt for password. Enter the password, and you are good to go.

Git Commands

-------------------------------------------------------
To merge one branch into another:
-------------------------------------------------------

1) Open git bash. Navigate to the branch into which you need to merge other branch (ex: b)
$ git pull
$ git checkout a //here, a means the branch from where you are copying changes into branch b
// You will get message like - Switched to a new branch a
$ git pull
$ git checkout b
$ git merge a
$ git push origin b

--------------------------------------------------------------------------------------------------------------
To revert the changed files you added using git add command:
--------------------------------------------------------------------------------------------------------------
For example, you did: $ git add .
This would have added all changed files.
When you fire $ git status , you will see all files in green.
But if you want to remove/un-add certain files, fire this command:
$ git reset filename/pathname

--------------------------------------------------------------------------------------------------------------
To pull and push latest code:
--------------------------------------------------------------------------------------------------------------
$ git pull
$ git add filename OR git add .
$ git status //to check which files will be committed
$ git commit -m "comments"
$ git push

---------------------------------------------------------------
To rename phase-2 branch to new name 'phase-3'
---------------------------------------------------------------

$ git branch -m phase-2 phase-3


$ git push origin :phase-2 phase-3


-------------------------------------------
To merge phase-2 branch with master branch
-------------------------------------------
Switch to Master branch with following command:

$ git checkout master

Merging/Pushing phase-2 branch code to master branch:

$ git merge feature/phase-2

To push this change:

$ git push

--------------------------------------------------------------------------------------
Create new branch "phase-4" from master branch:
--------------------------------------------------------------------------------------
Go to master branch and run:
$git checkout -b phase-4
// This above command with -b will create a new branch if not existing; without -b it will just switch to an existing branch

$ git push

NOTE: IF you get below error on firing 'git push' command, then fire the third command mentioned below.
fatal: The current branch phase-4 has no upstream branch.
To push the current branch and set the remote as upstream, use

    git push --set-upstream origin phase-4


$ git push --set-upstream origin phase-4