Skip to main content

MySQL InnoDB cluster configuration | Router | MEM Agent

Configure InnoDB cluster:
  • Install MySQL 8.0.13 enterprise edition
  • Configure MySQL, create users for replication
  • Configure systemctl to manager MySQL
  • Configure InnoDB cluster

Prepare Database server for InnoDB cluster:
Stop firewall daemon, disable firewall daemon and reboot server
#systemctl stop firewalld
#systemctl disable firewalld
#systemctl status firewalld

Update /etc/host file:
Following configuration is required to address Bug 28214173 : [SIERRA ONLY] INNODB CLUSTER MEMBERS STOP BY FAILED START OF GROUP REPLICATION
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
IP_ADDRESS_1   hostname1.com         hostname1
IP_ADDRESS_2   hostname2.com         hostname2
IP_ADDRESS_3   hostname3.com         hostname3
Update /etc/sysconfig/network file:
# Created by anaconda
NETWORKING=yes
HOSTNAME=hostname1.com
Disable SeLinux:
Edit file /etc/sysconfig/selinux and set SELINUX=permissive and REBOOT server
SELINUX=permissive
$ getenforce
Permissive
Create my.cnf file as follows for each server:
[mysqld]
### Customize for MySQL8.0.13
default_authentication_plugin           = mysql_native_password
max_allowed_packet                      = 1024M
event_scheduler                         = ON
tmp_table_size               = 83886080
max_heap_table_size          = 83886080
max_connections = 500
log_timestamps = SYSTEM
### Customization end
socket                                  = /mysql/<db_name>/data/<db_name>.sock
port                                    = 3556
server-id                               = 3556
pid-file                                = /mysql/<db_name>/data/<db_name>.pid
datadir                                 = /mysql/<db_name>/data
user                                    =  mysql
tmpdir                                  = /mysql/<db_name>/temp
slow_query_log                          = ON
slow_query_log_file                     = /mysql/<db_name>/logs/<hostname>-slow.log
log-error                               = /mysql/<db_name>/logs/<db_name>.err
########################
## -- InnoDB parameters
########################
innodb_temp_data_file_path = /mysql/<db_name>/temp/ibtmp1:2G:autoextend
innodb_log_group_home_dir=/mysql/<db_name>/iblogs/
innodb_flush_method=O_DIRECT
innodb_log_file_size=5GB
innodb_log_files_in_group=2
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=2
innodb_buffer_pool_size=5G
innodb_buffer_pool_instances= 8
innodb_lock_wait_timeout=50
##########################
## -- Binlog configuration
##########################
disabled_storage_engines                = MyISAM,BLACKHOLE,FEDERATED,ARCHIVE
#Storage Engine MyISAM is required during upgrade
#disabled_storage_engines               = BLACKHOLE,FEDERATED,ARCHIVE
binlog_format                           = ROW
log_bin                                 = /mysql/<db_name>/binlogs/<db_name>-bin
log_bin_index                           = /mysql/<db_name>/binlogs/<db_name>-bin.index
max_binlog_size                         = 10M
binlog_rows_query_log_events            = ON
#################
## -- Replication
#################
gtid-mode                               = ON
enforce_gtid_consistency                = ON
master-info-repository                  = TABLE
relay-log-info-repository               = TABLE
relay-log                               = /mysql/<db_name>/binlogs/<db_name>-relay
relay-log-index                         = /mysql/<db_name>/binlogs/<db_name>-relay.index
expire_logs_days                        = 7
log_slave_updates                       = ON
relay_log_recovery                      = ON
transaction_write_set_extraction        = XXHASH64
binlog_checksum                         = NONE
#######################
## -- Group Replication
#######################
plugin-load                       = group_replication.so
group_replication_transaction_size_limit = 2147483647
group_replication                 = FORCE_PLUS_PERMANENT
group_replication_start_on_boot   = OFF
group_replication_bootstrap_group = OFF
group_replication_local_address   = IP_ADDRESS_2:35560
group_replication_group_seeds     = IP_ADDRESS_1:35550,IP_ADDRESS_2:35560,IP_ADDRESS_3:35570
group_replication_ip_whitelist    = IP_ADDRESS_1,IP_ADDRESS_2,IP_ADDRESS_3
group_replication_member_expel_timeout=60

