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