MySQL Tipps und Tricks

Tipps und Tricks

Oneliner

All grants

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

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)

Filesystems for MySQL

ext3/ext4

Mountoptions are:

• 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

Sample InnoDB configuration

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

[mysqld]
# InnoDB Parameters

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

# bulk_insert_buffer_size
bulk_insert_buffer_size=256M

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

# innodb_thread_concurrency= 2*CPUs
innodb_thread_concurrency=4

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

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

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

Analyze

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

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

• See [MySQL Performance Tuning]

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_rw \
    --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

# 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

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