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

 

 

 

 

Disk usage of mysql DB

To calculate the disk usage of your DBs in mysql

 

you can use “DU” which is less reliable and if you hosting on cloud database services, you cannot use this.

du -sh /var/lib/mysql/database

OR

use the below query.

 

select table_schema, sum((data_length+index_length)/1024/1024) AS MB from information_schema.tables group by 1;

Port forwarding using ssh

 

How to use port forwarding when you are behind a firewall.

to access a service running locally on the remote server.

To setup a proxy

ssh -L 3128:localhost:80 <username>@YOURREMOTESERVER

REMOTE Port forwarding

Below example could be used to access RDP behind a firewall, but accessible from YOURREMOTESERVER

ssh -L 3389:YOURREMOTE_SERVER_WHERESERIVERUN_IP:3389 <username>@YOURREMOTESERVER -N

3389 – RDP port

N – will not start a terminal session.

 

 

Use localhost as the hostname/IP to establish connection from your desktop

compressing a pdf to a lower size

Use Ghost Script from commandline

gs -sDEVICE=pdfwrite -dNOPAUSE -dQUIET -dBATCH -dPDFSETTINGS=/screen -dCompatibilityLevel=1.6 -sOutputFile=pdfoutsmallfile.pdf pdfinputlargefile.pdf

-dNOPAUSE – disable the interactive prompting
pdfwrite – create pdf files
-dCompatibilityLevel=1.6 – compatibility with each versions of adobe. Use 1.3,1.4,1.6,1.7
-sOutputFile – name of the output file.

Port check script

#!/bin/bash
RED=’\033[0;31m’
GREEN=”\033[0;32m”
NC=’\033[0m’ # No Color

function checkport {
if nc -zv -w30 $1 $2 <<< ” &> /dev/null
then
echo “${GREEN}[O]${NC} $1:$2”
else
echo “${RED}[C]${NC} $1:$2”
fi
}

printf “server list:”;
read server
printf “ports list[enter for default]:”
read ports_to_check

if [[ $ports_to_check = “” ]]; then
echo “using default port list”;
ports_to_check=”22 389 3306 8080″; #enter your default list of ports here
echo “$ports_to_check”;
fi

for i in $server;do
{
echo “==========================================”
for j in $ports_to_check ; do
checkport $i $j
done
}; done

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