MySQL master slave repliaction

Posted on by AHMED

installing MySQL Enterprise db1 & db2

we already downloaded the MySQL enterprise iso and added it on vm

we have added three spread disk on the vm that we use to create separate directory for the following

  • MySQL data dir
  • MySQL bin-log
  • MySQL backup
    also make sure to disable selinuix
    d2bbed2f48887b89ce411651717d25b9.png
    once we have setup the separate mount point for the three directory we will now proceed with the installation

the installation file itself will be compressed , we can use tar -xf to extract the file
cbccf3b243dd27f307892e856aa5497b.png
we will start by installing mysql-commercial-backup-8.4.0-1.javascript.1.el8.x86_64.rpmusing yum localinstall

yum localinstall mysql-commercial-backup-8.4.0-1.javascript.1.el8.x86_64.rpm

now snice we found everything is working fine and we are able to install rpm packages on system, lets continue installing the reset of the rpm package

note: you need to install the rpm in the order as showing in below command because some rpm packages relay on the other for dependency

yum localinstall mysql-commercial-client-8.4.0-1.javascript.1.el8.x86_64.rpm mysql-commercial-client-plugins-8.4.0-1.javascript.1.el8.x86_64.rpm mysql-
commercial-common-8.4.0-1.javascript.1.el8.x86_64.rpm mysql-commercial-icu-data-files-8.4.0-1.javascript.1.el8.x86_64.rpm mysql-commercial-libs-8.4.0-1.javascript.1.el8.x86_64.rpm mysql
-commercial-server-8.4.0-1.javascript.1.el8.x86_64.rpm  mysql-commercial-test-8.4.0-1.javascript.1.el8.x86_64.rpm

do the same on db2
106a6030ba143ba91528f13833475c34.png

editing my.cnf file db01

before we start the services for MySQL we need to update config for MySQL to point binlog and MySQL data to new mount point we created

use any prefeed note editing tool i will be using vi

vi /etc/my.cnf

update datadir and and add log-bin and update it with binlog diretcory
9d32060a65115ce11cc1dfa7e197668e.png

updating the owner of mysql directory’s db1&db2

you need to update owner of the directory we have setup for MySQL with MySQL user
chown -R mysql:mysql mysqldata/ chown -R mysql:mysql mysqlbinlog/ chown -R mysql:mysql mysqlbackup/

b2fa081f904f2534650f484a5fd5bdb7.png

update host file db01& db02

using any note editing tool and update the host file we hostname and ip of both DB

vi /etc/hosts

503f323d336b8d90d2287136c150e161.png

configure master -slave DB01

before we start the up MySQL

we will update my.cnf file with some important variables

–bin-address make it equal to 0.0.0.0
–server-id give unique server id for both master and slave

  • log_bin_trust_function_creators =1
  • lower_case_table_name=1
  • performance_schema_consumer_events_statements_history_long = ON
  • performance_schema=ON
  • performance-schema-instrument='statement/%=ON'
  • performance-schema-consumer-statements-digest=ON
  • innodb_monitor_enable=all
  • innodb_buffer_pool_size=2G or 70 to 80 % out of the memeory in the OS

c28c5f4cfa920d052b6b59db38011c4e.png

below is the full my.cnf file after edititing

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

datadir=/mysqldata
socket=/var/lib/mysql/mysql.sock
log-bin=/mysqlbinlog/mysql-bin.log
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

bind-address=0.0.0.0
server-id = 1

log_bin_trust_function_creators =1
lower_case_table_names=1
performance_schema_consumer_events_statements_history_long = ON
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all
innodb_buffer_pool_size=2G
~

start mysql DB01

to start mysql using the below command

mysqld --initialize-insecure --user=mysql --lower_case_table_names=1

after that start mysql services

systemctl start mysqld systemctl enable mysqld
84e8133314d6d3114b4930c716d449c1.png

now you can login to mysql mysql -uroot

we will set password for the root user

set password='password';

c9194b0ae83ed1808f34a10acae46381.png

exit and login again with root using the password you setup

8462e6f562ee8f75a52ff4e44ed383f0.png

creating replication user DB01

we will create user for replication on db1

create user 'repl' identified by 'repl123';

a24fd4482a9001680b1fac99a3e7865f.png

give the necessary grants for repl user for replication prepose

grant replication slave,backup_admin,clone_admin on *.* to 'repl'@'%';
grant select on performance_schema.* to 'repl'@'%';

65990f20665deb5baa5d666516233d67.png

