• databases
• mysql

MySQL

Preface

This digest started in 2016 and is under permanent review. There may be options that no-longer or not-yet exist in the MySQL-ish Version you use. You will need to adjust the recommendations, processes and techniques to your very own case and gusto.

Commercial MySQL Support

You may acquire support for your database-server here.

• Percona Support

• MariaDB Support

• Oracle Support

About

• The official way to pronounce “MySQL” is “My Ess Que Ell” (not “my sequel”), but we do not mind if you pronounce it as “my sequel” or in some other localized way.
• The MySQL mascot is a dolphin called "Sakila".

• MySQL Docs

MySQL reference manual

• Still available as website

  • MySQL RefMan 5.6

  • MySQL RefMan 5.7

  • MySQL RefMan 8.0

• Only as PDF

  • MySQL RefMan 5.1 en

  • MySQL RefMan 5.1 en (attachment, 24.3MiB)

  • MySQL RefMan 5.1 de

  • MySQL RefMan 5.1 de (attachment, 9.5MiB)

  • MySQL RefMan 5.5

  • MySQL RefMan 5.5 en (attachment, 27MiB)

• Sakila Sample Database

Percona

• https://www.percona.com/

• Percona Software

Configure MySQL Clients

   1 touch /root/.my.cnf
   2 chmod 600 /root/.my.cnf

/root/.my.cnf

   1 [client]
   2 user            = root
   3 password        = <aSecureMySQLRootPassword>

This is not less secure than the default Debian configuration, because a user with root-privileges can also

• utilize mysql with the option --defaults-extra-file=/etc/mysql/debian.cnf or

• reconfigure mysql with the Option skip-grant-tables, restart mysqld and access without authentication.

mysql_config_editor

• mysql_config_editor

Regain Access

Please delete the statement that updates the passwords from your ~/.mysql_history

   1 mysql --defaults-extra-file=/etc/mysql/debian.cnf
   2 Welcome to the MySQL monitor.  Commands end with ; or \g.
   3 Your MySQL connection id is 204
   4 Server version: 5.5.46-0+deb8u1 (Debian)
   5 
   6 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
   7 
   8 Oracle is a registered trademark of Oracle Corporation and/or its
   9 affiliates. Other names may be trademarks of their respective
  10 owners.
  11 
  12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13 
  14 mysql> 
  15 mysql> update mysql.user set password=PASSWORD('aSecureMySQLRootPassword') where user='root';
  16 Query OK, 4 rows affected (0.02 sec)
  17 Rows matched: 4  Changed: 4  Warnings: 0
  18 mysql> update mysql.user set password=PASSWORD('aSecureMySQLKolabPassword') where user='kolab';
  19 Query OK, 1 row affected (0.00 sec)
  20 Rows matched: 1  Changed: 1  Warnings: 0
  21 mysql> flush privileges;
  22 Query OK, 0 rows affected (0.11 sec)

Test Access

Make sure it works, before you spend time in debugging.

   1 mysql
   2 Welcome to the MySQL monitor.  Commands end with ; or \g.
   3 Your MySQL connection id is 205
   4 Server version: 5.5.46-0+deb8u1 (Debian)
   5 
   6 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
   7 
   8 Oracle is a registered trademark of Oracle Corporation and/or its
   9 affiliates. Other names may be trademarks of their respective
  10 owners.
  11 
  12 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13 
  14 mysql> Bye
  15 mysql -u kolab -p
  16 Enter password: 
  17 Welcome to the MySQL monitor.  Commands end with ; or \g.
  18 Your MySQL connection id is 206
  19 Server version: 5.5.46-0+deb8u1 (Debian)
  20 
  21 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
  22 
  23 Oracle is a registered trademark of Oracle Corporation and/or its
  24 affiliates. Other names may be trademarks of their respective
  25 owners.
  26 
  27 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  28 
  29 mysql> quit
  30 Bye

Basic MySQL Setup

• There are some parameters that are important to be set, if performance matters.
• The configuration is dosini-style and will not take effect if you forget

  • to make sure the file-suffix is .cnf (without "o")!

  • to add a group definition (like "[mysqld]") in front of your configuration
  • the assignment operator "="
• Within option names, dash (-) and underscore (_) may be used interchangeably.

List of parameters to adjust

The defaults were always fine for very small databases with little demand on performance. But once they grew and needed to be fast, there had some tuning to be done. With newer versions (v5.7+) this improved a lot! New installations are almost perfectly tuned by default.

Pre mysql_install_db

• datadir

• default_character_set

• innodb_file_per_table

• innodb_log_file_size

• performance_schema

• tmpdir

• slave_load_tmpdir

• innodb_tmpdir

During tuning

• innodb_log_buffer_size

• innodb_buffer_pool_size

• innodb_buffer_pool_instances

• innodb_stats_on_metadata

• query_cache_limit

• query_cache_size

• open_files_limit

• table_open_cache

• slow_query_log

• slow_query_log_file

• long_query_time

In the end that's not too much - we can handle them.

Filesystem Layout

Please also see:
#Initializing MySQL

Get current config

   1 FSLAYOUT_CNF="/etc/mysql/conf.d/fslayout.cnf"
   2 echo '[mysqld]' > "$FSLAYOUT_CNF"
   3 mysqld --help --verbose 2>/dev/null \
   4         |grep '^\w.*dir\s' \
   5         |sed -r 's/^([[:alnum:]-]*\s*)/#\1= /' >> "$FSLAYOUT_CNF"
   6 ### ALTERNATIVELY
   7 mysqld --help --verbose 2>/dev/null \
   8         |awk ' /^\w.*dir\s/ {print "#"$1, "=" ,$2}' \
   9         |column -t >> "$FSLAYOUT_CNF"

/etc/mysql/conf.d/fslayout.cnf

   1 [mysqld]
   2 #basedir                    = /usr
   3 #character-sets-dir         = /usr/share/mysql/charsets/
   4 datadir                     = /var/lib/mysql/data
   5 #innodb-data-home-dir       = (No  default  value)
   6 #innodb-log-group-home-dir  = (No  default  value)
   7 #innodb_tmpdir              =
   8 innodb_tmpdir               = /var/lib/mysql/tmp/innodb
   9 #lc-messages-dir            = /usr/share/mysql/
  10 #plugin-dir                 = /usr/lib/mysql/plugin/
  11 #slave-load-tmpdir          = /tmp
  12 slave-load-tmpdir           = /var/lib/mysql/tmp/slave
  13 #tmpdir                     = /tmp

To save some disk-IO, we mount a tmpfs at /tmp.

/etc/fstab

   1 #<file_system>  <mount_point>  <type>  <options>  <dump>  <pass>
   2 none            /tmp           tmpfs   defaults   0       0

You might need to add some restrictive options to RAM usage to make memory layout more predictable size=4g.

And in this case we also should point other temporary files directories away from the volatile directory.

• #slave_load_tmpdir to have a permanent location

• #innodb_tmpdir to avoid filling up the RAM

Temporary files

MySQL uses temporary files in various on ocasions.

• Where MySQL Stores Temporary Files

• MySQL: Disk Space Exhaustion for Implicit Temporary Tables