Initialize mysqld after installation:
#mysqld --defaults-file=/etc/<db_name>.cnf --initialize&
   
Start mysql daemon:
$sudo su - mysql
$mysqld --defaults-file=/etc/<db_name>.cnf&
   
Get temporary password for user root from error log file:
$grep password /mysql/<db_name>/logs/<db_name>.err

Connect mysqld, set password for root user and update root user host='%':
$mysql -uroot -p -S/mysql/<db_name>/data/<db_name>.sock

Disable Bin Log:
mysql> SET SQL_LOG_BIN = 0; OR set sql_log_bin='off';
Change root id password:
mysql> alter user 'root'@'localhost' identified by '<new_code>';
mysql> update mysql.user set host='%' where user='root';
mysql> select * from mysql.user where user='root'\G
mysql> SET SQL_LOG_BIN = 1; OR set sql_log_bin='on';
   
Create replication user on all nodes:
Note: MySQL 8.0.11 default_authetication_plugin is caching_sha2_password, change it mysql_native_password so user can connect using password.
When you decide to use MySQL 8.0.11 caching_sha2_password which is defautl check with Application owner whether it supports or not.
   
mysql> SET SQL_LOG_BIN = 0; OR set sql_log_bin='off';
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '<code>';
mysql> ALTER USER repl IDENTIFIED WITH mysql_native_password;
OR
mysql> CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '<code>';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN = 1; OR set sql_log_bin='on';   

Configure passwordless connection for MySQL user netbkmysql:
Configure passwordless connection for user netbkmysql using mysql_config_editor, become root user
# mysql_config_editor set --socket=/mysql/<db_name>/data/<db_name>.sock --user=netbkmysql --password --host=localhost

# mysql_config_editor print --all
Check passwordless connection for user netbkmysql:
#mysql --login-path=nbmysql
   
If require you can reset it also   
/usr/bin/mysql_config_editor reset
   
Configure systemctl (System Daemon) to manage mysqld for one service only:
If you multiple instance on same machine then there is a different process to configure

Edit file on all nodes  - /usr/lib/systemd/system/mysqld.service
Add following line under [Service] section below Type=notify
PIDFile=/mysql/<db_name>/data/<db_name>.pid

Comment existing ExecStart and add new Execstart as follows under section - # Start main service

ExecStart=/usr/sbin/mysqld --defaults-file=/etc/<db_name>.cnf --daemonize --pid-file=/mysql/<db_name>/data/<db_name>.pid $MYSQLD_OPTS

Shutdown running mysqld using following:
$mysqladmin -uroot -p<code> -S/mysql/<db_name>/data/<db_name>.sock shutdown
       
Reload daemon:
#systemctl daemon-reload
   
Enable systemctl for mysqld, so when ever server reboot, mysqld start automatically:
#systemctl enable mysqld
       
Start mysqld using systemd:
#systemctl start mysqld

If you get Permission Denied error, set SELINUX=permissive - http://shrenikp.blogspot.com/2018/11/mysql-error-could-not-open-file.html

Create  cluster :
bash-4.2$ mysqlsh
JS > \c root@IP_ADDRESS_1:3555
JS > var cluster = dba.createCluster('<db_name>')
JS > cluster.addInstance('root@IP_ADDRESS_2:3556')
JS > cluster.addInstance('root@IP_ADDRESS_3:3557')
JS > dba.getCluster ()
JS > dba.getCluster().status()

Create application user:
create user '<user>'@'%' identified by '<code>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES, CREATE VIEW, SHOW VIEW ON <db_name>.* TO '<user>'@'%';

create user '<user_rpt>'@'%' identified by '<code>';
grant SELECT, SHOW VIEW ON <db_name>.* TO <user>;

