AWS / Azure / GCP Cloud | Database | Data Analytics | LakeHouse | Machine Learning | Shrenik Parekh

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    ...

MEB - MySQL Enterprise Backup perform as follows: 01. FLUSH ENGINE LOGS;     Flush logs of all engines to disk. 02. Copy binlog files start from beginning of backup. 03. Copy non-InnoDB files. 04. Write the meta files: server-all.cnf server-my.cnf 05. Rescan InnoDB tablespace files for schema changes. 06. Remove dropped InnoDB tablespace files. 07. For image backups, copy InnoDB symbolic link files - .isl to the image. 08. Stop copying InnoDB redo log. 09. Create meta/backup_variables.txt, meta/ibbackup_slave_info, meta/backup_gtid_executed.sql files.

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(...

Getting Error - Could not open file '/mysql/<db_name>/logs/<db_name>.err' for error logging: Permission denied: If you are implementing systemctl to start, stop, and check status of mysqld and getting following error, while starting mysqld service, then check whether SeLinux is disabled or not. mysqld[44278]: 2018-11-06T01:36:48.303642Z 0 [ERROR] [MY-010187] [Server] Could not open file '/mysql/<db_name>/logs/<db_name>.err' for error logging: Permission denied mysqld[44278]: 2018-11-06T01:36:48.303785Z 0 [ERROR] [MY-010119] [Server] Aborting Disable SeLinux: Check whether SeLinux is enforcing or permissive # getenforce Enforcing Edit file /etc/sysconfig/selinux and set SELINUX=permissive as follows and REBOOT server # cat /etc/sysconfig/selinux # This file controls the state of SELinux on the system. # SELINUX= can take one of these three values: #     enforcing - SELinux security policy is enforced. #     permissive - SEL...

Can't change size of file (OS errno 28 - No space left on device): You might surprise even though there is plenty of free space available on the disk and you encounter " Can't change size of file (OS errno 28 - No space left on device): " while using MySQL database server. There could be multiple reason for it. I have implemented different fix for different environment, such as Increase physical memory and inndo_buufer_pool_size Increase tmp_table_size Increase max_heap_table_size BUT In one of the environment issue was fixed only after setting innodb_temp_data_file_path = /mysql/<db_name>/temp/ibtmp1:1G:autoextend

ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'": If you are getting following error when trying to grant select privilege on infomation_schema, then explore what is information_schema and you don't need to grant select to any user for information_schema. ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema' Information_schema database for mysql is specialized, virtual database. By default all users have select privilege on database information_schema. Even root user can't insert, update, delete, and drop objects of information_schema database. Information_schema database, built when service is started. Explore more about information_schema at https://dev.mysql.com/doc/refman/8.0/en/information-schema-introduction.html

Install MySQL agent unattended mode / non-interactive mode: 01. Get file mysqlmonitoragent-8.0.3.8197-linux-x86-64bit-installer.bin from Oracle e-delivery. 02. Copy file mysqlmonitoragent-8.0.3.8197-linux-x86-64bit-installer.bin to /tmp/ of the target server where agent need to install. 03. Create options file - options.txt and include following parameters. debuglevel=4 installer-language=en debugtrace=/mysql/MEM803/agent/install.debugtrace.monitor.log mode=unattended # User defined installation location of agent installdir=/mysql/MEM803/agent/ # Following is the server where MySQL Service Manager is running managerhost=<MEM Server> managerport=<Port No. of MEM server> # Following is the user created on MEM and not in MySQL database agentuser=msqlagent agentpassword=<code> # database type of installation will monitor server and database agent_installtype=database # Mysql user of the server where agent is installed mysqluser=root mysqlpassword=<code> # Mysql serv...

Can't start mysqld using systemctl:  | Permission Denied 01. Check SELinux is enforced or not using following command, if the value is enforced means SELinux is enforced. #getenforce 02. Edit file /etc/selinux/config and set SELINUX=permissive 03. Reboot server, and type following command to see value permissive. #getenforce 05. Check status using following command #sestatus 04. Execute following command to start mysqld #sysetmctl start mysqld Explore more how to disable SELinux at following link https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/6/html/security-enhanced_linux/sect-security-enhanced_linux-working_with_selinux-changing_selinux_modes

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...

MySQL InnoDB cluster limitations: 01. A group can consist of maximum 9 servers . Attempting to add another server to a group with 9 members causes the request to join to be refused. 02. Limitations and issues described for multi-primary mode groups can also apply in single-primary mode clusters during a failover event, while the newly elected primary flushes out its applier queue from the old primary. 03. Individual transactions that result in GTID contents which are large enough that it cannot be copied between group members over the network within a 5 second window can cause failures in the group communication . To avoid this issue try and limit the size of your transactions as much as possible. For example, split up files used with LOAD DATA INFILE into smaller chunks. 04. Group Replication cannot currently make use of replication Event Checksums . The certification process does not take into account gap locks, table lock, and named locks . Setting a transaction...