Default configuration

   1 MariaDB [(none)]> SHOW VARIABLES WHERE VARIABLE_NAME LIKE '%tmp%' OR VARIABLE_NAME LIKE '%heap%';
   2 +----------------------------+----------------------+
   3 | Variable_name              | Value                |
   4 +----------------------------+----------------------+
   5 | default_tmp_storage_engine |                      |
   6 | encrypt_tmp_disk_tables    | OFF                  |
   7 | encrypt_tmp_files          | OFF                  |
   8 | innodb_tmpdir              |                      |
   9 | max_heap_table_size        | 16777216             |
  10 | max_tmp_tables             | 32                   |
  11 | slave_load_tmpdir          | /tmp                 |
  12 | tmp_disk_table_size        | 18446744073709551615 |
  13 | tmp_memory_table_size      | 16777216             |
  14 | tmp_table_size             | 16777216             |
  15 | tmpdir                     | /tmp                 |
  16 +----------------------------+----------------------+
  17 11 rows in set (0.001 sec)

MySQL arranges that temporary files are removed if mysqld is terminated. On platforms that support it (such as Unix), this is done by unlinking the file after opening it. The disadvantage of this is that the name does not appear in directory listings and you do not see a big temporary file that fills up the file system in which the temporary file directory is located. (In such cases, lsof +L1 may be helpful in identifying large files associated with mysqld.)

When sorting (ORDER BY or GROUP BY), MySQL normally uses one or two temporary files.

tmpdir

tmpdir might reside on a non-permanent location.
mysqld_tmpdir

slave_load_tmpdir

The name of the directory where the slave creates temporary files. When the slave SQL thread replicates a LOAD DATA statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table.
slave_load_tmpdir

innodb_tmpdir

Used to define an alternate directory for temporary sort files created during online ALTER TABLE operations that rebuild the table.
innodb_tmpdir

Logging

• The general-log is a performance killer and should only be used in very seldom cases.
• The error-log can overrides the default configuration to have in one place if you like.
• Slow-query log is essential stuff for performance analysis.

/etc/mysql/conf.d/logging.cnf

   1 [mysqld]
   2 general-log                                                = FALSE
   3 general-log-file                                           = /var/log/mysql/mail.log
   4 #log-error                                                 = /var/log/mysql/error.log
   5 #log-isam                                                  = myisam.log
   6 #log-output                                                = FILE
   7 log-queries-not-using-indexes                              = TRUE
   8 #log-raw                                                   = FALSE
   9 #log-short-format                                          = FALSE
  10 log-slow-admin-statements                                  = TRUE
  11 #log-slow-slave-statements                                 = FALSE
  12 #log-tc                                                    = tc.log
  13 #log-tc-size                                               = 24576
  14 #log-throttle-queries-not-using-indexes                    = 0
  15 #log-warnings                                              = 1
  16 slow-query-log                                             = TRUE
  17 slow-query-log-file                                        = /var/log/mysql/mail-slow.log
  18 #long-query-time                                           = 10
  19 

InnoDB configuration

The InnoDB Storage Engine

I like generating a file /etc/mysql/conf.d/innodb.cnf, which is included at the end of /etc/mysql/my.cnf overriding the defaults, with the following commands.

   1 INNODB_CNF="/etc/mysql/conf.d/innodb.cnf"
   2 echo '[mysqld]' > "$INNODB_CNF"
   3 mysqld --help --verbose 2>/dev/null \
   4         |grep '^innodb' \
   5         |sed -r 's/^([[:alnum:]-]*\s*)/#\1= /' >> "$INNODB_CNF"
   6 ### ALTERNATIVELY
   7 mysqld --help --verbose 2>/dev/null \
   8         |awk ' /^innodb/ {print "#"$1, "=" ,$2}' \
   9         |column -t >> "$INNODB_CNF"

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

   1 [mysqld]
   2 #innodb                           = ON
   3 #innodb-adaptive-flushing         = TRUE
   4 #innodb-adaptive-hash-index       = TRUE
   5 #innodb-additional-mem-pool-size  = 8388608
   6 #innodb-autoextend-increment      = 8
   7 #innodb-autoinc-lock-mode         = 1
   8 #innodb-buffer-page               = ON
   9 #innodb-buffer-page-lru           = ON
  10 ### Default 1
  11 innodb-buffer-pool-instances      = 1
  12 ### Default 128M
  13 innodb-buffer-pool-size           = 256M
  14 #innodb-buffer-pool-stats         = ON
  15 #innodb-change-buffering          = all
  16 #innodb-checksums                 = TRUE
  17 #innodb-cmp                       = ON
  18 #innodb-cmp-reset                 = ON
  19 #innodb-cmpmem                    = ON
  20 #innodb-cmpmem-reset              = ON
  21 #innodb-commit-concurrency        = 0
  22 #innodb-concurrency-tickets       = 500
  23 #innodb-data-file-path            = (No          default  value)
  24 #innodb-data-home-dir             = (No          default  value)
  25 #innodb-doublewrite               = TRUE
  26 #innodb-fast-shutdown             = 1
  27 #innodb-file-format               = Antelope
  28 #innodb-file-format-check         = TRUE
  29 #innodb-file-format-max           = Antelope
  30 #innodb-file-io-threads           = 4
  31 innodb-file-per-table             = true
  32 #innodb-flush-log-at-trx-commit   = 1
  33 #innodb-flush-method              = (No          default  value)
  34 #innodb-force-load-corrupted      = FALSE
  35 #innodb-force-recovery            = 0
  36 #innodb-io-capacity               = 200
  37 #innodb-large-prefix              = FALSE
  38 #innodb-lock-wait-timeout         = 50
  39 #innodb-lock-waits                = ON
  40 #innodb-locks                     = ON
  41 #innodb-locks-unsafe-for-binlog   = FALSE
  42 #innodb-log-buffer-size           = 8388608
  43 ### Default 5M
  44 innodb-log-file-size              = 128M
  45 #innodb-log-files-in-group        =  2
  46 #innodb-log-group-home-dir        = (No          default  value)
  47 #innodb-max-dirty-pages-pct       = 75
  48 #innodb-max-purge-lag             = 0
  49 #innodb-mirrored-log-groups       = 1
  50 #innodb-old-blocks-pct            = 37
  51 #innodb-old-blocks-time           = 0
  52 #innodb-open-files                = 300
  53 #innodb-print-all-deadlocks       = FALSE
  54 #innodb-purge-batch-size          = 20
  55 #innodb-purge-threads             = 0
  56 #innodb-random-read-ahead         = FALSE
  57 #innodb-read-ahead-threshold      = 56
  58 #innodb-read-io-threads           = 4
  59 #innodb-replication-delay         = 0
  60 #innodb-rollback-on-timeout       = FALSE
  61 #innodb-rollback-segments         = 128
  62 #innodb-spin-wait-delay           = 6
  63 #innodb-stats-method              = nulls_equal
  64 ### DISABLE CARDINALITY GUESS ON METADATA ACCESS
  65 innodb-stats-on-metadata          = FALSE
  66 #innodb-stats-sample-pages        = 8
  67 #innodb-status-file               = FALSE
  68 #innodb-strict-mode               = FALSE
  69 #innodb-support-xa                = TRUE
  70 #innodb-sync-spin-loops           = 30
  71 #innodb-table-locks               = TRUE
  72 #innodb-thread-concurrency        = 0
  73 #innodb-thread-sleep-delay        = 10000
  74 #innodb-trx                       = ON
  75 #innodb-use-native-aio            = TRUE
  76 #innodb-use-sys-malloc            = TRUE
  77 #innodb-write-io-threads          = 4

