Posts

MySQL 8.0 : Error Logging

Image
MySQL 8.0 : Error Logging
Here I would like to explain, How MySQL 8.0 server can be configured to log diagnosis messages inside the error log file. 
Generally, the error log contains: A record for mysqld service startup and shutdown.Diagnostic messages such as errors, warnings, and notes during mysqld server startup, running or shut down. A stack trace if mysqld exits abnormally. mysqld_safe writes once it finds mysqld exists abnormally and mysqld_safe invoke/start mysqld.  Error Log Component Configuration MySQL 8.0 uses the MySQL error log component architecture that performs log event filtering and writing. These error log components can be configured using the system variable to achieve the desired logging results inside the error log file.

We have mainly two types of error log components: filter & sink.

Error Log Filter Components --->  Filter components, mainly process log events, to add, delete or modify event fields or to delete events entirely. Error Log Sink Components

How to properly shutdown MySQL before any maintenance activity

Image
How to properly shutdown MySQL before any maintenance activity
We might have different scenarios once we need to stop MySQL service before performing either server or database activity like patching/upgrade.  
Before performing such activity, we need to make sure that we stop MySQL service properly to avoid any unforeseen crashes once our maintenance activity complete and MySQL service is started. 
Here I would like to share the below steps which should be performed and take care to properly stop MySQL service. 
Step 1: Ensure we don't have any long-running queries. Manually verify by checking the processlist. show processlist;  Step 2: If it is the master node, then ensure to stop application write hits on the database by doing either stop an app and put in maintenance OR if we have any failover solution then move write hits on another node. 
Step 3: If it is a slave node, then STOP replication and note down the stopped replication co-ordinates.  stop slave; show slave status\G…

How to Prepare for MySQL Certification Exam

Image
How to Prepare for MySQL Certification Exam

Here I am going to suggest MySQL DBA, student or any newbie that how they can prepare for "Oracle's MySQL 5.x Database Administrator" certification exam. 
Step 1: Lab
-- Prepare your local MySQL lab environment and make sure to install the respective MySQL version on your lab. (i.e., if you are going to appear on MySQL 5.6 DBA exam 1Z0-883 then install MySQL 5.6 on your lab) 
-- You can follow my below blog post to create your local MySQL lab environment. 
How to create CentOS VM using Vagrant Install MySQL on CentOS using DBdeployer
Step 2: Book + HandsOn Experience
-- If you don't have below book, then go right now and purchase this book. (Here Please, you don't judge that below-suggested book is for 5.0 version whereas you are going to prepare for MySQL 5.6/5.7 DBA certification exam. Just go ahead and purchase below book. In the end, you will definitely say it was your investment for yourself.) 
MySQL 5.0 Certification S…

Install MySQL on CentOS using DBdeployer

Image
Install MySQL on CentOS using DBdeployer
1. Here It is assumed You will have a CentOS system/VM. If not, then please find my this blog post link where you will get instruction about, how you can create CentOS virtual machine using the vagrant. 
2. Connect to CentOS VM abhinavs-MacBook-Air:centos7-test-vm agupta$ pwd /Users/agupta/vagrant_box/centos7-test-vm
abhinavs-MacBook-Air:centos7-test-vm agupta$ vagrant ssh 
[vagrant@centos7-test-vm ~]$ sudo su -
[root@centos7-test-vm ~]#
3. Run below command to install the latest DBdeployer package  [root@centos7-test-vm ~]# yum -y install wget
[root@centos7-test-vm ~]# VERSION=1.30.0
[root@centos7-test-vm ~]# OS=linux
[root@centos7-test-vm ~]# origin=https://github.com/datacharmer/dbdeployer/releases/download/v$VERSION
[root@centos7-test-vm ~]# wget $origin/dbdeployer-$VERSION.$OS.tar.gz
[root@centos7-test-vm ~]# tar -xzf dbdeployer-$VERSION.$OS.tar.gz
[root@centos7-test-vm ~]# chmod +x dbdeployer-$VERSION.$OS
[root@centos7-test-vm ~]#…

errno: 24 - Too many open files

Image
errno: 24 - Too many open files
You might see this error inside MySQL's error log file or during mysql_upgrade operation.

Can't open file: ‘./db/table.frm' (errno: 24 - Too many open files)
To solve this issue, you will need to change the open files limit at system and db service level.

--> To check and change this limit at the system level, we use ulimit client tool to set it dynamically.

* Check current system open file limit.
root@test:~# ulimit -a core file size          (blocks, -c) 0 data seg size           (kbytes, -d) unlimited scheduling priority             (-e) 0 file size               (blocks, -f) unlimited pending signals                 (-i) 515256 max locked memory       (kbytes, -l) 64 max memory size         (kbytes, -m) unlimited open files                      (-n) 1024 pipe size            (512 bytes, -p) 8 POSIX message queues     (bytes, -q) 819200 real-time priority              (-r) 0 stack size              (kbytes, -s) 8192 cpu time            …

ERROR 1040 (HY000): Too many connections

Image
ERROR 1040 (HY000): Too many connections
An application might face this issue when all the defined max_connections parameter value is exhausted. 
mysqld reserve a  +1 connection (i.e., max_connections + 1) for the user who is having the SUPER privilege. 
When this issue appears, Only the user with the SUPER privileges can be connected if any other user who has SUPER privileges is not already connected to the database and we have reached max_connections. 
1. Always make sure to DON'T give the SUPER privilege to any application user or any user which is being used inside the scripts. Because if that user has already been connected to db when "too many connections" issue appears, then that time root user can't be connected even though root user is having all privileges along with SUPER privileges. 
Here I am presenting 3 cases which will help us, how MySQL handles "too many connections" and reserve 1 connection. For below example, we have manually set the max_…

File/Pos Replication : Skip Slave's sql_thread error [COMMAND]

File/Pos Replication : Skip Slave's sql_thread error [COMMAND]

For binlog file & position based replication setup, To skip the slave's sql_thread error, run below command.


stop slave;  set global sql_slave_skip_counter=1; start slave; select sleep(3); show slave status\G