====== 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: 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: 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: 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 --pass --host --port --defaults-file /etc/mysql/my.cnf > mysqltuner --user --pass > mysqltuner --defaults-file /etc/mysql/my.cnf > mysqltuner --user --pass --defaults-file /etc/mysql/my.cnf > mysqltuner | tee mysqltuner_1.log