innodb_buffer_pool

• InnoDB Startup Options and System Variables

Sizing

• Configuring InnoDB Buffer Pool Size

• How MySQL Uses Memory

• InnoDB Startup Configuration

innodb_buffer_pool_size

innodb_buffer_pool_size

• Is always to be seen in conjunction with #innodb_buffer_pool_instances and #innodb_buffer_pool_chunk_size

• The larger, the less disk-IO.
• Default: 128MB

innodb_buffer_pool_instances

innodb_buffer_pool_instances

With large buffer-pools you can optimize concurrency by dividing the pool into regions. Each chunk should be at least 1GiB in size. This option only takes effect if the buffer-pool is bigger than 1GiB. The default value is 1 or 8, min. value is 1, max. value is 64.

innodb_buffer_pool_chunk_size

innodb_buffer_pool_chunk_size

Default: 128MiB

Size of memory operations that MySQL performs, e.g. when changing innodb_buffer_pool_size.

Reduce Warmup

• Saving and Restoring the Buffer Pool State

• MySQL Glossary Buffer Pool Warmup

Introduced: MySQL 5.6+ Default: ON (5.7+), OFF (5.6)

innodb_buffer_pool_dump_at_shutdown

innodb_buffer_pool_load_at_startup

innodb_buffer_pool_dump_pct

innodb_buffer_pool_dump_pct

Specifies the percentage of the most recently used pages for each buffer pool to read out and dump.

The range is 1 to 100. The default value is 25.

For example, if there are 4 buffer pools with 100 pages each, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.

innodb_buffer_pool_filename

innodb_buffer_pool_filename

Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is named ib_buffer_pool and is located in the InnoDB data directory. A non-default location must be specified relative to the data directory.

innodb_log

• Optimizing InnoDB Redo Logging

innodb_log_buffer

innodb_log_buffer_size

innodb_log_buffer_size

• Helps with large transactions: The larger, the less disk-io and better performance.
• Default: 16MiB

innodb_log_file

One of the ib_logfileN files that make up the redo log. Data is written to these files from the log buffer memory area.

• By default located in datadir: ib_logfile0, ib_logfile1

• Sum of required diskspace is #innodb_log_file_size * #innodb_log_files_in_group

innodb_log_file_size

innodb_log_file_size

• There should be enough redo log space to handle more than an hour of write activity.
• Default: 48MiB

innodb_log_files_in_group

innodb_log_files_in_group

• Log files are rotated.
• Default: 2 (recommmended)

Initializing MySQL

Prior to executing these steps you should consider creating a #Backup with mysqldump

Cleanup/purge anything or move it away (as you like and have the resources)

   1 systemctl stop mysql
   2 my /var/lib/mysql{,_old}
   3 #rm -r /var/lib/mysql/{ib*,mysql,performance_schema}
   4 

Initialization

   1 install -o mysql -g mysql -m 0750 \
   2         -d /var/lib/mysql/{,data,relay_logs,bin_logs,tmp,tmp/{slave,innodb}}
   3 ls -laR /var/lib/mysql
   4 
   5 mysql_install_db
   6 Installing MySQL system tables...
   7 160109  0:19:55 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1) starting as process 5043 ...
   8 
   9 OK
  10 Filling help tables...
  11 160109  0:19:57 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0+deb8u1) starting as process 5049 ...
  12 OK
  13 
  14 To start mysqld at boot time you have to copy
  15 support-files/mysql.server to the right place for your system
  16 
  17 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
  18 To do so, start the server, then issue the following commands:
  19 
  20 /usr/bin/mysqladmin -u root password 'new-password'
  21 /usr/bin/mysqladmin -u root -h mail password 'new-password'
  22 
  23 Alternatively you can run:
  24 /usr/bin/mysql_secure_installation
  25 
  26 which will also give you the option of removing the test
  27 databases and anonymous user created by default.  This is
  28 strongly recommended for production servers.
  29 
  30 See the manual for more instructions.
  31 
  32 You can start the MySQL daemon with:
  33 cd /usr ; /usr/bin/mysqld_safe &
  34 
  35 You can test the MySQL daemon with mysql-test-run.pl
  36 cd /usr/mysql-test ; perl mysql-test-run.pl
  37 
  38 Please report any problems at http://bugs.mysql.com/

Open a tmux session, the log (following) and start the MySQL-Daemon.

   1 colortail -f /var/log/mysql/error.log
   2 systemctl start mysql.service

If you want to restore the complete data see #Restore with mysqldump Otherwise on debian you should create a user according to your /etc/mysql/debian.cnf and grant him the appropriate privileges.

   1 GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY '<password>';

Timezone Configuration

• Useful to make timezone conversions (e.g. to UTC)
• Import timezone data into MySQL-Tables.

   1 # mysql_tzinfo_to_sql
   2 Usage:
   3  mysql_tzinfo_to_sql timezonedir
   4  mysql_tzinfo_to_sql timezonefile timezonename
   5  mysql_tzinfo_to_sql --leap timezonefile
   6 # mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql mysql
   7 Warning: Unable to load '/usr/share/zoneinfo//iso3166.tab' as time zone. Skipping it.
   8 Warning: Unable to load '/usr/share/zoneinfo//leap-seconds.list' as time zone. Skipping it.
   9 Warning: Unable to load '/usr/share/zoneinfo//zone.tab' as time zone. Skipping it.
  10 Warning: Unable to load '/usr/share/zoneinfo//zone1970.tab' as time zone. Skipping it.

Index Statistics Maintenance

It is difficult for mysql to determine the cardinality of a column index reliably. The technique used to guess the cardinality is called "random dives". innodb_stats_sample_pages (default 8) random index pages are screened and a distinct values for the whole table is extrapolated. The amount of data to be read (e.g. from disk) therefore depends on innodb_page_size, which is 16KiB by default.

The MySQL-Query-Optimzer then uses these gathered statistics to plan the execution. But the cardinality can be guessed terribly wrong because the content of the random pages can differ severely and finally leads to poor query performance. Better statistics can be gathered by increasing innodb_stats_sample_pages to a certain point under the cost of some IO.

Statistics are also gathered with ANALYSE TABLE … or mysqlanalyse.

Prior to mysql 5.6.6 statistics were not permanent innodb_stats_persistent and renewed on meta data access innodb-stats-on-metadata.

• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_persistent

• https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata

• https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-optimizer-statistics.html

Query Cache

Some info

• https://dev.mysql.com/doc/refman/5.7/en/query-cache.html

• https://www.percona.com/sites/default/files/MySQL_Query_Cache.pdf

Notes

• The query cache have been removed in MySQL 8.

• Always, adjust it according to the numbers. It strictly depends on the workload.
• Also consider disabling it (might win up to 13%).
• Updates/Inserts of a table invalidate the cached queries of that table.
  • Better with a reading workload.

General optimizations

• Batched writes
• Many small tables
• don't make it too big

Optimization process

• Cache hit-ratio good?
• Cacheable (DERMINISTIC? (RAND), LIMIT, …) ?
• Fragmentation?
• many low memory prunes?
• Write heavy workload?

