Apache Kafka – Topic to Database (MySQL – Table replication from Employees to Employees_replica via kafka topic)

https://github.com/chriszones2000/Kafka-Java/blob/master/TopicToDB.java

 

Apache Kafka & MySQL – Database to Topic

https://github.com/chriszones2000/Kafka-Java/blob/master/DBToTopic.java

MySQL – Enterprise – Installation – Linux

Phase #1 –  PreRequisites

MAKE SURE A MOUNT POINT /MySql IS CREATED BEFORE RUNNING THIS SCRIPT…………………………

Creating the symbolic soft link for parallel database updations

ln -s /data /MySql/mysqldb
ln -s /data /MySql/mysql_db

Soft Links Created.
User and Group Adding.

groupadd -g27 mysql
echo ‘System Group mysql created with GID 27.’
useradd -m -d /var/lib/mysql -g mysql -G mysql -p root123 -u 27 mysql
echo ‘System User mysql created with UID 27 home dir=/var/lib/mysql.’
echo ‘root’ >>cron.allow
echo ‘mysql’ >>cron.allow
service crond restart
echo ‘added the user mysql to the cron’

DIRECTORY STRUCTURE CREATION

mkdir -p /MySql/mysqldb/configfiles
mkdir -p /MySql/mysqldb/datadump
mkdir -p /MySql/mysqldb/software_depot
mkdir -p /MySql/mysqldb/dbbackup
mkdir -p /MySql/mysqldb/archival
echo ‘DIRECTORY STRUCTURE COMPLETE’

CONTAINER CREATION

mkdir -p /MySql/mysql_db/mysql/2345/var/lib/mysql
mkdir -p /MySql/mysql_db/mysql/2345/tmp
mkdir -p /MySql/mysql_db/mysql/2345/var/log/binlogs
echo ‘CONTAINER STRUCTURE COMPLETE.’

SOFTWARE DEPOT PRE-REQUISITES

mkdir -p /MySql/mysqldb/software_depot/meb
cp /tmp/meb/bin /MySql/mysqldb/software_depot/meb/bin
mkdir -p /opt/product/meb
ln -s /MySql/mysqldb/software_depot/meb/bin /opt/product/meb
sh mysqlbackup –help
echo ‘SUCCESSFULL LINKED MEB’
chown -R mysql:mysql /opt/ /MySql/mysqldb/ /MySql/mysql_db/
echo ‘PRE-REQUISITES COMPLETED SUCCESSFULLY NOW KINDLY INSTALL MYSQL-SERVER RPM AND MYSQL-CLIENT RPM’

Phase #2 – Installation

Install

 

Phase #3 – Configuration – my.cnf

RUN ONLY AS MYSQL USER.

cd /MySql/mysqldb/configfiles

 

echo [mysqld]

#This Option tells the server to load the plugin and prevent it from being removed while the server is running.
audit-log=FORCE_PLUS_PERMANENT

#Audit Log File Location in the Container.
audit_log_file=/MySql/mysql_db/mysql/2345/var/log/audit_2345.log

#Audit Log Policy Parameter
audit_log_policy=LOGINS

#Rotate/Refresh the Log File after it reaches the size 1GB
audit_log_rotate_on_size=1073741824

#The number of TCP/IP connections that are queued at once. If you have many remote users connecting to your database simultaneously, you may need to increase this value. The trade-off for a high value is slightly increased memory and CPU usage.
back_log=128

#The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has the binary log enabled (–log-bin option). If you often use large, multiple-statement transactions, you can increase this cache size to get better performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.
binlog_cache_size=1M

#Use charset_name as the default server character set.
character-set-server=utf8

#Use collation_name as the default server collation.
collation-server=utf8_general_ci

#The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake.
connect_timeout=10

#***********MYSQL DATA DIRECTORY ****************
datadir=/MySql/mysql_db/mysql/2345/var/lib/mysql

#************DEAFULT STORAGE ENGINE ***************
default-storage-engine=innodb
ft_min_word_len=2
general_log=0

#General Log File Path.
general_log_file=/MySql/mysql_db/mysql/2345/var/log/general_2345.log

group_concat_max_len=500000
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_instances=5
innodb_buffer_pool_size=8G
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=32M
innodb_log_file_size=500M
innodb_thread_concurrency=64
interactive_timeout=900

#Binary Logs Index File Path.
log-bin-index=/MySql/mysql_db/mysql/2345/var/log/binlogs/logbin_2345.index
log_bin_trust_function_creators=1

#Binary Log File Path.
log-bin=/MySql/mysql_db/mysql/2345/var/log/binlogs/bin_2345.log

#Error Log File Path.
log-error=/MySql/mysql_db/mysql/2345/var/log/mysqld_2345.log
log-queries-not-using-index
log-slow-slave-statements
log_warnings
long_query_time=0.05
max_allowed_packet=1G
max_binlog_size=1073741824
max_connect_errors=4294967295