sysbench oltp_read_write –db-driver=pgsql –pgsql-host=10.10.10.4 –pgsql-user=postgres -tables=10 –table-size=100000 \ –pgsql-db=prodc
ution

sysbench oltp_read_write –db-driver=pgsql –pgsql-host=10.10.10.4 –pgsql-user=postgres –tables=10 –ta
ble-size=100000 –pgsql-db=prodcution prepare

copy my.cnf file from db01 to db02

to save time we will copy the my.cnf file from db01 to db02 using scp

scp -rp /etc/my.cnf [email protected]:/etc

a682853601a2272d915e24be34676626.png

906a3a8017adf3466376d96193ad2ee7.png

next we will just update server-id in my.cnf file to unique one than one setup in db01

vi /etc/my.cnf
40950e4704b93162a2f171d220c953fe.png

start mysql DB02

to start mysql using the below command

mysqld --initialize-insecure --user=mysql --lower_case_table_names=1

after that start mysql services

systemctl start mysqld systemctl enable mysqld

1d1abf057eb1d7d794c759d1d735d158.png
now you can login to mysql mysql -uroot

we will set password for the root user

set password='password';

12ec28e460592cc31b4a6500f44137a4.png

e7a1921d4bea50db0d9b1724d0448037.png

install plugins on db02 for cloning

we will start by installing plugin for cloning instance of db01 to db02

install plugin clone soname  "mysql_clone.so";
install plugin group_replication soname 'group_replication.so';

set global clone_valid_donor_list='mysql-enterprise-db01:3306';

set global log_error_verbosity=3;

18b09d87b6c4679b60b703db3ef80891.png

on db1
also install the below plugin

install plugin clone soname "mysql_clone.so";
f5c000700d3c79188d11dda7927defb7.png
now clone the instance using the below command inside mysql console

clone instance from 'repl'@'mysql-enterprise-db01':3306 identified by 'repl123';

now stop mysqld on db02
and go to the data dir
and remove the auto.cnf file
4294519db2dd6d20c58dd8dd5169bc1d.png

then start mysqld on db02

changer master on db02

now we will change the master to db01 on db02

before that we need to get log position on db01
using the below command
SHOW BINARY LOG STATUS ;

af337a50df6a546bf05a93f25191153a.png
so the log file is 000007 and postion is 573 this information is important when we start the replica

CHANGE MASTER  TO   MASTER_HOST='10.217.10.7', MASTER_USER='repl', MASTER_password='repl123',  MASTER_log_file='mysql-bin.000002', MASTER_log_pos=1903 ;

35518f396714d85bf0b472acee336ef8.png
now start replica by using the below command

START REPLICA;

8fcca99f1ab97b82521f31e84e951dfa.png

now to verify use the below command

show replica status\G ;

2cd196db3ae860d0b0f12cd688ffbf3e.png

failover

we will demonstrated how to failover to slave node
SET PRIMARY TO READ-ONLY
SET GLOBAL read_only = TRUE; SET GLOBAL event_scheduler = 'OFF'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;

Stop service on old primary
systemctl disable mysqld.service systemctl stop mysqld.service

ff516cc48dc39207c8460438dd670c97.png

RESET REPLICA ON NEW-PRIMARY

SHOW REPLICA STATUS\G
RESET MASTER;
stop replica ;
RESET REPLICA ALL;
SHOW REPLICA STATUS;

002d454a2481a93cd165fbea3af5b6a0.png

START MYSQL ON OLD-PRIMARY

systemctl enable mysqld.service
systemctl start mysqld.service

CONFIGURE OLD-PRIMARY TO BECOME REPLICA

before that obtain the log position from the new primary node (old slave)

show master status ;
1da63dcc94fe43ea6bf057a6575874ff.png

RESET REPLICA ALL;
CHANGE master   TO master_HOST='10.217.10.8', master_USER='repl', master_PASSWORD='repl123', MASTER_log_pos=157 ;
START REPLICA;
SHOW REPLICA STATUS\G;

59899f832d74e6861b3075deb955dfe9.png

you may get the below erro related to our repl user usingf stringsha2_password
so we will have to alter user to be saved in mysql+naitave_sql authecating methoid

0e2981c99b6c0d9366df074272e239e2.png

alter USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl123';

d83daa6a71533012656d2169916d4bc3.png

stop and start the replica in old primary

stop replica

start replica ;

b291c6312a6395ed628bdb4268bdc196.png

1008d5c218ac6f1420688ec52522635c.png

error is clear you may start testing the replication .

0
Visited 18 times, 1 visit(s) today
Category: mysql