/etc/mysql/conf.d/query-cache.cnf

   1 [mysqld]
   2 ### Default 1048576 (1M)
   3 query-cache-limit                                  = 2M
   4 #query-cache-min-res-unit                          = 4096
   5 ### Default 16777216 (2M)
   6 query-cache-size                                   = 32M
   7 #query-cache-type                                  = ON
   8 #query-cache-wlock-invalidate                      = FALSE

Monitoring

   1 SHOW GLOBAL STATUS LIKE 'Q%';
   2 SHOW GLOBAL STATUS LIKE 'Com_select%';

Tuning

/etc/mysql/conf.d/tuning.cnf

   1 [mysqld]
   2 open_files_limit                = 65536
   3 table_open_cache                = 2048
   4 table_open_cache_instances      = 2

Schemas

Performance Schema

Prior to 5.6 performance schema is disabled by default.

   1 [mysqld]
   2 performance_schema = on

Sys Schema

• https://github.com/mysql/mysql-sys

A collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

Optional Sysschema installation for MySQL 5.6 Sysschema is installed by default under MySQL 5.7 and MySQL 8 from Oracle. By default, on MySQL 5.6/5.7/8, performance schema is enabled by default. For previous 5.6 version, you can follow this command to create a new database sys containing very useful view on Performance schema

   1 curl "https://codeload.github.com/mysql/mysql-sys/zip/master" > sysschema.zip
   2 # check zip file
   3 unzip -l sysschema.zip
   4 unzip sysschema.zip
   5 cd mysql-sys-master
   6 mysql -uroot -p < sys_56.sql

Encoding

• http://www.bluebox.net/news/2009/07/mysql_encoding

• https://dev.mysql.com/doc/refman/8.0/en/charset-unicode.html

• https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/

• https://home.unicode.org/

Display default encoding

   1 mysql> show variables like 'char%';
   2 +--------------------------+----------------------------+
   3 | Variable_name            | Value                      |
   4 +--------------------------+----------------------------+
   5 | character_set_client     | utf8                       |
   6 | character_set_connection | utf8                       |
   7 | character_set_database   | utf8                       |
   8 | character_set_filesystem | binary                     |
   9 | character_set_results    | utf8                       |
  10 | character_set_server     | utf8                       |
  11 | character_set_system     | utf8                       |
  12 | character_sets_dir       | /usr/share/mysql/charsets/ |
  13 +--------------------------+----------------------------+
  14 8 rows in set (0.00 sec)

You should probably use: utf8mb4

• utf8mb3 uses max. 3 byte for a character and is a subset of utf8mb4.
• utf8mb4 uses max. 4 byte for a character

To set the default character encoding to utf8mb4 add the following lines to your my.cnf and restart the server.

   1 [client]
   2 default-character-set   = utf8mb4
   3 
   4 [mysqld]
   5 default-character-set   = utf8mb4

Converting the tables later may be a rattle.

Replication

There are several replication methods

• Simple MySQL internal replication

  • Statement based replication
  • Rowbased based replication (default since 5.1)

• Multi-master MySQL internal replication

  • 2-master replication
  • Circular replication
• MySQL NDB-Cluster (Network Database)

  • MySQL cluster features

  • not capable of XA-transactions
• Galera Cluster replication - to be examined - maybe later more
• Mariadb multi-source replication
• Percona multi-source replication

MySQL internal replication

• Official docs from the mysql reference manual:

  • Replication 5.1

  • Replication 5.5

  • Replication 5.6

  • Replication 5.7

• every slave can only have one master (replication source)
  • with the blackhole-storage engine a multi-source replication can be simulated
    • as long as you don't use "ALTER TABLE"

    • still a linear topology M1 (db1) -> M2 (db1(bh), db2) -> S1 (db1,db2)

• single threaded until MySQL 5.6

  • https://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html

Simple MySQL internal replication

• basic build-block to implement non-invasive backups

Multi-master MySQL internal replication

• enables the possibility of read-scaling
• no write-scaling, since binary log has to be applied to slaves as well
• limited capability to attach additional masters depending on the chosen row-increment

   1 SHOW MASTER STATUS\G
   2 SHOW SLAVE STATUS\G

Migration MyISAM to InnoDB

Migrate any table with Engine MyISAM (with exception of schemas "information_schema" and "mysql") to InnoDB.

Fast coded script that does the job.

alter_engine.sh

   1 #!/bin/bash
   2 
   3 DATABASES="$(mysql -Nrse 'show databases;')"
   4 for DATABASE in $DATABASES; do
   5 
   6         if (grep -qv -e information_schema -e mysql <<< "$DATABASE"); then
   7                 TABLES="$(mysql -Nrse "select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA = '$DATABASE' and ENGINE = 'MyISAM';")"
   8 
   9                 for TABLE in $TABLES; do
  10                 echo "Processing TABLE '$DATABASE.$TABLE'"
  11                 mysql -re "ALTER TABLE $DATABASE.$TABLE ENGINE=InnoDB;"
  12                 done
  13         else
  14                 echo "Skipping database '$DATABASE'"
  15                 continue
  16         fi
  17 done

Untested version with less spaghetti-style. alter_engine.sh

   1 #!/bin/bash
   2 
   3 DATABASES="$(mysql -Nrse 'show databases;')"
   4 for DATABASE in $DATABASES; do
   5         if (grep -qv -e information_schema -e mysql <<< "$DATABASE"); then
   6                 echo -e "Processing Database '$DATABASE'"
   7                 TABLES="$(mysql -Nrs <<-EOH
   8                         SELECT TABLE_NAME FROM information_schema.TABLES
   9                         WHERE TABLE_SCHEMA = '$DATABASE'
  10                         AND ENGINE = 'MyISAM';")"
  11                 EOH
  12                 for TABLE in $TABLES; do
  13                         echo -e "\nProcessing TABLE '$DATABASE.$TABLE'"
  14                         mysql -re "ALTER TABLE $DATABASE.$TABLE ENGINE=InnoDB;"
  15                 done
  16         else
  17                 echo -e "Database '$DATABASE' is on blacklist - skipping …\n"
  18                 continue
  19         fi
  20 done

Backup and Restore

Please keep in mind that backup is only valid if you can restore it!

• You'll probably need binary and statement-based/SQL backup of your databases.
  • A binary backup is quickly taken and restored.
  • A SQL backup of your data is much smaller and may may be restored elsewhere.

Offline

Copy with rsync

Create Initial Backup

For some experiments it is useful to keep a binary copy of the initial setup, that can be restored in a fraction of a second.

   1 systemctl stop mysql.service
   2 rsync -aP /var/lib/mysql/{data/,data_backup}
   3 systemctl start mysql.service

Restore Initial Backup

To avoid running mysql_install_db, mysql_secure_installation just do the following

   1 systemctl stop mysql.service
   2 rm -r /var/lib/mysql/data
   3 rsync -aP /var/lib/mysql/{data_backup/,data}
   4 systemctl start mysql.service

Online

Please try

• mydumper/myloader - since it is multi-threaded

• xtrabackup - Percona-Tools since it is optimized to be non-invasive

• mysqldump - since it's available everywhere

Nice additions

• pigz (Parallel Implementation of GZip) is so much faster than gzip on multicore systems and compatible to gzip

