percona_mysql_5.7_-_server_mit_gtid
Inhaltsverzeichnis
Percona MySQL 5.7 - Server mit GTID
- MySQL 5.7 key features - MySQL 5.7 kann mehr als einen Master haben!!! Das kennt man sonst nur von Galera
> 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
die BIN-Logs in der DB verwalten:
[mysqld] master_info_repository=TABLE; relay_log_info_repository=TABLE;
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 | +----------------------------------+----------------+
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
[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
vi /etc/mysql/conf.d/iqo_57.cnf
#==============================================================================# [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 #------------------------------------------------------------------------------#
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
#!/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}"
/home/http/wiki/data/pages/percona_mysql_5.7_-_server_mit_gtid.txt · Zuletzt geändert: von manfred