In this blog post, I’ll look at the types of NVMe flash health information you can get from using the NVMe command line tools.

Checking SATA-based drive health is easy. Whether it’s an SSD or older spinning drive, you can use the

smartctl

 command to get a wealth of information about the device’s performance and health. As an example:

root@blinky:/var/lib/mysql# smartctl -A /dev/sda
smartctl 6.5 2016-01-24 r4214 [x86_64-linux-4.4.0-62-generic] (local build)
Copyright (C) 2002-16, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF READ SMART DATA SECTION ===
SMART Attributes Data Structure revision number: 16
Vendor Specific SMART Attributes with Thresholds:
ID# ATTRIBUTE_NAME          FLAG     VALUE WORST THRESH TYPE      UPDATED  WHEN_FAILED RAW_VALUE
 1 Raw_Read_Error_Rate     0x002f   100   100   000 …

I was working on a seemingly basic query the other day where the user needed to have an INT column listed in ascending order (i.e., 1, 2, 3, …).

However, the tricky part came in because the column allowed NULLs and the user needed the NULLs to be listed last, not first, which is the default behavior in both MariaDB and MySQL.

We first devised a somewhat convoluted solution where we used ISNULL() first in the ORDER BY, and then the column, but that wasn’t ideal since it added an additional check for each row in the ORDER BY, which we wanted to avoid in a query returning ~5M rows.

To illustrate, a normal query just sorting in ASC order returned:

MariaDB> SELECT * FROM t1 ORDER BY col1 ASC, col3 ASC;
+--------+--------+------+
| col1   | col2   | col3 |
+--------+--------+------+
| apple  | yellow | NULL |
| apple  | red    |    5 |
| apple  | green  |   10 |
| banana | brown  | NULL |
| banana | green  |    5 |
| …

MySQL replication setups can take different shapes. The main topology is probably a simple master-slave setup. But it is also possible to construct more elaborate setups with multiple masters and chained setups. ClusterControl 1.4 takes advantage of this flexibility and gives you possibility to deploy multimaster setups. In this blog post, we will look at a couple of different setups and how they would be used in real-life situations.

New Deployment Wizard

First of all, let’s take a look at the new deployment wizard in ClusterControl 1.4. It starts with SSH configuration: user, path to ssh key and whether you use sudo or not.

Next, we pick a vendor and version, data directory, port, configuration template, password for root user and, finally, from which repository ClusterControl should install the software.

Then, the third and final step to define the topology.

Let’s go through some of these topologies …

Introduction As previously explained, you can run database integration tests 20 times faster! The trick is to map the data directory in memory, and my previous article showed you what changes you need to do when you have a PostgreSQL or MySQL instance on your machine. In this post, I’m going to expand the original … Continue reading How to run integration tests at warp speed using Docker and tmpfs →

Find Table Size :
mysql> SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES               WHERE TABLE_SCHEMA = 'mrsqa4'; +-----------------+ | SUM(TABLE_ROWS) | +-----------------+ |           10655 | +-----------------+ 1 row in set (0.29 sec)
 pc_auditlog               |          1 | | pc_dbobject               |        475 | | pc_dbrconfig              |          2 | | pc_iclass_sig             |       1660 | | pc_namespace              |          8 | | pc_namespace_sequence     |          0 | | pc_ormstate           …

               Capturing MySQL Queries

I know there is mytop, mtop, and innodbtop out there, just google them, but it can be very hard to Freeze The Frame to get every query.  So, here’s a quick and dirty script to catch the queries and see what you users are doing.

#!/bin/sh #./run_show_full_processlist.ksh > SHOW_PROCCESSLIST.doc &

x=1

while [ $x -le 3600 ]                      #Loop for 1 Hour (60sec * 60 = 3600)

do

echo "START TIME"

date

echo "show full processlist;" | mysql –h HOSTNAME -uUSERNAME -pPASSWD

  x=$(( $x + 1 ))

echo "END DATE"

date

sleep …

Display All CPU(s) With the top Command

As you know, the top command displays All CPU(s) so it is difficult to identify if All CPU(s) are pegged.  Interactively, you can toggle top by pressing the Number 1 on the keyboard, however that toggle cannot be scripted.

The work around is to create a .toprc file in your home directory by pressing the W key (capital W) while top is running.  If you are paying attention, you will see a message that the .toprc file was just created.  From there on out, typing top will display All CPU(s).

Below is a script I run to capture top activity during peak loads.  Make sure you have enough disk space!

vi run_top.sh

#!/bin/sh

#Syntax Below

#./run_top.sh > TOP_UNDER_LOAD.doc

x=1

while [ $x -le 4000 ]        #Run 4000 Times

…

Locking User Accounts in MySQL

For SOX Compliance when an employee, contractor, or vendor leaves your company their account needs to be locked, expired, or removed.  Some of these people may return, and their Account may have many complicated permissions.  In these cases, it would be better to “Lock” their Account while they are no longer doing business with your company. 
However, MySQL does not have the ability to “Lock” or “Expire” a User Account.   The following is a simple procedure to “Lock” and “Unlock” a MySQL Account.

Typically, the one-way encrypted hash value of a users password is stored in a password field  in an user account table.  MySQL supports three builtin one-way hashed functions, PASSWORD(), MD5() and SHA1().  It is recommended not to directly use the PASSWORD() function. Reason, is that prior to PHP 4.1 version, the …

MySQL – Script to Kill A User Session

I googled around and found some code that could be used with a few tweaks to kill all users EXCEPT the User(s) you specify.

#vi kill_all_users.sh

#!/bin/bash

connections=$(mysql -h$1 -u$2 -p$3 -e "SHOW PROCESSLIST" |grep -v wmayall| grep -v Id | grep -v root|awk '{print $1}' )

for id in $connections

do

    mysql -h$1 -u$2 -p$3 -e "KILL $id"

done

#chmod 700 kill_all_users.sh

The syntax is:

./kill_all_users.sh  –host –user –password:

Below is an example:

./kill_all_users.sh localhost root my_password

The grep –v excludes the User you do NOT want to kill their session.  
If the User is connected through ODBC, a new …

How to Setup MySQL Replication
Configure the Master Database :
First you will need to configure the my.cnf file.  The my.cnf file is not located in the same place on all servers, so you should use the ps command to list how mysql was started.
[root@hostname repl_setup]$ ps -ef | grep mysql
mysql 6290 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps/mysql/my.cnf --pid-file=/apps/mysql/mysql.pidmysql 6318 6290 0 May18 ? 00:48:49 /usr/sbin/mysqld --defaults-file=/apps/mysql/my.cnf --basedir=/usr --datadir=/apps/mysql --pid-file=/apps/mysql/mysql.pid --skip-external-locking --port=3300 --socket=/apps/mysql/mysql.sockmysql 6473 1 0 May18 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/apps2/mysql/my.cnf --pid-file=/apps2/mysql/mysql.pidmysql 6501 6473 0 May18 ? 00:19:18 /usr/sbin/mysqld --defaults-file=/apps2/mysql/my.cnf --basedir=/usr --datadir=/apps2/mysql …