Common Mysql Problems & Solutions - Part -1

Here are few common Mysql Replication Errors & their solutions

Problem - 1   

Last_Errno: 1594
Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Solution

Run Show slave status & Check these 2 positions
Relay_Master_Log_File: db1-bin.002183
Exec_Master_Log_Pos: 9866809

Stop Slave & Reset the same & provide the position you have noted earlier
mysql> stop slave;
mysql> reset slave;
mysql> change master to master_log_file='db1-bin.002183', master_log_pos=9866809;
mysql> start slave;

It's done now. Let the slave recover it's data with Master

#####################################

PROBLEM - 2

ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository




This error comes when you are creating new Mysql slave & just started the replication with Master

Solition:

Reset slave;
Stop Mysql
Delete mysqld-relay-bin.index in Mysql folder
Start Mysql
Run Change Master to Master command
Start Slave;


#################################

PROBLEM - 3

Last_SQL_Errno: 1305
Last_SQL_Error: Error 'FUNCTION DB.SPLIT_STR does not exist' on query. Default database: 'DB'. Query: 'insert into tablename(folderid, subuid, company, title, summary, descr, keyskills, job_type, minsal, maxsal, minsal_channel,

Solution -
It is related with functions as the above function is not available at slave server
 
Take backup of functions of DB from destination server
mysqldump -u root -p --routines --no-create-info --no-data --no-create-db --skip-opt  DBName > /home/bhagwat/functions.sql

Restore with below command on Slave
mysql --defaults-file=/root/.my.cnf.root DBNAME < /home/bhsingh/functions.sql


##############################

Problem - 4

ERROR 1235 (42000) at line 21: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

Solution -
Add --skip-triggers in command & take backup of functions

mysqldump --defaults-file=/root/.my.cnf.root --routines --no-create-info --no-data --no-create-db --skip-opt --skip-triggers bazooka > /home/bhsingh/bazooka1.sql

##############################

Problem - 5

Last_SQL_Error: Error 'Out of resources when opening file


Solution:

Increase Open files limit on server to fix this  issue

Check current limit with below command
[root@server ~]# mysql -u root -p -e 'show variables;' | grep open
have_openssl    DISABLED
innodb_open_files    300
open_files_limit    8000
table_open_cache    2048


Increase the same with below command

[root@server ~]# cat /etc/security/limits.conf |grep mysql
mysql         soft      nofile     12000
mysql         hard      nofile     16000

For Immediate effect change in below file
 vim /etc/security/limits.d/90-nproc.conf
*          soft    nproc     1024
mysql      soft    nofile    12000
mysql      hard    nproc     16000
root       soft    nproc     unlimited

##########################

Will share the next Part of this series in few days

OPTIMIZE THE USE OF RAM for BETTER Performance on High Load Linux Server

Swapiness which is a kernel module that defines when linux has to start to write in the SWAP (an allocated space in the disk) to relieve the RAM.
It may seem obvious that with access times in milliseconds for Hard Disk and in nanoseconds for RAM, it is better to write in the RAM.

Swap Values are defined below   

vm.swappiness = 0 : Swapping will be activated urgently only to avoid out of memory condition.
vm.swappiness = 60 : This is the default value and means that from 40% of Ram occupancy, the kernel writes to the swap.
vm.swappiness = 10 : This what is recommended to improve performance.
vm.swappiness = 100 : The kernel will swap aggressively.

To see your swappiness setting :

cat /proc/sys/vm/swappiness
60
Let’s consider that we have a server having 156G of RAM. fixing the swappiness to 5 is enough since kernel will use swap only when the free RAM space is less or equal to 7.8G.

As root set the swappiness value:

echo 5 > /proc/sys/vm/swappiness

or alternatively, run:

sysctl -w vm.swappiness=5

Verify your changes:

cat /proc/sys/vm/swappiness
5

or:

sysctl vm.swappiness
vm.swappiness = 5

To change this value permanently edit /etc/sysctl.conf and modify (or add if not exist):

vm.swappiness = 5
apply modification:

sysctl -p
swappoff -a && swapon -a

FIX "CRITICAL – Socket timeout after 10 seconds" - Nagios

