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 …

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 …

MySQL is available for a huge variety of platforms, in a wide range of packaging formats and through many channels. Part of our mission is to keep up with the times by making sure that all MySQL products are available where people expect and need them to be. As part of that we have invested […]

CentOS 7 Systemd issue with Percona Cluster is that SST fails to sync the nodes, unable to join cluster group and giving the misleading broken pipe 32 SIG errors.

The post How to Resolve Systemd Issue with Percona XtraDB Cluster on CentOS 7 appeared first on Datavail.

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
• mysql_config_editor and Credential security
• Monitoring scripts
• backup scripts

Credential security

Following is a simple script, which will monitor MySQL service. In case MySQL …

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 …

Over the recent years, there has been a large number of individuals as well as organizations who are ditching the Windows platform for Linux platform, and this number will continue to grow as more developments in Linux are experienced. Linux has for long been the leader in Web servers as most of the web servers run on Linux, and this could be one of the reasons why the high migration is being experienced.

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
• MySQL Variables and Configuration
• …

In this blog we are going to share my recent experience with an issue which we faced during migration between two Ubuntu servers.

The activity was pretty simple, the client had bought a new machine with enhanced memory and faster disk, so the data has to be transferred to the new server and live MySQL replication has to be configured between the old and the new server.

For the hot and live data transfer we used xtrabackup’s stream method, the data transfer happened smoothly without any issue.

Everything was set and we had applied the log and followed the normal procedure to change the ownership of datadir to “mysql” and all the entries in the my.cnf were double checked.

When we intended to start mysql, the service didn’t come up instead we had a weird error as below.

MySQL Error:
——————

2016-08-31 22:35:27 9938 [ERROR] InnoDB: ./ibdata1 can't be opened in …

The behavior of vm.swappiness was always a bit confusing for novice linux users, as setting vm.swappiness to 0 would not completely disable swapping in the system during a memory crunch. vm.swappiness would only affect the agressiveness of swapping.

Following upstream commit tried to give more control to parameter. This commit tried to avoid swapping completely when vm.swappiness is set to 0.

commit fe35004fbf9eaf67482b074a2e032abb9c89b1dd
Author: Satoru Moriya
Date: Tue May 29 15:06:47 2012 -0700
mm: avoid swapping out with swappiness==0

With above commit, setting vm.swappiness to “0” instructs the kernel not to initiate swapping until the amount of free and file-backed pages is less than the high water mark in a memory zone. In other words, it tries to reclaim as much memory that can be reclaimed, before swapping starts.
This greatly reduced the chances of swapping.

When this …