Percona MySQL MGR集群

sudo yum install http://10.10.102.88:8090/percona-release-latest.noarch.rpm -y
sudo percona-release setup ps80
sudo dnf -y module disable mysql
sudo yum install -y percona-server-server percona-toolkit percona-xtrabackup-80 pmm2-client
sudo systemctl start mysql
sudo mysql_secure_installation

set sql_log_bin = 0;
create user 'dbreplica'@'%' identified by '8888888888';
grant replication slave on *.* to 'dbreplica'@'%';
flush privileges;
set sql_log_bin = 1;


mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.04 sec)

mysql> show master status;
+----------------+----------+--------------+------------------+------------------------------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------+----------+--------------+------------------+------------------------------------------+
| bin-log.000002 |      796 |              |                  | f35165a5-b163-11ef-8690-562ea4aeb918:1-3 |
+----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='dbreplica',SOURCE_PASSWORD='8888888888' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show master status;
+----------------+----------+--------------+------------------+------------------------------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------+----------+--------------+------------------+------------------------------------------+
| bin-log.000002 |      796 |              |                  | f35165a5-b163-11ef-8690-562ea4aeb918:1-3 |
+----------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

[client]
port = 3306
socket = /data/mysql/mysql.sock

[mysqld]
port = 3306
socket = /data/mysql/mysql.sock
secure_log_path = /var/log/mysql
pid-file        = /var/run/mysqld/mysqld.pid
datadir         = /data/mysql
log-error       = /var/log/mysql/mysql.log
server-id = 135

default-authentication-plugin = mysql_native_password
#authentication_policy = caching_sha2_password

back_log = 50
max_connections = 2048
wait_timeout = 256
max_connect_errors = 10
skip-name-resolve

table_open_cache = 2048
max_allowed_packet = 16M
max_heap_table_size = 512M

read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M

thread_cache_size = 8
thread_stack = 240K


ft_min_word_len = 4
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M

slow_query_log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_slow_admin_statements = ON
log_slow_replica_statements = ON
log_slow_rate_limit=100
log_slow_rate_type='query'
slow_query_log_always_write_time=1
log_slow_verbosity='full'
slow_query_log_use_global_control='all'


# INNODB options
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25

#innodb_log_file_size = 125M
#innodb_log_files_in_group = 2
innodb_redo_log_capacity= 250M
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256

innodb_file_per_table

default_time_zone = +00:00

performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-statements-digest=ON
innodb_monitor_enable=all


#skip-log-bin
log_bin = /data/mysql/bin-log
log_bin_index = /data/mysql/bin-log.index
read-only = 0
#binlog_format = STATEMENT
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
gtid_mode = on
enforce_gtid_consistency = on
relay-log=db03-relay-bin

#plugin_load="group_replication=group_replication.so"
loose-group_replication_group_name="aa07d676-b13d-11ef-bead-d2b98de6614b"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "192.168.11.135:3406"
loose-group_replication_group_seeds= "192.168.11.135:3406,192.168.11.136:3406,192.168.11.137:3406"
loose-group_replication_bootstrap_group=off
loose-group_replication_member_weight=50
group_replication_single_primary_mode=ON

[mysqldump]
quick
max_allowed_packet = 50M

[mysql]
no-auto-rehash

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

mysql> set global group_replication_bootstrap_group = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (1.34 sec)

mysql> set global group_replication_bootstrap_group = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members ;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 7424f73a-b163-11ef-8586-8ac1fb47fcf9 | db03   |        3306 | ONLINE       | PRIMARY     | 8.0.39         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
1 row in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> start group_replication;
Query OK, 0 rows affected (9.34 sec)

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
| group_replication_applier | 7424f73a-b163-11ef-8586-8ac1fb47fcf9 | db03   |        3306 | ONLINE       | PRIMARY     | 8.0.39         | XCom                       |
| group_replication_applier | e04e5933-b163-11ef-86a0-ceb3ee64bf3a | db04   |        3306 | ONLINE       | SECONDARY   | 8.0.39         | XCom                       |
| group_replication_applier | f35165a5-b163-11ef-8690-562ea4aeb918 | db05   |        3306 | ONLINE       | SECONDARY   | 8.0.39         | XCom                       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+----------------------------+
3 rows in set (0.00 sec)