Latest revision as of 15:17, 23 November 2022

Oneliner

Show MySQL-traffic fired from a client

# tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

Mysql processes each second

# mysqladmin  -i 1 --verbose processlist

All grants

# mysql --skip-column-names --batch --execute 'select concat("`",user,"`@`",host,"`") from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'

Or a little nicer:

#!/bin/bash

#
## Written by Lars Timmann <L@rs.Timmann.de> 2017
#

function usage () {
  cat << EOH
Usage: $0 [--all] [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ...
           --help:    This output
--grant-user|--gu:    You can specify this option several times.
                      The <pattern> can be:
                          <user>        : You will get grants on all hosts for this user.
                          @<host>       : You will get grants for all users on this host.
                          <user>@<host> : You will get specific grants for user@host.
                      The pattern may contain % as wildcard.
                      If the pattern is @% it shows all grants where host is exactly '%'.
 --grant-db|--gdb:    You can specify this option several times.
                      The pattern names the database to look for.
                      The pattern may contain % as wildcard.
            --all:    Show all grants                 
              ...:    Optional parameters to the mysql command
EOH
  exit
}

show_all_grants=0
declare -a grant_user
for ((param=1;param<=${#};param++))
do
  case ${!param} in
  --grant-user|--gu)
    param=$[ ${param} + 1 ]
    grant_user+=( "${!param}" )
    # delete 2 parameters from list and set back $param
    set -- "${@:1:param-2}" "${@:param+1}"
    param=$[ ${param} - 2 ]
    ;;
  --grant-db|--gdb)
    param=$[ ${param} + 1 ]
    grant_db+=( "${!param}" )
    # delete 2 parameters from list and set back $param
    set -- "${@:1:param-2}" "${@:param+1}"
    param=$[ ${param} - 2 ]
    ;;
  --all)
    show_all_grants=1
    # delete 1 parameter from list and set back $param
    set -- "${@:1:param-1}" "${@:param+1}"
    param=$[ ${param} - 1 ]
    ;;
  --help)
    usage
    ;;
  *)
    ;;
  esac
done

