MySQL High Availability with Keepalived and HAProxy

In this blog post, we are going to test load balancer solution for MySQL high availability by integrating it with Keepalived, HAProxy, xinetd software components.

LB_diag

High availability databases use an architecture that is designed to continue to function normally even when there are hardware or network failures within the system.

Why we need this?

Let’s take a scenario where we have MySQL Multi-Master / MASTER-SLAVE replication setup for high availability. In the case of Hardware/Network failure on MASTER, In order to failover to a next available server, we need to manually do the configuration changes for client connections.In this case, downtime is expected since manual failover will take some times. To solve this we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.

To avoid such downtimes and for the maximum high availability of the database, we can integrate load balancer with MySQL to take care of this manual work and do automatic failover connections.

Advantages:

  • Almost ZERO DOWNTIME for DB maintenance activities like database patching/upgrades, configuration changes that need DB restart etc.
  • Easy Read-Write load distribution.
  • Automatic Failover.
  • Easy to setup and manage.

Load Balancer is a set of integrated software components that provide for balancing IP traffic across a set of real servers. It consists of two main technologies to monitor cluster members and cluster services: Keepalived and HAProxy.

Keepalived: It uses Linux virtual server (LVS) to perform load balancing and failover tasks on.

HAProxy: It performs load balancing and high-availability services to TCP and HTTP applications.

xinetd: “Extended Internet daemon” is an open-source super-server daemon which runs on many Unix-like systems and manages Internet-based connectivity.xinetd runs constantly and listens on all ports for the services it manages. When a connection request arrives for one of its managed services, xinetd starts up the appropriate server for that service.

VIP:  Virtual IP addresses (or VIPs) allow you to use multiple IPs on a single physical network interface.

Keepalive Configuration:

Load Balancer #1 Configuration

global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr1@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}
vrrp_instance VRRP1 {
    state MASTER
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 200
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

Load Balancer #2 Configuration


global_defs {
   notification_email {
     mysql-user@mydomain.com
   }
   notification_email_from svr2@mydomain.com
   smtp_server localhost
   smtp_connect_timeout 30
}

vrrp_instance VRRP1 {
    state BACKUP
#   Specify the network interface to which the virtual address is assigned
    interface eth0
#   The virtual router ID must be unique to each VRRP instance that you define
    virtual_router_id 71
#   Set the value of priority higher on the master server than on a backup server
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1066
    }
virtual_ipaddress {
192.168.2.111
192.168.2.112
}
}

HAProxy Configuration on Load balancer #1 and #2 :


  global
        daemon
        maxconn 256

    defaults
        mode http
        timeout connect 5000ms
        timeout client 50000ms
        timeout server 50000ms

# MYSQL Configuration 

listen write_connections 192.168.2.111:3306
mode tcp
balance roundrobin
option httpchk
server mysql_db1 192.168.2.105:3306 check port 9200
server mysql_db2 192.168.2.106:3306 check port 9200 backup 

listen read_connections 192.168.2.112:3306
mode tcp
balance leastconn
option httpchk
server mysql_db3 192.168.2.107:3306 check port 9200 

Note: Add all IP and hostname entries in /etc/hosts file.

xinetd configuration on all mysql server:

/etc/xinetd.d/mysqlchk

