MySQL Tipps und Tricks: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
 
(17 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("`",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:
Or a little nicer:
<source lang=bash>
<syntaxhighlight lang=bash>
#!/bin/bash
#!/bin/bash


Line 18: Line 34:
function usage () {
function usage () {
   cat << EOH
   cat << EOH
Usage: $0 [--grant-user <pattern>|--gu <pattern>] [--help] ...
Usage: $0 [--all] [--grant-user <pattern>|--gu <pattern>] [--grant-db <pattern>|--gdb <pattern>] [--help] ...
           --help:    This output
           --help:    This output
--grant-user|--gu:    You can specify this option several times.
--grant-user|--gu:    You can specify this option several times.
Line 27: Line 43:
                       The pattern may contain % as wildcard.
                       The pattern may contain % as wildcard.
                       If the pattern is @% it shows all grants where host is exactly '%'.
                       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
               ...:    Optional parameters to the mysql command
EOH
EOH
Line 32: Line 52:
}
}


show_all_grants=0
declare -a grant_user
declare -a grant_user
for ((param=1;param<=${#};param++))
for ((param=1;param<=${#};param++))
Line 38: Line 59:
   --grant-user|--gu)
   --grant-user|--gu)
     param=$[ ${param} + 1 ]
     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
     # delete 2 parameters from list and set back $param
     set -- "${@:1:param-2}" "${@:param+1}"
     set -- "${@:1:param-2}" "${@:param+1}"
     param=$[ ${param} - 2 ]
     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)
   --help)
Line 51: Line 85:
done
done


# if no users specified, show all grants
count=${#grant_user[@]}
if [ ${#grant_user[@]} -eq 0 ]
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
then
   printf -- '--\n-- %s\n--\n' "all grants";
   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 ) )
   grant_user=( $(mysql $* --silent --skip-column-names --execute "select concat('\'',user,'\'@\'',host,'\'') as user from mysql.user" | sort ) )
else
fi
  # Fill users which are without host
 
  count=${#grant_user[@]}
for user in ${grant_user[@]}
   for((param=0;param<count;param++))
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
   do
     user="${grant_user[${param}]}"
     regex='GRANT[ ]+.*[ ]+ON[ ]+(FUNCTION[ ]+|)`([^`]*)`\..*'
     if [[ ${user} != ?*"@"?* ]]
     if [[ $grant =~ $regex ]]
     then
     then
       before=${#grant_user[@]}
       database=${BASH_REMATCH[2]}
       if [[ ${user} == "@"?* ]]
       if [ ${#grant_db[@]} -gt 0 ]
       then
       then
        host="${user/@}"
if [[ " ${grant_db[@]} " =~ " ${database} " ]]
        if [[ "_${host}_" == "_%_" ]]
         then
         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}" )
  echo "${grant}"
        else
fi
          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
       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}" )
         echo "${grant}"
       fi
       fi
       after=${#grant_user[@]}
    else
      param=$[ param + after - before ]
       echo "${grant}"
      count=$[ count + after - before ]
     fi
     fi
   done
   done
fi
for user in ${grant_user[@]}
do
  printf -- '--\n-- %s\n--\n' "${user}";
  mysql $* --silent --skip-column-names --execute "show create user ${user}; show grants for ${user}" | sed 's/$/;/'
done
done
</source>
</syntaxhighlight>


===Last update time===
===Last update time===
* Per table
* 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;
mysql> SELECT TABLE_SCHEMA AS DB,TABLE_NAME,UPDATE_TIME FROM INFORMATION_SCHEMA.TABLES ORDER BY DB,UPDATE_TIME;
</source>
</syntaxhighlight>


* Per database
* 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;
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==
==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 131: 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 161: 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 175: 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 ==
== Slave ==
Line 186: Line 259:
==== What did we see from the master ====
==== What did we see from the master ====
Read the binlog from 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
# 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
if you get
  ERROR: Failed on connect: SSL connection error: protocol version mismatch
  ERROR: Failed on connect: SSL connection error: protocol version mismatch
try
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
# 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:
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:"'
# mysql -e 'show slave status\G' | awk '$1=="Master_Log_File:"'
</source>
</syntaxhighlight>


==Filesystems for MySQL==
==Filesystems for MySQL==
Line 205: Line 278:


====Create Options====
====Create Options====
<source lang=bash>
<syntaxhighlight lang=bash>
# mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data
# mkfs.ext4 -b 4096  /dev/mapper/vg--data-lv--ext4--mysql_data
</source>
</syntaxhighlight>


====Mountoptions====
====Mountoptions====
Line 221: 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 244: 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 268: 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 299: 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 306: 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 ===
=== NFS ===
==== NFSv4 ====
==== NFSv4 ====
===== On NetApp CDOT SVM =====
===== 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::> 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::>
Line 330: Line 403:
cdot1nfsv4        MYSQLNFS_LOG          1GB  mysql_clients /MYSQLNFS_LOG   
cdot1nfsv4        MYSQLNFS_LOG          1GB  mysql_clients /MYSQLNFS_LOG   
2 entries were displayed.
2 entries were displayed.
</source>
</syntaxhighlight>


Links:
Links:
Line 339: Line 412:


===== On Linux =====
===== 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 ======
====== /etc/sysctl.d/99-mysql.conf ======
<source>
<syntaxhighlight lang=text>
#
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
Line 356: Line 437:
vm.swappiness = 0
vm.swappiness = 0
vm.vfs_cache_pressure = 50
vm.vfs_cache_pressure = 50
</source>
</syntaxhighlight>


====== /etc/sysctl.d/99-netapp-nfs.conf ======
====== /etc/sysctl.d/99-netapp-nfs.conf ======
<source>
<syntaxhighlight lang=text>
#
#
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
## http://www.ajohnstone.com/achives/optimizing-mysql-over-nfs-with-netapp/
Line 403: Line 484:
net.ipv4.neigh.default.base_reachable_time = 86400
net.ipv4.neigh.default.base_reachable_time = 86400
net.ipv4.neigh.default.gc_stale_time = 86400
net.ipv4.neigh.default.gc_stale_time = 86400
</source>
</syntaxhighlight>




Line 409: Line 490:
====== Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf ======
====== Raise allowed number of open files for mysql in /etc/security/limits.d/mysql.conf ======


<source>
<syntaxhighlight lang=text>
mysql soft nofile 1024000
mysql soft nofile 1024000
mysql hard nofile 1024000
mysql hard nofile 1024000
mysql soft nproc 10240
mysql soft nproc 10240
mysql hard nproc 10240
mysql hard nproc 10240
</source>
</syntaxhighlight>


====== Modify systemd mysql.service to raise the number of files limit ======
====== 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.
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
# systemctl edit mysql.service
</source>
</syntaxhighlight>
and enter:
and enter:
<source lang=inifile>
<syntaxhighlight lang=ini>
[Service]
[Service]
         LimitNOFILE=1024000
         LimitNOFILE=1024000
</source>
</syntaxhighlight>
<source lang=bash>
<syntaxhighlight lang=bash>
# systemctl cat mysql
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# /lib/systemd/system/mysql.service
Line 435: Line 516:
[Service]
[Service]
         LimitNOFILE=1024000
         LimitNOFILE=1024000
</source>
</syntaxhighlight>


Do not forget to activate and check the limit
Do not forget to activate and check the limit
<source lang=bash>
<syntaxhighlight lang=bash>
# systemctl daemon-reload
# systemctl daemon-reload
# systemctl restart mysql
# systemctl restart mysql
Line 444: Line 525:
Limit                    Soft Limit          Hard Limit          Units     
Limit                    Soft Limit          Hard Limit          Units     
Max open files            1024000              1024000              files     
Max open files            1024000              1024000              files     
</source>
</syntaxhighlight>


====== Modify systemd service to wait for NFS ======
====== 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.
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
# systemctl edit mysql.service
</source>
</syntaxhighlight>
and enter:
and enter:
<source lang=inifile>
<syntaxhighlight lang=ini>
[Unit]
[Unit]
         Description=MySQL Community Server
         Description=MySQL Community Server
         After=network.target
         After=network.target
         After=nfs-client.target
         After=nfs-client.target
</source>
</syntaxhighlight>


<source lang=bash>
<syntaxhighlight lang=bash>
# systemctl cat mysql
# systemctl cat mysql
# /lib/systemd/system/mysql.service
# /lib/systemd/system/mysql.service
Line 492: Line 573:
[Service]
[Service]
         LimitNOFILE=1024000
         LimitNOFILE=1024000
</source>
</syntaxhighlight>


Do not forget to activate the changes...
Do not forget to activate the changes...
<source lang=bash>
<syntaxhighlight lang=bash>
# systemctl daemon-reload
# systemctl daemon-reload
# systemctl restart mysql
# systemctl restart mysql
</source>
</syntaxhighlight>


... and check they are active:
... and check they are active:
<source lang=bash>
<syntaxhighlight lang=bash>
# systemctl list-dependencies --after mysql.service | grep nfs-client.target
# systemctl list-dependencies --after mysql.service | grep nfs-client.target
● ├─nfs-client.target
● ├─nfs-client.target
</source>
</syntaxhighlight>


====== /etc/idmapd.conf ======
====== /etc/idmapd.conf ======
<source lang=conf>
<syntaxhighlight lang=text>
# Domain = localdomain
# Domain = localdomain
Domain = this.domain.tld
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 ======
====== /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_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
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 ======
====== /etc/mysql/mysql.conf.d/mysqld.cnf ======
<source lang=inifile>
<syntaxhighlight lang=ini>
[mysqld]
[mysqld]
...
...
datadir = /MYSQLNFS_DATA/data/mysql
datadir = /MYSQLNFS_DATA/data/mysql
...
...
</source>
</syntaxhighlight>


====== /etc/mysql/mysql.conf.d/innodb.cnf ======
====== /etc/mysql/mysql.conf.d/innodb.cnf ======
<source lang=inifile>
<syntaxhighlight lang=ini>
[mysqld]
[mysqld]
#
#
Line 537: Line 624:
innodb_flush_log_at_trx_commit  = 2
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table          = on
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%'"
# mysql -e "show variables where variable_name like '%dir' and value like '/MYSQLNFS%'"
+---------------------------+------------------------------------+
+---------------------------+------------------------------------+
Line 547: Line 634:
| innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log              |
| innodb_log_group_home_dir | /MYSQLNFS_LOG/ib_log              |
+---------------------------+------------------------------------+
+---------------------------+------------------------------------+
</source>
</syntaxhighlight>
====== /etc/mysql/mysql.conf.d/query_cache.cnf ======
====== /etc/mysql/mysql.conf.d/query_cache.cnf ======
<source lang=inifile>
<syntaxhighlight lang=ini>
[mysqld]
[mysqld]
#
#
Line 558: Line 645:
query_cache_min_res_unit = 2k
query_cache_min_res_unit = 2k
query_cache_size        = 80M
query_cache_size        = 80M
</source>
</syntaxhighlight>


<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
+------------------+-------+
Line 580: Line 667:
+------------------------------+----------+
+------------------------------+----------+
5 rows in set (0,00 sec)
5 rows in set (0,00 sec)
</source>
</syntaxhighlight>


====== apparmor : /etc/apparmor.d/local/usr.sbin.mysqld ======
====== apparmor : /etc/apparmor.d/local/usr.sbin.mysqld ======
<source lang=apparmor>
<syntaxhighlight lang=text>
# vim:syntax=apparmor
# vim:syntax=apparmor


Line 598: Line 685:
   /MYSQLNFS_LOG/ r,
   /MYSQLNFS_LOG/ r,
   /MYSQLNFS_LOG/** rwk,
   /MYSQLNFS_LOG/** rwk,
</source>
</syntaxhighlight>


====== Short stupid performance test ======
====== Short stupid performance test ======
<source lang=bash>
<syntaxhighlight lang=bash>
# time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
# time dd if=/dev/zero of=/MYSQLNFS_DATA/io.test bs=16k count=65536
65536+0 records in
65536+0 records in
Line 610: Line 697:
user 0m0.016s
user 0m0.016s
sys 0m0.672s
sys 0m0.672s
</source>
</syntaxhighlight>
Some things seem to work...
Some things seem to work...


Line 616: Line 703:


/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 643: 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==
==Analyze==
<source lang=mysql>
 
<syntaxhighlight lang=mysql>
mysql> select * from <tablename> PROCEDURE ANALYSE();
mysql> select * from <tablename> PROCEDURE ANALYSE();
</source>
</syntaxhighlight>


<source lang=mysql>
<syntaxhighlight lang=mysql>
mysql> SHOW /*!50000 GLOBAL*/ STATUS;
mysql> SHOW /*!50000 GLOBAL*/ STATUS;
</source>
</syntaxhighlight>
* See [[http://de.slideshare.net/shinguz/pt-presentation-11465700 MySQL Performance Tuning]]
* 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===
===percona-toolkit===


<source lang=bash>
<syntaxhighlight lang=bash>
# aptitude install percona-toolkit
# aptitude install percona-toolkit


Line 676: Line 776:
   +- Table
   +- Table
       table          user
       table          user
</source>
</syntaxhighlight>


===Sysbench===
===Sysbench===
<source lang=bash>
<syntaxhighlight lang=bash>
# mysql -u root -e "create database sbtest;"
# mysql -u root -e "create database sbtest;"
# sysbench \
# sysbench \
Line 706: Line 806:
     run
     run
# mysql -u root_rw -e "drop table sbtest;" sbtest
# mysql -u root_rw -e "drop table sbtest;" sbtest
</source>
</syntaxhighlight>


==Recover a damaged root account==
==Recover a damaged root account==
===Lost grants===
===Lost grants===
Try out:
Try out:
<source lang=bash>
<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 719: Line 819:
# rm /root/mysql-init
# rm /root/mysql-init
# service mysql start
# service mysql start
</source>
</syntaxhighlight>
Or:
Or:
<source lang=bash>
<syntaxhighlight lang=bash>
# service mysql stop
# service mysql stop
# mysqld_safe --skip-grant-tables &
# mysqld_safe --skip-grant-tables &
Line 728: Line 828:
# mysqladmin -u root  shutdown
# mysqladmin -u root  shutdown
# service mysql start
# service mysql start
</source>
</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 739: Line 839:
# rm /root/mysql-init
# rm /root/mysql-init
# service mysql start
# service mysql start
</source>
</syntaxhighlight>


==Structured configuration==
==Structured configuration==


This is the default in Ubuntus /etc/mysql/my.cnf:
This is the default in Ubuntus /etc/mysql/my.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
...
...
#
#
Line 751: Line 851:
#
#
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/conf.d/
</source>
</syntaxhighlight>


/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 783: Line 883:
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>


/etc/mysql/conf.d/myisam.cnf:
/etc/mysql/conf.d/myisam.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
#key_buffer = 512M
#key_buffer = 512M
Line 807: Line 907:
#  
#  
myisam_use_mmap=1
myisam_use_mmap=1
</source>
</syntaxhighlight>


/etc/mysql/conf.d/mysqld.cnf:
/etc/mysql/conf.d/mysqld.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
datadir        = /var/lib/mysql/data/data
datadir        = /var/lib/mysql/data/data
Line 866: Line 966:


performance_schema = ON
performance_schema = ON
</source>
</syntaxhighlight>


/etc/mysql/conf.d/mysqld_safe.cnf:
/etc/mysql/conf.d/mysqld_safe.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld_safe]
[mysqld_safe]
</source>
</syntaxhighlight>


/etc/mysql/conf.d/mysqld_safe_syslog.cnf:
/etc/mysql/conf.d/mysqld_safe_syslog.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld_safe]
[mysqld_safe]
syslog
syslog
</source>
</syntaxhighlight>


/etc/mysql/conf.d/query_cache.cnf:
/etc/mysql/conf.d/query_cache.cnf:
<source lang=mysql>
<syntaxhighlight lang=mysql>
[mysqld]
[mysqld]
query_cache_limit        = 4M
query_cache_limit        = 4M
query_cache_size        = 128M
query_cache_size        = 128M
query_cache_min_res_unit = 2K
query_cache_min_res_unit = 2K
</source>
</syntaxhighlight>


=MySQL Clients=
=MySQL Clients=
Line 891: Line 991:
==PHP==
==PHP==
===PHP PDO===
===PHP PDO===
<source lang=php>
<syntaxhighlight lang=php>
$ php -r '
$ php -r '
   $pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
   $pdo=new PDO("mysql:host=mydbhost;dbname=mydb", "user", "pass", ARRAY(
Line 906: Line 1,006:
   $pdo=null;
   $pdo=null;
'
'
</source>
</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"