count=${#grant_user[@]}
for((param=0;param<count;param++))
do
  before=${#grant_user[@]}
  grant="${grant_user[${param}]}"
  user="${grant%@*}"
  if [[ "${grant}" == *\@?* ]]
  then
    host="${grant/*@}"
  else
    host=''
  fi
  case ${host} in
    '')
      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where user like '${user}'"
      ;;
    '%')
      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host='${host}' ${user:+and user like '${user}'}"
      ;;
    *)
      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host like '${host}' ${user:+and user like '${user}'}"
      ;;
  esac
  grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "${select}" | sort ) "${grant_user[@]:param+1}" )
  after=${#grant_user[@]}
  param=$[ param + after - before ]
  count=$[ count + after - before ]
done

# Get user for database in grant_db array
for db in ${grant_db[@]}
do
  grant_user+=( $(mysql $* --silent --skip-column-names --execute "
    select concat('\'',user,'\'@\'',host,'\'') as user from mysql.db where db like '${db}';
    select concat('\'',user,'\'@\'',host,'\'') as user from mysql.columns_priv where db like '${db}';
    select concat('\'',user,'\'@\'',host,'\'') as user from mysql.tables_priv where db like '${db}';
  " | sort -u ) )
done


# --all
if [ ${show_all_grants} -eq 1 ]
then
  printf -- '--\n-- %s\n--\n' "all grants";
  grant_user=( $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user" | sort ) )
fi

for user in ${grant_user[@]}
do
  printf -- '--\n-- %s\n--\n' "${user}";
  show_create_user="$(mysql $* --silent --skip-column-names --execute "select (substring_index(version(), '.',1) >= 5) and (substring_index(substring_index(version(), '.', 2),'.',-1) >=7) as show_create_user;";)"
  if [ "${show_create_user}" -eq 1 ]
  then
    mysql $* --silent --skip-column-names --execute "show create user ${user};" | sed 's/$/;/'
  fi
  OLD_IFS=${IFS}
  IFS=$'\n'
  for grant in $(mysql $* --silent --skip-column-names --execute "show grants for ${user}" | sed 's/$/;/')
  do
    regex='GRANT[ ]+.*[ ]+ON[ ]+(FUNCTION[ ]+|)`([^`]*)`\..*'
    if [[ $grant =~ $regex ]]
    then
      database=${BASH_REMATCH[2]}
      if [ ${#grant_db[@]} -gt 0 ]
      then
	if [[ " ${grant_db[@]} " =~ " ${database} " ]]
        then
	  echo "${grant}"
	fi
      else
        echo "${grant}"
      fi
    else
      echo "${grant}"
    fi
  done
done

Last update time

Per table

mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;

Per database

mysql> SELECT TABLE_SCHEMA AS DB,MAX(UPDATE_TIME) AS LAST_UPDATE FROM INFORMATION_SCHEMA.TABLES GROUP BY DB ORDER BY LAST_UPDATE;

InnoDB space

Per database

mysql> select table_schema as database_name, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema order by total_size_mb;

Per table

mysql> select table_schema as database_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;

Logging

If you use SET GLOBAL it is just for the moment.

Don't forget to add it in your my.cnf to make it permanent!

What can I log?

The interesting variables here are:

log_queries_not_using_indexes
log_slave_updates
log_slow_queries
general_log

Choose logging destination FILE/TABLE/NONE

This affects general_log and slow_query_log.

Log to the table mysql.slow_log and mysql.general_log

mysql> SET GLOBAL log_output=TABLE;

Log to the table mysql.slow_log and mysql.general_log

mysql> SET GLOBAL log_output=TABLE;

Both: tables and files

mysql> SET GLOBAL log_output = 'TABLE,FILE';

None, if NONE appears in the log_output destinations there is no logging

mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';

is equal to

mysql> SET GLOBAL log_output = 'NONE';

Enable/disable general logging

mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

Enable/disable logging of slow queries

mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)

Slave

Debugging

What did we see from the master

Read the binlog from Master:

# mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password'                     --base64-output=auto --database='limit output to this database' -vv  mysql-bin.number | less

if you get

ERROR: Failed on connect: SSL connection error: protocol version mismatch

try

# mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --ssl-mode=DISABLED --base64-output=auto --database='limit output to this database' -vv  mysql-bin.number | less

For an idea of the binlog file to investigate on the master do this on your slave:

# mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'

Filesystems for MySQL

ext3/ext4

Create Options

# mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data

Mountoptions

noatime
data=writeback (best performance , only metadata is logged)
data=ordered (ok performance , recording metadata and grouping metadata related to the data changes)
data=journal (worst performance, but best data protection, ext3 default mode, recording metadata and all data)

Raw devices with InnoDB

Take a look at setting device permissions via udev first.

After that the device is owned by mysql:

# ls -alL /dev/vg-data/lv-rawdisk-innodb01 
brw-rw---- 1 mysql mysql 252, 0 Aug 12 15:07 /dev/vg-data/lv-rawdisk-innodb01

Determine the size:

# lvs vg-data 
  LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
  lv-rawdisk-innodb01 vg-data -wi-a---- 25.00g

# fdisk -l /dev/vg-data/lv-rawdisk-innodb01 

Disk /dev/vg-data/lv-rawdisk-innodb01: 26.8 GB, 26843545600 bytes
255 heads, 63 sectors/track, 3263 cylinders, total 52428800 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

# bc -l
26843545600/(1024*1024*1024)
25.00000000000000000000

Yes... really 25GB!

Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf :

[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw

Start mysql:

# service mysql start

Aaaaaand.. do not forget apparmor! Like I did.. :-D

InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name /dev/dm-0
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

# tail /var/log/kern.log
...
Aug 12 15:30:09 mysql kernel: [ 5840.118528] audit: type=1400 audit(1439386209.399:33): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/dev/dm-0" pid=11810 comm="mysqld" requested_mask="wr" denied_mask="wr" fsuid=108 ouid=108
...

Add your raw device to the apparmor config in /etc/apparmor.d/local/usr.sbin.mysqld :

# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
/dev/dm-* rwk,

Reload apparmor:

# service apparmor reload

Another try!

# service mysql start

InnoDB: The first specified data file /dev/vg-data/lv-rawdisk-innodb01 did not exist:
InnoDB: a new database to be created!
150812 15:48:23  InnoDB: Setting file /dev/vg-data/lv-rawdisk-innodb01 size to 25600 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...

Much better!

So shutdown MySQL again!

Change your configuration /etc/mysql/conf.d/innodb.cnf and change newraw to raw! :

[mysqld]
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

NFS

NFSv4

On NetApp CDOT SVM

cdot1nfsv4::> export-policy rule create -policyname default       -clientmatch 172.18.128.0/22 -superuser none -rwrule none -rorule sys -allow-dev false -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> export-policy      create -policyname mysql_clients
cdot1nfsv4::> export-policy rule create -policyname mysql_clients -clientmatch 172.18.128.0/22 -superuser sys -rwrule sys -rorule sys -allow-dev true -allow-suid false
cdot1nfsv4::>
cdot1nfsv4::> nfs server modify -v4.0 enabled -v4-id-domain this.domain.tld           
cdot1nfsv4::> set -units GB
cdot1nfsv4::> vol show -volume MYSQLNFS_* -fields volume,policy,size,junction-path
vserver            volume                size policy        junction-path          
------------------ --------------------- ---- ------------- ---------------------- 
cdot1nfsv4         MYSQLNFS_DATA         40GB mysql_clients /MYSQLNFS_DATA 
cdot1nfsv4         MYSQLNFS_LOG          1GB  mysql_clients /MYSQLNFS_LOG  
2 entries were displayed.

Links:

On Linux

Blacklist rpcsec_gss_krb5

To disable loading of the rpcsec_gss_krb5 kernel module which causes problems with performance, do this:

# echo "blacklist rpcsec_gss_krb5" > /etc/modprobe.d/blacklist-rpcsec_gss_krb5.conf
# rmmod rpcsec_gss_krb5

/etc/sysctl.d/99-mysql.conf

#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Semaphores & IPC for optimizations in innodb
kernel.shmmax=2147483648
kernel.shmall=2147483648
kernel.msgmni=1024
kernel.msgmax=65536
kernel.sem=250 32000 32 1024

###################################################################
# Swap
vm.swappiness = 0
vm.vfs_cache_pressure = 50

/etc/sysctl.d/99-netapp-nfs.conf

#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
#
###################################################################
# Optimization for netapp/nfs increased from 64k, @see http://tldp.org/HOWTO/NFS-HOWTO/performance.html#MEMLIMITS
net.core.wmem_default=262144
net.core.rmem_default=262144
net.core.wmem_max=262144
net.core.rmem_max=262144
 
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216
net.ipv4.tcp_no_metrics_save = 1
 
# Guidelines from http://media.netapp.com/documents/mysqlperformance-5.pdf
net.ipv4.tcp_sack=0
net.ipv4.tcp_timestamps=0
sunrpc.tcp_slot_table_entries=128

#nfs.v3.enable on
nfs.tcp.enable=on
nfs.tcp.recvwindowsize=65536
nfs.tcp.xfersize=65536
 
#iscsi.iswt.max_ios_per_session 128
#iscsi.iswt.tcp_window_size 131400
#iscsi.max_connections_per_session 16
 
net.ipv4.tcp_tw_reuse = 1
net.ipv4.ip_local_port_range = 1024 65023
net.ipv4.tcp_max_syn_backlog = 10240
net.ipv4.tcp_max_tw_buckets = 400000
net.ipv4.tcp_max_orphans = 60000
net.ipv4.tcp_synack_retries = 3
net.core.somaxconn = 10000
 
kernel.sysrq=0
 
net.ipv4.neigh.default.gc_thresh1 = 4096
net.ipv4.neigh.default.gc_thresh2 = 8192
net.ipv4.neigh.default.gc_thresh3 = 8192
net.ipv4.neigh.default.base_reachable_time = 86400
net.ipv4.neigh.default.gc_stale_time = 86400

Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf

mysql soft nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql hard nproc 10240

Modify systemd mysql.service to raise the number of files limit

To raise the number of files for the service you have to tell the systemd the new limit.

# systemctl edit mysql.service

and enter:

[Service]
        LimitNOFILE=1024000

# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file
...
# /etc/systemd/system/mysql.service.d/override.conf
[Service]
        LimitNOFILE=1024000

Do not forget to activate and check the limit

# systemctl daemon-reload
# systemctl restart mysql
# awk 'NR==1 || /Max open files/' /proc/$(pgrep mysqld$)/limits 
Limit                     Soft Limit           Hard Limit           Units     
Max open files            1024000              1024000              files

Modify systemd service to wait for NFS

To be sure that the NFS mount is ready when the mysql server starts add After=nfs-client.target to the systemd service in the Unit-section.

# systemctl edit mysql.service

and enter:

[Unit]
        Description=MySQL Community Server
        After=network.target
        After=nfs-client.target

# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755

# /etc/systemd/system/mysql.service.d/override.conf
[Unit]
        Description=MySQL Community Server
        After=network.target
        After=nfs-client.target

[Service]
        LimitNOFILE=1024000

Do not forget to activate the changes...

# systemctl daemon-reload
# systemctl restart mysql

... and check they are active:

# systemctl list-dependencies --after mysql.service | grep nfs-client.target
● ├─nfs-client.target

/etc/idmapd.conf

# Domain = localdomain
Domain = this.domain.tld

# systemctl restart nfs-idmapd.service

Alternative switch off kerberos authentication with the mount option sec=sys.

/etc/fstab

cdot-nfsv4-svm:/MYSQLNFS_LOG  /MYSQLNFS_LOG  nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime	
cdot-nfsv4-svm:/MYSQLNFS_DATA /MYSQLNFS_DATA nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
...
datadir		= /MYSQLNFS_DATA/data/mysql
...

/etc/mysql/mysql.conf.d/innodb.cnf

[mysqld]
#
# * InnoDB
#
innodb_data_home_dir            = /MYSQLNFS_DATA/InnoDB
innodb_data_file_path           = ibdata1:200M:autoextend
innodb_log_group_home_dir       = /MYSQLNFS_LOG/ib_log
#innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = on

# mysql -e "show variables where variable_name like '%dir' and value like '/MYSQLNFS%'"
+---------------------------+------------------------------------+
| Variable_name             | Value                              |
+---------------------------+------------------------------------+
| datadir                   | /MYSQLNFS_DATA/data/mysql/         |
| innodb_data_home_dir      | /MYSQLNFS_DATA/InnoDB              |
| innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log               |
+---------------------------+------------------------------------+

/etc/mysql/mysql.conf.d/query_cache.cnf

[mysqld]
#
# * Query Cache Configuration
#
query_cache_type         = 1
query_cache_limit        = 256K
query_cache_min_res_unit = 2k
query_cache_size         = 80M

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+
1 row in set (0,00 sec)

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 262144   |
| query_cache_min_res_unit     | 2048     |
| query_cache_size             | 83886080 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0,00 sec)

apparmor : /etc/apparmor.d/local/usr.sbin.mysqld

# vim:syntax=apparmor

# This should be always there...
  owner @{PROC}/@{pid}/status r,
  /sys/devices/system/node/ r,
  /sys/devices/system/node/** r,

# The mysql datadir, innodb_data_home_dir
  /MYSQLNFS_DATA/ r,
  /MYSQLNFS_DATA/** rwk,

# The mysql innodb_log_group_home_dir
  /MYSQLNFS_LOG/ r,
  /MYSQLNFS_LOG/** rwk,

Short stupid performance test

# time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
65536+0 records in
65536+0 records out
1073741824 bytes (1,1 GB, 1,0 GiB) copied, 1,7552 s, 612 MB/s

real	0m1.772s
user	0m0.016s
sys	0m0.672s

Some things seem to work...

Sample InnoDB configuration

/etc/mysql/conf.d/innodb.cnf

[mysqld]
# InnoDB Parameters

# innodb_buffer_pool_size=(0.7*total_mem_size)
innodb_buffer_pool_size=1433M

# bulk_insert_buffer_size
bulk_insert_buffer_size=256M

# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2

# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4

# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT

# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log

Analyze

mysql> select * from <tablename> PROCEDURE ANALYSE();

mysql> SHOW /*!50000 GLOBAL*/ STATUS;

See [MySQL Performance Tuning]

Find statements which lead into an error

mysql> select CURRENT_SCHEMA,DIGEST_TEXT,MYSQL_ERRNO,MESSAGE_TEXT  from performance_schema.events_statements_history where errors!=0\G
*************************** 1. row ***************************
CURRENT_SCHEMA: NULL
   DIGEST_TEXT: NULL
   MYSQL_ERRNO: 1046
  MESSAGE_TEXT: No database selected
1 row in set (0,00 sec)

percona-toolkit

# aptitude install percona-toolkit

# mysql -e "explain select * from mysql.user,mysql.db where user.user=db.user" | pt-visual-explain
JOIN
+- Bookmark lookup
|  +- Table
|  |  table          db
|  |  possible_keys  User
|  +- Index lookup
|     key            db->User
|     possible_keys  User
|     key_len        48
|     ref            mysql.user.User
|     rows           3
+- Table scan
   rows           68
   +- Table
      table          user

Sysbench

# mysql -u root -e "create database sbtest;"
# sysbench \
    --test=oltp \
    --oltp-table-size=10000000 \
    --db-driver=mysql \
    --mysql-table-engine=innodb \
    --mysql-db=sbtest \
    --mysql-user=root \
    --mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
    --mysql-socket=/var/run/mysqld/mysqld.sock \
    prepare
# sysbench \
    --test=oltp \
    --oltp-test-mode=complex \
    --oltp-table-size=80000000 \
    --db-driver=mysql \
    --mysql-table-engine=innodb \
    --mysql-db=sbtest \
    --mysql-user=root \
    --mysql-password=$(nawk -F'=' '/password/{print $2}' /root/.my.cnf) \
    --mysql-socket=/var/run/mysqld/mysqld.sock \
    --num-threads=4 \
    --max-time=900 \
    --max-requests=500000 \
    run
# mysql -u root_rw -e "drop table sbtest;" sbtest

Recover a damaged root account

Lost grants

Try out:

# service mysql stop
# echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:14:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start

Or:

# service mysql stop
# mysqld_safe --skip-grant-tables &
...
# mysql -e "UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root'; FLUSH PRIVILEGES; GRANT ALL ON *.* TO 'root'@'localhost';"
# mysqladmin -u root  shutdown
# service mysql start

Lost password

# service mysql stop
# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
# mysqld_safe --init-file=/root/mysql-init
...
150812 19:15:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
# rm /root/mysql-init
# service mysql start

Structured configuration

This is the default in Ubuntus /etc/mysql/my.cnf:

...
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

/etc/mysql/conf.d/innodb.cnf:

[mysqld]
# InnoDB Parameters

# innodb_buffer_pool_size=(0.7*total_mem_size)
#innodb_buffer_pool_size=512M
innodb_buffer_pool_size=256M

# bulk_insert_buffer_size
#bulk_insert_buffer_size=256M
bulk_insert_buffer_size=128M

# innodb_buffer_pool_instances=... more = more concurrency
innodb_buffer_pool_instances=2

# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4

# innodb_flush_method=O_DIRECT (avoids double buffering)
innodb_flush_method=O_DIRECT

# InnoDB data raw disks
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw

# InnoDB log files
innodb_log_files_in_group=2
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log

/etc/mysql/conf.d/myisam.cnf:

[mysqld]
#key_buffer = 512M
key_buffer = 128M
table_cache = 8K
myisam_sort_buffer_size = 64M
tmp_table_size = 64M

# Variable: concurrent_insert
# Value	Description
# 0	Disables concurrent inserts
# 1	(Default) Enables concurrent insert for MyISAM tables that do not have holes
# 2	Enables concurrent inserts for all MyISAM tables, even those that have holes.
#       For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread.
#       Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.
concurrent_insert=2

# Variable: myisam_use_mmap
# https://www.percona.com/blog/2006/05/26/myisam-mmap-feature-51/
# 
myisam_use_mmap=1

/etc/mysql/conf.d/mysqld.cnf:

[mysqld]
datadir         = /var/lib/mysql/data/data
# because mysql is soooo stupid
#ignore-db-dirs = lost+found # when we will have mysql >= 5.6.3

bind-address            = 127.0.0.1

open-files-limit       = 4096
max_connections        = 512

max_allowed_packet     = 16M
thread_stack           = 192K
thread_cache_size      = 8
myisam-recover-options = BACKUP
max_connections        = 512
table_cache            = 8192
thread_concurrency     = 4

default-storage-engine = innodb

# Enable the full query log. Every query (even ones with incorrect
# syntax) that the server receives will be logged. This is useful for
# debugging, it is usually disabled in production use.
#log

# Print warnings to the error log file.  If you have any problem with
# MySQL you should enable logging of warnings and examine the error log
# for possible explanations. 
log_warnings

# Log slow queries. Slow queries are queries which take more than the
# amount of time defined in "long_query_time" or which do not use
# indexes well, if log_long_format is enabled. It is normally good idea
# to have this turned on if you frequently add new queries to the
# system.
log_slow_queries
slow_query_log_file = /var/log/mysql/mysql-slow.log

# All queries taking more than this amount of time (in seconds) will be
# trated as slow. Do not use "1" as a value here, as this will result in
# even very fast queries being logged from time to time (as MySQL
# currently measures time with second accuracy only).
long_query_time = 2

# Log more information in the slow query log. Normally it is good to
# have this turned on. This will enable logging of queries that are not
# using indexes in addition to long running queries.
#log_long_format

log_bin                 = /var/lib/mysql/binlog/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
sync_binlog		= 0

performance_schema	= ON

/etc/mysql/conf.d/mysqld_safe.cnf:

[mysqld_safe]

/etc/mysql/conf.d/mysqld_safe_syslog.cnf:

[mysqld_safe]
syslog

/etc/mysql/conf.d/query_cache.cnf:

[mysqld]
query_cache_limit        = 4M
query_cache_size         = 128M
query_cache_min_res_unit = 2K

MySQL Clients

Small one liners for testing purposes.

PHP

PHP PDO

$ php -r '
  $pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
                     PDO::ATTR_PERSISTENT => true
           )
         );
  $stmt=$pdo->prepare("SELECT * FROM mytable");
  if($stmt->execute()){
    while($row = $stmt->fetch()){
      print_r($row);
    }
  };
  $stmt = null;
  $pdo=null;
'

@@ Line 1: / Line 1: @@
-[[Kategorie:MySQL|Tipps und Tricks]]
+[[Category:MySQL|Tipps und Tricks]]
 ==Oneliner==
+===Show MySQL-traffic fired from a client===
+<syntaxhighlight lang=bash>
+# tcpdump -i any -s 0 -l -vvv -w - dst port 3306 | strings | perl -e '
+while(<>) { chomp; next if /^[^ ]+[ ]*$/;
+  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
+    if (defined $q) { print "$q\n"; }
+    $q=$_;
+  } else {
+    $_ =~ s/^[ \t]+//; $q.=" $_";
+  }
+}'
+</syntaxhighlight>
+===Mysql processes each second===
+<syntaxhighlight lang=bash>
+# mysqladmin  -i 1 --verbose processlist
+</syntaxhighlight>
 ===All grants===
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mysql --skip-column-names --batch --execute 'select concat("`",user,"`@`",host,"`") from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'
-</source>
+</syntaxhighlight>
 Or a little nicer:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 #!/bin/bash
@@ Line 18: / Line 34: @@
 function usage () {
    cat << EOH
-Usage: $0 [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ...
+Usage: $0 [--all] [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ...
             --help:    This output
 --grant-user|--gu:    You can specify this option several times.
@@ Line 30: / Line 46: @@
                        The pattern names the database to look for.
                        The pattern may contain % as wildcard.
+            --all:    Show all grants
                ...:    Optional parameters to the mysql command
 EOH
@@ Line 36: / Line 52: @@
 }
+show_all_grants=0
 declare -a grant_user
 for ((param=1;param<=${#};param++))
@@ Line 42: / Line 59: @@
    --grant-user|--gu)
      param=$[ ${param} + 1 ]
-     grant_user+=( $( echo ${!param} | awk -F'@' "NF==2 && \$1 {printf \"'%s'@'%s'\n\",\$1,\$2;next;}{print}") )
+     grant_user+=( "${!param}" )
      # delete 2 parameters from list and set back $param
      set -- "${@:1:param-2}" "${@:param+1}"
@@ Line 53: / Line 70: @@
      set -- "${@:1:param-2}" "${@:param+1}"
      param=$[ ${param} - 2 ]
+    ;;
+  --all)
+    show_all_grants=1
+    # delete 1 parameter from list and set back $param
+    set -- "${@:1:param-1}" "${@:param+1}"
+    param=$[ ${param} - 1 ]
      ;;
    --help)
@@ Line 62: / Line 85: @@
 done
-# Fill users which are without host
 count=${#grant_user[@]}
 for((param=0;param<count;param++))
 do
-   user="${grant_user[${param}]}"
+   before=${#grant_user[@]}
-   if [[ ${user} != ?*"@"?* ]]
+  grant="${grant_user[${param}]}"
+  user="${grant%@*}"
+   if [[ "${grant}" == *\@?* ]]
    then
-     before=${#grant_user[@]}
+     host="${grant/*@}"
-    if [[ ${user} == "@"?* ]]
+  else
-    then
+    host=''
-      host="${user/@}"
-      if [[ "_${host}_" == "_%_" ]]
-      then
-        grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host='${host}'" | sort ) "${grant_user[@]:param+1}" )
-      else
-        grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host like '${host}'" | sort ) "${grant_user[@]:param+1}" )
-      fi
-    else
-      grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where user like '${user}'" | sort ) "${grant_user[@]:param+1}" )
-    fi
-    after=${#grant_user[@]}
-    param=$[ param + after - before ]
-    count=$[ count + after - before ]
    fi
+  case ${host} in
+    '')
+      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where user like '${user}'"
+      ;;
+    '%')
+      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host='${host}' ${user:+and user like '${user}'}"
+      ;;
+    *)
+      select="select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user where host like '${host}' ${user:+and user like '${user}'}"
+      ;;
+  esac
+  grant_user=( "${grant_user[@]:0:param}" $(mysql $* --silent --skip-column-names --execute "${select}" | sort ) "${grant_user[@]:param+1}" )
+  after=${#grant_user[@]}
+  param=$[ param + after - before ]
+  count=$[ count + after - before ]
 done
@@ Line 99: / Line 125: @@
-# if no users specified, show all grants
+# --all
-if [ ${#grant_user[@]} -eq 0 -a ${#grant_db[@]} -eq 0 ]
+if [ ${show_all_grants} -eq 1 ]
 then
    printf -- '--\n-- %s\n--\n' "all grants";
@@ Line 109: / Line 135: @@
 do
    printf -- '--\n-- %s\n--\n' "${user}";
-   mysql $* --silent --skip-column-names --execute "show create user ${user}; show grants for ${user}" | sed 's/$/;/'
+   show_create_user="$(mysql $* --silent --skip-column-names --execute "select (substring_index(version(), '.',1) >= 5) and (substring_index(substring_index(version(), '.', 2),'.',-1) >=7) as show_create_user;";)"
+  if [ "${show_create_user}" -eq 1 ]
+  then
+    mysql $* --silent --skip-column-names --execute "show create user ${user};" | sed 's/$/;/'
+  fi
+  OLD_IFS=${IFS}
+  IFS=$'\n'
+  for grant in $(mysql $* --silent --skip-column-names --execute "show grants for ${user}" | sed 's/$/;/')
+  do
+    regex='GRANT[ ]+.*[ ]+ON[ ]+(FUNCTION[ ]+|)`([^`]*)`\..*'
+    if [[ $grant =~ $regex ]]
+    then
+      database=${BASH_REMATCH[2]}
+      if [ ${#grant_db[@]} -gt 0 ]
+      then
+	if [[ " ${grant_db[@]} " =~ " ${database} " ]]
+        then
+	  echo "${grant}"
+	fi
+      else
+        echo "${grant}"
+      fi
+    else
+      echo "${grant}"
+    fi
+  done
 done
-</source>
+</syntaxhighlight>
 ===Last update time===
 * Per table
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;
-</source>
+</syntaxhighlight>
 * Per database
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SELECT TABLE_SCHEMA AS DB,MAX(UPDATE_TIME) AS LAST_UPDATE FROM INFORMATION_SCHEMA.TABLES GROUP BY DB ORDER BY LAST_UPDATE;
-</source>
+</syntaxhighlight>
 ==InnoDB space==
 ===Per database===
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> select table_schema as database_name, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema order by total_size_mb;
-</source>
+</syntaxhighlight>
 ===Per table===
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> select table_schema as database_name,table_name,round(data_length/1024/1024,2) as size_mb from information_schema.tables order by size_mb;
-</source>
+</syntaxhighlight>
 ==Logging==
@@ Line 153: / Line 204: @@
 * Log to the table mysql.slow_log and mysql.general_log
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL log_output=TABLE;
-</source>
+</syntaxhighlight>
 * Log to the table mysql.slow_log and mysql.general_log
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL log_output=TABLE;
-</source>
+</syntaxhighlight>
 * Both: tables and files
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL log_output = 'TABLE,FILE';
-</source>
+</syntaxhighlight>
 * None, if NONE appears in the log_output destinations there is no logging
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';
-</source>
+</syntaxhighlight>
 is equal to
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL log_output = 'NONE';
-</source>
+</syntaxhighlight>
 ===Enable/disable general logging===
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
 Query OK, 0 rows affected (0.00 sec)
@@ Line 183: / Line 234: @@
 mysql> SET GLOBAL general_log = 'ON';
 Query OK, 0 rows affected (0.00 sec)
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL general_log = 'OFF';
 Query OK, 0 rows affected (0.00 sec)
-</source>
+</syntaxhighlight>
 ===Enable/disable logging of slow queries===
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
 Query OK, 0 rows affected (0.00 sec)
@@ Line 197: / Line 248: @@
 mysql> SET GLOBAL slow_query_log = 'ON';
 Query OK, 0 rows affected (0.00 sec)
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SET GLOBAL slow_query_log = 'OFF';
 Query OK, 0 rows affected (0.00 sec)
-</source>
+</syntaxhighlight>
 == Slave ==
@@ Line 208: / Line 259: @@
 ==== What did we see from the master ====
 Read the binlog from Master:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password'                     --base64-output=auto --database='limit output to this database' -vv  mysql-bin.number | less
-</source>
+</syntaxhighlight>
 if you get
   ERROR: Failed on connect: SSL connection error: protocol version mismatch
 try
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mysqlbinlog --read-from-remote-server --host='your replication host' --user='your replication user' --password='your replication password' --ssl-mode=DISABLED --base64-output=auto --database='limit output to this database' -vv  mysql-bin.number | less
-</source>
+</syntaxhighlight>
 For an idea of the binlog file to investigate on the master do this on your slave:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'
-</source>
+</syntaxhighlight>
 ==Filesystems for MySQL==
@@ Line 227: / Line 278: @@
 ====Create Options====
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data
-</source>
+</syntaxhighlight>
 ====Mountoptions====
@@ Line 243: / Line 294: @@
 '''After''' that the device is owned by mysql:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # ls -alL /dev/vg-data/lv-rawdisk-innodb01
 brw-rw---- 1 mysql mysql 252, 0 Aug 12 15:07 /dev/vg-data/lv-rawdisk-innodb01
-</source>
+</syntaxhighlight>
 Determine the size:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # lvs vg-data
    LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
@@ Line 266: / Line 317: @@
 26843545600/(1024*1024*1024)
 .00000000000000000000
-</source>
+</syntaxhighlight>
 Yes... really 25GB!
 Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf :
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 # InnoDB raw disks
 innodb_data_home_dir=
 innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw
-</source>
+</syntaxhighlight>
 Start mysql:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service mysql start
-</source>
+</syntaxhighlight>
 Aaaaaand.. do not forget apparmor! Like I did.. :-D
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 InnoDB: Operating system error number 13 in a file operation.
 InnoDB: The error means mysqld does not have the access rights to
@@ Line 290: / Line 341: @@
 InnoDB: File operation call: 'open'.
 InnoDB: Cannot continue operation.
-</source>
+</syntaxhighlight>
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # tail /var/log/kern.log
 ...
 Aug 12 15:30:09 mysql kernel: [ 5840.118528] audit: type=1400 audit(1439386209.399:33): apparmor="DENIED" operation="open" profile="/usr/sbin/mysqld" name="/dev/dm-0" pid=11810 comm="mysqld" requested_mask="wr" denied_mask="wr" fsuid=108 ouid=108
 ...
-</source>
+</syntaxhighlight>
 Add your raw device to the apparmor config in /etc/apparmor.d/local/usr.sbin.mysqld :
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # Site-specific additions and overrides for usr.sbin.mysqld.
 # For more details, please see /etc/apparmor.d/local/README.
 /dev/dm-* rwk,
-</source>
+</syntaxhighlight>
 Reload apparmor:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service apparmor reload
-</source>
+</syntaxhighlight>
 Another try!
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service mysql start
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 InnoDB: The first specified data file /dev/vg-data/lv-rawdisk-innodb01 did not exist:
 InnoDB: a new database to be created!
@@ Line 321: / Line 372: @@
 InnoDB: Database physically writes the file full: wait...
 InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...
-</source>
+</syntaxhighlight>
 Much better!
@@ Line 328: / Line 379: @@
 Change your configuration /etc/mysql/conf.d/innodb.cnf and '''change newraw to raw!''' :
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 # InnoDB raw disks
 innodb_data_home_dir=
 innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
-</source>
+</syntaxhighlight>
 === NFS ===
 ==== NFSv4 ====
 ===== On NetApp CDOT SVM =====
-<source lang=cdot>
+<syntaxhighlight lang=text>
 cdot1nfsv4::> export-policy rule create -policyname default       -clientmatch 172.18.128.0/22 -superuser none -rwrule none -rorule sys -allow-dev false -allow-suid false
 cdot1nfsv4::>
@@ Line 352: / Line 403: @@
 cdot1nfsv4         MYSQLNFS_LOG          1GB  mysql_clients /MYSQLNFS_LOG
 entries were displayed.
-</source>
+</syntaxhighlight>
 Links:
@@ Line 361: / Line 412: @@
 ===== On Linux =====
+====== Blacklist rpcsec_gss_krb5 ======
+To disable loading of the rpcsec_gss_krb5 kernel module which causes problems with performance, do this:
+<syntaxhighlight lang=text>
+# echo "blacklist rpcsec_gss_krb5" > /etc/modprobe.d/blacklist-rpcsec_gss_krb5.conf
+# rmmod rpcsec_gss_krb5
+</syntaxhighlight>
 ====== /etc/sysctl.d/99-mysql.conf ======
-<source>
+<syntaxhighlight lang=text>
 #
 ## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
@@ Line 378: / Line 437: @@
 vm.swappiness = 0
 vm.vfs_cache_pressure = 50
-</source>
+</syntaxhighlight>
 ====== /etc/sysctl.d/99-netapp-nfs.conf ======
-<source>
+<syntaxhighlight lang=text>
 #
 ## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
@@ Line 425: / Line 484: @@
 net.ipv4.neigh.default.base_reachable_time = 86400
 net.ipv4.neigh.default.gc_stale_time = 86400
-</source>
+</syntaxhighlight>
@@ Line 431: / Line 490: @@
 ====== Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf ======
-<source>
+<syntaxhighlight lang=text>
 mysql soft nofile 1024000
 mysql hard nofile 1024000
 mysql soft nproc 10240
 mysql hard nproc 10240
-</source>
+</syntaxhighlight>
 ====== Modify systemd mysql.service to raise the number of files limit ======
 To raise the number of files for the service you have to tell the systemd the new limit.
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl edit mysql.service
-</source>
+</syntaxhighlight>
 and enter:
-<source lang=inifile>
+<syntaxhighlight lang=ini>
 [Service]
          LimitNOFILE=1024000
-</source>
+</syntaxhighlight>
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl cat mysql
 # /lib/systemd/system/mysql.service
@@ Line 457: / Line 516: @@
 [Service]
          LimitNOFILE=1024000
-</source>
+</syntaxhighlight>
 Do not forget to activate and check the limit
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl daemon-reload
 # systemctl restart mysql
@@ Line 466: / Line 525: @@
 Limit                     Soft Limit           Hard Limit           Units
 Max open files            1024000              1024000              files
-</source>
+</syntaxhighlight>
 ====== Modify systemd service to wait for NFS ======
 To be sure that the NFS mount is ready when the mysql server starts add After=nfs-client.target to the systemd service in the Unit-section.
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl edit mysql.service
-</source>
+</syntaxhighlight>
 and enter:
-<source lang=inifile>
+<syntaxhighlight lang=ini>
 [Unit]
          Description=MySQL Community Server
          After=network.target
          After=nfs-client.target
-</source>
+</syntaxhighlight>
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl cat mysql
 # /lib/systemd/system/mysql.service
@@ Line 514: / Line 573: @@
 [Service]
          LimitNOFILE=1024000
-</source>
+</syntaxhighlight>
 Do not forget to activate the changes...
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl daemon-reload
 # systemctl restart mysql
-</source>
+</syntaxhighlight>
 ... and check they are active:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # systemctl list-dependencies --after mysql.service | grep nfs-client.target
 ● ├─nfs-client.target
-</source>
+</syntaxhighlight>
 ====== /etc/idmapd.conf ======
-<source lang=conf>
+<syntaxhighlight lang=text>
 # Domain = localdomain
 Domain = this.domain.tld
-</source>
+</syntaxhighlight>
+<syntaxhighlight lang=bash>
+# systemctl restart nfs-idmapd.service
+</syntaxhighlight>
+Alternative switch off kerberos authentication with the mount option <i>sec=sys</i>.
 ====== /etc/fstab ======
-<source lang=fstab>
+<syntaxhighlight lang=text>
 cdot-nfsv4-svm:/MYSQLNFS_LOG  /MYSQLNFS_LOG  nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime
 cdot-nfsv4-svm:/MYSQLNFS_DATA /MYSQLNFS_DATA nfs rw,hard,nointr,rsize=65536,wsize=65536,bg,vers=4,proto=tcp,noatime
-</source>
+</syntaxhighlight>
 ====== /etc/mysql/mysql.conf.d/mysqld.cnf ======
-<source lang=inifile>
+<syntaxhighlight lang=ini>
 [mysqld]
 ...
 datadir		= /MYSQLNFS_DATA/data/mysql
 ...
-</source>
+</syntaxhighlight>
 ====== /etc/mysql/mysql.conf.d/innodb.cnf ======
-<source lang=inifile>
+<syntaxhighlight lang=ini>
 [mysqld]
 #
@@ Line 559: / Line 624: @@
 innodb_flush_log_at_trx_commit  = 2
 innodb_file_per_table           = on
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 # mysql -e "show variables where variable_name like '%dir' and value like '/MYSQLNFS%'"
 +---------------------------+------------------------------------+
@@ Line 569: / Line 634: @@
 | innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log               |
 +---------------------------+------------------------------------+
-</source>
+</syntaxhighlight>
 ====== /etc/mysql/mysql.conf.d/query_cache.cnf ======
-<source lang=inifile>
+<syntaxhighlight lang=ini>
 [mysqld]
 #
@@ Line 580: / Line 645: @@
 query_cache_min_res_unit = 2k
 query_cache_size         = 80M
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SHOW VARIABLES LIKE 'have_query_cache';
 +------------------+-------+
@@ Line 602: / Line 667: @@
 +------------------------------+----------+
 rows in set (0,00 sec)
-</source>
+</syntaxhighlight>
 ====== apparmor : /etc/apparmor.d/local/usr.sbin.mysqld ======
-<source lang=apparmor>
+<syntaxhighlight lang=text>
 # vim:syntax=apparmor
@@ Line 620: / Line 685: @@
    /MYSQLNFS_LOG/ r,
    /MYSQLNFS_LOG/** rwk,
-</source>
+</syntaxhighlight>
 ====== Short stupid performance test ======
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
 +0 records in
@@ Line 632: / Line 697: @@
 user	0m0.016s
 sys	0m0.672s
-</source>
+</syntaxhighlight>
 Some things seem to work...
@@ Line 638: / Line 703: @@
 /etc/mysql/conf.d/innodb.cnf
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 # InnoDB Parameters
@@ Line 665: / Line 730: @@
 innodb_log_file_size=100M
 innodb_log_group_home_dir=/var/lib/mysql/ib_log
-</source>
+</syntaxhighlight>
 ==Analyze==
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> select * from <tablename> PROCEDURE ANALYSE();
-</source>
+</syntaxhighlight>
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 mysql> SHOW /*!50000 GLOBAL*/ STATUS;
-</source>
+</syntaxhighlight>
 * See [[http://de.slideshare.net/shinguz/pt-presentation-11465700 MySQL Performance Tuning]]
+===Find statements which lead into an error===
+<syntaxhighlight lang=mysql>
+mysql> select CURRENT_SCHEMA,DIGEST_TEXT,MYSQL_ERRNO,MESSAGE_TEXT  from performance_schema.events_statements_history where errors!=0\G
+*************************** 1. row ***************************
+CURRENT_SCHEMA: NULL
+   DIGEST_TEXT: NULL
+   MYSQL_ERRNO: 1046
+  MESSAGE_TEXT: No database selected
+row in set (0,00 sec)
+</syntaxhighlight>
 ===percona-toolkit===
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # aptitude install percona-toolkit
@@ Line 698: / Line 776: @@
     +- Table
        table          user
-</source>
+</syntaxhighlight>
 ===Sysbench===
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # mysql -u root -e "create database sbtest;"
 # sysbench \
@@ Line 728: / Line 806: @@
      run
 # mysql -u root_rw -e "drop table sbtest;" sbtest
-</source>
+</syntaxhighlight>
 ==Recover a damaged root account==
 ===Lost grants===
 Try out:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service mysql stop
 # echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
@@ Line 741: / Line 819: @@
 # rm /root/mysql-init
 # service mysql start
-</source>
+</syntaxhighlight>
 Or:
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service mysql stop
 # mysqld_safe --skip-grant-tables &
@@ Line 750: / Line 828: @@
 # mysqladmin -u root  shutdown
 # service mysql start
-</source>
+</syntaxhighlight>
 ===Lost password===
-<source lang=bash>
+<syntaxhighlight lang=bash>
 # service mysql stop
 # echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
@@ Line 761: / Line 839: @@
 # rm /root/mysql-init
 # service mysql start
-</source>
+</syntaxhighlight>
 ==Structured configuration==
 This is the default in Ubuntus /etc/mysql/my.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 ...
 #
@@ Line 773: / Line 851: @@
 #
 !includedir /etc/mysql/conf.d/
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/innodb.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 # InnoDB Parameters
@@ Line 805: / Line 883: @@
 innodb_log_file_size=100M
 innodb_log_group_home_dir=/var/lib/mysql/ib_log
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/myisam.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 #key_buffer = 512M
@@ Line 829: / Line 907: @@
 #
 myisam_use_mmap=1
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/mysqld.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 datadir         = /var/lib/mysql/data/data
@@ Line 888: / Line 966: @@
 performance_schema	= ON
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/mysqld_safe.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld_safe]
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/mysqld_safe_syslog.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld_safe]
 syslog
-</source>
+</syntaxhighlight>
 /etc/mysql/conf.d/query_cache.cnf:
-<source lang=mysql>
+<syntaxhighlight lang=mysql>
 [mysqld]
 query_cache_limit        = 4M
 query_cache_size         = 128M
 query_cache_min_res_unit = 2K
-</source>
+</syntaxhighlight>
 =MySQL Clients=
@@ Line 913: / Line 991: @@
 ==PHP==
 ===PHP PDO===
-<source lang=php>
+<syntaxhighlight lang=php>
 $ php -r '
    $pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
@@ Line 928: / Line 1,006: @@
    $pdo=null;
 '
-</source>
+</syntaxhighlight>

MySQL Tipps und Tricks: Difference between revisions