MySQL Tipps und Tricks: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
 
(54 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[Kategorie:MySQL|Tipps und Tricks]]
[[Category:MySQL|Tipps und Tricks]]




==Oneliner==
==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===
===All grants===
<source lang=bash>
<syntaxhighlight lang=bash>
# mysql --skip-column-names --batch --execute 'select concat_ws("@",user,host) from mysql.user' | xargs -n 1 -i mysql --execute 'show grants for {}'
# 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:
<syntaxhighlight lang=bash>
#!/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
</syntaxhighlight>
 
===Last update time===
* Per table
<syntaxhighlight lang=mysql>
mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;
</syntaxhighlight>
 
* Per database
<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;
</syntaxhighlight>


==InnoDB space==
==InnoDB space==
===Per database===
===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;
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===
===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;
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==
==Logging==
Line 37: Line 204:
   
   
* Log to the table mysql.slow_log and mysql.general_log
* Log to the table mysql.slow_log and mysql.general_log
<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL log_output=TABLE;
mysql> SET GLOBAL log_output=TABLE;
</source>
</syntaxhighlight>


* Log to the table mysql.slow_log and mysql.general_log
* Log to the table mysql.slow_log and mysql.general_log
<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL log_output=TABLE;
mysql> SET GLOBAL log_output=TABLE;
</source>
</syntaxhighlight>


* Both: tables and files
* Both: tables and files
<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL log_output = 'TABLE,FILE';
mysql> SET GLOBAL log_output = 'TABLE,FILE';
</source>
</syntaxhighlight>


* None, if NONE appears in the log_output destinations there is no logging
* 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';
mysql> SET GLOBAL log_output = 'TABLE,FILE,NONE';
</source>
</syntaxhighlight>
is equal to
is equal to
<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL log_output = 'NONE';
mysql> SET GLOBAL log_output = 'NONE';
</source>
</syntaxhighlight>


===Enable/disable general logging===
===Enable/disable general logging===
<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
mysql> SET GLOBAL general_log_file = '/var/lib/mysql/general.log';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Line 67: Line 234:
mysql> SET GLOBAL general_log = 'ON';
mysql> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
</source>
</syntaxhighlight>


<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL general_log = 'OFF';
mysql> SET GLOBAL general_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
</source>
</syntaxhighlight>


===Enable/disable logging of slow queries===
===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';
mysql> SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Line 81: Line 248:
mysql> SET GLOBAL slow_query_log = 'ON';
mysql> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
</source>
</syntaxhighlight>


<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SET GLOBAL slow_query_log = 'OFF';
mysql> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
</source>
</syntaxhighlight>
 
== Slave ==
=== Debugging ===
==== What did we see from the master ====
Read the binlog from Master:
<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
</syntaxhighlight>
if you get
ERROR: Failed on connect: SSL connection error: protocol version mismatch
try
<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
</syntaxhighlight>
 
For an idea of the binlog file to investigate on the master do this on your slave:
<syntaxhighlight lang=bash>
# mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'
</syntaxhighlight>


==Filesystems for MySQL==
==Filesystems for MySQL==
===ext3/ext4===
===ext3/ext4===


Mountoptions are:
====Create Options====
<syntaxhighlight lang=bash>
# mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data
</syntaxhighlight>
 
====Mountoptions====
* noatime
* noatime
* data=writeback (best performance , only metadata is logged)
* data=writeback (best performance , only metadata is logged)
* data=ordered  (ok performance  , recording metadata and grouping metadata related to the data changes)
* 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)
* data=journal  (worst performance, but best data protection, ext3 default mode, recording metadata and all data)


===Raw devices with InnoDB===
===Raw devices with InnoDB===
Line 101: Line 294:


'''After''' that the device is owned by mysql:
'''After''' that the device is owned by mysql:
<source lang=bash>
<syntaxhighlight lang=bash>
# ls -alL /dev/vg-data/lv-rawdisk-innodb01  
# 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
brw-rw---- 1 mysql mysql 252, 0 Aug 12 15:07 /dev/vg-data/lv-rawdisk-innodb01
</source>
</syntaxhighlight>


Determine the size:
Determine the size:
<source lang=bash>
<syntaxhighlight lang=bash>
# lvs vg-data  
# lvs vg-data  
   LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
   LV                  VG      Attr      LSize  Pool Origin Data%  Move Log Copy%  Convert
Line 124: Line 317:
26843545600/(1024*1024*1024)
26843545600/(1024*1024*1024)
25.00000000000000000000
25.00000000000000000000
</source>
</syntaxhighlight>
Yes... really 25GB!
Yes... really 25GB!


Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf :
Add your logical volume to your configuration /etc/mysql/conf.d/innodb.cnf :
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
# InnoDB raw disks
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Gnewraw
</source>
</syntaxhighlight>