How to fix “CRITICAL – Socket timeout after 10 seconds” error?
We can fix this by increasing the “Socket timeout” value from the default 10 seconds to let’s say 20.

We can do this by adding a parameter to a specific command defined in commands.cfg file on your Nagios server. Commands.cfg file is usually located at /usr/local/nagios/etc/objects/commands.cfg (if you compiled Nagios) or /etc/nagios/commands.cfg (if you installed Nagios from RPM).

Read more about commands.cfg in my post “Nagios configuration – How to configure Nagios” post.

BEFORE (/usr/local/nagios/etc/objects/commands.cfg):

define command {
 command_name    check_nrpe
 command_line    $USER1$/check_nrpe -H $HOSTADDRESS$ -c $ARG1$
 }
AFTER (/usr/local/nagios/etc/objects/commands.cfg):

define command {
 command_name    check_nrpe
 command_line    $USER1$/check_nrpe -H $HOSTADDRESS$ -c $ARG1$ -t 20
 }
There are also other commands that support the “-t” parameter! Be sure to add a “-t 20” parameter to the command you had “CRITICAL – Socket timeout after 10 seconds” problems with ??

Block YouTube HTTPS Traffic In Squid & Iptables

Blocking 443 traffic in iptables or squid (Transparent proxy) is possible with below rule

Iptables Rule to Block YouTube in IPTABLES

iptables -I FORWARD -p tcp --dport 80 -m string --string 'youtube.com' --algo bm --to 65535 -j DROP
iptables -I FORWARD -p tcp --dport 443 -m string --string 'youtube.com' --algo bm --to 65535 -j DROP

Above rules will block entire traffic destine to 443 port but you can apply same rule on particular single ip, range of ips or complete network.

To Apply Same Rule on Single IP Address or Subnet

iptables -I FORWARD -p tcp --dport 443 -s 192.168.2.10 -m string --string ‘youtube.com’ --algo bm --to 65535 -j DROP

You can also block "facebook", "twitter" or other https websites with above method.

Ping with Real time (Timestamp)

Ping with Real time (Timestamp)

ping google.com | awk '/^[0-9]+ bytes from / { "date" | getline pong; close("date"); print pong":",$0; }'

You can also save the results with a txt file with simply adding > after the command

ping google.com | awk '/^[0-9]+ bytes from / { "date" | getline pong; close("date"); print pong":",$0; }' >results.txt


If you’re interested in all types of replies (unreachable, no route to host, etc), the following should work on most systems;

ping google.com | while read pong; do echo "$(date): $pong"; done


You can also save the results with a txt file with simply adding > after the command

ping google.com | while read pong; do echo "$(date): $pong"; done > results.txt

Cron job not running after a timezone change

New time is not reflecting on crons logs even after chaning timezone & time.
Here is the simple trick to fix this

Restart the rsyslog service with below command to sync the new time with all applications (including cron)

service rsyslog restart

Forward SMPT port 25 with other port

Linux does not allow root users to bind port traffic to ports 1024 and below. However, the simple mail transfer protocol (SMTP) has a default value of port 25. If you are running Linux, you must configure the reserve SMTP agent to listen to a custom port instead of the default port 25.

Before you begin
Run the commands in the following procedure as a root user.

Update your firewall to open the port above port 1024 by adding the following lines to your

vim /etc/sysconfig/iptables file:

-A OUTPUT -p tcp -s serverIP -d 0/0 --dport Port_Above_1024   -m state --state ESTABLISHED -j ACCEPT
-A INPUT -s 0/0 -d serverIP -m state --state NEW,ESTABLISHED  -p tcp --dport Port_Above_1024 -i eth0 -j ACCEPT

Restart your firewall:

/etc/init.d/iptables restart

Open port 25 for forwarding:

iptables -A FORWARD -p tcp --destination-port 25 -j ACCEPT;

Forward port 25 to your custom port above 1024:

iptables -t nat -A PREROUTING -j REDIRECT   -p tcp --destination-port 25 --to-port Port_Above_1024

To verify that port 25 is forwarding, Run a telnet command:

telnet yourServer 25

If you do not receive a successful response, forward port 25 to a different custom port.

Port_Above_1024 change the same to any port no. above 1024