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:

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...
1 comment
Read more

MySQL slave Error_code: 1032 | MySQL slave drift | HA_ERR_KEY_NOT_FOUND

MySQL slave Error_code: 1032 | MySQL slave drift: With several MySQL, instance with master slave replication, I have one analytics MySQL, environment which is larger in terabytes, compared to other MySQL instances in the environment. Other MySQL instances with terabytes of data are running fine master, slave replication. But this analytics environment get started generating slave Error_code :1032. mysql> show slave status; Near relay log: Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_log_pos 5255306 Near master section: Could not execute Update_rows event on table <db_name>.<table_name>; Can't find record in '<table_name>', Error_code: 1032; Can't find record in '<table_name>', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log <name>-bin.000047, end_l...
2 comments
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