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

Can't add instance to InnoDB cluster: If mySQL instance is expelled from InnoDB cluster, you tried to add MySQL instance back to the cluster, but cant add it back, you tried to remove it and you can't remove it and got message "There might be too many transactions to apply or some replication error. In the former case, you can retry the operation (using a higher timeout value by setting the global shell option 'dba.gtidWaitTimeout'). In the later case, analyze and fix any replication error. You can also choose to skip this error using the 'force: true' option, but it might leave the instance in an inconsistent state and lead to errors if you want to reuse it. Cluster.removeInstance: Timeout reached waiting for cluster transactions to be applied on instance 'IP_ADDRESS:PORT_NO' (RuntimeError)" Execute following steps: $ mysqlsh JS > \c root@<IP_ADDRESS>:<PORT_NO> JS > var cluster = dba.getCluster() Remove cluster using force:tr...

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