Router configuration:
# mysqlrouter --bootstrap root@IP_ADDRESS_1:3555 --user=mysql -d /mysql/db_name/db_name
Please enter MySQL password for root:

Start router:
$./start.sh

MEM 8.0.3 agent installation:

Create /mysql/MEM8.0.3/agent/option.txt file for each server as follows and update mysqlhost and mysqlport for each server

debuglevel=4
installer-language=en
debugtrace=/mysql/MEM8.0.3/agent/install.debugtrace.monitor.log
mode=unattended
installdir=/mysql/MEM8.0.3/agent/
managerhost=manger_hostname.com
managerport=18446
agentuser=mysqlagent
agentpassword=mysqlagent_code
agent_installtype=database
mysqluser=root
mysqlpassword=<code>
mysqlhost=local_hsotname.com
mysqlport=3555

Install agent using option file:
#/tmp/mysqlmonitoragent-8.0.3.8197-linux-x86-64bit-installer.bin --optionfile /mysql/MEM8.0.3/agent/option.txt

Start mysql agent:
#/mysql/MEM8.0.3/agent/etc/init.d/mysql-monitor-agent start

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

MySQL InnoDB cluster troubleshooting | commands

Cluster Validation: select * from performance_schema.replication_group_members; All members should be online. select instance_name, mysql_server_uuid, addresses from  mysql_innodb_cluster_metadata.instances; All instances should return same value for mysql_server_uuid SELECT @@GTID_EXECUTED; All nodes should return same value Frequently use commands: mysql> SET SQL_LOG_BIN = 0;  mysql> stop group_replication; mysql> set global super_read_only=0; mysql> drop database mysql_innodb_cluster_metadata; mysql> RESET MASTER; mysql> RESET SLAVE ALL; JS > var cluster = dba.getCluster() JS > var cluster = dba.getCluster("<Cluster_name>") JS > var cluster = dba.createCluster('name') JS > cluster.removeInstance('root@<IP_Address>:<Port_No>',{force: true}) JS > cluster.addInstance('root@<IP add>,:<port>') JS > cluster.addInstance('root@ <IP add>,:<port> ') JS > dba.getC...
Post a Comment
Read more

InnoDB cluster Remove Instance Force | Add InnoDB instance

InnoDB cluster environment UUID is different on node: To fix it stop group replication, remove instance (use force if require), add instance back Identify the node which is not in sync: Execute following SQL statement on each node and identify the node has different UUID on all nodes. mysql> select * from mysql_innodb_cluster_metadata.instances; Stop group replication: Stop group replication on the node which does not have same UUID on all nodes. mysql > stop GROUP_REPLICATION; Remove instances from cluster: Remove all secondary node from the cluster and add them back if require. $mysqlsh JS >\c root@<IP_Address>:<Port_No> JS > dba.getCluster().status() JS > dba.getCluster () <Cluster:cluster_name> JS > var cluster = dba.getCluster("cluster_name"); JS >  cluster.removeInstance('root@<IP_Address>:<Port_No>'); If you get "Cluster.removeInstance: Timeout reached waiting......" JS > cluster.removeInstance(...
2 comments
Read more

Oracle E-Business Suite Online Patch Phases executing adop

Following description about Oracle E-Business Suite is high level and from documentation https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531062.htm#5281339 for in depth and detail description refer it. The online patching cycle phases: Prepare Apply Finalize Cutover Cleanup Prepare phase: Start a new online patching cycle, Prepares the environment for patching. $ adop phase=prepare Apply phase: Applies the specified patches to the environment. Apply one or more patches to the patch edition. $ adop phase=apply patches=123456,789101 workers=8 Finalize phase: Performs any final steps required to make the system ready for cutover. Perform the final patching operations that can be executed while the application is still online. $ adop phase=finalize Cutover phase: Shuts down application tier services, makes the patch edition the new run edition, and then restarts application tier services. This is the only phase that involves a brief ...
Post a Comment
Read more