Deprecated

• mysqlhotcopy - a perl-script that can backup MyISAM and Archive-Tables

mysqldump

• SQL statement based
• Available anywhere.
• doesn't scale too well
• single-treaded and therefore slow as hell
• Take care of the order of the parameters. An option that was disabled may be enabled implicitly later.

Backup with mysqldump

Prepare a destination

   1 DEST="/var/backups/mysql"
   2 [ -d "$DEST" ] || \
   3         install -o root -g root -d -m 0750 "$DEST"

Create a backup of your data, where all tables are transactional InnoDB.

   1 mysqldump \
   2         --defaults-extra-file=/etc/mysql/debian.cnf \
   3         --opt --quote-names \
   4         --events --triggers \
   5         --flush-logs --single-transaction \
   6         --default-character-set=utf8mb4 \
   7         --log-error="$DEST/mysql_backup_$(date +%F).log" \
   8         --all-databases \
   9         |pigz -c > "$DEST/mysql_backup_$(date +%F).sql.gz"

Create a backup of your data, where non-transactional tables have to be backed up and need to be consistent to each other.

   1 mysqldump \
   2         --defaults-extra-file=/etc/mysql/debian.cnf \
   3         --opt --quote-names \
   4         --events --triggers \
   5         --flush-logs --lock-all-tables \
   6         --default-character-set=utf8mb4 \
   7         --log-error="$DEST/mysql_backup_$(date +%F).log" \
   8         --all-databases \
   9         |pigz -c > "$DEST/mysql_backup_$(date +%F).sql.gz"

Notes

• Enabled by default mysqldump --print-defaults

  • --opt equals to
    

    --add-drop-table --add-locks --create-options --disable-keys \
    --extended-insert --lock-tables --quick --set-charset

  • --triggers

  • --quick

  • --quote-names

  • --max_allowed_packet=16M

  • --default-character-set=utf8mb4

  • --routines

    • Include stored procedures and functions.
    • Alternatively routines may be restored

      with the database table mysql.proc, to keep their original timestamps.)

• The option --master-data=2 ensures that the backup contains the master binary log coordinates as a comment (=2).

  • Disable it if you don't have replication or binary logs or you'll get

    mysqldump: Error: Binlogging on server not active

• --routines is not necessary if mysql.proc is dumped and restored. This option does not preserve time of creation or creator of a routine.

Make sure the backup has been created successfully.

   1 zcat /var/backups/mysql_backup_2016-01-06.sql.gz |head
   2 -- MySQL dump 10.13  Distrib 5.5.46, for debian-linux-gnu (x86_64)
   3 --
   4 -- Host: localhost    Database: 
   5 -- ------------------------------------------------------
   6 -- Server version       5.5.46-0+deb8u1
   7 
   8 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   9 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  10 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  11 /*!40101 SET NAMES utf8 */;

Restore with mysqldump

Preparation

1. Make sure there is enough storage space.
   • Cleanup old data
2. You may want to disable foreign-key constraints to speed up the restore.

During the restore

1. Make sure binary logs do not consume all storage space, while taking care of you slaves

   1 # pigz -dc < /var/backups/mysql_backup_2016-01-06.sql.gz |mysql -p
   2 Enter password: 
   3 # mysql
   4 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
   5 # mysql -p
   6 Enter password: 
   7 Welcome to the MySQL monitor.  Commands end with ; or \g.
   8 Your MySQL connection id is 18
   9 Server version: 5.5.46-0+deb8u1 (Debian)
  10 
  11 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
  12 
  13 Oracle is a registered trademark of Oracle Corporation and/or its
  14 affiliates. Other names may be trademarks of their respective
  15 owners.
  16 
  17 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  18 
  19 mysql> flush privileges;
  20 Query OK, 0 rows affected (0.00 sec)
  21 
  22 mysql> Bye
  23 mysql -p
  24 Enter password: 
  25 ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  26 # mysql
  27 Welcome to the MySQL monitor.  Commands end with ; or \g.
  28 Your MySQL connection id is 20
  29 Server version: 5.5.46-0+deb8u1 (Debian)
  30 
  31 Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
  32 
  33 Oracle is a registered trademark of Oracle Corporation and/or its
  34 affiliates. Other names may be trademarks of their respective
  35 owners.
  36 
  37 Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  38 
  39 mysql> 

mydumper

• https://github.com/maxbube/mydumper

• SQL statement based
• multi-threaded and therefore fast

Backup with mydumper

