Category: MySQL/MariaDB

The way to achieve table-level replication in MySQL is to use the combination of two replication parameters named replicate-do-db and replicate-ignore-table along with other mandatory replication parameters. You need to identify the name of the database of your tables you need to replicate and from the database opt out the tables that you don’t want…

Requirements: MySQL 8.0 CentOS 7 Virtual Machine or Physical Server/Desktop Internet on the CentOS Server Installation: Create an operating system user named as “mysql” by logging in to the operating system root user and add the user to the “sudo” list. # useradd mysql # passwd mysql # usermod -aG wheel mysql Install the wget…

Scenario: Got below error from application end. SQL state [HY001]; error code [1038]; (conn=15945) Out of sort memory, consider increasing server sort buffer size; nested exception is java.sql.SQLException: (conn=15945) Out of sort memory, consider increasing server sort buffer size at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89) Investigation: Detailed dig down on the MariaDB error log found the below error. [ERROR]…

Scenario: All the three nodes of Galera cluster uses 98 percent of memory and swapping heavily, no DML could be performed. Investigation: From the error log on the node 3 the below error has been notified: Detected table cache mutex contention at instance 1: 60% waits. Additional table cache instance activated. Number of instances after…

mysqldump backup utility of MySQL/MariaDB  supports the export of metadata or specifically creating DDL scripts with the option –no-data. $ mysqldump -uroot -p test –no-data –routines –events –triggers > test_ddl.sql Note: Remember to mention the –routines, –events and –triggers also to export the related objects.

Prerequisites: Two virtual machine each of 2GB RAM, 10 GB HDD and Two vCPU with CentOS 7 installed on these. Workaround: Configure the networking between two nodes: Step1: Change the host name of the master node to master-node and make entries to the /etc/hosts file. # hostnamectl set-hostname master-node # echo ‘192.168.56.103 master-node’ >> /etc/hosts…

MariaDB Error: ERROR 1419 (HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_routine_creators variable) Cause: The user who is executing the create function doesn’t have the required SUPER privilege. Solution: AS for security concern the user couldn’t be given the SUPER privilege…