Start mysql:
Start mysql:
<source lang=bash>
<syntaxhighlight lang=bash>
# service mysql start
# service mysql start
</source>
</syntaxhighlight>


Aaaaaand.. do not forget apparmor! Like I did.. :-D
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: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: The error means mysqld does not have the access rights to
Line 148: Line 341:
InnoDB: File operation call: 'open'.
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.
InnoDB: Cannot continue operation.
</source>
</syntaxhighlight>


<source lang=bash>
<syntaxhighlight lang=bash>
# tail /var/log/kern.log
# 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
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 :
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.
# Site-specific additions and overrides for usr.sbin.mysqld.
# For more details, please see /etc/apparmor.d/local/README.
# For more details, please see /etc/apparmor.d/local/README.
/dev/dm-* rwk,
/dev/dm-* rwk,
</source>
</syntaxhighlight>


Reload apparmor:
Reload apparmor:
<source lang=bash>
<syntaxhighlight lang=bash>
# service apparmor reload
# service apparmor reload
</source>
</syntaxhighlight>


Another try!
Another try!
<source lang=bash>
<syntaxhighlight lang=bash>
# service mysql start
# 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: The first specified data file /dev/vg-data/lv-rawdisk-innodb01 did not exist:
InnoDB: a new database to be created!
InnoDB: a new database to be created!
Line 179: Line 372:
InnoDB: Database physically writes the file full: wait...
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...
InnoDB: Progress in MB: 100 200 300 400 500 600 700 800 900 1000 1100 1200 ...
</source>
</syntaxhighlight>


Much better!
Much better!
Line 186: Line 379:


Change your configuration /etc/mysql/conf.d/innodb.cnf and '''change newraw to raw!''' :
Change your configuration /etc/mysql/conf.d/innodb.cnf and '''change newraw to raw!''' :
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
# InnoDB raw disks
# InnoDB raw disks
innodb_data_home_dir=
innodb_data_home_dir=
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
innodb_data_file_path=/dev/vg-data/lv-rawdisk-innodb01:25Graw
</source>
</syntaxhighlight>
 
=== NFS ===
==== NFSv4 ====
===== On NetApp CDOT SVM =====
<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::>
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.
</syntaxhighlight>
 
