Wissen

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: Start » gtid-cluster_mit_mysql_8.0
Zuletzt angesehen:
gtid-cluster_mit_mysql_8.0

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen RevisionVorhergehende Überarbeitung
Nächste Überarbeitung		Vorhergehende Überarbeitung
gtid-cluster_mit_mysql_8.0 [2023-05-02 13:20:30] – manfredgtid-cluster_mit_mysql_8.0 [2023-05-05 13:08:53] (aktuell) – [Tuning] manfred
Zeile 1: Zeile 1:
 +====== GTID-Cluster mit MySQL 8.0 ======
 +
 +siehe auch:
 +  * **[[https://youtu.be/RkbIqbXCWqI|MySQL 8 Replication on Ubuntu 20.04]]**
 +  * [[https://severalnines.com/blog/moving-mysql-57-mysql-80-what-you-should-know/|Considerations Before Migrating to MySQL 8.0]]
 +  * [[https://dev.mysql.com/doc/refman/8.0/en/upgrade-before-you-begin.html|2.10.1 Before You Begin]]
 +  * [[https://dev.mysql.com/blog-archive/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/|INPLACE upgrade from MySQL 5.7 to MySQL 8.0]]
 +
 +  * [[https://dev.mysql.com/doc/refman/8.0/en/replication-configuration.html]]
 +  * **[[https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-howto.html]]**
 +  * **[[https://dev.mysql.com/doc/refman/8.0/en/reset-replica.html]]**
 +  * [[https://dev.mysql.com/doc/refman/8.0/en/replication-administration-skip.html]]
 +  * [[https://dev.mysql.com/doc/mysql-replication-excerpt/8.0/en/replication-administration-skip-gtid.html]]
 +
 +  > lsb_release -a
 +  No LSB modules are available.
 +  Distributor ID: Ubuntu
 +  Description: Ubuntu 22.04.2 LTS
 +  Release: 22.04
 +  Codename: jammy
 +
 +
 +===== MySQL-Installation =====
 +
 +auf allen drei Knoten wird als erstes Oracle-MySQL-8.0 installiert //(es kann auch ein Percona-MySQL-8.0 sein, die folgenden Schritte sind "prinzipiell" gleich)//
 +  > apt search mysql | grep -E '^mysql'
 +  ...
 +  mysql-server/jammy-updates,jammy-security 8.0.32-0ubuntu0.22.04.2 all
 +  mysql-server-8.0/jammy-updates,jammy-security 8.0.32-0ubuntu0.22.04.2 amd64
 +  mysql-server-core-8.0/jammy-updates,jammy-security 8.0.32-0ubuntu0.22.04.2 amd64
 +  mysql-source-8.0/jammy-updates,jammy-security 8.0.32-0ubuntu0.22.04.2 amd64
 +  ...
 +
 +  > apt install mysql-server
 +  
 +  > dpkg -l mysql-server
 +  ...
 +  +++-==============-=======================-============-===================================================================
 +  ii  mysql-server   8.0.32-0ubuntu0.22.04.2 all          MySQL database server (metapackage depending on the latest version)
 +  
 +  > mysqlshow 
 +  +--------------------+
 +  |     Databases      |
 +  +--------------------+
 +  | information_schema |
 +  | mysql              |
 +  | performance_schema |
 +  | sys                |
 +  +--------------------+
 +
 +
 +===== GTID-Replikation aktivieren =====
 +
 +da es eine frische Installation ist, gibt es noch keine GTID und die GTID-Unterstützung ist noch nicht aktiviert worden
 +  > echo "SHOW GLOBAL VARIABLES LIKE 'GTID_EXECUTED';" | mysql -t
 +  +---------------+-------+
 +  | Variable_name | Value |
 +  +---------------+-------+
 +  | gtid_executed |       |
 +  +---------------+-------+
 +  
 +  > echo "SHOW VARIABLES LIKE 'enforce_gtid_consistency';" | mysql -t
 +  +--------------------------+-------+
 +  | Variable_name            | Value |
 +  +--------------------------+-------+
 +  | enforce_gtid_consistency | OFF   |
 +  +--------------------------+-------+
 +
 +  > echo "bind-address                    = 0.0.0.0" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  > echo "server_id                       = 1" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  > echo "log_bin                         = mysql-bin.log" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  > echo "binlog_expire_logs_seconds      = 604800" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  
 +  > echo "[mysqld]" > /etc/mysql/conf.d/cluster.cnf
 +  > echo "gtid_mode                       = ON" >> /etc/mysql/conf.d/cluster.cnf
 +  > echo "enforce_gtid_consistency        = ON" >> /etc/mysql/conf.d/cluster.cnf
 +  > echo "#ignore-db-dir                   = lost+found" >> /etc/mysql/conf.d/cluster.cnf
 +  > chown 0 /var/lib/mysql/lost+found
 +  
 +  > service mysql restart
 +  
 +  > echo "SHOW VARIABLES LIKE 'server_id';" | mysql -t
 +  +---------------+-------+
 +  | Variable_name | Value |
 +  +---------------+-------+
 +  | server_id     | 1     |
 +  +---------------+-------+
 +  
 +  > echo "SHOW VARIABLES LIKE 'gtid_mode';" | mysql -t
 +  +---------------+-------+
 +  | Variable_name | Value |
 +  +---------------+-------+
 +  | gtid_mode     | ON    |
 +  +---------------+-------+
 +
 +  ...
 +  > echo "server_id               = 2" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  > service mysql restart
 +  > echo "SHOW VARIABLES LIKE 'server_id';" | mysql -t
 +  +---------------+-------+
 +  | Variable_name | Value |
 +  +---------------+-------+
 +  | server_id     | 2     |
 +  +---------------+-------+
 +
 +  ...
 +  > echo "server_id               = 3" >> /etc/mysql/mysql.conf.d/mysqld.cnf
 +  > service mysql restart
 +  > echo "SHOW VARIABLES LIKE 'server_id';" | mysql -t
 +  +---------------+-------+
 +  | Variable_name | Value |
 +  +---------------+-------+
 +  | server_id     | 3     |
 +  +---------------+-------+
 +
 +  > echo "SHOW MASTER STATUS \G;" | mysql -t
 +  *************************** 1. row ***************************
 +               File: mysql-bin.000001
 +           Position: 157
 +       Binlog_Do_DB: 
 +   Binlog_Ignore_DB: 
 +  Executed_Gtid_Set: 
 +
 +  > echo "CREATE USER otto IDENTIFIED BY 'FOSp85bLOpzH'; GRANT REPLICATION SLAVE ON *.* TO 'otto'@'%'; FLUSH PRIVILEGES;" | mysql
 +  
 +  > echo "SHOW MASTER STATUS \G;" | mysql -t
 +  *************************** 1. row ***************************
 +               File: mysql-bin.000001
 +           Position: 857
 +       Binlog_Do_DB: 
 +   Binlog_Ignore_DB: 
 +  Executed_Gtid_Set: 
 +
 +
 +===== GTID-Replikation verbinden =====
 +
 +
 +==== Knoten 1 ====
 +
 +  > ip a s ens3
 +  2: ens3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 8950 qdisc fq_codel state UP group default qlen 1000
 +      link/ether fa:16:3e:dd:66:26 brd ff:ff:ff:ff:ff:ff
 +      altname enp0s3
 +      inet 10.10.20.25/24 metric 100 brd 10.10.20.255 scope global dynamic ens3
 +         valid_lft 78642sec preferred_lft 78642sec
 +      inet6 fe80::f816:3eff:fedd:6626/64 scope link 
 +         valid_lft forever preferred_lft forever
 +
 +  > echo "STOP REPLICA;" | mysql
 +  > echo "RESET REPLICA ALL;" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.31', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_2';" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.50', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_3';" | mysql
 +  > echo "START REPLICA;" | mysql
 +  > echo "SHOW REPLICA STATUS \G; SHOW SLAVE HOSTS;" | mysql -t
 +
 +
 +==== Knoten 2 ====
 +
 +  > ip a s ens3
 +  2: ens3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 8950 qdisc fq_codel state UP group default qlen 1000
 +      link/ether fa:16:3e:fa:08:cb brd ff:ff:ff:ff:ff:ff
 +      altname enp0s3
 +      inet 10.10.20.31/24 metric 100 brd 10.10.20.255 scope global dynamic ens3
 +         valid_lft 78646sec preferred_lft 78646sec
 +      inet6 fe80::f816:3eff:fefa:8cb/64 scope link 
 +         valid_lft forever preferred_lft forever
 +
 +  > echo "STOP REPLICA;" | mysql
 +  > echo "RESET REPLICA ALL;" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.25', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_1';" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.50', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_3';" | mysql
 +  > echo "START REPLICA;" | mysql
 +  > echo "SHOW REPLICA STATUS \G; SHOW SLAVE HOSTS;" | mysql -t
 +
 +
 +==== Knoten 3 ====
 +
 +  > ip a s ens3
 +  2: ens3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 8950 qdisc fq_codel state UP group default qlen 1000
 +      link/ether fa:16:3e:95:0c:87 brd ff:ff:ff:ff:ff:ff
 +      altname enp0s3
 +      inet 10.10.20.50/24 metric 100 brd 10.10.20.255 scope global dynamic ens3
 +         valid_lft 78654sec preferred_lft 78654sec
 +      inet6 fe80::f816:3eff:fe95:c87/64 scope link 
 +         valid_lft forever preferred_lft forever
 +
 +  > echo "STOP REPLICA;" | mysql
 +  > echo "RESET REPLICA ALL;" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.25', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_1';" | mysql
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.10.20.31', SOURCE_PORT=3306, SOURCE_USER='otto', SOURCE_PASSWORD='FOSp85bLOpzH', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1 FOR CHANNEL 'Knoten_2';" | mysql
 +  > echo "START REPLICA;" | mysql
 +  > echo "SHOW REPLICA STATUS \G; SHOW SLAVE HOSTS;" | mysql -t
 +
 +
 +==== sonstiges ====
 +
 +  > echo "CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='source_log_name', SOURCE_LOG_POS=source_log_pos;" | mysql
 +
 +  > echo "SHOW VARIABLES LIKE 'enforce_gtid_consistency';" | mysql -t
 +  +--------------------------+-------+
 +  | Variable_name            | Value |
 +  +--------------------------+-------+
 +  | enforce_gtid_consistency | ON    |
 +  +--------------------------+-------+
 +
 +
 +===== Kontrolle =====
 +
 +Konoten 1
 +  > echo "SHOW REPLICA STATUS \G;" | mysql -t | grep -E 'Replica_..*_Running:|Source_[HP]|Seconds_Behind_Source:'
 +                    Source_Host: 10.10.20.31
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +                    Source_Host: 10.10.20.50
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +
 +
 +Konoten 2
 +  > echo "SHOW REPLICA STATUS \G;" | mysql -t | grep -E 'Replica_..*_Running:|Source_[HP]|Seconds_Behind_Source:'
 +                    Source_Host: 10.10.20.25
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +                    Source_Host: 10.10.20.50
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +
 +Konoten 3
 +  > echo "SHOW REPLICA STATUS \G;" | mysql -t | grep -E 'Replica_..*_Running:|Source_[HP]|Seconds_Behind_Source:'
 +                    Source_Host: 10.10.20.25
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +                    Source_Host: 10.10.20.31
 +                    Source_Port: 3306
 +             Replica_IO_Running: Yes
 +            Replica_SQL_Running: Yes
 +          Seconds_Behind_Source: 0
 +
 +
 +===== Tuning =====
 +
 +Quelle: [[https://stackify.com/performance-tuning-in-mysql/|Automatic Performance Improvement]]
 +
 +Unsere Empfehlung für die drei wichtigsten Tools zur automatischen Leistungsverbesserung sind **MySQLTuner**, tuning-primer und PHPMyAdmin Adviser. __Das erste Tool, MySQLTuner, ist am praktischsten mit modernen MySQL-Datenbanken. Es findet optimierungsfähige Abschnitte, empfiehlt Anpassungen und **ist mit MySQL 8.x kompatibel**__.  Die anderen beiden führen ähnliche Funktionen aus, obwohl tuning-primer älter ist und für MySQL 5.5 bis 5.7 erstellt wurde.
 +
 +  > apt install mysqltuner
 +  
 +  > mysqltuner --help
 +  > mysqltuner --user <username> --pass <password> --host <hostname> --port <port> --defaults-file /etc/mysql/my.cnf
 +  > mysqltuner --user <username> --pass <password>
 +  > mysqltuner --defaults-file /etc/mysql/my.cnf
 +  > mysqltuner --user <username> --pass <password> --defaults-file /etc/mysql/my.cnf
 +  
 +  > mysqltuner | tee mysqltuner_1.log
 +