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 :

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 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.

user = mysql
port = 3306
default_storage_engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid_file = /var/run/mysql/

datadir =/var/lib/mysql/data

innodb_buffer_pool_size = 4000M (60-70 % of RAM memory)
innodb_data_file_path= ibdata1:1G;ibdata2:500M:autoextend
innodb_flush_method = O_DIRECT

log_error = /var/log/mysql/mysqld.log
master_info_repository = TABLE
relay_log_info_repository = TABLE
log-slave-updates= 1
expire_logs_days = 7

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

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 :

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




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

Value:  TABLE

relay-log relay log name


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 [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.


(Remote connection enabled for this user)

-User details getting stored under mysql.user table.


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
  1. Locked User account


DROP USER 'remote_user1'@'%’;

MySQL Database Users Access Restrictions using privileges

Grant privileges to user:

Privileges can be granted on database and table only.


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:



Check User Privileges using SHOW GRANTS command:


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


Full Database backup:

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


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


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


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:


# Connection details





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"


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';

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


4.  Start replication and check 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 !!

MySQL 5.7 for production

With the time MySQL as database getting better in terms of High performance , scalability and security.
MySQL 5.7 new features :

As a MySQL user my favorites are from above list:

  •  New options for replication :
    Changing replication filters online including and excluding table/db and enabling GTID transaction online.
  •  InnoDB related changes:
    Online buffer pool resize and many defaults are changed to more secure and optimized values.
  • Security features :
    Improved User authentication like default users, SSL and data encryption with key capabilities in order to secure overall database.
  • Monitoring and analysis statistics :
    Improved performance schema for live transactions analysis which we can not mostly find out with SHOW PROCESSLIST command or Be Using INFORMATION_SCHEMA database tables.
  • All in this very important is “Setting configuration variables dynamically while a server is running.This change will save many downtimes or mysqld service restarts.
  • Optimizer:
    New optimizer changes will be the one doing magic inside for Query performance.

Something New :

  • Multi-source replication
  • Innodb tablespace encryption using key
  • MySQL x-protocol and Document store using JSON datatype capabilities.
  • Optimized and secure defaults settings for initial MySQL database setup.

I believe these are the most imported MySQL database areas used by MySQL user in the production environment.

How many of you using mysql-5.7 in production or planning for implementation in future? Please share your experience with it.

Mysql table locking

Locking is important in many scenarios to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. for example altering table definition online or any kind of table definition changes.locking Mysql provides an option to lock table/s with different types of locks, depends on need.

syntax for lock table:

    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...



Following are the examples for READ and WRITE LOCK:


session1> create table t1( c1 int);
Query OK, 0 rows affected (0.06 sec)

session1> insert into test.t1 values(1001);
Query OK, 1 row affected (0.01 sec)

session1> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session1> select count(*) from t1;
| count(*) |
| 1 |
1 row in set (0.00 sec)
session1> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

Session1 acquired READ lock on table t1 explicitly. After applying READ lock on table users can read the table but not write it.

session2> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session2> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

session3> select * from t1;
| c1 |
| 1001 |
1 row in set (0.00 sec)

Multiple sessions can acquire a READ lock on the table at the same time and other sessions can read the table without explicitly acquiring a READ lock.

currently, READ lock s acquired by session1 and session2, both locks need to be unlocked in order to perform a write operation on lock table.

session1> UNLOCK TABLES;
session1> insert into t1 values(1005);

INSERT operation executed from session1  will go in waiting state, since READ lock acquired on table t1 by session2 and not  released yet.

You can see this using:

session3> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 2 | session1 | localhost | test | Query | 89 | Waiting for table metadata lock | insert into test.t1 values(1005) |
| 3 | session3 | localhost | test | Query | 0 | starting | show processlist |
| 4 | session2 | localhost | test | Sleep | 77 | | NULL |
3 rows in set (0.00 sec)

After releasing READ lock by session2 insert operation will execute on t1 table.

session2> UNLOCK TABLES;
session1>  select * from t1;
| c1 |
| 1001 |
| 1005 |
2 rows in set (0.00 sec)


FLUSH TABLE:  Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement. FLUSH TABLE will not work when table acquires READ LOCK.

UNLOCK TABLES: UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.



-The session that holds the lock can read and write the table.

session1> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

session1> insert into test.t1 values(1006);
Query OK, 1 row affected (0.01 sec)

session1> select count(*) from t1;
| c1 |
| 1001 |
| 1005 |
| 1006 |
3 rows in set (0.00 sec)

-Only the session that holds the lock can access the table. No other session can access it until the lock is released.

session2> select count(*) from t1;
session3> insert into test.t1 values(1002);

session1> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 2 | session1 | localhost | test | Query | 0 | starting | show processlist |
| 3 | session2 | localhost | test | Query | 127 | Waiting for table metadata lock | select count(*) from t1 |
| 4 | session3 | localhost | test | Query | 116 | Waiting for table metadata lock | insert into test.t1 values(1002) |
3 rows in set (0.00 sec)

Look into performance_schema.metadata_locks table for more status information on table locks. (Thanks for the hint daniel )

– 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_’;

-Lock requests for the table by other sessions block while the WRITE lock is held.

All set  :) ……

[ERROR] COLLATION ‘utf8_general_ci’ is not valid for CHARACTER SET ‘latin1’

Recently came across the problem where mysql server stop running and refusing to start with an error

The server quit without updating PID file (/var/run/mysqld/

Checked Mysql error log and found that an invalid usage of charset with collation causing problem.

error log:
141017 12:20:41 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
while 15088 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
15088 [ERROR] Aborting

In this case mysqld trying to start  with  character-set-server = latin1 and collation-server = utf8_general_ci, which is not valid.
The following is the right charset value for COLLATION ‘utf8_general_ci’

node1 [localhost] {msandbox} ((none)) > SHOW COLLATION LIKE ‘utf8_general_ci’;


| Collation       | Charset | Id | Default | Compiled | Sortlen |


| utf8_general_ci | utf8    | 33 | Yes     | Yes      |       1 |



Add following options in my.cnf

character-set-server = utf8
collation-server = utf8_general_ci
character-set-client-handshake = false

And start  mysql server.It will start without any error.


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 ?


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 :


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

mysql geographical replication lag

Recently while working on geographical master slave replication we encountered an issue, where in peak hours replication slave lagging.

when we checked SHOW SLAVE STATUS value of Seconds_Behind_Master was jumping from 0 to a random high value, and then back to 0.

We also found this in the processlist of Master,

1366318 rpluser [IP Address]:15120  NULL  Binlog  Dump   9033    Writing to net  NULL

The status “Writing to net” means that MASTER is sending (or trying to send) data to SLAVE.

In a healthy replication, the status would be “Has sent all binlog to slave; waiting for binlog to be updated“.

It usually happens when MASTER-SLAVE geographically distributed in different data-center,network , and slow network.

Here limited network bandwidth was causing a problem , and we needed a solution to compress the transmission between the master and slave databases. MySQL provides a variable, which is dynamic and is set on the slave: slave_compressed_protocol=1

If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).

changes on slave:

show global variables like 'slave_compressed_protocol';   //default OFF

stop slave;

set global slave_compressed_protocol=1;

start slave;

show global variables like 'slave_compressed_protocol';     // ON

Also,  check PROCESSLIST on master and replication user status,