Dump anything!

   1 DIR_BASE="/var/backups/mydumper"
   2 install -o root -g root -m 0750 -d "$DIR_BASE"
   3 cd "$DIR_BASE"
   4 DIR_OUT="$DIR_BASE/export_$(date --utc +%F_%T_%Z)"
   5 mydumper \
   6         --defaults-file "/etc/mysql/debian.cnf" \
   7         --outputdir "/var/backups/mydumper/$DIR_OUT" \
   8         --threads "$(($(nproc)/2))" \
   9         --triggers --events --routines --binlogs \
  10         --logfile "/var/backups/mydumper/$DIR_OUT/mydumper.log" \
  11         --use-savepoints --compress --tz-utc --verbose 2
  12 
  13 ### OR COPRESS MANUALLY AFTERWARDS
  14 for FILE in "$DIR_OUT"/*; do
  15         pigz "$FILE"
  16 done

If you are replicating you should backup the binlogs, too: --binlogs.

You'll find the master position in a file .metadata in the output directory.

If you are backing up a remote server you may want to add --compress-input to save some traffic in trade for some cpu-cycles.

Restore with myloader

   1 DIR_IN="export_foo_bar"
   2 myloader \
   3         --defaults-file "/etc/mysql/debian.cnf" \
   4         --directory "/var/backups/mydumper/$DIR_IN" \
   5         --threads "$(($(nproc)/2))" \
   6         --queries-per-transaction 1000 \
   7         --compress-protocol

Restore time depends much on your largest table.

Interesting options

• --overwrite-tables

• --source-db SOURCE_DB --database DESTINATION_DB

xtrabackup

• percona.com xtrabackup

• percona.com xtrabackup doc

• About Percona XtraBackup

• How Percona XtraBackup Works

• LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements

• Index of files created by Percona XtraBackup

Percona XtraBackup is an open-source hot backup utility for MySQL-based servers that doesn’t lock your database during the backup.

The ondisk format has significantly diverged between mysql/percona and mariadb since the dork. Since MariaDB 10.1 a own tool based on percona-xtrabackup 2.3 must be used to backup the MariaDB
'''mariadb-backup'''.

So i guess, options a are interchangeable between percona-xtrabackup and mariadb-backup. So just exchange xtrabackup with mariabackup.

Features

• binary backup
• multi-threaded and therefore fast
• Point-In-Time-Recovery (PITR)
• non-invasive hot

• incremental (based on log sequence number (LSN)) -> even faster

• read database config (linked against libmysql-client)

Install xtrabackup

#Configure Percona repository

apt install percona-xtrabackup-80 }}}

Backup-user needs privilege

• BACKUP_ADMIN for LOCK TABLES FOR BACKUP or LOCK INSTANCE FOR BACKUP to block all DDL operations

• PROCESS, SUPER to kill long queries

Check backup privileges

   1 xtrabackup --backup --check-privileges
   2 xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --tmpdir=/tmp 
   3 xtrabackup: recognized client arguments: --socket=/var/run/mysqld/mysqld.sock --backup=1 --check-privileges=1 
   4 xtrabackup version 8.0.12 based on MySQL server 8.0.20 Linux (x86_64) (revision id: 01cce6d)
   5 200604 22:26:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/run/mysqld/mysqld.sock' (using password: NO).
   6 200604 22:26:07  version_check Connected to MySQL server
   7 200604 22:26:07  version_check Executing a version check against the server...
   8 
   9 # A software update is available:
  10 200604 22:26:08  version_check Done.
  11 200604 22:26:08 Connecting to MySQL server host: localhost, user: not set, password: not set, port: not set, socket: /var/run/mysqld/mysqld.sock
  12 Error: Unsupported server version: '10.3.22-MariaDB-1'.
  13 This version of Percona XtraBackup can only perform backups and restores against MySQL 8.0 and Percona Server 8.0
  14 Please use Percona XtraBackup 2.4 for this database.

xtrabackup defaults

xtrabackup --print-defaults

   1 xtrabackup would have been started with the following arguments:
   2 --user=mysql \
   3 --pid-file=/run/mysqld/mysqld.pid \
   4 --socket=/run/mysqld/mysqld.sock \
   5 --basedir=/usr \
   6 --datadir=/var/lib/mysql \
   7 --tmpdir=/tmp \
   8 --lc-messages-dir=/usr/share/mysql \
   9 --bind-address=127.0.0.1 \
  10 --query_cache_size=16M \
  11 --log_error=/var/log/mysql/error.log \
  12 --expire_logs_days=10 \
  13 --character-set-server=utf8mb4 \
  14 --collation-server=utf8mb4_general_ci

Backup with xtrabackup

   1 install -o root -g backup -m 750 -d \
   2        /var/backups/xtrabackup/{full,incr,prepare}

Full backup

   1 BACKUP1="$(date +%F_%T)"
   2 OPTIONS="--compress --compress-threads=4 --parallel=4 --slave-info"
   3 xtrabackup --backup $OPTIONS \
   4         --target-dir=/var/backups/xtrabackup/full/"$BACKUP1" \
   5         2> /var/backups/xtrabackup/full/"$BACKUP1".log

Create a incremental backup based on full backup

   1 BACKUP2="$(date +%F_%T)"
   2 OPTIONS="--compress --compress-threads=4 --parallel=4 --slave-info"
   3 xtrabackup --backup $OPTIONS \
   4          --target-dir=/var/backups/xtrabackup/incr/"$BACKUP2" \
   5          --incremental-basedir=/var/backups/xtrabackup/full/"$BACKUP1" \
   6         2> /var/backups/xtrabackup/full/"$BACKUP2"/"$BACKUP2".log

Create a incremental backup based on first incremental

   1 BACKUP3="$(date +%F_%T)"
   2 OPTIONS="--compress --compress-threads=4 --parallel=4 --slave-info"
   3 xtrabackup --backup $OPTIONS \
   4          --target-dir=/var/backups/xtrabackup/incr/"$BACKUP3" \
   5          --incremental-basedir=/var/backups/xtrabackup/incr/"$BACKUP2" \
   6         2> /var/backups/xtrabackup/full/"$BACKUP3"/"$BACKUP3".log

Prepare restore with xtrabackup

Apply redo-log and delta files against (base-)backup.

Create a working copy of your backup to leave the "original" untouched.

   1 [ -d "/var/backups/xtrabackup/prepare" ] \
   2          && rm -r /var/backups/xtrabackup/prepare
   3 ## REFLINK IS USEFUL WITH COW FILESYSTEMS LIKE BTRFS
   4 cp --reflink=always /var/backups/xtrabackup/full/"$BACKUP1" \
   5          /var/backups/xtrabackup/prepare

With a base backup and a intermediate incremental backup you must skip the rollback of uncommitted transactions when preparing a backup --apply-log-only, because transactions that were uncommitted at the time of your backup may be in progress, and it is likely that they will be committed in the next incremental backup.

Prepare base full backup (without rollback)

   1 OPTIONS="--decompress --decompress-threads=4 --parallel --use-memory 1G"
   2 xtrabackup --prepare $OPTIONS --apply-log-only \
   3         --target-dir=/var/backups/xtrabackup/prepare

Prepare intermediate incremental backup (without rollback)

   1 OPTIONS="--decompress --decompress-threads=4 --parallel --use-memory 1G"
   2 xtrabackup --prepare $OPTIONS --apply-log-only \
   3         --target-dir=/var/backups/xtrabackup/prepare \
   4         --incremental-dir=/var/backups/xtrabackup/full/"$BACKUP1"

Prepare final incremental backup (with rollback)

   1 OPTIONS="--decompress --decompress-threads=4 --parallel --use-memory 1G"
   2 xtrabackup --prepare $OPTIONS \
   3         --target-dir=/var/backups/xtrabackup/prepare \
   4         --incremental-dir=/var/backups/xtrabackup/full/"$BACKUP2" 

Restore with xtrabackup

   1 OPTIONS="--parallel=4"
   2 xtrabackup --copy-back $OPTIONS \
   3         --target-dir=/var/backups/xtrabackup/prepare

   1 

mysqlhotcopy

• mariadb.com mysqlhotcopy

   1 

Storage engines

• The InnoDB Storage Engine is default since 5.1

• But there are several Alternative Storage Engines built in.

• Percona Blog: The Doom of Multiple Storage Engines

   1 mysql -e 'SHOW STORAGE ENGINES\G'
   2 *************************** 1. row ***************************
   3       Engine: FEDERATED
   4      Support: NO
   5      Comment: Federated MySQL storage engine
   6 Transactions: NULL
   7           XA: NULL
   8   Savepoints: NULL
   9 *************************** 2. row ***************************
  10       Engine: CSV
  11      Support: YES
  12      Comment: CSV storage engine
  13 Transactions: NO
  14           XA: NO
  15   Savepoints: NO
  16 *************************** 3. row ***************************
  17       Engine: MEMORY
  18      Support: YES
  19      Comment: Hash based, stored in memory, useful for temporary tables
  20 Transactions: NO
  21           XA: NO
  22   Savepoints: NO
  23 *************************** 4. row ***************************
  24       Engine: BLACKHOLE
  25      Support: YES
  26      Comment: /dev/null storage engine (anything you write to it disappears)
  27 Transactions: NO
  28           XA: NO
  29   Savepoints: NO
  30 *************************** 5. row ***************************
  31       Engine: MyISAM
  32      Support: YES
  33      Comment: MyISAM storage engine
  34 Transactions: NO
  35           XA: NO
  36   Savepoints: NO
  37 *************************** 6. row ***************************
  38       Engine: MRG_MYISAM
  39      Support: YES
  40      Comment: Collection of identical MyISAM tables
  41 Transactions: NO
  42           XA: NO
  43   Savepoints: NO
  44 *************************** 7. row ***************************
  45       Engine: ARCHIVE
  46      Support: YES
  47      Comment: Archive storage engine
  48 Transactions: NO
  49           XA: NO
  50   Savepoints: NO
  51 *************************** 8. row ***************************
  52       Engine: InnoDB
  53      Support: DEFAULT
  54      Comment: Supports transactions, row-level locking, and foreign keys
  55 Transactions: YES
  56           XA: YES
  57   Savepoints: YES
  58 *************************** 9. row ***************************
  59       Engine: PERFORMANCE_SCHEMA
  60      Support: YES
  61      Comment: Performance Schema
  62 Transactions: NO
  63           XA: NO
  64   Savepoints: NO

Password Hashing Algorithm

The functionPASSWORD() has been deprecated, but the hashes are still around. Here's a way to generate these hashes using mysql internal methods.

   1 mysql> select CONCAT('*',UPPER(SHA1(UNHEX(SHA1('test')))));
   2 +----------------------------------------------+
   3 | CONCAT('*',UPPER(SHA1(UNHEX(SHA1('test'))))) |
   4 +----------------------------------------------+
   5 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29    |
   6 +----------------------------------------------+
   7 1 row in set (0.01 sec)
   8 
   9 mysql> select PASSWORD('test');
  10 +-------------------------------------------+
  11 | PASSWORD('test')                          |
  12 +-------------------------------------------+
  13 | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
  14 +-------------------------------------------+
  15 1 row in set (0.00 sec)

As a hint there might by systems like Plesk, that store their passwords something like this. cat /etc/psa/.psa.shadow

   1 $AES-128-CBC$ssdfsdfsdfsdf==$kilwoihkjlnfvkilje==

But the string itself, the content if the file, is the password, No, it's not decrypted as the following command proves.

   1 mysql -u admin -p"$(cat /etc/psa/.psa.shadow)" \
   2         -e "SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1('$(cat /etc/psa/.psa.shadow)'))))) as 'authentication_string';" \
   3         -e "SELECT user, host, authentication_string FROM mysql.user WHERE user = 'admin';"

Debian specific Notes on MySQL 5.6

Systemd Changes

• Start with systemd broken since /usr/share/mysql/mysql-systemd-start is not configurable and so doesn't honor f.e. datadir.

• The sanity checks fail if eather

  /etc/mysql/my.cnf, /var/lib/mysql/ or /var/lib/mysql/mysql is missing. Nothing is logged to /var/log/mysql/error.log, instead you can find the following in /var/log/daemon.log.

     1 Jan 26 10:41:35 mail systemd[1]: Starting MySQL Community Server...
     2 Jan 26 10:41:35 mail mysql-systemd-start[19395]: MySQL system database not found. Please run mysql_install_db tool.
     3 Jan 26 10:41:35 mail systemd[1]: mysql.service: Control process exited, code=exited status=1
     4 Jan 26 10:41:35 mail systemd[1]: Failed to start MySQL Community Server.
     5 Jan 26 10:41:35 mail systemd[1]: mysql.service: Unit entered failed state.
     6 Jan 26 10:41:35 mail systemd[1]: mysql.service: Failed with result 'exit-code'.
     7 Jan 26 10:41:36 mail systemd[1]: mysql.service: Service hold-off time over, scheduling restart.
     8 Jan 26 10:41:36 mail systemd[1]: Stopped MySQL Community Server.
  

• As a workaround create a sym link in the specific place

     1 ln -s /var/lib/mysql/data/mysql /var/lib/mysql/mysql
  

Configuration Layout

mysql-server-5.6 introduces a new configuration layout

   1 root@mail /etc/mysql # ll
   2 insgesamt 16
   3 drwxr-xr-x 2 root root    6 Jan 26 10:27 conf.d/
   4 -rw------- 1 root root  333 Jul 27  2014 debian.cnf
   5 -rwxr-xr-x 1 root root 1193 Jan 15 15:15 debian-start*
   6 lrwxrwxrwx 1 root root   24 Jan 26 10:08 my.cnf -> /etc/alternatives/my.cnf
   7 -rw-r--r-- 1 root root  839 Dez 20 15:16 my.cnf.fallback
   8 -rw-r--r-- 1 root root  682 Dez 20 15:16 mysql.cnf
   9 drwxr-xr-x 2 root root  147 Jan 26 10:52 mysql.conf.d/

my.cnf is now a sym-link that can be administered using Debian-Alternatives

   1 root@mail /etc/mysql # update-alternatives --list my.cnf
   2 /etc/mysql/my.cnf.fallback
   3 /etc/mysql/mysql.cnf
   4 root@mail /etc/mysql # update-alternatives --config my.cnf
   5 Es gibt 2 Auswahlmöglichkeiten für die Alternative my.cnf (welche /etc/mysql/my.cnf bereitstellen).
   6 
   7   Auswahl      Pfad                        Priorität Status
   8 ------------------------------------------------------------
   9 * 0            /etc/mysql/mysql.cnf         200       automatischer Modus
  10   1            /etc/mysql/my.cnf.fallback   100       manueller Modus
  11   2            /etc/mysql/mysql.cnf         200       manueller Modus
  12 
  13 Drücken Sie die Eingabetaste, um die aktuelle Wahl[*] beizubehalten,
  14 oder geben Sie die Auswahlnummer ein: 0

The new configuration file mysql.cnf contains two include dirs, unfortunately in the wrong order.

   1 root@mail /etc/mysql # cat mysql.cnf
   2 #
   3 # The MySQL database server configuration file.
   4 #
   5 # You can copy this to one of:
   6 # - "/etc/mysql/my.cnf" to set global options,
   7 # - "~/.my.cnf" to set user-specific options.
   8 # 
   9 # One can use all long options that the program supports.
  10 # Run program with --help to get a list of available options and with
  11 # --print-defaults to see which it would actually understand and use.
  12 #
  13 # For explanations see
  14 # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
  15 
  16 #
  17 # * IMPORTANT: Additional settings that can override those from this file!
  18 #   The files must end with '.cnf', otherwise they'll be ignored.
  19 #
  20 
  21 !includedir /etc/mysql/conf.d/
  22 !includedir /etc/mysql/mysql.conf.d/

• /etc/mysql/conf.d is no longer able to override the default configuration file because it resides in /etc/mysql/mysql.conf.d, which is read later in the course.

• In /etc/mysql/mysql.conf.d an alphanumerical order is of importance. And the default configuration file is in the middle -> /etc/mysql/mysql.conf.d/mysql.cnf. We sopuld move it to the start to allow overrides but there will be conflicts during updates.

   1 mv /etc/mysql/mysql.conf.d/mysql.cnf \
   2         /etc/mysql/mysql.conf.d/00mysql.cnf

• New configuration layout apprears to be broken.

Profiling, Performance Tuning, Problem Tracing

mysqltuner.pl

Great tool to identify problems and bottlenecks !1!

Star it on Github
https://github.com/major/MySQLTuner-perl

It's already included in the distributions

   1 aptitude install mysqltuner

Or download it like this

root@mail /var/lib/mysql # wget -O mysqltuner.pl 'http://mysqltuner.pl'
--2016-01-09 00:37:17--  http://mysqltuner.pl/
Auflösen des Hostnamen »mysqltuner.pl (mysqltuner.pl)«... 217.70.184.38
Verbindungsaufbau zu mysqltuner.pl (mysqltuner.pl)|217.70.184.38|:80... verbunden.
HTTP-Anforderung gesendet, warte auf Antwort... 301 Moved Permanently
Platz: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl[folge]
--2016-01-09 00:37:18--  https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Auflösen des Hostnamen »raw.github.com (raw.github.com)«... 23.235.43.133
Verbindungsaufbau zu raw.github.com (raw.github.com)|23.235.43.133|:443... verbunden.
HTTP-Anforderung gesendet, warte auf Antwort... 301 Moved Permanently
Platz: https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl[folge]
--2016-01-09 00:37:18--  https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
Auflösen des Hostnamen »raw.githubusercontent.com (raw.githubusercontent.com)«... 23.235.43.133
Verbindungsaufbau zu raw.githubusercontent.com (raw.githubusercontent.com)|23.235.43.133|:443... verbunden.
HTTP-Anforderung gesendet, warte auf Antwort... 200 OK
Länge: 108419 (106K) [text/plain]
In »»mysqltuner.pl«« speichern.

mysqltuner.pl                              100%[==========================================================================================>] 105,88K  --.-KB/s   in 0,02s  

2016-01-09 00:37:19 (4,50 MB/s) - »»mysqltuner.pl«« gespeichert [108419/108419]

Invoke mysqltuner with perl as interpreter. You may force a colored output by adding thze option --color.

root@mail /var/lib/mysql # perl mysqltuner.pl
[OK] Logged in using credentials from debian maintenance account.
 >>  MySQLTuner 1.6.2 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0+deb8u1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 1008K (Tables: 33)
[!!] Total fragmented tables: 33

-------- Security Recommendations  -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] User 'roundcube@%' has user name as password.
[!!] User 'roundcube@localhost' has user name as password.
[!!] User 'roundcube@%' hasn't specific host restriction.
[!!] There is no basic password file list!

-------- CVE Security Recommendations  -------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 21m 53s (568 q [0.067 qps], 26 conn, TX: 22K, RX: 599K)
[--] Reads / Writes: 46% / 54%
[--] Binary logging is disabled
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum reached memory usage: 194.7M (10.47% of installed RAM)
[OK] Maximum possible memory usage: 597.8M (32.15% of installed RAM)
[OK] Slow queries: 0% (0/568)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 57.69%  (15/26)
[!!] Query cache efficiency: 0.0% (0 cached / 13 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 3 total)
[OK] Thread cache hit rate: 96% (1 created / 26 connections)
[OK] Table cache hit rate: 26% (25 open / 96 opened)
[OK] Open file limit used: 1% (16/1K)
[OK] Table locks acquired immediately: 100% (99 immediate / 99 locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
[OK] Read Key buffer hit rate: 100.0% (6K cached / 0 reads)
[OK] Write Key buffer hit rate: 98.2% (3K cached / 61 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/1008.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 5.05% (414 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 100.00% (6508 hits/ 6508 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 46 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)

Maximum output

   1 mysqltuner \
   2         --buffers --dbstat --idxstat --sysstat --pfstat --tbstat \
   3         --outputfile mysqltuner_$(date +%F).txt
   4 
   5 ### OR SIMPLY 
   6 mysqltuner -v --outputfile mysqltuner_$(date +%F).txt
   7 
   8 ### OR WRITE THE OUTPUT TO DIFFERENT FILES
   9 for PARAM in buffers dbstat idxstat sysstat pfstat tbstat; do
  10         mysqltuner "--$PARAM" --outputfile "mysqltuner_$(date +%F)_$PARAM.txt"
  11 done

Moderate/optimal output

• Table Column Metrics (--tbstat) are to much for me and will probably contain sensitive data

   1 mysqltuner \
   2         --buffers --dbstat --idxstat --sysstat --pfstat \
   3         --outputfile mysqltuner_$(date +%F).txt

sysbench

• https://github.com/akopytov/sysbench

   1 apt install sysbench

Compiled-in tests

   1 fileio - File I/O test
   2 cpu - CPU performance test
   3 memory - Memory functions speed test
   4 threads - Threads subsystem performance test
   5 mutex - Mutex performance test

Test defined via lua in /usr/share/sysbench

   1 bulk_insert.lua
   2 oltp_common.lua
   3 oltp_delete.lua
   4 oltp_insert.lua
   5 oltp_point_select.lua
   6 oltp_read_only.lua
   7 oltp_read_write.lua
   8 oltp_update_index.lua
   9 oltp_update_non_index.lua
  10 oltp_write_only.lua
  11 select_random_points.lua
  12 select_random_ranges.lua

Prepare setup

   1 PASSWORD="$(pwgen 16)"
   2 DIR_TEST="/usr/share/sysbench/"
   3 
   4 mysqladmin drop sbtest
   5 mysqladmin create sbtest
   6 mysql <<EOH
   7         CREATE USER 'sbtest@localhost' IDENTIFIED BY '$PASSWORD';"
   8         GRANT ALL PRIVILEGES ON sbtest.* to 'sbtest@localhost';
   9 EOH

Prepare MyISAM, TRX=no

   1 sysbench \
   2         --threads 1 --time 300 \
   3         --db-driver=mysql \
   4         --mysql-user=sbtest --mysql-password="$PASSWORD" \
   5         --mysql-table-engine=MyISAM \
   6         --mysql-engine-trx=no \
   7         --test="$DIR_TEST/oltp_read_write.lua" \
   8         prepare

Defaults:

• 10min
• 1 thread

Run MyISAM, TRX=no, THREADS=1,2,4

   1 for THREADS in  1 2 4; do
   2 sysbench \
   3         --threads "$THREADS" --time 300 \
   4         --db-driver=mysql \
   5         --mysql-user=sbtest --mysql-password="$PASSWORD" \
   6         --mysql-table-engine=MyISAM \
   7         --mysql-engine-trx=no \
   8         --test="$DIR_TEST/oltp_read_write.lua" \
   9         run
  10 done

percona-tools

Configure Percona repository

• Installing Percona XtraBackup on Red Hat Enterprise Linux and CentOS

• https://www.percona.com/doc/percona-xtrabackup/2.3/installation/apt_repo.html

• percona-release

   1 #CODENAME="$(lsb_release -sc)"
   2 CODENAME="buster"
   3 #VERSION="1.0-19"
   4 VERSION="latest"
   5 wget https://repo.percona.com/apt/percona-release_${VERSION}.${CODENAME}_all.deb
   6 dpkg -i percona-release_${VERSION}.${CODENAME}_all.deb
   7 apt update

lsb_release determines sid in my case … :-/

   1 percona-release disable all
   2 rm /etc/apt/sources.list.d/percona-*
   3 percona-release enable tools
   4 sed -i 's/ '"$(lsb_release -sc)"' / buster /' \
   5         /etc/apt/sources.list.d/percona-*
   6 apt update

Install percona-toolkit

Percona Toolkit is a collection of advanced command-line tools used by Percona (http://www.percona.com/) support staff to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually.

These tools are ideal alternatives to private or "one-off" scripts because they are professionally developed, formally tested, and fully documented. They are also fully self-contained, so installation is quick and easy and no libraries are installed.

Your enterprise DBAs oversee one of your most valuable assets: the database. They need to plan, design, and deploy new architectures to meet the needs of your applications. They can’t do that if they are busy with routine tasks.

• Percona Toolkit

• Percona Toolkit docs lastest

• Percona Toolkit docs lastest - the tools

   1 apt install percona-toolkit