====== Percona MySQL 5.7 - Server mit GTID ======

  * [[https://www.unixe.de/wechsel-auf-mysql-5-7/|Wechsel auf mysql-5.7]]
  * [[https://www.percona.com/blog/2015/05/27/mysql-5-7-key-features/|MySQL 5.7 key features]] - MySQL 5.7 kann mehr als einen Master haben!!! Das kennt man sonst nur von Galera
  * [[http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html]]
  * [[http://dev.mysql.com/doc/refman/5.7/en/replication.html]]

  > aptitude -y purge percona-server-server-5.7
  > rm -fr /usr/share/mysql/ /var/lib/mysql/
  > aptitude install percona-server-server-5.7
  > mysql_upgrade
  Checking if update is needed.
  Checking server version.
  Running queries to upgrade MySQL server.
  Checking system database.
  mysql.columns_priv                                 OK
  mysql.db                                           OK
  mysql.engine_cost                                  OK
  mysql.event                                        OK
  mysql.func                                         OK
  mysql.general_log                                  OK
  mysql.gtid_executed                                OK
  mysql.help_category                                OK
  mysql.help_keyword                                 OK
  mysql.help_relation                                OK
  mysql.help_topic                                   OK
  mysql.innodb_index_stats                           OK
  mysql.innodb_table_stats                           OK
  mysql.ndb_binlog_index                             OK
  mysql.plugin                                       OK
  mysql.proc                                         OK
  mysql.procs_priv                                   OK
  mysql.proxies_priv                                 OK
  mysql.server_cost                                  OK
  mysql.servers                                      OK
  mysql.slave_master_info                            OK
  mysql.slave_relay_log_info                         OK
  mysql.slave_worker_info                            OK
  mysql.slow_log                                     OK
  mysql.tables_priv                                  OK
  mysql.time_zone                                    OK
  mysql.time_zone_leap_second                        OK
  mysql.time_zone_name                               OK
  mysql.time_zone_transition                         OK
  mysql.time_zone_transition_type                    OK
  mysql.user                                         OK
  The sys schema is already up to date (version 1.5.0).
  Checking databases.
  sys.sys_config                                     OK
  Upgrade process completed successfully.
  Checking if update is needed.
  
  > mysqld --print-defaults | tr -s ' ' '\n'

  > echo "SELECT host,user,authentication_string,password_expired,password_last_changed FROM user;" | mysql -t mysql
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+
  | host      | user      | authentication_string                     | password_expired | password_last_changed |
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+
  | localhost | root      | *EC575F88BA2EC345EAE597C24C33B0364D5DBA60 | N                | 2016-03-18 13:12:39   |
  | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2016-03-18 13:12:39   |
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+

Zugriffe auch aus dem Netzwerk erlauben:
  > echo "UPDATE user SET HOST='%' WHERE USER='root';"|mysql mysql
  > mysqladmin reload
  
  > echo "SELECT host,user,authentication_string,password_expired,password_last_changed FROM user;" | mysql -t mysql
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+
  | host      | user      | authentication_string                     | password_expired | password_last_changed |
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+
  | %         | root      | *EC575F88BA2EC345EAE597C24C33B0364D5DBA60 | N                | 2016-03-18 13:12:39   |
  | localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2016-03-18 13:12:39   |
  +-----------+-----------+-------------------------------------------+------------------+-----------------------+

Passwort ändern:
  > echo "UPDATE user SET authentication_string=PASSWORD('geheim') WHERE User='root';" | mysql mysql
  > mysqladmin reload

  > echo "SELECT * FROM users;" | mysql -t performance_schema
  +------+---------------------+-------------------+
  | USER | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS |
  +------+---------------------+-------------------+
  | NULL |                   1 |                 3 |
  | root |                   1 |                17 |
  +------+---------------------+-------------------+

  > echo "SELECT * FROM global_variables LIMIT 20;" | mysql -t performance_schema
  +-----------------------------------------+----------------+
  | VARIABLE_NAME                           | VARIABLE_VALUE |
  +-----------------------------------------+----------------+
  | auto_generate_certs                     | ON             |
  | auto_increment_increment                | 1              |
  | auto_increment_offset                   | 1              |
  | autocommit                              | ON             |
  | automatic_sp_privileges                 | ON             |
  | avoid_temporal_upgrade                  | OFF            |
  | back_log                                | 80             |
  | basedir                                 | /usr           |
  | big_tables                              | OFF            |
  | bind_address                            | *              |
  | binlog_cache_size                       | 32768          |
  | binlog_checksum                         | CRC32          |
  | binlog_direct_non_transactional_updates | OFF            |
  | binlog_error_action                     | ABORT_SERVER   |
  | binlog_format                           | ROW            |
  | binlog_group_commit_sync_delay          | 0              |
  | binlog_group_commit_sync_no_delay_count | 0              |
  | binlog_gtid_simple_recovery             | ON             |
  | binlog_max_flush_queue_time             | 0              |
  | binlog_order_commits                    | ON             |
  +-----------------------------------------+----------------+


==== 5.7-Replikation einrichten ====

  * [[https://www.percona.com/blog/2013/10/02/mysql-5-7-multi-source-replication/|MySQL 5.7 multi-source replication]]

die BIN-Logs in der DB verwalten:
<file>
[mysqld]
master_info_repository=TABLE;
relay_log_info_repository=TABLE;
</file>

Replikationsuser anlegen:
  root@dbhost01 # echo "CREATE USER otto IDENTIFIED BY 'geheim'" | mysql
  root@dbhost01 # echo "GRANT REPLICATION CLIENT,RELOAD,REPLICATION SLAVE ON *.* TO 'otto'@'%';" | mysql
  root@dbhost01 # mysqladmin reload
  root@dbhost01 # echo "SHOW GRANTS FOR otto" | mysql -t
  root@dbhost01 # echo "SELECT host,user,authentication_string,password_expired,password_last_changed FROM user;" | mysql -t mysql

Zugriff mit dem Replikationsuser testen:
  root@dbhost02 # mysqlshow -uotto -pgeheim -hdbhost01

Status ansehen:
  root@dbhost02 # echo "SELECT * FROM global_variables WHERE VARIABLE_NAME LIKE '%gtid%' OR VARIABLE_NAME='server_id';" | mysql -t performance_schema
  +----------------------------------+----------------+
  | VARIABLE_NAME                    | VARIABLE_VALUE |
  +----------------------------------+----------------+
  | binlog_gtid_simple_recovery      | ON             |
  | enforce_gtid_consistency         | ON             |
  | gtid_executed                    |                |
  | gtid_executed_compression_period | 1000           |
  | gtid_mode                        | ON             |
  | gtid_owned                       |                |
  | gtid_purged                      |                |
  | server_id                        | 9992           |
  | session_track_gtids              | OFF            |
  +----------------------------------+----------------+

  * [[http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html|CHANGE MASTER TO Syntax]]
    * [[https://www.percona.com/blog/2013/10/02/mysql-5-7-multi-source-replication/|multi-source replication]]

Replikation aktivieren:
  root@dbhost02 # echo "CHANGE MASTER TO MASTER_HOST='dbhost01', MASTER_PORT=3306, MASTER_USER='otto', MASTER_PASSWORD='geheim', MASTER_AUTO_POSITION=1, MASTER_SSL=0 FOR CHANNEL 'dbhost01';" | mysql
  root@dbhost02 # echo "STOP SLAVE;" | mysql
  root@dbhost02 # watch -n1 'uptime;df -h / ; echo "SHOW SLAVE STATUS FOR CHANNEL \"dbhost01\" \G; SHOW MASTER STATUS; SHOW SLAVE HOSTS;" | mysql -t'
  root@dbhost02 # echo "START SLAVE;" | mysql

  vi /etc/mysql/conf.d/cluster57.cnf
<file>
[mysqld]
server_id                       = 9991
report_host                     = dbhost01
bind-address                    = 0.0.0.0
slave_sql_verify_checksum       = 1
relay_log_info_repository       = TABLE
relay_log_recovery              = 1
master_info_repository          = TABLE
master_verify_checksum          = 1
master_info_repository          = TABLE
relay_log_info_repository       = TABLE
#slave_skip_errors               = 1032,1062
replicate_wild_ignore_table     = specialCharFilter.%

gtid_mode                       = ON
enforce_gtid_consistency
</file>

  vi /etc/mysql/conf.d/iqo_57.cnf
<file>
#==============================================================================#
[client]

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock
default_character_set           = utf8

#==============================================================================#
[mysql]
default_character_set           = utf8

#==============================================================================#
[mysqld_safe]
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

#==============================================================================#
[server]
sql-mode=""

#==============================================================================#
[mysqld]
log_bin                         = mysql-bin
relay_log                       = mysql-relay-bin
log_error                       = /var/log/mysql/error.log
log_warnings                    = 1
#log_warnings                    = 2
binlog_format                   = ROW
explicit_defaults_for_timestamp = 1
sync_binlog                     = 1
log_slave_updates               = 1
local_infile                    = 1
auto_increment_offset           = 1
auto_increment_increment        = 1
expire_logs_days                = 7
skip-name-resolve
#------------------------------------------------------------------------------#
### http://dev.mysql.com/doc/refman/5.1/de/blocked-host.html
max_connect_errors              = 1000
max_connections                 = 1000
connect_timeout                 = 10
interactive_timeout             = 60
wait_timeout                    = 60
#------------------------------------------------------------------------------#
character_set_server            = utf8
collation_server                = utf8_unicode_ci
#init_connect                    = 'SET NAMES utf8';
skip_character_set_client_handshake
#------------------------------------------------------------------------------#
# innodb_buffer_pool_size: max. 70-80% vom RAM (laut MySQL-Dokumentation)
# innodb_buffer_pool_size: opt. 35-40% vom RAM (laut MySQL-tuning-Werkzeug)
innodb_buffer_pool_size         = 1800M
innodb_file_per_table           = 1
innodb_autoinc_lock_mode        = 2
innodb_log_buffer_size          = 32M
innodb_thread_concurrency       = 8
innodb_flush_method             = O_DIRECT
innodb_flush_log_at_trx_commit  = 2
innodb_log_file_size            = 1G
#------------------------------------------------------------------------------#
</file>


==== Replikation wieder herstellen ====

das Vorgehen, zum wieder herstellen einer Replikation, hat sich seit der Version 5.6 nicht geändert


=== einen DB-Knoten mit Hilfe einer Binärkopie wieder herstellen ===

auf dem defekten Knoten muss die defekte DB gelöscht werden:
  > ssh mysqldefekt
  > service mysql stop
  > rm -fr $(du -sh /var/lib/mysql/* | awk '{print $2}' | fgrep -v mysql-bin. | fgrep -v .pem | fgrep -v mysql-relay-bin. | fgrep -v auto.cnf)

auf dem funktionierenden Knoten muss die Kopie zusammen gepackt werden:
  > ssh mysqlheil
  > service mysql stop
  > tar czf /tmp/mysql_data.tgz $(du -sh /var/lib/mysql/* | awk '{print $2}' | fgrep -v mysql-bin. | fgrep -v .pem | fgrep -v mysql-relay-bin. | fgrep -v auto.cnf)
  > service mysql start
  > tar tzf /tmp/mysql_data.tgz

nach dem rüberkopieren des Archives, die Binärkopie auf dem defekten Knoten auspacken:
  > ssh mysqldefekt
  > tar xzf /tmp/mysql_data.tgz -C /
  > service mysql start
  > mysqlshow


===== Statistische Daten auslesen =====

==== Statistische Daten über die Nutzung des Puffers ====

<file bash>
#!/bin/bash

#------------------------------------------------------------------------------#
# Zeigt statistische Informationen über die Nutzung des Puffers der Datenbank
#------------------------------------------------------------------------------#

VERSION="v2017090502"


PORT="${1}"


if [ -z "${PORT}" ] ; then
        PORT="3306"
fi

echo "SELECT * FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL;" | mysql -t -S /var/run/mysqld/mysqld_${PORT}.sock


################################################################################

#for PATTERN in read writ wait dirty
#do
#       echo "SELECT * FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL AND NAME LIKE '%${PATTERN}%';" | mysql -t -S /var/run/mysqld/mysqld_${PORT}.sock
#done


################################################################################

LESEAUFRUFFE="$(echo "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL AND NAME='buffer_pool_read_requests';" | mysql -N -S /var/run/mysqld/mysqld_${PORT}.sock)"
SCHREIBAUFRUFFE="$(echo "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL AND NAME='buffer_pool_write_requests';" | mysql -N -S /var/run/mysqld/mysqld_${PORT}.sock)"
if [ "${LESEAUFRUFFE}" -gt "${SCHREIBAUFRUFFE}" ] ; then
        LESE_ZU_SCHREIBAUFRUFFE="$(echo "${LESEAUFRUFFE} ${SCHREIBAUFRUFFE}" | awk '{print $1 / $2,"/ 1"}')"
else
        LESE_ZU_SCHREIBAUFRUFFE="$(echo "${LESEAUFRUFFE} ${SCHREIBAUFRUFFE}" | awk '{print "1 /",$2 / $1}')"
fi

echo "AUFRUFE aus dem PUFFER  -  gelesen / geschrieben  =  gelesen / geschrieben:  ${LESEAUFRUFFE} / ${SCHREIBAUFRUFFE}  =  ${LESE_ZU_SCHREIBAUFRUFFE}"


################################################################################

GELESENE_DATENMENGE="$(echo "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL AND NAME='buffer_data_reads';" | mysql -N -S /var/run/mysqld/mysqld_${PORT}.sock)"
GESCHRIEBENE_DATANMENGE="$(echo "SELECT COUNT FROM information_schema.INNODB_METRICS WHERE TIME_ENABLED IS NOT NULL AND NAME='buffer_data_written';" | mysql -N -S /var/run/mysqld/mysqld_${PORT}.sock)"
if [ "${GELESENE_DATENMENGE}" -gt "${GESCHRIEBENE_DATANMENGE}" ] ; then
        GELESENE_ZU_GESCHRIEBENE_DATENMENGE="$(echo "${GELESENE_DATENMENGE} ${GESCHRIEBENE_DATANMENGE}" | awk '{print $1 / $2,"/ 1"}')"
else
        GELESENE_ZU_GESCHRIEBENE_DATENMENGE="$(echo "${GELESENE_DATENMENGE} ${GESCHRIEBENE_DATANMENGE}" | awk '{print "1 /",$2 / $1}')"
fi

echo "Datenmenge im PUFFER  -  gelesen / geschrieben  =  gelesen / geschrieben:  ${GELESENE_DATENMENGE} / ${GESCHRIEBENE_DATANMENGE}  =  ${GELESENE_ZU_GESCHRIEBENE_DATENMENGE}"
</file>