MySQL Replication ERROR 1062: Duplicate Entry for Primary Key when starting Slave

In this blog,we will see how to resolve the MySQL Replication ERROR 1062 : Duplicate Entry for Primary Key when starting Slave. Here we will skip the MySQL slave error. This error,will be raising after successfully establishing MySQL Master and Slave Connection and got error like “MySQL slave duplicate entry with MySQL error code 1062” When we take dump from Master database,there will be possibility that our Master Database has duplicate entries,but it wont work in the slave.Hence we have to make one change in Configuration file of MySQL “my.cnf”…

Read More

Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline

Hi All, this document deals with how to fix ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline. These messages indicate that the table’s definition allows rows that the table’s InnoDB row format can’t actually store. The Below configuration fixed my issue.   Restart MySQL or MariaDB restart or   © 2020, Techrunnr. All rights reserved.

Read More

how backup and restore mysql database using ansible

Hi All, This document shows you how backup and restore MySQL database using ansible. Ansible is a Provisioning and configuration management tool which can be used for both Linux and windows platform. Ansible has reached the best place under the best DevOps tools because it is very is easy and simple. Ansible playbook is written in YAML file which is human readable and understandable which makes ansible popular. Step 1: Create a YAML file vi backup-restore.yml Paste the below contents, explanations are given below. First task perform DB backup to…

Read More

how to install MariaDB 10.4 in Centos 7

Hi All, this document shows you how to install MariaDB 10.4 in Centos 7. MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system, intended to remain free and open-source software under the GNU General Public License. Let’s go to the installation Part. Step 0: Update the system This will help you to keep update the system to the latest version. Once you update the system reboot the server as some packages need a reboot to updates to take effect. Step 1: Add the MariaDB 10.4…

Read More

how to get database structure

Hi All, this document deals with how to get database structure. MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language How to perform   The above command creates a .sql file with database and table structure without data. -d means no data or else you can use the below command,   © 2020, Techrunnr. All rights reserved.

Read More

Change the MariaDB Default Data Directory

We need to change the default data directory /var/lib/mysql to a different location. Now we will see how to change the default MySQL / MariaDB data directory to a different path on a CentOS/RHEL 7 server. The new directory in /data/techrunnr/mysql-data and this directory should have ownership of mysql:mysql   1. Find the current default data directory using the following command. Now we can see default directory is /var/lib/mysql/ 2. Copy MySQL data to a new location First, we need to stop the MariaDB service Now Database is stopped, now…

Read More

Clear Mysql ibdata1

Hi Techrunnr, this document deals with How to shrink ibdata1 file in /var/lib/mysql/. By default ibdata1 file in /var/lib/mysql/ normally consists of 4 types of data Table Data Table Indexes MVCC (Multi versioning Concurrency Control) Data Table Metadata Follow the below steps to shrink the ibdata1 Take the dump of all the databases Stop mysql service Now we will remove the ibdata1, ib_logfile0, ib_logfile1 Add the below content to the my.cnf file Start the mysql service ibdata1 contains table information, so we have deleted this file, so databases will be…

Read More

how to change the keycloak database to MySQL from h2

In this document, we will check how to change the keycloak database to MySQL from h2. Keycloak comes with its own embedded Java-based relational database called H2. This is the default database that Keycloak will use to persist data and really only exists so that you can run the authentication server out of the box. It’s fine for development purposes, but for the production, it is recommended to replace it with a more production-ready external database. Prerequisites * Keycloak server, if not check this link * Running MySQL server, if…

Read More

how to remove slow queries and general log from Amazon RDS

Hi All, this document deals with how to remove slow queries and general log from Amazon RDS. Sometimes there is a requirement to enable slow query in RDS to see the queries which are taking more time to execute. there are to options while enabling slow queries either the queries can be written to TABLE or FIle. Whenever the size is RDS instance storage, there is a requirement to delete the queries to free up some space. Here is the command to delete the general logs and slow queries. Step…

Read More

how to check the size of the individual database in MySQL or MariaDB

Hi all, this document deals with how to check the size of the individual database in MySQL or MariaDB. Sometimes as a database administrator it’s required to check the size of the individual databases in database servers. So here is the command to check the sizes. Step 1: log in to Database server ( Mysql/MariaDB) Step 2: Execute the following command to get the individual size of all the schemas. In GB IN MB Here is the command to see the Size of the table.   © 2019, Techrunnr. All…

Read More