GTID-Cluster mit MySQL 8.0

siehe auch:

• MySQL 8 Replication on Ubuntu 20.04
• Considerations Before Migrating to MySQL 8.0
• 2.10.1 Before You Begin
• 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

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                |
+--------------------+

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: 

> 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

> 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

> 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

> 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    |
+--------------------------+-------+

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

Quelle: 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