# default: on
# description: mysqlchk
service mysqlchk
{
flags = REUSE
socket_type = stream
port = 9200
wait = no
user = nobody
server = /opt/mysqlchk
log_on_failure += USERID
disable = no
only_from = 0.0.0.0/0
per_source = UNLIMITED

NOTE: Create following script on all mysql server to monitor mysql status.
mysqlchk.stauts script [/opt/mysqlchk.stauts] : The purpose of this script is make haproxy capable of monitoring mysql properly


# It is recommended that a low-privileged-mysql user is created to be used by
# this script. Something like this:
#mysql> CREATE USER IF NOT EXISTS 'mysqlchkuser'@'localhost' IDENTIFIED BY 'P@ssword#567';
# mysql> GRANT show databases on *.* TO 'mysqlchkuser'@'localhost';
# mysql> flush privileges;

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USERNAME="mysqlchkusr"
MYSQL_PASSWORD="P@ssword#567"

TMP_FILE="/opt/tmp/mysqlchk.out"
ERR_FILE="/opt/tmp/mysqlchk.err"

#
# We perform a simple query that should return a few results.
#
mysql --host=$MYSQL_HOST --port=$MYSQL_PORT --user=$MYSQL_USERNAME --password=$MYSQL_PASSWORD -e"show databases;" > $TMP_FILE 2> $ERR_FILE

#
# Check the output. If it is not empty then everything is fine and we return
# something. Else, we just do not return anything.
#
if [ "$(/bin/cat $TMP_FILE)" != "" ]
then
    # mysql is fine, return http 200
    /bin/echo -e "HTTP/1.1 200 OKrn"
    /bin/echo -e "Content-Type: Content-Type: text/plainrn"
    /bin/echo -e "rn"
    /bin/echo -e "MySQL is running.rn"
    /bin/echo -e "rn"
else
    # mysql is fine, return http 503
    /bin/echo -e "HTTP/1.1 503 Service Unavailablern"
    /bin/echo -e "Content-Type: Content-Type: text/plainrn"
    /bin/echo -e "rn"
    /bin/echo -e "MySQL is *down*.rn"
    /bin/echo -e "rn"
fi

Testing:

  • Test MySQL connection with VIP address.
  • Stop MySQL Active MASTER, and connect to MySQL.It should connect to backup MySQL server specified in HAProxy configuration.
  • Test Keepalived and HAProxy failover.

All Set!!

Advertisements

Basics of MySQL Administration and best practices

Following are the few best practices and basic commands for MySQL Administration.

MySQL Access and credential security

shell> mysql -u testuser -pMyP@ss0rd
mysql: [Warning] Using a password on the command line interface can be insecure.

By looking at OS cmd’s history using history cmd other os users can see/get MySQL user password easily. It always good to not use a password on the command line interface. Another option for securing password while automating MySQL scripts is a use of mysql_config_editor. For more info on this check out my blog post about credential security.

Consider of having following implementation for Strong access policy.

  • use of  validate_password plugin for a strong password policy.
  • Limit the user access by specifying IP or IP range in a hostname.
  • Do not grant accessive privileges to user/s.
  • Have separate users for different operations like backup user with required backup privileges only.
  • Avoid giving FILE and super privileges to remote users.
  • For public network communication between client and server use SSL connection method.

Replication

  •  IF EXISTS and IF NOT EXISTS use while creating DB objects.

Most common problem for replication break or errors is that OBJECT already exists on SLAVE. By using IF EXISTS and IF NOT EXISTS while creating database objects we can avoid.

  • Use of GTID and crash-safe replication.
  • Keep your slave in read-only mode.
  • Run your backups and query optimization on SLAVE. This will avoid unnecessary load on MASTER.

Logging

Logs are great significance for admin. Following types of logs, you can enable for MySQL servers.

  • Binary log: Extra copy of your database transactions.
  • Relay log:  By default enable and get created when you setup replication.
  • General log: To log MySQL client tool commands.
  • Slow query log: Log slow queries taking more time for execution.
  • Error / MySQL server log: Record NOTES, WARNINGS and ERROR for MySQL server.
  • Audit Log:  Log user info and activities like executes queries by a user/s along with source IP, timestamp, target database etc.
  • To maintain these logs like purging OLD logs using logrotate. Check MYSQL SERVER LOG MAINTENANCE for more info.

 MySQL STARTUP- SHUTDOWN

Always check MySQL error log for STARTUP- SHUTDOWN and make sure for clean STARTUP/ SHUTDOWN.

Basic commands for MySQL Administration

MySQL database and table creation

CREATE IF NOT EXISTS DATABASE:

CREATE IF NOT EXISTS  DATABASE test_db ;
Use test_db ;

CREATE TABLE:

CREATE IF NOT EXISTS TABLE t1 (id int(11) primary key auto_incremnet ,uname varchar(50), comments text);

INSERT INTO TABLE:

INSERT INTO t1 (id, uname, comments) VALUES(101,’lalit’,’mysql DBA’);

MySQL Database Users

CREATE USER:

  1. The CREATE USER statement creates new MySQL accounts.
CREATE USER IF NOT EXISTS 'local_user1'@'localhost' IDENTIFIED BY 'mypass'; (Remote connection restricted for this user)

If you specify only a username part of the account name, a host name part of ‘%’ is used.

CREATE USER IF NOT EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';

(Remote connection enabled for this user)

  1. User details are getting stored under user table.
SELECT user,host FROM mysql.user;

 

RENAME USER:

RENAME USER 'abc'@'localhost' TO 'xyz'@'%';

DROP USER:

 DROP IF EXISTS USER 'remote_user1'@'%’;

User password management:

  1. Change/Update user password.
ALETR USER IF EXISTS 'remote_user1'@'%' IDENTIFIED BY 'mypass';
  1. Password expire user account
ALTER USER IF EXISTS 'remote_user1'@'%' PASSWORD EXPIRE;
  1. Locked User account
ALTER USER IF EXISTS 'remote_user1'@'%' ACCOUNT LOCK;

 

MySQL Database Users Access Restrictions using privileges.

Grant privileges to a user:

Privileges can be granted on database/s, table/s and related objects to it.

Example.

Case1:  Grant all privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT ALL PRIVILEGES ON db1.* TO 'remote_user1'@'%';

Case2: Grant selected privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'remote_user1'@'%';

Case3. Grant SELECT privilege single table access to user ‘remote_user1’@’%’

GRANT SELECT ON db1.table1 TO 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/grant.html

Revoking privileges from user:

Example:

REVOKE SELECT, INSERT, UPDATE, DELETE ON db1.* FROM 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Check User Privileges using SHOW GRANTS command:

Example:

SHOW GRANTS FOR 'mysqldba'@'localhost';

SHOW GRANTS; (It will display the privileges granted to the current account)

SHOW GRANTS FOR 'remote_user1'@'%';

Ref: http://dev.mysql.com/doc/refman/5.7/en/show-grants.html

MySQL monitoring

Check database size:

Information_schema (Metadata)

SELECT table_schema "Data Base Name",    sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",    sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLESGROUP BY table_schema ;

Check Active users:

show processlist ;

InnoDB Engine Status:

SHOW STATUS;

SHOW ENGINE INNODB STATUS;
  1. Performance schema: Live statistics

Example:

– Enable Locking related instruments (if it’s not enabled):

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

  1. MySQL Enterprise monitor
  2. Customized scripts

Check Database objects info:

Databases:

SHOW DATABASES;

Select Database:

Use db_name;

Tables in Database:

SHOW TABLES;

SELECT TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = 'test_db';

ROUTINES:

select * from ROUTINES where ROUTINE_SCHEMA='db_name’;

INDEX:

select TABLE_NAME,INDEX_NAME,COLUMN_NAME,INDEX_TYPE  from information_schema.STATISTICS where TABLE_SCHEMA = 'db_name';

View:

select * from information_schema.VIEWS where TABLE_SCHEMA = 'db_name';

Mysqldump  Backup-Restore:

Require privileges: mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Backup:

Full Database backup:

mysqldump -u root  -p --single-transaction --databases db1  --routines > db1_fullbkp.sql

 OR

mysqldump -u root  -p --single-transaction  --databases db1 --routines | gzip >  db1_fullbkp.sql.gz

 

Single table backup:

mysqldump -u  -h  -p --single-transaction db_name table_name --routines > db1_full.sql

Ref : http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Restore:

To reload a dump file, you must have the privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

mysql -u username -p db_name < db1_fullbkp.sql

OR

gunzip < db1_fullbkp.sql.gz | mysql -u username -p db_name

MySQL Replication:

  1. Create replication user on MASTER with replication privileges.
CREATE USER [IF NOT EXISTS] 'rpluser'@'%' IDENTIFIED BY 'rpluser1234';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rpluser'@'%';
  1. On SLAVE: setup replication as follows:
CHANGE MASTER TO MASTER_HOST='<MASTER_IP',MASTER_USER='rpluser',MASTER_PASSWORD='rpluser1234',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
  1. Start slave
START SLAVE;
  1. Check slave status
SHOW SLAVE STATUS;

Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’

MYSQL service [Linux]

MySQL SHUTDOWN steps:

shell> sudo service mysqld stop

MySQL STARTUP steps:

shell> sudo service mysqld start

All Set !!

MySQL Server log Maintenance

As a part database administration, DBA has to take care of sub-components of database like server logs and has to plan for maintenance activity for these components regularly.

MySQL has various types of log i.e binary log, error log, slow query log, general log for different purposes. And after certain time these logs will grow and you will start seeing issues like low disk space, a large number of logs etc.

MySQL allows you to flush logs using flush logs command, next “How to rotate and clean up old MySQL logs? ”

Linux has a utility called “logrotate” , using logrotate we can implement log rotation for MySQL server logs.

Binary logs: This one is critical if you have replication setup, By enabling  expire_logs_days mysql variable you can manage cleanup and flush logs cmd will rotate binary log.

For General and Slow query that’s not the case, “flush log” cmd will flush the content from memory to respective log files, but it will not rotate these logs. logrotate by default configured and managed with OS root user.On a Linux (Red Hat) installation, you can use the mysql-log-rotate script for this. If you installed MySQL from an RPM distribution, this script should have been installed automatically. It kind of sample script for full implementation, let’s create a separate mysql-log-rotate script.

Prerequisites:

USER and Privileges:

CREATE USER  'logadmin'@'localhost' IDENTIFIED BY 'xyzpwd';
GRANT RELOAD ON *.* TO 'logadmin'@'localhost';

Secure user credentials using mysql_config_editor:

shell> mysql_config_editor set --login-path=logadmin_client --host=localhost --user=monitor --password                                                                                     

Enter password:<enter_mysql_logadmin_user_password>

NOTE: It will store user credentials info into .mylogin.cnf (This conf file will be get created under current OS user home directory)

mysql-log-rotate script

/PATH/log/mysqld.log /PATH/log/slow-query.log /PATH/log/general-query.log {
create 640 mysql mysql
rotate 5
daily
minsize 1M
notifempty
missingok
compress
postrotate
# just if mysqld is really running
if test -x /usr/bin/mysqladmin
/usr/bin/mysqladmin --login-path=logadmin_client ping >/dev/null
then
/usr/bin/mysqladmin --login-path=logadmin_client flush-logs
fi
endscript
}

NOTE: Above script will flush logs 3 times since we have 3 logs in one code block.To flush log only at once you can create separate rotate code block for each log and add postrotate script only in the last rotation code block.

Automation:

00 03 * * * /usr/sbin/logrotate -s /PATH/log/logrotate.status /PATH/monitor/mysql-log-rotate.sh > /PATH/log/logrotate_cron.log 2>&1

Key points:

  • You can set rotation on the basis of SIZE, TIME or both. Explore logrotate option for more options.
  • -s /PATH/log/logrotate.status file will get create/update with log name and timestamp, Which will get use for next rotation on the basis of filename and timestamp it has.
  • -f, --force
    Tells logrotate to force the rotation, even if it doesn’t think
    this is necessary. Sometimes this is useful after adding new
    entries to logrotate, or if old log files have been removed by
    hand, as the new files will be created, and logging will continue correctly.

All Set !!

MySQL script automation and security

After MySQL installation, If you don’t have any enterprise level / any GUI interface for monitoring, backup then one of the option is, write your own scripts to automate these tasks.
In this Blog post, we are going to see few monitoring and backup scripts with covering common security issues.

Credential security

Following is a simple script, which will monitor MySQL service. In case MySQL service [mysqld] is down, then it will send email alert.

#!/bin/sh

# Connection details
MYSQL_USER="monitor"
MYSQL_PASS="lemon_pwd@123" ----> [# Plain text password, Security thread]
MYSQL_HOST="localhost"
SERVER_HOST=$( hostname )
EMAIL=user1@xyz.com,user2@xyz.com

# MySQL status
mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "MySQL database is down " | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL"
fi
# END!!

The Problem with above script is, it has mysql user credentials info in plain text. We should store mysql user credentials to somewhere safe in encrypted file.

mysql_config_editor and Credential security

The mysql_config_editor utility enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf.

1. Create a user for monitoring with required privileges.

CREATE USER IF NOT EXISTS 'monitor'@'localhost' IDENTIFIED BY 'strong_pwd'; 

GRANT SELECT, RELOAD, PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

2. Store user credentials info into .mylogin.cnf (This conf file will get created under current OS user home directory)

shell> mysql_config_editor set --login-path=monitor_client --host=localhost --user=monitor --password                                                                                     

Enter password:<enter_mysql_monitor_user_password>

3. Verify the file contains

shell> mysql_config_editor print --login-path=monitor_client
[monitor_client]
user = monitor
password = *****
host = localhost

4. Implementation and use.

MySQL access with credentials:

 
shell> mysql -u monitor -h localhost -p  
  

MySQL access with mysql_config_editor login_path:

shell> mysql --login-path=minitor_client

Monitoring scripts

Shell script for MySQL service and replication monitoring.


#!/bin/bash

SERVER_HOST=$( hostname )
EMAIL=user1@xyz.com,user2@xyz.com

# MySQL service monitoring
mysqladmin --login-path=monitor_client ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
echo "MySQL database is down " | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL"
fi

# Replication monitoring 

for MYSQL_HOST in localhost
do
  MSG1=`/usr/bin/mysql --login-path=monitor_client -e "show slave status\G;" | grep 'Slave_IO_Running:'`
  OUTPUT=(${MSG1//:/ })
  STATUS1=`echo ${OUTPUT[1]}`
  MSG2=`/usr/bin/mysql --login-path=monitor_client -e "show slave status\G;" | grep 'Slave_SQL_Running:'`
  OUTPUT=(${MSG1//:/ })
  STATUS2=`echo ${OUTPUT[1]}`
  if [ "$STATUS1" == "Yes" ] && [ "$STATUS2" == "Yes" ];
  then
    echo "$MYSQL_HOST = $STATUS1 - $STATUS2"
  else
    echo "$SERVER_HOST replication not working"
    mail -s "Replication DOWN - $SERVER_HOST" "$EMAIL" <<EOF
Please check $SERVER_HOST database replication.
EOF
  fi
done
# End!!

Backup scripts

1. Create a user for backup with required privileges.

CREATE USER IF NOT EXISTS 'backup'@'localhost' IDENTIFIED BY 'strong_pwd'; 

GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES,SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

2. Store backup user credentials info into .mylogin.cnf (This conf file will get created under current OS user home directory)

shell> mysql_config_editor set --login-path=backup_client --host=localhost --user=backup --password                                                                                          

Enter password:<enter_mysql_backup_user_password>

3. Backup script


#!/bin/bash

NOW="$(date +"%d-%m-%Y")"
BKP_DIR="/backups/full_backups/$NOW/"
SERVER_HOST=$( hostname )

mkdir -p "$BKP_DIR"
touch "$BKP_DIR/backup.log"

echo "Dumping MySQL databases into separate SQL command files into dir=$BKP_DIR" >> $BKP_DIR/backup.log

db_count=0

for d in $(mysql -NBA --login-path=backup_client -e 'show databases')
do
   if [[ "$d" != information_schema ]] && [[ "$d" != mysql ]] && [[ "$d" !=  performance_schema ]] && [[ "$d" !=  sys ]]; then
    (( db_count++ ))
   echo "DUMPING DATABASE: $d " >> $BKP_DIR/backup.log
mysqldump --login-path=backup_client --single-transaction  $d | gzip > $BKP_DIR/$d.sql.gz
echo "Dumping --triggers --routines --events for databases $d into dir=$BKP_DIR" >> $BKP_DIR/backup.log
mysqldump --login-path=backup_client --triggers --routines --events --no-create-info --no-data --no-create-db --skip-opt $d | gzip > $BKP_DIR/$d-routines.sql.gz
  fi
done

echo "$db_count databases dumped into dir=$BKP_DIR" >> $BKP_DIR/backup.log

find /apps/backups/full_backups/ -type d -ctime +6 -exec rm -rf {} \;

echo "OLDER than 6 days BACKUPS deleted successfully" >> $BKP_DIR/backup.log

# End!!

Automate these scripts runs and All set!!

MySQL service : Unable to setup unix socket lock file.

How to solve mysqld service restart problem for above error?

Problem :
I was adding shell and home directory for mysql user,executed following cmd,

shell> usermod -m -d /home/mysql -s /bin/bash mysql

If mysql is running and process running with mysql , we need to stop mysql otherwise it will throw an error like usermod: user mysql is currently used by process 27768

After stopping MySQL service and adding shell and homedir for mysql user, at the time mysqld service startup it started throwing error.


shell> service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
Shell> systemctl status mysqld.service

● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: deactivating (final-sigterm) (Result: exit-code) since Wed 2016-12-14 22:16:42 MST; 7min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 35222 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 35204 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 27768 (code=exited, status=0/SUCCESS)
CGroup: /system.slice/mysqld.service
└─35226 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

mysqld error logs:

[ERROR] Could not create unix socket lock file /var/lib/mysql/mysql.sock.lock.
[ERROR] Unable to setup unix socket lock file.
[ERROR] Aborting

Tried to stop, kill mysqld service but still it’s not going out from process list.

Root cause : Suspecting change in process id after modifying mysql user properties.

Solution:

Just Move/Rename your my.cnf and start mysqld service with default configuration.You will see no more error at the time for service startup.
Move backuped up /renamed original my.cnf and restart mysqld service again.

service mysqld restart

And should start working fine, as it is.

ALl set!!

MySQL 5.7 and administration

MySQL 5.7 improved as compare to previous releases in terms of transnational capabilities, performance with high load, high Availability, Security and it’s defaults.

Check my blog post : MySQL 5.7 features

This blog post will describe End to End implementation of MySQL on Linux distributions Which will cover MySQL Installation, configuration and administration in production environment with proper configuration. So you can start using your application by implementing following setup and in future you can change it if requires.

Database Installation:

There are number of ways to install MySQL,

  • Source code
  • Binaries : Compressed tar file binary distribution
  • Packages :  RPM-based Linux distributions
  • MySQL Installer MSI and ZIP Archive
  • Yum repository

MySQL installation using packages is one the easy way to install MySQL and you don’t have to worry about installation configuration part.Another option is installing MySQL using compressed tar file, with this installation method user has to perform installation and most of the configuration part.

On Windows : MySQL Installer MSI will take care of everything including installation of supporting monitoring tool and utilities, MySQL configuration settings and user management

Let’s Install MySQL RPM packages.For standard installation, we will install mysql-community-servermysql-community-clientmysql-community-libsmysql-community-common, and mysql-community-libs-compat packages.

Always use new version of MySQL GA release for new installations.

MySQL Installation steps:

1. Download RPM package from for your OS architecture : http://dev.mysql.com/downloads/mysql

2. Run following command to install MySQL.

sudo yum install mysql-community-{server,client,common,libs}-*

The installation also creates a user named mysql and a group named mysql on the system.

3. Default configuration file location /etc/my.cnf file.

4. Standard directory structure for MySQL:

A standard installation of MySQL using the RPM packages result in files and resources created under the system directories, shown in the following table.

mysql-rpm

MySQL basedir will have default distributed dir structure, except datadir. It is recommended to keep datadir in a different location for numerous reasons.For, this you just need to update ‘datadir’  variable value with new datadir location

5. MySQL configuration file: /etc/my.cnf

By, default my.cnf will get created by MySQL rpm installation with default configuration in it. We need to add few more configuration variables in order to make MySQL DB server ready for production use. Following are the standard configuration settings for a production database. These variable values may vary as per the application scope and data workload.

MySQL configuration file sections – MySQL configuration file have many sessions, such as [mysqld], [mysql], [client], [mysqld_safe] , [mysqldump] and so on.

  • Add/update following variables to appropriate configuration section of my.cnf.
[mysqld]

#GENERAL
user = mysql
port = 3306
server_id=152
skip-name-resolve
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysql/mysqld.pid

# DATA STORAGE
datadir =/var/lib/mysql/data

#INNODB
innodb_file_per_table=1
innodb_buffer_pool_size = 4000M (60-70 % of RAM memory)
innodb_data_file_path= ibdata1:1G;ibdata2:500M:autoextend
innodb-log-files-in-group=3
innodb_flush_method = O_DIRECT

#Logging
log_error = /var/log/mysql/mysqld.log
master_info_repository = TABLE
relay_log_info_repository = TABLE
log-bin=mysql-bin152
relay-log=relay-bin152
relay_log_recovery=on
log-slave-updates= 1
expire_logs_days = 7
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row

[mysql]
socket = /var/lib/mysql/mysql.sock

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

6. Start MySQL service

sudo service mysqld start

7. At the initial start-up of the server, the following happens, when MySQL data directory is empty:

  • The server is initialized.
  • An SSL certificate and key files are generated in the data directory.
  • Thevalidate_password plugin is installed and enabled.
  • A superuser account’root’@’localhost’ is created. A password for the superuser is set and stored in the error log file.
  • Look for root password in error log file.
sudo grep 'temporary password' /var/log/mysqld.log

8. After login first time into MySQL , we can not proceed further without resetting root password.

shell> mysql –uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'r00t$PWD';

For any startup issue  check  /var/log/mysqld.log 

MySQL Variables and Configuration:

MySQL Variables
User mysql service user
Server-id Value : 1 default

Any number in DB group

Port Value: 3306 default
Skip-name-resolve Do not resolve host names when checking client connections. Use only IP addresses.
bind_address MySQL bind_address for network interfaces.

IPv4 : 0.0.0.0

IPv4 and IPv6 : *

Socket Unix socket file for listening local connections
Pid-file The path name of the process ID file.
default_storage_engine Default storage engine for MySQL

Value: Innodb

Datadir Main directory where database,system tablespace and log files will get store.
innodb_file_per_table Seperate tablespace for each table.Good for performance and reclaiming free space.

Value : on

Innodb_buffer_pool_size Value should be 60-70 percent of RAM memory of server
innodb_log_file_size Redo and undo logs ,useful for innodb recovery.

Value should be greater if you are using BLOB datatype in your database.

Value: innodb_log_file_size=150M

innodb_log_files_in_group Number for innodb_log_file

Value : 3

innodb_data_file_path= System tablespace configuration

Value:ibdata1:1G;ibdata2:1G:autoextend (vary)

innodb_flush_method Method used to flush data to the InnoDB data files and log files.

value : O_DIRECT

innodb_tmpdir tmp directory for ONLINE ALTER operations.
log_error mysql server log
log-bin Binary log file name

Value : mysql-bin152

binlog_format binary log formate for data

Value:row

master_info_repository

relay_log_info_repository

crash-safe replication settings, storing log info in table instead of file.

Value:  TABLE

relay-log relay log name

relay-log=relay-bin152

relay_log_recovery relay_log_recovery= on
log-slave-updates log-slave-updates=1
expire_logs_days Auto delete binary logs after mentioned days

expire_logs_days= 60

gtid-mode Enable GTID for transactions

Value : on

enforce-gtid-consistency Value : on

MySQL User Management

MySQL Database Users

CREATE USER :

CREATE USER [IF NOT EXISTS] 'local_user1'@'localhost' IDENTIFIED BY 'usr#PWD';

(Remote connection restricted for this user)

If you specify only the username part of the account name, a host name part of ‘%’ is used.

CREATE USER [IF NOT EXISTS] 'remote_user1'@'%' IDENTIFIED BY 'usr#PWD';

(Remote connection enabled for this user)

-User details getting stored under mysql.user table.

RENAME USER:

RENAME USER 'abc'@'localhost' TO 'xyz'@'%';

User password management:

  1. Change/Update user password. [IF EXISTS] -Optional
ALETR USER [IF EXISTS] 'remote_user1'@'%' IDENTIFIED BY 'usr#PWD';
  1. Password expire user account
ALETR USER[IF EXISTS] 'remote_user1'@'%' PASSWORD EXPIRE;
  1. Locked User account
ALTER USER [IF EXISTS] 'remote_user1'@'%' ACCOUNT LOCK;

DROP USER:

DROP USER 'remote_user1'@'%’;

MySQL Database Users Access Restrictions using privileges

Grant privileges to user:

Privileges can be granted on database and table only.

Examples.

Case1:  Grant all privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT ALL ON db1.* TO 'remote_user1'@'%';

Case2: Grant selected privileges on ‘db1’ database to user ‘remote_user1’@’%’

GRANT SELECT, INSERT, UPDATE, DELETE ON db1.* TO 'remote_user1'@'%';

Case3. Grant SELECT privilege single table access to user ‘remote_user1’@’%’

GRANT SELECT ON db1.table1 TO 'remote_user1'@'%';

Revoking privileges from user:

Example:

REVOKE SELECT, INSERT, UPDATE, DELETE ON db1.* FROM 'remote_user1'@'%';

Check User Privileges using SHOW GRANTS command:

Example:

SHOW GRANTS FOR 'root'@'localhost';
SHOW GRANTS; (It will display the privileges granted to the current account)
SHOW GRANTS FOR 'remote_user1'@'%';

MySQL DB Backup and Restore:

Logical backup

The mysqldump client utility performs logical backups, which create a pain file with sql statements in it.

System database backup not required, so while taking backup only take backup of non-system database i.e. application databases

Require privileges for mysqldump :  SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER

Full Database backup:

mysqldump -u root -h db_host -p --single-transaction --databases db1 --routines --events > db1_fullbkp.sql

 OR

mysqldump -u root -h db_host -p --single-transaction --databases db1 --routines --events | gzip > db1_fullbkp.sql.gz

Single table backup:

mysqldump -u db_username -h db_host -p --single-transaction db_name table_name > db1_full.sql

Restore:

To reload a dump file, you must have the privileges required to execute the statements that it contains.

mysql -u username -p db_name < db1_fullbkp.sql

OR

gunzip < db1_fullbkp.sql.gz | mysql -u username -p db_name

Note:  –single-transaction option for consistent non-blocking backup.

For InnoDB tables, it is possible to perform an online backup that takes no locks on tables using the –single-transaction option to mysqldump.

  • Create separate backup user with require backup privileges.
  • Schedule the backup script in crontab of mysql UNIX account.

Physical/Binary backup:

MySQL Enterprise Backup

Percona XtraBackup

Note: Binary backup mostly use for backing up large volume database.For small volume database use mysqldump.

MySQL DB Monitoring:

1. MySQL enterprise monitor

2.  MySQL workbench GUI tool.

3. Script automation : Write scripts with required monitoring command and automate this scripts using cronjobs. Email notification also can be added in this script for critical alerts.

– Create a separate user for monitoring with required privileges.

CREATE USER [IF NOT EXISTS] 'monitor'@'localhost' IDENTIFIED BY 'mon$pwd';
GRANT SELECT,PROCESS ON *.* TO 'monitor'@'localhost';

–  Create monitoring schedule it with cronjob for automatic runs.

Sample script To check server status:

#!/bin/sh

EMAIL_IDS=user1@abc.com,user2@abc.com

# Connection details

MYSQL_USER=monitor

MYSQL_PASS=mon$pwd

MYSQL_HOST=localhost

MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST}"

SERVER_HOST=$( hostname )

# MySQL status

mysqladmin ${MYSQL_CONN} ping 2>/dev/null 1>/dev/null

if [ $? -ne 0 ]; then

echo "MySQL Down" | mail -s " MySQL not running on $SERVER_HOST" "$EMAIL_IDS"

fi

NOTE: There are many other mysql monitoring command you can add in this script.

Replication :

We can setup relication using binlog and binlog position and other one is GTID based replication.Will setup GTID Replication which is new and more relable.

  1. Enable binary log and GTID on both master and slave.
  2. Create a replication user on MASTER as follow:
CREATE USER 'rpluser'@'%' IDENTIFIED BY 'rpluser1234';
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'rpluser'@'%';
FLUSH PRIVILEGES;

3. Setup replication on slave using CHANGE MASTER TO cmd as follow:

CHANGE MASTER TO MASTER_HOST='',MASTER_USER='rpluser',MASTER_PASSWORD='rpluser1234',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;

4.  Start replication and check slave status.

START SLAVE;
SHOW SLAVE STATUS;

5 .  Slave_IO_Running and Slave_SQL_Running column value should be ‘YES’.

Check My Blog post : GTID Replication and troubleshooting

All set !!

linux swap and mysql memory

After mysql installation, To make mysql server production ready we have to tune default mysql variables considering the hardware, production load, performance, durability etc.

But What about the optimizing  Linux OS settings  with mysql ?

mysql-linux1

Here it is, i encountered  an issue once when i was checking TOP output for mysqld process and found that the mysqld  memory continuously increasing. Also there was one more thing  that it was using swap memory even if there was enough RAM memory available. we have monitored for a week and this was causing an issue like slow query processing, performance and slowness on DB server.

To solve this problem we have to set correct swappiness

  •       To check vm.swappiness

                    cat /proc/sys/vm/swappiness    (Default value : 60 (Range 0 to 100))

  •    To set a new non-persistent value :

                     sysctl -w vm.swappiness=0

  •    To set a new persistent value :

                     add vm.swappiness=0 in the /etc/sysctl.conf file

All Set !!

About SWAP space:

The swappiness parameter controls the tendency of the kernel to move processes out of physical memory and onto the swap disk. Because disks are much slower than RAM, this can lead to slower response times for system and applications if processes are too aggressively moved out of memory.

  1. swappiness can have a value of between 0 and 100
  2. swappiness=0 tells the kernel to avoid swapping processes out of physical memory for as long as possible
  3. swappiness=100 tells the kernel to aggressively swap processes out of physical memory and move them to swap cache

Following are basic instructions for checking swappiness, emptying your swap and changing the swappiness to 0:

To check memory usage status :

          free

To check the swappiness value:

         cat /proc/sys/vm/swappiness

To turn swapoff :
This will empty your swap and transfer all the swap back into memory. First make sure you have enough memory available by viewing the resources tab of gnome-system-monitor, your free memory should be greater than your used swap. This process may take a while, use gnome-system-monitor to monitor and verify the progress.

sudo swapoff -a

To set the new value to 0:

echo 0 | sudo tee /proc/sys/vm/swappiness

To turn swap back on:

sudo swapon -a