Links:
* [https://kb.netapp.com/support/s/article/how-to-configure-nfsv4-in-cluster-mode How to configure NFSv4 in Cluster-Mode]
* [https://kb.netapp.com/support/s/article/clustered-data-ontap-nfs-expert-recommended-articles Clustered Data ONTAP NFS Expert recommended articles]
* [https://kb.netapp.com/support/s/article/how-to-configure-netapp-storage-systems-for-network-file-system-version-4-in-aix-and-linux-environments  How to configure NetApp storage systems for Network File System version 4 in AIX and Linux environments]
* [https://kb.netapp.com/support/s/article/how-to-enable-or-disable-nfsv4-on-netapp-storage-systems How to enable or disable NFSv4 on NetApp storage systems]
 
===== 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 ======
<syntaxhighlight lang=text>
#
## 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
</syntaxhighlight>
 
====== /etc/sysctl.d/99-netapp-nfs.conf ======
<syntaxhighlight lang=text>
#
## 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
</syntaxhighlight>
 
 
 
====== Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf ======
 
<syntaxhighlight lang=text>
mysql soft nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql hard nproc 10240
</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.
 
<syntaxhighlight lang=bash>
# systemctl edit mysql.service
</syntaxhighlight>
and enter:
<syntaxhighlight lang=ini>
[Service]
        LimitNOFILE=1024000
</syntaxhighlight>
<syntaxhighlight lang=bash>
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# MySQL systemd service file
...
# /etc/systemd/system/mysql.service.d/override.conf
[Service]
        LimitNOFILE=1024000
</syntaxhighlight>
 
Do not forget to activate and check the limit
<syntaxhighlight lang=bash>
# 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   
</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.
<syntaxhighlight lang=bash>
# systemctl edit mysql.service
</syntaxhighlight>
and enter:
<syntaxhighlight lang=ini>
[Unit]
        Description=MySQL Community Server
        After=network.target
        After=nfs-client.target
</syntaxhighlight>
 
<syntaxhighlight lang=bash>
# 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
</syntaxhighlight>
 
Do not forget to activate the changes...
<syntaxhighlight lang=bash>
# systemctl daemon-reload
# systemctl restart mysql
</syntaxhighlight>
 
... and check they are active:
<syntaxhighlight lang=bash>
# systemctl list-dependencies --after mysql.service | grep nfs-client.target
● ├─nfs-client.target
</syntaxhighlight>
 
====== /etc/idmapd.conf ======
<syntaxhighlight lang=text>
# Domain = localdomain
Domain = this.domain.tld
</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 ======
<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
</syntaxhighlight>
====== /etc/mysql/mysql.conf.d/mysqld.cnf ======
<syntaxhighlight lang=ini>
[mysqld]
...
datadir = /MYSQLNFS_DATA/data/mysql
...
</syntaxhighlight>
 
====== /etc/mysql/mysql.conf.d/innodb.cnf ======
<syntaxhighlight lang=ini>
[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
</syntaxhighlight>
<syntaxhighlight lang=mysql>
# 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              |
+---------------------------+------------------------------------+
</syntaxhighlight>
====== /etc/mysql/mysql.conf.d/query_cache.cnf ======
<syntaxhighlight lang=ini>
[mysqld]
#
# * Query Cache Configuration
#
query_cache_type        = 1
query_cache_limit        = 256K
query_cache_min_res_unit = 2k
query_cache_size        = 80M
</syntaxhighlight>
 
<syntaxhighlight lang=mysql>
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)
</syntaxhighlight>
 
====== apparmor : /etc/apparmor.d/local/usr.sbin.mysqld ======
<syntaxhighlight lang=text>
# 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,
</syntaxhighlight>
 
====== Short stupid performance test ======
<syntaxhighlight lang=bash>
# 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
</syntaxhighlight>
Some things seem to work...


==Sample InnoDB configuration==
==Sample InnoDB configuration==


/etc/mysql/conf.d/innodb.cnf  
/etc/mysql/conf.d/innodb.cnf  
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
# InnoDB Parameters
# InnoDB Parameters
Line 223: Line 730:
innodb_log_file_size=100M
innodb_log_file_size=100M
innodb_log_group_home_dir=/var/lib/mysql/ib_log
innodb_log_group_home_dir=/var/lib/mysql/ib_log
</source>
</syntaxhighlight>
 
==Analyze==


==Analyse==
<syntaxhighlight lang=mysql>
<source lang=mysql>
mysql> select * from <tablename> PROCEDURE ANALYSE();
mysql> select * from <tablename> PROCEDURE ANALYSE();
</source>
</syntaxhighlight>
 
<syntaxhighlight lang=mysql>
mysql> SHOW /*!50000 GLOBAL*/ STATUS;
</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
1 row in set (0,00 sec)
</syntaxhighlight>
 
 
===percona-toolkit===
 
<syntaxhighlight lang=bash>
# 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
</syntaxhighlight>
 
===Sysbench===
<syntaxhighlight lang=bash>
# 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
</syntaxhighlight>


==Recover a damaged root account==
==Recover a damaged root account==
===Lost grants===
===Lost grants===
<source lang=bash>
Try out:
<syntaxhighlight lang=bash>
# service mysql stop
# service mysql stop
# echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
# echo "grant all privileges on *.* to 'root'@'localhost' with grant option;" > /root/mysql-init
Line 240: Line 819:
# rm /root/mysql-init
# rm /root/mysql-init
# service mysql start
# service mysql start
</source>
</syntaxhighlight>
Or:
<syntaxhighlight lang=bash>
# 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
</syntaxhighlight>


===Lost password===
===Lost password===
<source lang=bash>
<syntaxhighlight lang=bash>
# service mysql stop
# service mysql stop
# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
# echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('the root password for mysql');" > /root/mysql-init
Line 251: Line 839:
# rm /root/mysql-init
# rm /root/mysql-init
# service mysql start
# service mysql start
</source>
</syntaxhighlight>
 
==Structured configuration==
 
This is the default in Ubuntus /etc/mysql/my.cnf:
<syntaxhighlight lang=mysql>
...
#
# * 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/
</syntaxhighlight>
 
/etc/mysql/conf.d/innodb.cnf:
<syntaxhighlight lang=mysql>
[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
</syntaxhighlight>
 
/etc/mysql/conf.d/myisam.cnf:
<syntaxhighlight lang=mysql>
[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
</syntaxhighlight>
 
/etc/mysql/conf.d/mysqld.cnf:
<syntaxhighlight lang=mysql>
[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
</syntaxhighlight>
 
/etc/mysql/conf.d/mysqld_safe.cnf:
<syntaxhighlight lang=mysql>
[mysqld_safe]
</syntaxhighlight>
 
/etc/mysql/conf.d/mysqld_safe_syslog.cnf:
<syntaxhighlight lang=mysql>
[mysqld_safe]
syslog
</syntaxhighlight>
 
/etc/mysql/conf.d/query_cache.cnf:
<syntaxhighlight lang=mysql>
[mysqld]
query_cache_limit        = 4M
query_cache_size        = 128M
query_cache_min_res_unit = 2K
</syntaxhighlight>
 
=MySQL Clients=
Small one liners for testing purposes.
==PHP==
===PHP PDO===
<syntaxhighlight lang=php>
$ 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;
'
</syntaxhighlight>

Latest revision as of 14: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;

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;
'
Retrieved from "https://lars.timmann.de/wiki/index.php?title=MySQL_Tipps_und_Tricks&oldid=2700"