#The number of simultaneous connections allowed by the database server. If some users are being denied access during busy times, you may need to increase this value. The trade-off is a more heavily loaded server. In other words, CPU usage, memory usage, and disk I/O will increase.
max-connections=4096
max_heap_table_size=64M
net_read_timeout=120
net_write_timeout=3600
old_password=0
open_files_limit=4096

#Process ID File Path.
pid-file=/MySql/mysql_db/mysql/2345/var/lib/mysql/mysql_2345.pid

#Port Number Used By MySql.
port=2345

query-cache-limit=1M
query_cache_size=64M
read_buffer_size=1M
read_rnd_buffer_size=8M

#Relay Log Index File Path
relay-log-index=/MySql/mysql_db/mysql/2345/var/log/binlogs/relaylog_2345.index

#Relay Log Information File Path.
relay-log-info-file=/MySql/mysql_db/mysql/2345/var/log/binlogs/relaylog_2345.info

#Relay Log File Path
relay-log=/MySql/mysql_db/mysql/2345/var/log/binlogs/relay_2345.log
server-id=222345
skip-character-set-client-handshake
skip-name-resolve
skip-slave-start
slave_net_timeout=60
slow_query_log=1

#Slow Query Log File Path.
slow_query_log_file=/MySql/mysql_db/mysql/2345/var/log/slowqueries_2345.log

#MySQL Socket Path
socket=/MySql/mysql_db/mysql/2345/var/lib/mysql_2345.sock
table-definition-cache=2048
table_open_cache=4096
thread_cache_size=16

#MySql Temp Directory.
tmpdir=/MySql/mysql_db/mysql/2345/tmp
tmp_table_size=64M
>>my-23456.cnf

Phase #4 – Start/Stop Service and Login

Start-Stop.sh

#!/bin/bash

set -x

echo “Do You want to Start the MySql Daemon ??? [Select ‘start’ or ‘stop’ followed by an ENTER]:- ”
read bool

if [ $bool -eq “start”];
then
/usr/bin/mysqld_safe –defaults-file=/MySql/mysqldb/configfiles/my-2345.cnf &
echo ‘CHECKING FOR ERRORS’
cat=”$(which cat)”
path=”/MySql/mysql_db/mysql/2345/var/log/mysqld_2345.log”
err=”$cat $path|$(which grep) ERROR|$(which wc) -l”
if [$err -eq 0];
then
echo ‘NO ERRORS YIPPIE’
rm -rf /MySql/mysql_db/mysql/2345/var/log/mysqld_2345.log
elif [$err -gt 0];
then
echo ‘CHECK FOR THESE ERRORS’
$cat /MySql/mysql_db/mysql/2345/var/log/mysqld_2345.log|grep ERROR >>/MySql/mysql_db/mysql/2345/var/log/mysqld_err_2345.log
$cat /MySql/mysql_db/mysql/2345/var/log/mysqld_err_2345.log
rm -rf /MySql/mysql_db/mysql/2345/var/log/mysqld_2345.log
echo ‘RE-RUN the SCRIPT NOW IF YOU HAVE ERRORS.’

else;
echo ‘EXCEPTION ERROR !!!!!!!!!!!!!!!!!! ‘
fi
echo $?

elif [ $bool -eq “stop”];
then
count=”ps -eaf |grep mysqld|grep 2345|wc -l”
if [ $count -gt 0];
then
echo “Please Enter the MySql User. [Give the entry followed by ENTER]:- ”
read user
/usr/bin/mysqladmin –socket=/MySql/mysql_db/mysql/2345/var/lib/mysql/mysql_2345.sock –port=2345 -u$user -p shutdown
else;
echo “MYSQL PROCESS NOT RUNNING”
fi

else;
echo “INVALID INPUT PLEASE TRY AGAIN”
fi

Login.sh

#!/bin/bash

##  PASSWORD CHANGE SECTION ##
echo “Do you Want to Change the password for the user ??? [Type Y or N followed by an ENTER]:- ”
read bool

if [ $bool -eq “Y”];
then
echo “Enter the User to Change the password [Type the username followed by an ENTER]:- ”
read user
echo “Enter the password for $user [Type the Password followed by an ENTER]:- ”
read password
/usr/bin/mysqladmin –socket=/MySql/mysql_db/mysql/2345/var/lib/mysql_2345.sock –port=2345 -u $user password $password
elif [ $bool -eq “N”];
then
echo “PASSWORD WILL NOT BE CHANGED”

else;
echo “Please Provide a Valid Input”
fi

## LOGIN SECTION ##
echo “Do You Want to Login to MySQL ????”
read bool1
echo “Please Enter the User:- [Type the username followed by an ENTER]:- ”
read user
echo “Please enter the password for $user [Type the Password followed by an ENTER]:- ”
read password
if [ $bool1 -eq “Y”];
then
/usr/bin/mysql -A -v –socket=/MySql/mysql_db/mysql/2345/var/lib/mysql_2345.sock –port=2345 -u$user -p$password
elif [ $bool1 -eq “N”];
then
echo “OHK FINE WILL NOT LOGIN”

else;
echo “Please Provide a Valid Input”
fi

############################################

Advertisement